SQL文についてご教授願います。


【tableA:商品一覧テーブ】
id, name
1 , りんご
2 , みかん
3 , いちご


【tableB:売上表テーブル】
id, date, price
1 , 1265375594 ,50
1 , 1260265023 ,57
2 , 1265375594 ,150
2 , 1260265023 ,160


【抽出したいデータ】
id, name, date, price, price2, price3
1, りんご, , , ,
2, みかん, , , ,
3, いちご, , , ,
4, ×××, , , ,
5, ○○○, , , ,
6, △△△, , , ,


date = IDごとの最新日付
price = IDごとの最新日付の値段
price2 = priceより1つ前に売上があった日の値段
price3 = priceとprice2の差額

・各IDごとのデータがほしい
・商品によっては売上の無い日もある
・IDと日付で一意

(参考)
仕様:MYSQL5.1
日付:UNIXTIMEのINT型

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2010/03/09 16:23:14
  • 終了:2010/03/10 20:09:52

ベストアンサー

id:Km1967 No.2

Km1967回答回数224ベストアンサー獲得回数352010/03/09 21:20:55

ポイント300pt

まずは下記を実行して趣旨を理解してほしい!

以前kn1967さんが作ってくださった「SQLを実行してHTMLのTABLEとして出力するためのルーチン」を用いてメインルーチン部分にて実行すると楽だと思う!!

SET @id = '', @c = 0;

SELECT a.*
  , FROM_UNIXTIME(b.date)
  , b.price
  , IF(@id <> a.id, @c := 1, @c := @c + 1) c
  , IF(@id <> a.id, @id := a.id, @id := @id) i
  , IF(@c = 1, b.date, 0) date
  , IF(@c = 1, b.price, 0) price
  , IF(@c = 2, b.price, 0) price2
FROM tableA a
LEFT JOIN tableB b ON b.id = a.id
ORDER BY a.id, b.date DESC;

ご理解いただけたら下記に丸ごと置き換える!

price2の上位10件に絞るようにORDER BYとLIMITもつけてあるので、このあたりは適当に変更よろし!!

SET @id = '', @c = 0;

SELECT c.id, c.name
  , FROM_UNIXTIME(SUM(c.date)) date, SUM(c.price) price, SUM(c.price2) price2
  , SUM(c.price2) - SUM(c.price) price3
FROM(
  SELECT a.*
    , IF(@id <> a.id, @c := 1, @c := @c + 1) c
    , IF(@id <> a.id, @id := a.id, @id := @id) i
    , IF(@c = 1, b.date, 0) date
    , IF(@c = 1, b.price, 0) price
    , IF(@c = 2, b.price, 0) price2
  FROM tableA a
  LEFT JOIN tableB b ON b.id = a.id
  ORDER BY a.id, b.date DESC
) c
GROUP BY c.id, c.name
ORDER BY price2 DESC
LIMIT 0, 10;

以上、テスト済みのコピペ!!

id:dekapurio

Km1967さん回答ありがとうございます。

結論からいうと、MYSQLではどのSQLもtableAが1000レコード、tableBが200,000レコードというデータ量に対応できていないようです。

(phpmyadminで試していますが、読み込み中のままブラウザが固まる)

データを大幅に減らして(10レコード、100レコード)試してみると、Km1967さんのSQLも、chuken_kenkouさんのSQLでもうまく動作しました。

ここまでSQLでやらせること自体が間違っているのでしょうが・・・

2010/03/10 07:15:09

