前年対比グラフを実装するとき、CTEを使えば読みやすいし簡単に歯抜けデータなどに対応できたよ

こちらの記事は金属加工プラットフォームを開発・運用するCatallaxyのCatallaxy Advent Calendar 2022の12/13の記事となります。

自己紹介

インターンをしている和田です！普段はフロントエンドの開発をしていますが、SQLでクエリを書くのも好きで、絶賛勉強しています。

まえがき

「SQLで前年比較するときにself joinすると良いよ！」みたいな記事はよく見かけるのですが、CTEで書くともっとわかりやすいよ！を伝えたくて書きました。
ついでに、歯抜けなデータがあったときの解決策を書いています。参考になれば！

実行環境

今回は良しななデータが見つからず、Metabaseのsample databaseを使用しました。

docker run -d -p 3000:3000 --name metabase metabase/metabase

で簡単に構築できちゃうので楽ちんです！
今回はORDERSテーブルのCREATED_ATとTOTALを使って、売り上げの前年対比を求めていこうと思います。中身はこんな感じ。

CREATED_AT	TOTAL
2020-04-01	906.28
2020-04-02	2,155.35
2020-04-03	1,433.78
2020-04-04	1,909.82
2020-04-05	1,761.23
2020-04-06	1,631.21
2020-04-07	1,384.37
2020-04-08	2,046.87
2020-04-09	2,106.97
...	...
2019-04-01	796.83
2019-04-02	1,629.97
2019-04-03	1,496.84
2019-04-04	1,348.54
2019-04-05	1,759.65
2019-04-06	1,902.25
2019-04-07	2,649.08
2019-04-08	1,539.21
2019-04-09	1,563.96
...	...

早速SQL

sample databaseはh2で構成されているので、ご自身のデータベースに合わせてクエリは書き換えてください

前年対比をCTEで構築するとこんな感じです

with
items as (
    select 
        to_char(CREATED_AT, 'yyyy-mm-dd') as created_at
        , sum(TOTAL) as total
    from 
        ORDERS
    group by created_at
    order by created_at
)
select 
    cur_items.created_at as current_date
    , cur_items.total as current_total
    , prev_items.created_at as previous_date
    , prev_items.total as previous_total
from items as cur_items
join items as prev_items
on cur_items.created_at = DATEADD('YEAR', 1, prev_items.created_at)
where cur_items.created_at >= '2020-04-01'

実行結果はこう

CURRENT_DATE	CURRENT_TOTAL	PREVIOUS_DATE	PREVIOUS_TOTAL
2020-04-01	906.28	2019-04-01	796.83
2020-04-02	2,155.35	2019-04-02	1,629.97
2020-04-03	1,433.78	2019-04-03	1,496.84
2020-04-04	1,909.82	2019-04-04	1,348.54
2020-04-05	1,761.23	2019-04-05	1,759.65
2020-04-06	1,631.21	2019-04-06	1,902.25
2020-04-07	1,384.37	2019-04-07	2,649.08
2020-04-08	2,046.87	2019-04-08	1,539.21
2020-04-09	2,106.97	2019-04-09	1,563.96
...	...	...	...

ここまでだと、「多少読みやすくなったけどこのくらい単純なら普通に慣れてるサブクエリ使いたいなあ」と思いそうです。
しかし、これは綺麗なデータだから単純なクエリで解決できたわけで実際には手動で集計しているデータなどは歯抜けやノイズになるデータがあり、沼ります...
そこでCTEで書いておけば、可読性も高まり、柔軟に対応できるようになります（少なくとも筆者はそう感じました）

例えばよくあるものとして歯抜けのデータを再現するため、2022-04-06のデータを削除してみます。
その上で同じクエリを流した結果がこちら

CURRENT_DATE	CURRENT_TOTAL	PREVIOUS_DATE	PREVIOUS_TOTAL
2020-04-01	906.28	2019-04-01	796.83
2020-04-02	2,155.35	2019-04-02	1,629.97
2020-04-03	1,433.78	2019-04-03	1,496.84
2020-04-04	1,909.82	2019-04-04	1,348.54
2020-04-05	1,761.23	2019-04-05	1,759.65
2020-04-07	1,384.37	2019-04-07	2,649.08
2020-04-08	2,046.87	2019-04-08	1,539.21
2020-04-09	2,106.97	2019-04-09	1,563.96
...	...	...	...

2019-04-06のデータが集計されない...

そうなんです。このクエリだと、DATEADDを使っているため、前年同日のデータも集計してくれないんです。
でも大丈夫！CTEなら、下記のように書き加えれば良いんです。

with
tmp as (
    select 0 generate_series 
    from DUAL 
    where (@num:=1-1)*0 
    union ALL
    select @num:=@num-1 
    from `information_schema`.COLUMNS
    LIMIT 1000
)
, dates as (
    select to_char(DATEADD('DAY',tmp.generate_series - 1, '2020-04-20'), 'yyyy-mm-dd') as date
    from tmp
)
, items as (
    select 
        to_char(CREATED_AT, 'yyyy-mm-dd') as created_at
        , sum(TOTAL) as total
    from 
        ORDERS
    group by created_at
    order by created_at
)
, items2 as (
    select
        dates.date as created_at 
        , items.total as total
    from 
        dates
    left join 
        items
    on
        dates.date = items.created_at
)
select 
    cur_items.created_at as current_date
    , cur_items.total as current_total
    , prev_items.created_at as previous_date
    , prev_items.total as previous_total
from items2 as cur_items
join items2 as prev_items
on cur_items.created_at = DATEADD('YEAR', 1, prev_items.created_at)
where cur_items.created_at >= '2020-04-01'

日付配列を作ってやればいい！という強行手段です。BQならUNNESTとGENERATE_DATE_ARRAYでもっと簡単に解決できそうです。普通のサブクエリを駆使してゴニョゴニョすると、もっと煩雑になりそうですが、CTEを使ってステップに分けることで、わかりやすくなっています！

終わりに

どうでしょうか？データ分析の際に、集計されたデータが汚くて沼にハマる...を駆け出しエンジニアなりにたくさん経験してきましたが、その度にCTEに救われていた気がします。
誰かの役に立てれば幸いです！