概要

日毎の売上実績テーブルを自己結合し、前日との売上比較を行うための SQL を紹介します。
実行環境は BigQuery を想定しています。

やりたいこと

下記のような、item 毎に日毎の売上を記録したテーブルがあります。

dt	item	sales
2021-01-01	A	100
2021-01-01	B	200
2021-01-02	A	800
2021-01-02	C	300
2021-01-03	A	800
2021-01-03	B	300
2021-01-03	C	1000

上記のテーブルを使い、item 毎に当日と前日の売上の差額を求めたいと思います。
つまり、下記のような抽出結果を求めることがゴールになります。

dt	item	sales	sales_1dayago	sales_diff
2021-01-01	A	100	0	100
2021-01-01	B	200	0	200
2021-01-02	A	800	100	700
2021-01-02	B	0	200	-200
2021-01-02	C	300	0	300
2021-01-03	A	800	800	0
2021-01-03	B	300	0	300
2021-01-03	C	1000	300	700

売上の変動を網羅的に把握するため、今回は前日には売上があったが当日は売上が0(元テーブルにレコードが存在しない)の場合や、前日は売上が無かったが当日は売上がある場合は集計結果に含めるようにします。

テーブル定義

集計で使用する売上テーブルを下記の通り定義します。

CREATE TABLE
 qiita.item_sales
(
 dt DATE,
 item STRING,
 sales INT64
);

INSERT qiita.item_sales (dt, item, sales)
VALUES 
(DATE("2021-01-01"), "A", 100),
(DATE("2021-01-01"), "B", 200),
(DATE("2021-01-02"), "A", 800),
(DATE("2021-01-02"), "C", 300),
(DATE("2021-01-03"), "A", 800),
(DATE("2021-01-03"), "B", 300),
(DATE("2021-01-03"), "C", 1000)
;

自己結合を使って前日比較を行う

下記SQLで所望の結果が得られます。

/*
item_sales 同士 FULL OUTER JOIN することで item 毎の売上比較を行う
*/
SELECT
  IFNULL(is1.dt, DATE_ADD(is2.dt, INTERVAL 1 DAY)) as dt,
  IFNULL(is1.item, is2.item) as item,
  IFNULL(is1.sales, 0) as sales,
  IFNULL(is2.sales, 0) as sales_1dayago,
  IFNULL(is1.sales, 0) - IFNULL(is2.sales, 0) as sales_diff
FROM
  qiita.item_sales as is1
FULL OUTER JOIN 
  qiita.item_sales as is2
ON
  is1.dt = DATE_ADD(is2.dt, INTERVAL 1 DAY)  /* 何日前と比較するか */
  and is1.item = is2.item
WHERE 
  IFNULL(is1.dt, DATE_ADD(is2.dt, INTERVAL 1 DAY)) 
  BETWEEN '2021-01-01' and '2021-01-03' 
ORDER BY 
  1,2

ポイントは大まかに下記2点です。

1. item_sales 同士を JOIN する
同じテーブル同士を JOIN し、結合条件で両者の日付を1日ずらすことで当日分と前日分の売上を紐づけています。
この書き方は応用が効きます。
is1.dt = DATE_ADD(is2.dt, INTERVAL 1 DAY) の部分を、例えば
is1.dt = DATE_ADD(is2.dt, INTERVAL 1 YEAR) と書けば、容易に昨年との比較を行うことができます。

2. FULL OUTER JOIN
前日、当日のどちらかしか売上が上がっていない item も集計結果に含めたいので、FULL OUTER JOIN で結合しています。
また、 IFNULL 関数を使い、結果が NULL だった時に 0 を返したり、もう片方のテーブルの値を返すようにしています。

参考にした書籍

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