unixtimeでの日付取得について - PHPプロ！Q&A掲示板

2450

0P

unixtimeでの日付取得について

質問日時 / 2010年1月12日 08:09 回答数 / 10件

Questioner: otakopu Tweet

PHP×MYSQL 勉強中の初心者です。どうぞよろしくお願いします。
現在MYSQLに、日付をunixtimeにてデータを以下のような形で保存しています。

■tableA
id , date , price
1 , 1263222000 , 500
1 , 1263049200 , 350
1 , 1262876400 , 400
1 , 1262790000 , 350
2 , 1263222000 , 100
3 , 1263222000 , 300

（参考）
1263222000 = 2010-1-12
1263049200 = 2010-1-10
1262876400 = 2010-1-8
1262790000 = 2010-1-7

■tableB
id , name
1 , りんご
2 , みかん
3 , バナナ

このテーブルを結合し、

SELECT B.*, A.* FROM
tableA A, tableB B
WHERE
A.id = B.id
AND date = (select max(date) from `tableA`)
AND A.price = '350'

とすると、

id , name , date , price
1 , りんご , 1263049200 , 350

が呼び出されますが、
この2010-1-10のひとつ前の日付データも一緒に呼び出したい場合どうしたらいいでしょうか？

理想としてはPHPで表示する際に以下のような形にしたいのですが、こういったことが可能でしょうか？

id , name , date , price , 前営業日売上
1 , りんご , 1263049200 , 350 , 400

SQL文を複数使っても構いません。

[仕様]
MYSQL 5.1.3
PHP 5.2.7

この質問への意見の募集は締め切られ、ポイントは既に配分されました。
意見を投稿することはできますが、ポイントを受け取ることはできません。

この質問に返信

ツリー一覧

unixtimeでの日付取得についてotakopu

A01shimix確認ですが・・・＃手元にチェック出来る環境がな

A01-1otakopushimix様　返信有難うございます。 >>キチンと取得

A01-1-1shimixえっと・・。再度仕様確認です（汗最終営業日、前

A01-1-1-1otakopuお世話になっております。 >>最終営業日、前営業日

A01-1-1-1-1shimix＃あぁ・・。（A.price = '350'）の行は全部出力する

A01-1-1-1-1-1otakopushimix様、お世話になります。解りやすくコードを

A01-1-1-1-1-1-1otakopushimix様、お世話になります。提示していただいた

A02Kanonbellちょっとテーブル構成を変えて。 SALESテーブル S

A02-1otakopuKanonbell様、返信有難うございます。試してみた

A02-1-1Kanonbell>試してみたのですが、呼び出せずタイムアウトしてし

回答一覧

A01
answerershimix [1月12日 09:03]

確認ですが・・・

＃手元にチェック出来る環境がないので、ハズしてる可能性大ですが（汗

【確認事項１】
サブクエリ「select max(date) from `tableA`」は2010-1-12を返すんじゃないですかね。そうなると

（date = 2010-1-12）AND（A.price = '350'）

が成り立つレコードは存在しない気がするのですが、キチンと取得出来ていますか？

【確認事項２】
また、もしこれで（A.price = '350'）の中でdateが最大のレコードを取得できているとしたら、別のidで同じ（A.price = '350'）のレコードがあった場合（dateの最大も同じ）には、id別に複数のレコードが返ると思いますが、（仕様として）各々について「日付がひとつ前のレコード」が必要なんでしょうか？

【以下雑感】
id昇順・日付降順で読んで、あとはプログラム側で処理した方がわかりやすい気もしますがどうでしょうかねぇ・・。

A01-1
replyerotakopu [1月12日 09:52]

shimix様　返信有難うございます。

>>キチンと取得出来ていますか？

出かける前に、前夜にやったことを思い出しながら書いたので混同してしまったようです。申し訳ないです。
帰宅したら確認します。

>>id別に複数のレコードが返ると思いますが、（仕様として）各々について「日付がひとつ前のレコード」が必要なんでしょうか？

そうですね。一応それを理想としています。

>>プログラム側で処理した方がわかりやすい気もしますが