その他の回答(1件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542010/03/09 18:17:07

ポイント150pt

price2は、アプリ側で計算すればいいと思いますが、SQLだけで”無理矢理”、やりたいでしょうか?

MySQLでは、@で始まる”ユーザ変数”を使えるので、price2はユーザ変数で求める例です。

select
  A.id
 ,A.name
 ,GB.maxdate
 ,date(from_unixtime(GB.maxdate)) as hizuke
 ,@price:=(select `price` from tableB where id=A.id and `date`=GB.maxdate) as `price`
 ,@price1:=(select `price` from tableB where id=A.id and `date`<GB.maxdate order by `date` desc limit 1) as `price2`
 ,@price-@price1 as price2
 from tableA as A
  left join 
   (select
     id
     ,max(`date`) as maxdate
     from tableB
     group by id) as GB
   on A.id=GB.id
 order by A.id
id:dekapurio

chuken_kenkou様、回答有難うございます。

7行目の最後は、as `price1`でよかったですかね?

どちらにしても、動かなかったです。

>SQLだけで無理矢理やりたいでしょうか

厳密にいうと、price2の大きい順に10件だけ表示をしたかったので、素人考えですがprice2も必要なのかなと思っています。

2010/03/09 19:20:44
id:Km1967 No.2

Km1967回答回数224ベストアンサー獲得回数352010/03/09 21:20:55ここでベストアンサー

ポイント300pt

まずは下記を実行して趣旨を理解してほしい!

以前kn1967さんが作ってくださった「SQLを実行してHTMLのTABLEとして出力するためのルーチン」を用いてメインルーチン部分にて実行すると楽だと思う!!

SET @id = '', @c = 0;

SELECT a.*
  , FROM_UNIXTIME(b.date)
  , b.price
  , IF(@id <> a.id, @c := 1, @c := @c + 1) c
  , IF(@id <> a.id, @id := a.id, @id := @id) i
  , IF(@c = 1, b.date, 0) date
  , IF(@c = 1, b.price, 0) price
  , IF(@c = 2, b.price, 0) price2
FROM tableA a
LEFT JOIN tableB b ON b.id = a.id
ORDER BY a.id, b.date DESC;

ご理解いただけたら下記に丸ごと置き換える!

price2の上位10件に絞るようにORDER BYとLIMITもつけてあるので、このあたりは適当に変更よろし!!

SET @id = '', @c = 0;

SELECT c.id, c.name
  , FROM_UNIXTIME(SUM(c.date)) date, SUM(c.price) price, SUM(c.price2) price2
  , SUM(c.price2) - SUM(c.price) price3
FROM(
  SELECT a.*
    , IF(@id <> a.id, @c := 1, @c := @c + 1) c
    , IF(@id <> a.id, @id := a.id, @id := @id) i
    , IF(@c = 1, b.date, 0) date
    , IF(@c = 1, b.price, 0) price
    , IF(@c = 2, b.price, 0) price2
  FROM tableA a
  LEFT JOIN tableB b ON b.id = a.id
  ORDER BY a.id, b.date DESC
) c
GROUP BY c.id, c.name
ORDER BY price2 DESC
LIMIT 0, 10;

以上、テスト済みのコピペ!!

id:dekapurio

Km1967さん回答ありがとうございます。

結論からいうと、MYSQLではどのSQLもtableAが1000レコード、tableBが200,000レコードというデータ量に対応できていないようです。

(phpmyadminで試していますが、読み込み中のままブラウザが固まる)

データを大幅に減らして(10レコード、100レコード)試してみると、Km1967さんのSQLも、chuken_kenkouさんのSQLでもうまく動作しました。

ここまでSQLでやらせること自体が間違っているのでしょうが・・・

2010/03/10 07:15:09
  • id:Km1967
    PostgreSQLなどであればこんな手抜きでも動く! MySQLだとなぜか動かない。少々てこずった!!
    SELECT *, price2 - price AS price3
    FROM (
    SELECT a.*
    , (SELECT date FROM tableB WHERE id = a.id ORDER BY date DESC OFFSET 0 LIMIT 1) date
    , (SELECT price FROM tableB WHERE id = a.id ORDER BY date DESC OFFSET 0 LIMIT 1) price
    , (SELECT price FROM tableB WHERE id = a.id ORDER BY date DESC OFFSET 1 LIMIT 1) price2
    FROM tableA a
    ) b;

    最近ご活躍のid:GreenStar君の真似をしてみたが似てるか?
  • id:chuken_kenkou
    >7行目の最後は、as `price1`でよかったですかね?

    誤植でしたね。失礼しました。

    >どちらにしても、動かなかったです

    「動かなかった」とは、具体的にどういう事象なのでしょうかね?

    syntax errorですか?
    それなら、誤植が関連かも知れません。

    期待した結果と異なっていましたか?
  • id:chuken_kenkou
    質問する場合は、以下の事項を明確にして欲しいです。

    (1)使用RDBMSとバージョン
    (2)各列のデータ型と主キー(どの列、あるいは列の組み合わせで、行を一意に識別できるのか)
    (3)基となるデータと得たい結果
    (4)何らかのアプリ経由なのか、なんとしてもSQLだけで実現したいのか

    毎回のことながら、質問者さんは、(3)が示されていません。

    「実際に使用するデータを公開できない」ということは、普通にあることです。
    しかしながら、限定的あるいは加工したデータを公開して、何らかの結果を得たいという質問をする場合、その公開した範囲で、他人にも検証可能なデータを公開してもらえないと、他人による検証ができません。

  • id:Km1967
    横槍すまんが、サブクエリを複数使ったりすると、環境によっては動かなかったりする場合もあるようだ。
    上にコメントしたような比較的単純なものですら走らなかったから id:chuken_kenkou さんの環境で動いていても他の環境では動かないのかもしれん。

    ちなみに私の回答はまったく別の集計方法を取っている。途中の検証用コードと最終コードまで書いてあるから本人でもデバッグできるだろう。気が向けば見てくれればいい。
    グリスタ君の真似をして文章は少々ふざけているが、そこは茶目っ気としてスルーで頼む。

    ところで、疑問なんだが、予約語を小文字で書くとは珍しいが、会社の規約にでもあるのか? 世間一般的には大文字で書くのが通例だし、見易さの点でも比較にならないから、回答投稿字は大文字が良くないだろうか? まぁ、余計なお世話だが。
  • id:Km1967
    さらに横槍ですまんがid:dekapurioさんは(1)から(4)についておおよそ質問文に書いている。それを理解できるかどうかは回答者側の問題だ。 先にコメント欄に書くか回答の中で注意するならまだしも、動かないと言われていきなりコメントを書くなど逆切れとしか思えん。 今回がはじめてのようであれば逆切れなどと失礼な事は言わんが30日ほど前にも同じ状態があったようだからな。 ブラックジョークでつけているKm1967というIDも癇に障ったのかも知れんが。

    http://q.hatena.ne.jp/1265472962

    まぁ、冷静になって考え直してみてはどうか?
  • id:dekapurio
    >chuken_kenkouさん

    回答の返信欄に動作しないと書きましたが、データを減らして試すとうまく動作しました。
    語弊がありまして失礼しました。

    >「動かなかった」とは、具体的にどういう事象なのでしょうかね?
    いつもphpmyadminからSQLを入力しているのですが、ブラウザが読み込み中になったままになりました。

    >検証可能なデータを公開してもらえないと
    確かにそうですね。どこかのサーバーにcsvファイルでも設置するようにしたほうがいいですよね。

  • id:dekapurio
    >Km1967さん

    >PostgreSQLなどであればこんな手抜きでも動く! MySQLだとなぜか動かない。
    PostgreSQLならできるのにMYSQLでは・・・という話が多くて、いい加減MYSQLに嫌気がさしてきていたりします。
    SELECT以下に一回サブクエリを使っただけでデータ量によっては動作しなくなることが多くて・・DBの問題では無いのかもしれませんが。

    ちなみに、こういった前日比の大きい順に10件だけ表示させるというような処理はPHPのアプリ側で出来るものなのでしょうか?
  • id:Km1967
    phpによる絞込みについてだが、MySQLで集計した結果を1レコードずつ取ってくる時に条件分岐させるという手を使うのだが、当然ながらSQLにやらせて結果だけを受け取るのとは比較にもならんほど非常識に時間を食うことになるから、現状では意味は無いだろう。

    最初に訂正がある。素人的なこちらの落ち度で恥ずかしい限りだが、小さいほうからではなく大きいほうからにしなければいけなかった。インデックスも利用されなかった可能性が高い。インデックスだけではない。tableAが1000件程度と小さいのであればメモリ上のファイルキャッシュに常駐し、ヒット率もあがるはずだ。
    FROM tableB b
    LEFT JOIN tableA a ON a.id = b.id
    ORDER BY b.id, b.date DESC
    これによって検証用コードは速度が変わるだろう。ただし20万件吐き出してくる事に違いは無いからLIMITをつけて数件から数十件程度でとまるようにしておいたほうがいい。あくまでも仕組みの検証用。
    集計用のほうは最初からLIMITが設けてあるから最終出力結果は10件になるわけだが、上記に変更したとしてもサブクエリが動くかどうかが疑問だ。MySQLはサブクエリの結果を溜め込んでから上位に結果を渡すような仕組みだったはずだ。サブクエリの結果は20万件分となるため、止まる可能性が高い。
    検証用コードを使ってtableCを作り、それを集計するようにしたほうが良いだろう。2段階になってしまうが少なくともMySQL直であれば動くはずだ。

    話は変わるが、毎度毎度集計させても良いが、日々増えていくのはtableBのほうであろう?
    どのようなタイミングでデータを更新しているかまでは判らぬがtableX(前日)およびtableY(前前日)を作っておいてはどうか?
    tableBにデータを書き込むステップを(1)tableYにtableXのデータを移し、(2)tableXに新しいデータを入れ、(3)最後にtableXのデータをtableBに追加するという3段階に見直してはどうか?
    この手順を組むのはさほど難しくはないだろう。集計したい時にはtableCとtableDはそれぞれ1000件程である訳だし単純にidでJOINするだけで作業は終了することになるから楽だと思う。

    念のためだがMySQLを非難したい訳ではない。むしろよく使わせてもらっている。今回やってみて面倒だったから面倒だと言ったまでだから勘違いしないでほしい。目的によって、どうすべきかを考えていかねばならんという事。そして、いかにシンプルにしていくかという事。 id:chuken_kenkou さんのようなコードを書けるのはすばらしいことだと思うが、逆に懲りすぎなのだ。いい意味で手を抜けば、きっともっとすばらしいものが作れると思う。彼に対しては少々あおってしまったが、多分、判ってもらえると思っている。

    とりあえず、以上。
  • id:dekapurio
    >Km1967さん

    9秒かかりましたが、ついに動きました。
    (あと一押しなんですが、数分放置しても動かなかったことを考えると物凄い進歩です。)

    3段階にテーブルを見直す方法は勉強になりました。
    テーブルを増やしてSQLをシンプルにするほうがいいのですね。
    今回においては前日比(前回比)を計算させるやり方が知りたかったので、質問した次第です。
  • id:Km1967
    9秒が早いか遅いかは何とも言えんが9秒で収まったのであればphpMyAdminやphpなどから呼び出しても使えるだろう。あとはMySQL自体のチューニングになってくるからWindows + xamppではちょっと難しいかもしれん。現段階ではテーブル構成を見直すなどのほうを先にしたほうがいいだろう。3段階というのは単なる思い付きにすぎん。今後、どのような集計や比較をやりたいかを先に考えてからテーブル構成を考えたほうが良い。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません