Hatena::ブログ(Diary)

130単位

2009-05-21

条件付きで集計するにはSUM(CASE WHEN ...)

区分値(もしくはON/OFFのフラグ)のカラムがあるとして、それぞれの区分値のレコード数をカウントしたものを、1度のSQLで同時に取得したい。

これを満たそうとして、ひどいSQLを書いてしまっていたことがテスト段階にて発覚。

  • サンプル
    • ページ情報のテーブルと、アクセスログのテーブル
    • 2つのテーブルから、各ページの月別アクセス数を求める
    • ただし、PCと携帯のアクセスは別々に集計する

こんな仕様とします。(※試したDBPostgreSQL 8.3.7)

間違ったSQL

select
    t1.page_id,
    coalesce(t2.count, 0) as pc_access_count,
    coalesce(t3.count, 0) as mobile_access_count,
    coalesce(t2.count, 0) + coalesce(t3.count, 0) as access_count,
    t2.target_yyyymm
from tbt_page t1
    left outer join (
        select
            page_id,
            count(page_id),
            to_char(add_date, 'yyyymm') as target_yyyymm
        from tbt_access
        where mobile_flg = '0'
        group by page_id, target_yyyymm) t2
        on (t1.page_id = t2.page_id)
    left outer join (
        select
            page_id,
            count(page_id),
            to_char(add_date, 'yyyymm') as target_yyyymm
        from tbt_access
        where mobile_flg = '1'
        group by page_id, target_yyyymm) t3
        on (t1.page_id = t3.page_id and t2.target_yyyymm = t3.target_yyyymm)
where a.del_flg = '0'

「カウントする=COUNT関数」しか頭になく、区分値をWHERE句に指定したサブクエリを年月で強引に結合しています。その年月で結合している部分がまずくて、t2に無くてt3に存在したときでも値が入ってきません。

正しいSQL

こういうときは、SUM関数とCASE式を組み合わせて使うのでした。

select
    t1.page_id,
    t2.pc_access_count,
    t2.mobile_access_count,
    t2.access_count,
    t2.target_yyyymm
from tbt_page t1
    left outer join (
        select
            page_id,
            sum(case when mobile_flg = '0' then 1 else 0 end) as pc_access_count,
            sum(case when mobile_flg = '1' then 1 else 0 end) as mobile_access_count,
            count(page_id) as access_count,
            to_char(add_date, 'yyyymm') as target_yyyymm
        from tbt_access
        group by page_id, target_yyyymm) t2
        on (t1.page_id = t2.page_id)
where t1.del_flg = '0'

結合も1度で済み、だいぶすっきりしました。以前にもやった経験があったはずなのですが、SUM関数の存在自体を完全に忘れていました。

そんなわけで、自戒の意味もこめて書き残してみた次第です。

参考リンク

SQLアタマ養成講座:第3回 SQL流条件分岐(3) 表頭の複雑な集計|gihyo.jp … 技術評論社
http://gihyo.jp/dev/serial/01/sql_academy/0003

HidenoriHidenori 2010/02/05 11:15 上記、参照させていただき大変助かりました。
ありがとうございます

deeekideeeki 2010/02/06 04:01 お役に立ててうれしく思います。
コメントありがとうございます!

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/deeeki/20090521/sumcasewhen
リンク元