日別で保持しているレコードを行単位で月別集計してみる

表題のような集計をDBで行うに辺り、その変換方法を交えて記載していきます。
使用DBMS：PostgreSQL9.3.5

はじめに

今回は日別で保持している個々の予約データを、月別でレッスン形式毎に予約ユーザー数を集計し、その結果を行単位（横持ち）に変換するまでを例にして解説していきます。

まず、日別で保持しているテーブル構成は以下の通りです。

テーブル名：lesson_reserve_table
user_id：予約ユーザーID
lesson_format：レッスン形式（01：教室開催 02:オンライン開催）
reserve_date：予約日

user_id	lesson_format	reserve_date
user1	01	2020-08-01
user1	01	2020-08-20
user2	01	2020-08-10
user3	01	2020-08-15
user3	01	2020-09-01
user5	01	2020-09-02
user1	01	2020-09-03
user1	02	2020-08-03
user2	02	2020-08-12
user3	02	2020-08-17
user4	02	2020-09-02
user4	02	2020-09-03
user5	02	2020-09-05

日別データを月別で集計

次に上記日別データをレッスン形式毎に月別集計します。
SQLは以下の通りです。

select
    date_trunc('month',lrt.reserve_data) as reserve_month,
    lrt.lesson_format,
    count(*) as reserve_cnt
from lesson_reserve_table lrt
group by lrt.lesson_format,reserve_month
order by reserve_month,lesson_format

上記SQLを実行した結果は以下の通りです。

reserve_month	lesson_format	reserve_cnt
2020-08-01	01	4
2020-08-01	02	3
2020-09-01	01	3
2020-09-01	02	3

月別集計データを行単位（横持ち）に変換する

次に上記集計結果を、行単位（横持ち）に変換します。
行単位に変換する為のSQLは以下の通りです。
※ポイントは、lesson_formatをmax句で集約してcase句で横持ちに変換します。

select 
    w.reserve_month,
    max(case when w.lesson_format = '01' then w.reserve_cnt end) as school_reserve_cnt,
    max(case when w.lesson_format = '02' then w.reserve_cnt end) as online_reserve_cnt, 
    sum(reserve_cnt) as reserve_totle
from 
(
    select
        date_trunc('month',lrt.reserve_data) as reserve_month,
        lrt.lesson_format,
        count(*) as reserve_cnt
    from lesson_reserve_table lrt
    group by lrt.lesson_format,reserve_month
)w
group by reserve_month
order by reserve_month

結果、以下の通り行単位に変換することが出来ます。

reserve_month	school_reserve_cnt	online_reserve_cnt	reserve_totle
2020-08-01	4	3	7
2020-09-01	3	3	6

最後に

横持ち変換すると視覚的にも分かりやすく、グラフ化もしやすいので、よかったら活用してみて下さい。