130単位

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

SQL

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

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

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

こんな仕様とします。(※試したDBはPostgreSQL 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

ツイートする

Permalink | コメント(2) | トラックバック(0)