【SQL】クロス集計のサンプル

テーブルを集計や分析で使うような形式に変換するSQLを紹介していきます。
サンプルに使うテーブルは以下のような構造になっています。

・コース一覧

・コース開催日

これをこんな風に集計する場合のSQLサンプルを紹介していきます。

group byとcaseを使用したケース

SQL

select
  inner_tbl.course_id as course_id,
  inner_tbl.course_name as course_name,
  -- ③グループごとにその月のコース開催レコードを持つかを判定
  max(case when inner_tbl.open_date between '2019-01-01' and '2019-01-31' then '○' else '×' end) as １月,
  max(case when inner_tbl.open_date between '2019-02-01' and '2019-02-28' then '○' else '×' end) as ２月,
  max(case when inner_tbl.open_date between '2019-03-01' and '2019-03-31' then '○' else '×' end) as ３月
from
  (
    -- ①コース一覧とコース開催日を結合
    select c.course_id, c.course_name, oc.open_date
    from practice2_course as c
    inner join practice2_open_course as oc
    on c.course_id = oc.course_id
  ) as inner_tbl
-- ②コース別にグループ化
group by inner_tbl.course_id, inner_tbl.course_name
order by inner_tbl.course_id;

・結果

existsを使ったケース

select
  c.course_id,
  c.course_name,
  -- ②existを使って、その期間に該当する開催日レコードがあるかを判定
  max(case when exists (
      select 1 
      from practice2_open_course as oc 
      where c.course_id = oc.course_id 
      and oc.open_date between '2019-01-01' and '2019-01-31') 
    then '○'
    else '×' end
   ) as １月,
  max(case when exists (
      select 1 
      from practice2_open_course as oc 
      where c.course_id = oc.course_id 
      and oc.open_date between '2019-02-01' and '2019-02-28')
    then '○'
    else '×' end
   ) as ２月,
  max(case when exists (
      select 1 
      from practice2_open_course as oc 
      where c.course_id = oc.course_id 
      and oc.open_date between '2019-03-01' and '2019-03-31')
    then '○'
    else '×' end
   ) as ３月
from
  practice2_course c
-- ①コース別にグループ化
group by
  c.course_id, c.course_name
order by c.course_id;

・結果
同じ結果になります。