こんにちは、アルファドライブのいわたです。
この記事は「AlphaDrive Advent Calendar 2023」の6日目のエントリーです。
本日はSQLで簡単なデータ分析を行った時のTIPSについて書きます。
実装に使用した環境はPostgreSQL 13.7です。
記述したSQLは分かりやすく変更しています。
はじめに
RDBなどのデータに対して、SQLでピボット的にクロス集計をしたい、というシーンはないでしょうか？
例えば、以下のようなテーブル（アクティビティデータという名称とします）があり、Webサービス上でユーザーが特定のアクティビティを行った時にデータが生成されるものとします。（だいぶ簡略化しています）
| ユーザーID | データ作成日 |
|---|---|
| 1 | 2023-12-01 |
| 2 | 2023-12-02 |
| 3 | 2023-12-02 |
| 1 | 2023-12-03 |
| 2 | 2023-12-02 |
SQLを書いてみる
以下のように集計して分析しやすくしたいと考えました。
ひとつのユーザーIDは一行にまとめ、縦に持っている項目を横持ちに展開するイメージです。
| ユーザーID | 2023-12-01 | 2023-12-02 | 2023-12-03 |
|---|---|---|---|
| 1 | 1 | 0 | 1 |
| 2 | 0 | 2 | 0 |
| 3 | 0 | 1 | 0 |
試行錯誤した結果、以下のように書くことで上記のイメージに沿ったデータを取得することができました。
SELECT "ユーザーID",
SUM(CASE WHEN "データ作成日" = '2023-12-01' THEN 1 ELSE 0 END) AS '2023-12-01',
SUM(CASE WHEN "データ作成日" = '2023-12-02' THEN 1 ELSE 0 END) AS '2023-12-02',
SUM(CASE WHEN "データ作成日" = '2023-12-03' THEN 1 ELSE 0 END) AS '2023-12-03'
FROM "アクティビティデータ"
GROUP BY "ユーザーID";
直感的に、ユーザーごとにGROUP BY を行うことが必要になると考えられると思います。
CASEを使用してサマリーすることでGROUP BYした時に複数行になる状態から変えることができました。
アレンジ
上記ではデータの件数を取得していますが、データの有無を端的に見たい場合は以下のように書くことが出来ます。
MAX(CASE WHEN "データ作成日" = '2023-12-01' THEN 1 ELSE 0 END) AS '2023-12-01',
件数0の場合を空欄にて視覚的にはっきりと見せたい場合は以下のように書くことも出来ます。
MAX(CASE WHEN "データ作成日" = '2023-12-01' THEN '○' ELSE null END) AS '2023-12-01',
上記のように書いた場合、以下の結果になります。
| ユーザーID | 2023-12-01 | 2023-12-02 | 2023-12-03 |
|---|---|---|---|
| 1 | ○ | ○ | |
| 2 | ○ | ||
| 3 | ○ |
上記以外の実現方法
今回のSQLを書いたあと、同様のことを実現する方法はないか調べてみました。
以下それぞれのRDBではビルトインの仕組みがあり、より簡単に書くことができそうです。
- PostgreSQL
- crosstab
- SQL Server
- pivot
- Oracle
- pivot
用途に応じて適切に使用しデータ分析を行うことが重要だと思いました。