トーハム紀行

ラベル SQL の投稿を表示しています。すべての投稿を表示

実に7ヶ月ぶりにこのシリーズを再開することにした。
前回なにをやったのか、というと、率直にリンク先を見ていただければ良いと思う。
「SQLでちゃんとしたランキングを作る【1/3】」

つまりseletしてorderしてlimitするだけでは正しいランキングがでない、ということだ。

幸いにも識者の方から色々アドバイスをもらい、俺なりに試してみた結果を書いてみようと思う。

まず、識者、つまりDupont氏から引用してもらったSQLがこれだ。

select (select count(distinct b.sal) from emp b where b.sal <= a.sal) as rnk, a.sal from emp a

個人的にSQLは改行を入れて見やすくする主義なので、以下のように適宜変更してみる。

select
  (
    select
      count(distinct b.sal)
    from
      emp b
    where
      b.sal <= a.sal
  ) as rnk, a.sal
from
  emp a

コードというものは、改行やインデントで適切にフォーマットしておくべきだと思っている。

しかしSQLに関してはまだ拘りはないので、だいたいいつもフォーマットは違う。統一しておいたほうが良い場合と、統一しないでも良い場合があるのだが、SQLは俺にとってまだ後者の方だ。

SQLを使ってランキングを表示させるなんてことをやったことがある人もいるだろう。
たとえばメンバー（訪問者という意味でのユーザは2種類あって、登録ユーザをメンバー、非登録ユーザをビジターと呼んでいる）にポイント属性が設定されていて、そのポイントを毎日集計する、そしてその結果、上位から10件取り出せば、ランキングデータが1位までそろう、というものだ。

ちょっと待て！！

それたぶん、インチキ！！

MySQLであれば、

select * from histories
order by point desc
limit 10

と吐けば、pointが高い順に上から10件の結果を得ることはできるが、ただ10件取得するだけで、ランキングとして正しいかどうかは別問題だ。

というのも、同位の場合があるからだ。

ある2人のユーザが同ポイントだった場合、内部ではpointではなくidや登録日などの順で順位が変わってしまうということになる。正直これは不本意だ。

というわけで、いくつかパターンを考えてみる。以下は、3位が二人いる場合を例にとってみた。

	順位シーケンシャル	順位スキップ
10件まで	1 2 3 3 4 5 6 7 8 9	1 2 3 3 5 6 7 8 9 10
10位まで	1 2 3 3 4 5 6 7 8 9 10	1 2 3 3 5 6 7 8 9 10

これだと、右下の「順位スキップ」＋「10位まで」がよさそうだが、実はそうじゃない。
さらに以下は、3位が二人、8位が3人いる場合になる。

先日のSQLでクロス集計した際、助言をいただけて別の方法で解決した（Dupont氏に感謝）。

俺なりに何が違うのか考えてみた結果、

sum()とcount()で結果が違う
coalesce()関数とは

の2点に絞って調査してみた。

まず前回俺が書いたSQLは以下になる。

select name as '商品名',
 count(case when color='白' then 1 else null end) as '白',
 count(case when color='黒' then 1 else null end) as '黒',
 count(case when color='赤' then 1 else null end) as '赤',
 count(case when color='青' then 1 else null end) as '青',
 count(case when color='緑' then 1 else null end) as '緑',
 case
  when name='ビックリ弁当箱' then sum(field(name, 'ビックリ弁当箱'))
  when name='最果てグローブ' then sum(field(name, '最果てグローブ'))
  when name='無駄毛ロンガー' then sum(field(name, '無駄毛ロンガー'))
  else null
 end as '合計'
from history
group by name with rollup

そしてDupont氏の助言は以下。

SELECT COALESCE(`name`, '合計') AS `商品名`,
SUM(CASE WHEN `color`='白' THEN 1 ELSE 0 END) AS '白',
SUM(CASE WHEN `color`='黒' THEN 1 ELSE 0 END) AS '黒',
SUM(CASE WHEN `color`='赤' THEN 1 ELSE 0 END) AS '赤',
SUM(CASE WHEN `color`='青' THEN 1 ELSE 0 END) AS '青',
SUM(CASE WHEN `color`='緑' THEN 1 ELSE 0 END) AS '緑',
COUNT(*) AS `合計`
FROM `history`
GROUP BY `name` WITH ROLLUP;

※COALESCE()関数とWITH ROLLUPは条件が一緒になるように追記してる

大文字小文字は抜きにして、基本的にcount()がsum()になっている点と、小計がcase式か、count()か、という点が大きく違う。

2回目はこちら。
こっちのほうがスマート。

たとえば

最果てグローブ
無駄毛ロンガー
ビックリ弁当箱

の3つの商品がそれぞれ

の色バージョンがあったとして、
それぞれどれだけ売れたのかを集計とかやってみる。

似たような作業が実務で発生し、SQL未熟者の俺としては、これはぜひ覚えたいと思い、いろいろ試行錯誤してみたら出来たので、備忘録がてらかいておく。

結果として、イメージされる表にするとこういう感じ。

商品名	白	黒	赤	青	緑
ビックリ弁当箱	1	1	2	1	2
最果てグローブ	2	2	2	1	0
無駄毛ロンガー	1	0	2	1	2

都道府県別に男女数を出すような、こんな感じをイメージしてもよい。

都道府県	男	女
東京都	5	8
神奈川県	4	7
千葉県	0	31

さて、正しくCSVファイルがダウンロードされただろうか。
今回はCSVファイルのカスタマイズなので、ここまでで終わりではない。

このようなCSVファイルを

id	item_id	name	price	created
1	1	あきたこまち	1980	2010-01-01 24:59:59
2	2	ささにしき	2034	2010-01-01 24:59:59
3	3	こしひかり	1680	2010-01-01 24:59:59
4	1	あきたこまち	1980	2010-01-01 24:59:59
5	3	こしひかり	1680	2010-01-01 24:59:59