EC-CUBE 開発コミュニティ

その他 > その他 > ランキングSQL

その他

投稿者	スレッド
lvsa	投稿日時: 2009/10/27 18:19 対応状況: −−−
一人前登録日: 2009/10/6 居住地: 投稿: 104	ランキングSQL いつもお世話になっております。現在、「この商品を買った人は、こんな商品も買っています」機能を作成中ですが、一覧をランキング形式で、上位5件表示させようと考えております。 ------------------------------------------------------------ SELECT product_id, count(), price01 FROM dtb_products_class AS class INNER JOIN ( SELECT product_id, count() FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE customer_id in ( SELECT customer_id FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE product_id = $product_id) AND o.create_date >= current_date - integer '360' GROUP BY product_id ORDER BY count(product_id) DESC ) AS rank ON class.product_id = rank.product_id LIMIT 5 OFFSET 0 ------------------------------------------------------------ こんな感じで、カウントの多い商品、同率の場合は、商品の価格順にてソートしたいと考えております。 SQLに詳しい方、ご教授お願い致します。 -------------------------------- ec-cube:2.4.1正式版 php:5.1.6 apache:2.2.3 db:porstgrsql8.1.11 --------------------------------

pineray	投稿日時: 2009/10/27 18:30 対応状況: −−−
長老登録日: 2006/9/9 居住地: 伊賀投稿: 292	Re: ランキングSQL 検証はしておりませんが、 ORDER BY の部分を以下のようにすれば、カウントが同数の場合は価格で並び替えが行われます。 `ORDER BY count(product_id) DESC, price DESC` ---------------- EC-CUBEのプラグインを公開しています！いつも reCAPTCHA

lvsa	投稿日時: 2009/10/27 18:42 対応状況: −−−
一人前登録日: 2009/10/6 居住地: 投稿: 104	Re: ランキングSQL pinerayさま返信ありがとうございます。 >ORDER BY count(product_id) DESC, price DESC 検証してみましたが、エラーとなってしまいました。 <エラー内容> column "od.price" must appear in the GROUP BY clause or be used in an aggregate function GROUP BYをproduct_idにて行っているので、product_id以外のカラムではソートはできないのかと思われます。色々、試してはいますが、上手くいかないですねぇ〜。アドバイスありがとうございます。他にもありましたら、ご教授よろしくお願い致します。

pineray	投稿日時: 2009/10/27 19:03 対応状況: −−−
長老登録日: 2006/9/9 居住地: 伊賀投稿: 292	Re: ランキングSQL よく見ずに書いてしまって申し訳ありません。 JOIN句の外で並び替えないといけませんね。 SELECT の部分を以下のようにして、 `SELECT product_id, count(*) AS cnt, price01` ORDER BY を追加してみてはいかがでしょう？ `ORDER BY cnt DESC, price01 DESC` ---------------- EC-CUBEのプラグインを公開しています！いつも reCAPTCHA

lvsa	投稿日時: 2009/10/28 10:07 対応状況: −−−
一人前登録日: 2009/10/6 居住地: 投稿: 104	Re: ランキングSQL pinerayさまありがとうございます。返信遅くなりました・・・。試してみましたが、今度は以下の部分がダメなようです。 SELECT 　product_id, count() AS cnt, price01 FROM dtb_products_class AS class LEFT JOIN ( 　SELECT 　　product_id, count() 　FROM dtb_order AS o 　INNER JOIN dtb_order_detail AS od 　　ON o.order_id = od.order_id 　WHERE customer_id in ( 　　SELECT customer_id FROM dtb_order AS o 　　INNER JOIN dtb_order_detail AS od 　　　ON o.order_id = od.order_id 　　WHERE product_id = 2) 　AND o.create_date >= current_date - integer '360' 　GROUP BY product_id) AS ranking ON class.product_id = ranking.product_id ORDER BY cnt DESC, price01 DESC LIMIT 5 OFFSET 0; エラー内容：column reference "product_id" is ambiguous SELECT句のproduct_idがダメなようですね・・。 class.product_idやranking.product_id,dtb_products_class.product_idを試しても column "class.product_id" must appear in the GROUP BY clause or be used in an aggregate function とエラーになってしまいます。。。どのようにしたら良いのでしょうか・・。

