SQLによる時系列分析 #SQL

環境
データベース:PostgreSQL ver.15.1.1
実行環境：pgAdmin4-6.19

グラフについては、pgAdminにグラフを作成する機能があるので、そちらを使用。
元々PythonやExcelで視覚化を試みていたが、pgAdminではGUI上で簡単に生成してくれることに気づいた。

1.分析前の基礎知識

1-1. 日付、日時、時間の操作

日付と日時はデータソースにより、様々なフォーマットがあるため、変換して揃える必要がある。

1-1-1.タイムゾーン

世界中には様々なタイムゾーンがあるため、それを無視して集計などすると時間がズレて正確な分析ができないことがある。例えば、売上の比較では時間のズレにより、売上を過小評価したり、過大評価してしまう。

多くのデータベースでは、協定世界時（UTC）が基準となっている。

タイムゾーン情報は以下のクエリで取得可能

SELECT * FROM pg_timezone_names

SELECT COUNT(*) FROM pg_timezone_names

596もあるらしい。

タイムゾーンを試しにpstに変換してみる。

SELECT '2023-01-01 00:00:00' at time zone 'pst'

時間が17時間早くなっているのが確認できる。
　

1-1-2. 日付とタイムスタンプのフォーマット

以下クエリで様々な時間情報を取得できる。

DATEを取得

SELECT current_date

TIMESTAMPを取得

SELECT current_timestamp

日時の変換

SELECT date_trunc('month', '2023-01-01 12:33:35'::timestamp)

date_part関数で年月日の値を取得できる。

select date_part('day', current_timestamp)

1/31に実行した場合

例えば、'day'を'month'に変えれば月が、'year'にすれば年の部分のみが取得できる。

日付の計算

単純にプラスやマイナスで計算できる。

SELECT date('2023-02-27') - date('2023-02-22') AS days

2.実際に時系列分析を実施

データについて

データ概要：米国小売業売上高データ
テーブル名：sales_retail
データ数：22620レコード

テーブル作成とデータインポート

DROP table if exists retail_sales;
CREATE table retail_sales
(
sales_month date
,naics_code varchar
,kind_of_business varchar
,reason_for_null varchar
,sales decimal
)

COPY retail_sales 
FROM '/Users/パス/ファイル名' 
DELIMITER ','
CSV HEADER

SELECT * FROM retail_sales

クエリ結果についてはレコード数が多いため、一部のみ表示。
kind_of_businessは商品カテゴリを表している。

2-1.単純なトレンドを確認

毎年の小売および食品サービス売上高のトレンド(単位：ドル)
試しに食品関連の売上データを見てみる。

SELECT 
    date_part('year', sales_month) as sales_year
,   SUM(sales) AS sales
FROM retail_sales
WHERE kind_of_business = 'Retail and food services sales, total'
GROUP BY sales_year
ORDER BY sales_year

本来はインフレ率によって調整をしなければならないが、今回は実施していない。
グラフを見ると安定して増加しているのが見て取れる。2020年はパンデミックが始まった年だが、食品は生活必需品のため大きな影響を受けていないと思われる。

2-2.移動計算

概要：
データを平滑化するために複数の期間を考慮。
株式市場、マクロ経済トレンド、視聴率などに適用。
計算方法は移動平均が最も一般的。

アクティブユーザーの測定
・DAU:Daily Actibe Users
日間アクティブユーザー数で分析。ピーク時の時間ごと、分ごとのユーザー情報が必要。
・MAU:Monthly Active Users
月間アクティブユーザー数を分析。アプリケーショやサービスの相対的なサイズを見積もるのに使用できる。
曜日ごとにユーザー利用頻度が異なる場合など、非定期的パターンを測定するのに役立つ。
・WAU:Weekly Active Users
週間アクティブユーザー数を分析。DAUとMAUの中間的。

12ヶ月のウィンドウを使用した移動年間売上高
移動平均が必要なのでAVG関数を使っている。
自己結合によって作ったt2テーブルから返されるレコードのCOUNTを入れて、各行を12のデータポイントで平均している。

SELECT 
    t1.sales_month
,   t1.sales
,   AVG(t2.sales) AS moving_avg
,   COUNT(t2.sales) AS records_count
FROM retail_sales AS t1
JOIN retail_sales AS t2 ON(
        t1.kind_of_business = t2.kind_of_business
	AND t2.sales_month BETWEEN t1.sales_month - interval '11 months'
	AND t1.sales_month
	AND t2.kind_of_business = 'Women''s clothing stores'
)
WHERE t1.kind_of_business = 'Women''s clothing stores'
  AND t1.sales_month >= '1993-01-01'
GROUP BY 
    t1.sales_month
,   t1.sales
ORDER BY
    t1.sales_month
,   t1.sales

2-3.季節性の分析

書籍の対月比売上を調査
前年比や前月比やといったある期間を過去の期間と比較する場合、時系列から前の値を返すlag関数が使える。
lagはそのまま使用するとデータ全体を振り返るので、PARTITION BY句+ORDER BY句で指定をする必要がある。

SELECT 
    kind_of_business
,   sales_month
,   sales
,   ROUND((sales / LAG(sales) OVER (PARTITION BY kind_of_business 
							  ORDER BY sales_month) -1) * 100 , 3) AS 対前月比
FROM retail_sales
WHERE kind_of_business = 'Book stores'
  AND sales_month >= '2018-01-01'

8月の売上が一気に増えているが、米国は9月始業のため、教科書などがよく売れていると思われる。

書籍の前年同月比売上を調査
absolute_diffとして、前年同月からの増加分を算出。
lagを使用し、売上/前年同月売上で前年同月比を算出。
そのままだと小数点以下が長々と続いたのでroundで桁数を指定。

SELECT
    sales_month
,   sales
,   sales - lag(sales) OVER (PARTITION BY date_part('month', sales_month)
							 ORDER BY sales_month) AS absolute_diff
,   ROUND((sales / lag(sales) OVER (PARTITION BY date_part('month', sales_month)
							 ORDER BY sales_month)-1) * 100 , 3) AS 前年同月比
FROM retail_sales
WHERE kind_of_business = 'Book stores'

補足：ウィンドウ関数
PARTITION BYなどのウィンドウ関数は、データを何らかの基準に従ってグループ分けする関数。GROUP BYもPARTITION BYもテーブルを指定されたキーで分割するが、違いは、GROUP BYは分割後に集約して１行にまとめる操作が入るだけ。
ウィンドウ関数の詳しい解説は下記。
window関数を使いこなす〜分析のためのSQL〜
SQL PARTITION BYの基本と効率的に集計する便利な方法

形を変えてみる

SELECT
    date_part('month', sales_month) AS month_number
,   to_char(sales_month, 'Month') AS month_name
,   max(CASE WHEN date_part('year', sales_month) = 2018 
	         THEN sales
	         END) AS sales_2018
,   max(CASE WHEN date_part('year', sales_month) = 2019 
	         THEN sales
	         END) AS sales_2019
,   max(CASE WHEN date_part('year', sales_month) = 2020
	         THEN sales
	         END) AS sales_2020
FROM retail_sales
WHERE kind_of_business = 'Book stores'
  AND sales_month BETWEEN '2018-01-01' AND '2020-12-01'
GROUP BY
    month_number
,   month_name

どの年も1月、8月、12月で売上が伸びているのが分かる。

参考図書：SQLではじめるデータ分析
学習用に、クエリは一部改変してます。