[pgsql-jp: 37803] Re: 階級ごとの最大値や最小値の取り出し方を教えてください

["なかで"]

中出です。

> こんにちは、渋谷です。

レス、ありがとうございます。


> > 以下のような3つのテーブルがあるとします。
> >
> > ●usrテーブル
> >
> > id | name
> > ---+-----
> > 1  | yamada
> > 2  | hamada
> > 3  | tanaka
> > 4  | ueda
> >
> > ●usr_ptテーブル
> >
> > id | usr_id | pt
> > ---+--------+----
> > 1  | 1      | 5
> > 2  | 2      | -10
> > 3  | 3      | 0
> > 4  | 4      | 15
> >
> > ●usr_ckテーブル
> >
> > id | usr_id | chkdate    | ck
> > ---+--------+------------+----
> > 1  | 1      | 2006-12-15 | 3
> > 2  | 1      | 2006-12-16 | 1
> > 3  | 1      | 2006-12-17 | 4
> > 4  | 2      | 2006-12-15 | 3
> > 5  | 2      | 2006-12-16 | 5
> > 6  | 3      | 2006-12-17 | 2
> > 7  | 4      | 2006-12-17 | 5
> >
> >
> > usr_ptテーブルのptフィールドの値を10きざみの階級にわけて
> > その階級に含まれるusr_ckテーブルのckフィールドの値の
> > 最大値、最小値、平均値とそのフィールドに含まれるデータ数
> > を取得したいと思ってます。
> > たとえば、2006-12-17のデータを取り出すのであれば
> > 以下のようなデータを取り出したいと思っているのですが、
> > これをSQL1本でしてしまうことは可能でしょうか？
> 
> 出来ると思いますよ。
> pt の値を加工し 10 刻みで同じ値になるようなグループ化列を導きましょう。
> （case 文で出力イメージの文字列にしてもいいですが、理想的には数値で
> グループ化し、出力の際に文字列化するのが良いと思います。）
> で、max、min、avg、count を取ってやればよいです。

select 
(usr_pt.pt / 10) as usr__pt,
max(usr_ck.ck) as ck_max,
min(usr_ck.ck) as ck_min,
avg(usr_ck.ck) as ck_avg,
count(usr_ck.ck) as ck_cnt
from usr
left join usr_pt on usr_pt.usr_id=usr.id
left join usr_ck on usr_ck.usr_id=usr.id
where usr_ck.chkdate='2006-12-17'
group by usr__pt
order by usr__pt;

という感じでしょうか？
ほぼ、期待どおりの結果を得ることができたのですが、usr_ckテーブル
に2006-12-17のデータがないものについては含むことができませんでした。


> 
> > pt           | ckの最大値 | ckの最小値 | ckの平均値 | データ数
> > -------------+------------+------------+------------+----------
> > -10以上0未満 | 0          | 0          | 0          | 1
> > 0以上10未満  | 4          | 2          | 3          | 2
> > 10以上20未満 | 5          | 5          | 5          | 1
> >

この表でいうと、「-10以上0未満」の欄のデータがない状態となりました。
（usr_ckテーブルでusr_idが2のものについては、2006-12-17のデータが
なかったので、上記SQLでは駄目だったようです。）


> >
> > 現在は、以下のようなSQLでptとckのリストを取り出し、phpのほうで
> > 上記のようなデータに整形しているのですが、データ数が多くなるに
> > つれて処理がつらくなってきたので、SQLのほうで楽に処理ができないか
> > と思い、相談させていただきました。
> > もし、よい案がございましたら教えてください。
> >
> > select
> > sum(case when usr_ck.chkdate='2006-12-17' then usr_ck.ck else 0 end)
> > as usr__ck,
> > usr_pt.pt as usr__pt
> > from usr
> > left join usr_pt on usr_pt.usr_id=usr.id
> > left join usr_ck on usr_ck.usr_id=usr.id
> > group by usr__pt
> > order by usr__pt
> 
> 12-17 以外のデータを結合・集計しているように見えるのですが、
> これには何か意味があるのでしょうか？
> 今のクエリだと、データが増えるに連れ処理が重くなるのは当然な気がします。
> （usr_ck テーブルの使い方が分からないので間違っているかもしれませんが）
> データの束を扱っていることを意識して、できるだけ早くその束を本当に必要な
> 数まで減らすことを考えましょう。

ご指摘ありがとうございます。
usr_ckテーブルに2006-12-17のデータがない場合を考えて
上記のようなSQLとしました。他にもっと簡単なSQLがございましたら
教えていただければ幸いです。

以上、よろしくお願いします。

-- 
nakade <nakade ＠ prjapan.jp>