[BigQuery]前日や前月との差異を求めるためにLAG関数を使ってみた

#ショートブログ

#Google BigQuery

森亮介

2024.01.12

売上やページビューなど、いろんな種類の数を集計するとき、前日との差異を求めることがあったので、どういう方法を使ったのかを書きます。

データ自体は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 で求めました。