概要
日毎の売上実績テーブルを自己結合し、前日との売上比較を行うための 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 を返したり、もう片方のテーブルの値を返すようにしています。