売上やページビューなど、いろんな種類の数を集計するとき、前日との差異を求めることがあったので、どういう方法を使ったのかを書きます。
データ自体はBigQueryに保存されています。
完成イメージ
とある商品の売上に関する情報をもとにし、この画像のような感じのデータをSQLで行うことが目標です。
方法
調べたところ、BigQeuryにはLAGという関数が用意されてるので、それを使いました。
LAGはBigQueryのウィンドウ関数の1つで、同じ結果セット内の前の行からデータにアクセスできるようにします。現在の行と前の行の差を計算するために使用されます。 別のカラムにアクセスしてデータを抽出するものはナビゲーション関数と呼ばれています。
使ってみる
分析元のデータ
本記事ではサンプルデータで行います。
WITH sales AS
(SELECT 'みかん' as name,
TIMESTAMP '2024-01-01 2:51:45' as closing_time,
1000 as amount
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-02 2:54:11', 1100
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-03 2:59:01', 1200
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-04 3:01:17', 900
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-05 3:05:42', 800
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-06 3:06:24', 1000
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-07 3:06:36', 1100
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-08 3:07:41', 700
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-09 3:08:58', 700
UNION ALL SELECT 'みかん', TIMESTAMP '2024-01-10 3:10:14', 1000
)
select * from sales
;
これをベースにして分析していきます。
前日のデータにアクセスする
日付ごとにデータが存在しているので、その1行ごとに前日の売上高のカラムを追加してみます。
前の行から列の値を取得する場合は、
LAG(column_name, 1)を使用します。第二引数には何行前を参照するかで、デフォルトは１です。
実際に前日のデータを参照するなら、以下のようなクエリを使って求めることができました。
OVER句は行の処理順序を指定するために使用しています。
SELECT
name,
DATE(closing_time) as closing_date,
amount,
LAG(amount, 1) OVER (ORDER BY closing_time) AS prev_amount
FROM
salesクエリを実行すると、以下のように前日のamountの値が追加されていました。
2024-01-01以前のデータはないので、2024-01-01の行のprev_amountはnullになりましたね。
前日との差分
前日のデータを参照できたので、差分も計算してみます。
SELECT
name,
DATE(closing_time) as closing_date,
amount,
LAG(amount, 1) OVER (ORDER BY closing_time) AS prev_amount,
amount - LAG(amount, 1) OVER (ORDER BY closing_time) AS diff_prev
FROM
sales売上高のカラムから、先ほど求めた前日の売上高カラムを引いてあげれば差分が計算されます。
おまけで前日比も求めてみた
SELECT
name,
DATE(closing_time) as closing_date,
amount,
LAG(amount, 1) OVER (ORDER BY closing_time) AS prev_amount,
amount - LAG(amount, 1) OVER (ORDER BY closing_time) AS diff_prev,
ROUND((amount / LAG(amount, 1) OVER (ORDER BY closing_time)) * 100 , 2) AS rate_prev,
FROM
sales(当日の売上高 / 前日の売上高) * 100 で求めました。
LAG関数を使えばデータをずらすことができるので、集計する際に役に立つことが多いので覚えておきたいですね。覚えておきたいですね。
ウィンドウ関数は使わないとすぐ忘れてしまう。。。