その手法を調べたりして考えていたのですが、解らずで困ってしまっています。
何か方法はありますでしょうか？

A01-1-1
replyershimix [1月12日 11:45]

えっと・・。再度仕様確認です（汗

最終営業日、前営業日は「全データを通して」でしょうか？それとも「品目ごと」ですか？

全データを通してであれば、あらかじめ最終営業日と前営業日を取得することになります。ただし最終営業日・前営業日のどちらか（場合によっては両方）にデータがない品目をどう扱うのかを決めなければいけません。

品目ごとであればとりあえず全データを読む（不要なデータは読み飛ばす）方が手っ取り早くなりそうです。その場合、品目ごとにどのくらいのデータ件数になるのか次第なので、ざっとした目安（1年分くらいとか）はわかりますか？

A01-1-1-1
replyerotakopu [1月12日 14:40]

お世話になっております。

>>最終営業日、前営業日は「全データを通して」でしょうか？それとも「品目ごと」ですか？

品目（id）ごとになります。

>>品目ごとにどのくらいのデータ件数になるのか？

品目が400個くらいありまして、データは1年分くらいあります。全部で10万レコードくらいです。

最終営業日というくくりをつけてしまったので、ややこしくなっていますよね。
例えば、以下のようにすると、

SELECT B.*, A.* FROM
tableA A, tableB B
WHERE
A.id = B.id
AND A.price = '350'

以下のように表示できると思いますが、

id , name , date , price
1 , りんご , 2010-01-12 , 350
1 , りんご , 2010-01-07 , 350
2 , みかん , 2010-01-12 , 350
2 , みかん , 2009-05-05 , 350

それを以下のようにしたいです。

id , name , date , price , 前営業日売上
1 , りんご , 2010-01-12 , 350 , 400（1/11や1/10のりんごの売上）
1 , りんご , 2010-01-07 , 350 , 200（1/6や1/5のりんごの売上）
2 , みかん , 2010-01-10 , 350 , 500
2 , みかん , 2009-05-05 , 350 , 220

A01-1-1-1-1
replyershimix [1月12日 15:23]

＃あぁ・・。（A.price = '350'）の行は全部出力するんですね（汗

全データ読まないとダメじゃないですかねぇ。対象データとその次のデータが必要になるので、出力タイミングを遅らせればいいように思います。

まったくの未検証ですが、こんな感じですかね。

$save_id = '';
$save_name = '';
$save_date = '';
$save_price = '';
$result = mysql_query('SELECT B.*, A.* FROM tableA A, tableB B WHERE A.id = B.id order by A.id, date desc'); // id昇順、日付降順
while ($row = mysql_fetch_assoc($result)) {
if ($save_id <> '') { // ひとつ前が対象データだったらセーブした内容と今のデータのpriceを出力
print $save_id;
print $save_name;
print $save_date;
print $save_price;
if ($save_id == $row['A.id']) {
print $row['price']; // 同一idだったら前営業日のpriceを出力
}
else {
print " "; // 別idだったら前営業日なし
}
print "<br />";
$save_id = '';
$save_name = '';
$save_date = '';
$save_price = '';
}
if ($row['price'] <> 350) { continue; } // 対象データ以外はskip
$save_id = $row['A.id']; // 対象データだったらセーブ
$save_name = $row['name'];
$save_date = $row['date'];
$save_price = $row['price'];
}
if ($save_id <> '') { // ひとつ前（最後のデータ）が対象データだったらセーブした内容を出力
print $save_id;
print $save_name;
print $save_date;
print $save_price;
print " "; // 前営業日なし
print "<br />";
}

A01-1-1-1-1-1
replyerotakopu [1月12日 16:11]

shimix様、お世話になります。

解りやすくコードを提示して頂いて有難うございます。
帰宅したらやってみようと思います。
実データにあてはめるのに時間がかかりそうなので、返信は明日になると思います。

A01-1-1-1-1-1-1
replyerotakopu [1月13日 05:20]

shimix様、お世話になります。

提示していただいたプログラムを実行したのですが、以下のように同じ350のデータが1つのid分だけ表示され、その同じデータが2つ表示されました。
前日分も表示されないようです。

