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

渋谷泰宏 yasuhiro_shibutani @ ybb.ne.jp
2006年 12月 18日 (月) 17:22:39 JST


渋谷です。

> 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のデータがないものについては含むことができませんでした。

10 で割った商だと -9 と 9 が同じ値になりませんか?
グループ化列の加工はもう少し工夫する必要がありそうです。

> >
> > > 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では駄目だったようです。)

データが存在しないときにも出力行数が変わらないようにしたいと
いうことでしょうか?

> > > 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がございましたら
> 教えていただければ幸いです。

集計すると存在しない行はでてきませんよね。

わたしがよくやるのは以下の2つの方法です。

1.出力イメージの識別列を左に外部結合する。
2.集計元のデータに列を加える。

2は、例えば usr_ck テーブルの 2006-12-17 のデータと
chkdate が 2006-12-17 のリテラル行を UNION します。
で、usr_ck テーブル部分をそのサブクエリに置き換えます。

#今日はあまり時間が取れず試せませんので、
#間違ったことを言ってたら申し訳ないです。

-- 
渋谷 泰宏



pgsql-jp メーリングリストの案内