hogehoge	投稿日時: 2009/10/28 11:05 対応状況: −−−
常連登録日: 2008/9/19 居住地: Cusco,Peru 投稿: 67	Re: ランキングSQL エラー内容：column reference "product_id" is ambiguous の対処として、class.product_id は、正解だと思います。ただ、countしてるのにGROUP BYが無いって怒られてるんですよね。要するにこういうことでしょうか。 SELECT class.product_id, coalesce(cnt,0) as cnt, class.price01 FROM dtb_products_class AS class LEFT JOIN (SELECT od.product_id, count(*) as cnt FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE customer_id in (SELECT customer_id FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE product_id = 2 ) AND o.create_date >= current_date - integer '360' GROUP BY od.product_id ) AS ranking ON class.product_id = ranking.product_id ORDER BY cnt DESC , price01 DESC LIMIT 5 OFFSET 0; なんか金額の扱いがおかしいような気が。。。

lvsa	投稿日時: 2009/10/28 11:59 対応状況: −−−
一人前登録日: 2009/10/6 居住地: 投稿: 104	Re: ランキングSQL hogehogeさま返信ありがとうございます。 >GROUP BY > od.product_id > ) AS ranking ON class.product_id = ranking.product_id このような記述の仕方もできるのですね！大変参考になりました。しかし・・SELECT結果は私の望むものではなかったです（泣） ↓↓↓↓↓↓結果↓↓↓↓↓↓ product_id \| cnt \| price01 ------------+-----+--------- 410 \| 9 \| 100 1 \| 5 \| 150 1 \| 5 \| 150 1 \| 5 \| 150 1 \| 5 \| 150 しかしながら、皆様のアドバイスを参考に試行錯誤した結果できました。以下のようなSQLになりました。 SELECT od.product_id, count(*) , c.price02 FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id INNER JOIN dtb_products_class AS c ON od.product_id = c.product_id INNER JOIN dtb_products AS p ON c.product_id = p.product_id WHERE customer_id in ( SELECT customer_id FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE product_id = $product_id AND o.create_date >= current_date - integer '360' ) AND p.status <> 2 AND p.status <> 3 GROUP BY od.product_id , c.price02 ORDER BY count(od.product_id) DESC, c.price02 DESC LIMIT 5 OFFSET 0 ↓↓↓↓↓↓結果↓↓↓↓↓↓↓ product_id \| count \| price02 ------------+-------+--------- 1 \| 45 \| 120 410 \| 9 \| 100000 100 \| 5 \| 10000 3 \| 4 \| 100 407 \| 4 \| 2 アドバイスして頂きました皆様ありがとうございました。

popo	投稿日時: 2009/10/28 18:31 対応状況: −−−
長老登録日: 2008/10/1 居住地: 投稿: 189	Re: ランキングSQL lvsa様「この商品を買った人は、こんな商品も買っています」と言う機能を導入したいと思っておりました。完成しましたら、導入方法を御教授して頂けると嬉しく思います。何卒、よろしくお願い致します。

ecbg	投稿日時: 2009/10/28 18:52 対応状況: −−−
仙人登録日: 2009/2/25 居住地: 東京投稿: 387	Re: ランキングSQL lvsa様すごいですね！！私のSQL知識じゃなにが行われているのか、ほとんど分らないのですが、買った商品に対して、どういった条件の抽出になっているのですか？

lvsa	投稿日時: 2009/10/28 19:06 対応状況: −−−
一人前登録日: 2009/10/6 居住地: 投稿: 104	Re: ランキングSQL ecbgさま SQLの抽出条件ですが、以下の感じです。 1.カートに購入予定商品を入れる。 2.カート内の商品を過去1年以内に他のユーザが購入しているかを検索 `SELECT 　customer_id FROM dtb_order AS o INNER JOIN dtb_order_detail AS od ON o.order_id = od.order_id WHERE product_id = $product_id 　AND o.create_date >= current_date - integer '360'` それが上記部分です。 3.また、2の結果の商品が非表示、削除されていないかをチェックする意味で `AND p.status <> 2 AND p.status <> 3` してます。あとは、2、3にて検索された商品のカウントとソート順を価格で行いたかったので、GROUP BYを商品IDと価格で行っています。こんな感じで伝わりますかね？？私自身、人に教えられるほどの知識がないので、わかりづらい部分は、突っ込んでくださいｗｗｗ

(1) 2 »