id , name , date , price
1 , りんご , 2010-01-12 , 350
1 , りんご , 2010-01-12 , 350
(実際はunixtime)

A02
answererKanonbell [1月12日 21:40] (最終編集：1月12日 22:01)

ちょっとテーブル構成を変えて。

SALESテーブル
SALES_ID　ITEM_ID DATE 　　　PRICE
1　　　　　1 　　　1263222000 500
2　　　　　1 　　　1263049200 350
3　　　　　1 　　　1262876400 400
4　　　　　1 　　　1262790000 350
5　　　　　2 　　　1263222000 100
6　　　　　3 　　　1263222000 300

ITEMテーブル
ITEM_ID　ITEM_NAME
1　　　　　りんご
2　　　　　みかん
3　　　　　バナナ

・TABLE A→SALES
・SALESテーブルにサロゲートキーSALES_IDを追加
・TABLE B→ITEM
・ITEMテーブルのID→ITEM_ID

こんなSQLでできないかな？
会社のOracleで試したのを記憶を頼りに書いてるんでどっか間違いあるかもしれませんが。

select
ITEM.ITEM_ID, ITEM_NAME, DATE,
PRESENT_PRICE, PREVIOUS_PRICE
from ITEM
left outer join (
select
s1.ITEM_ID, s1.DATE,
s1.PRICE as PRESENT_PRICE,
s2.PRICE as PREVIOUS_PRICE
from SALES S1
left outer join SALES s2
on s1.ITEM_ID = s2.ITEM_ID
and s1.DATE > s2.DATE
and s2.DATE > all (
select DATE
from SALES s3
where s2.ITEM_ID = s3.ITEM_ID
and s2.SALES_ID <> s3.SALES_ID
and s1.DATE > s3.DATE
)
) SUMMARY
on ITEM.ITEM_ID = SUMMARY.ITEM_ID
where PRESENT_PRICE = 350

TABLE Aは売り上げの記録のテーブルのようだし、日付相当のデータは主キーとしては不十分なことが多いしで、別にIDを追加しちゃいましたけれど。
MySQLはUnixTimestampを日付データ型に変換したりその逆も可能なようだけど、できればDatetimeあたりの日付型で持っておいた方がより扱いやすいかもと思わんでもないです。場合によるけど。

[追記]
って、考えてみたら既存テーブルだから今更サロゲートキー追加は厳しいかな？
ITEM_IDとDATEの複合主キーのように見えるので、下記SQLでもいけるかもです。動作確認してないけど。

select
ITEM.ITEM_ID, ITEM_NAME, DATE,
PRESENT_PRICE, PREVIOUS_PRICE
from ITEM
left outer join (
select
s1.ITEM_ID, s1.DATE,
s1.PRICE as PRESENT_PRICE,
s2.PRICE as PREVIOUS_PRICE
from SALES S1
left outer join SALES s2
on s1.ITEM_ID = s2.ITEM_ID
and s1.DATE > s2.DATE
and s2.DATE > all (
select DATE
from SALES s3
where s2.ITEM_ID = s3.ITEM_ID
and s2.DATE <> s3.DATE
and s1.DATE > s3.DATE
)
) SUMMARY
on ITEM.ITEM_ID = SUMMARY.ITEM_ID
where PRESENT_PRICE = 350

カラム名変えてるのは単に（私の）分かりやすさと見易さのためなので、別に変更しなくても構いません。

A02-1
replyerotakopu [1月13日 05:28]

Kanonbell様、返信有難うございます。

試してみたのですが、呼び出せずタイムアウトしてしまいました。

なぜかわたしのローカル環境で外部結合（LEFT JOIN や RIGHT JOIN）を使うと簡単な外部結合でも10秒以上かかります。PCが低スペックなわけでは無いのでxanmppの設定か何かだと思いますが･･･

A02-1-1
replyerKanonbell [1月14日 19:52]

>試してみたのですが、呼び出せずタイムアウトしてしまいました。

ギャー

動作確認してないので間違ってるかもしれませんね。
ちょっと難しいですが、mySQLの環境あったらこちらでも試して見ます。

<<質問一覧へ