【SQL】集計でよく使うSQL構文：count/ group by/having/join/unnest/union

はじめに

BigQuery と Redash を使用してサービスの会員獲得、MAU（Monthly Active Users）、リテンション率などを分析する際によく使用する SQL 構文をいくつかまとめておく。

集計でよく使うSQL構文

SELECT – データの選択

特定の列を選択してデータを取得する。

SELECT user_id, registration_date FROM users;

WHERE – 条件指定（グループ化の前）

WHERE 句は、特定の条件を満たす行のみを選択するために使用される。

データベースからデータをグループ化する前にフィルタリングを行う場合に有効。

例）登録日が2023年1月1日以降の顧客のユーザーIDを抽出する

SELECT user_id FROM customers WHERE registration_date >= '2023-01-01';

COUNT() – データの個数をカウント

条件を満たす行の数をカウントする。

COUNT(*)は、条件に一致するすべての行をカウントする。つまり、どの列を対象にしても条件に一致する行の数をカウントする。

例）登録日が2023年1月1日以降の顧客の数を数える

SELECT COUNT(*) FROM customers WHERE registration_date >= '2023-01-01';

COUNT(id)は、指定された列（この場合はid列）の値がNULLでない行の数をカウントする。したがって、条件に一致する行のうち、id列がNULLでない行の数が返される。

例）登録日が2023年1月1日以降の顧客のうち、id列がnullでない数を数える

SELECT COUNT(id) FROM customers WHERE registration_date >= '2023-01-01';

例）登録日が2023年1月1日以降のユニークな（重複するuidを除いた）顧客の数

SELECT COUNT(DISTINCT uid) FROM customers WHERE registration_date >= '2023-01-01';

AS – 列やテーブルにエイリアス（別名）を設定する

クエリの SELECT 部分で列や式にエイリアスを設定することができる。例えば、event_timestamp から日付を抽出する場合、その結果に event_date というわかりやすい名前を付けることができる。

この例では、date(timestamp_micros(event_timestamp), "Asia/Tokyo") という式の結果に event_date というエイリアスを設定している。これにより、結果セットではこの列が event_date として表示される。

SELECT date(timestamp_micros(event_timestamp), "Asia/Tokyo") AS event_date
FROM your_table;

AS – 集計結果にエイリアスを設定する場合: 集計関数の結果にもエイリアスを設定できる。これにより、集計結果の意味が明確になる。

ここでは、count(distinct user_pseudo_id) という集計関数の結果に users というエイリアスを設定している。結果セットではこの値が users 列として表示される。

SELECT count(distinct user_pseudo_id) AS users
FROM your_table;

GROUP BY – グループ化

特定の列でグループ化し、集計を行う。例えば、ユーザーごとのアクティビティ数をカウントする場合などに使用。

もちろん、GROUP BY句で指定する列名は、SELECT句で選択された列のうちのいずれかでなければならない（そうでないと、そもそも集計できない）。

SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

HAVING – グループ化後の条件指定

GROUP BY でグループ化した後に、集計結果に対して条件を指定する。

SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > 10;

WHEREとHAVING句の違い

• WHERE：GROUP BYの実行前に条件が適応される
• HAVING：GROUP BY句の実行後に条件が適応される

例： 特定のカテゴリの商品のみの販売数をカウントする。

sales テーブルからカテゴリが ‘Electronics’ の商品のみを選択し、それらの商品の数をカウントする。WHERE 句によってグループ化の前にフィルタリングが行われるため、’Electronics’ 以外のカテゴリの商品は集計に含まれない。

SELECT category, COUNT(product_id)
FROM sales
WHERE category = 'Electronics'
GROUP BY category;

HAVING句

HAVING 句は、グループ化した結果に対して条件を適用するために使用される。これは、集計関数の結果に基づいて特定のグループを選択したい場合に便利。

例： 各カテゴリで販売された商品数が10以上のものだけを表示する。

SELECT category, COUNT(product_id)
FROM sales
GROUP BY category
HAVING COUNT(product_id) > 10;

このクエリでは、まず sales テーブルのデータをカテゴリごとにグループ化し、各カテゴリの商品数をカウントする。その後、HAVING 句を使用して、商品数が10を超えるカテゴリのみを結果に含める。ここでの HAVING 句は、GROUP BY による集計結果に対してフィルタリングを行っている。

JOIN – 結合

2つのテーブルを結合し、関連するデータを組み合わせて取得する

SELECT a.column1, b.column2 FROM table1 a JOIN table2 b ON a.common_column = b.common_column;

UNNEST関数：配列や構造化データ内の要素を展開し、単一行に変換

UNNESTは、配列や構造化データ内の要素を展開し、単一の行に変換する関数。

下記クエリでは、event_params配列を展開し、keyが’page_location’のレコードからvalue.string_valueを取得している。これにより、各イベントに関連するページロケーションが抽出される。

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,

event_params が以下のような配列を含んでいる場合

[
  {"key": "page_location", "value": {"string_value": "https://example.com/signup"}},
  {"key": "user_action", "value": {"string_value": "completed"}},
  {"key": "session_duration", "value": {"string_value": "300"}}
]

この配列を含むレコードに対して UNNEST(event_params) を行った後の状態は、key と value のペアが個別の行として展開されたものになる。

UNNEST 関数により、event_params 配列内の各オブジェクトが個別の行として扱われるようになる。これにより、key や value.string_value に基づいて特定のデータを簡単にフィルタリングしたり、集計したりすることが可能になる。

例えば、page_location の値だけを抽出したい場合は、UNNEST した結果に対して WHERE key = 'page_location' という条件を適用することで、目的の行だけを選択することができる。

event_paramsの特定のキーと値でフィルタリングする方法2つ

特定のキーと値でフィルタリングするための方法として、WITH句を使用する方法や、通常のWHERE句を使用する方法がある。

1. WITH句を使用する方法:

WITH filtered_events AS (
  SELECT
    event_name,
    DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date,
    user_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_category') as event_category,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_label') as event_label
  FROM
    `argon-depth-340314.analytics_2XXXXXXX.events_20240404`
)

SELECT *
FROM filtered_events
WHERE event_name = 'show' AND event_category = 'your_value';

上記のクエリでは、まずWITH句を使用してイベントテーブルをフィルタリングし、それからその結果を使って必要な条件でフィルタリングされたクエリを実行する。

2. 通常のWHERE句を使用する方法:

SELECT
  event_name,
  DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_date,
  user_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_category') as event_category,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_label') as event_label
FROM
  `argon-depth-340314.analytics_2XXXXXXXXX.events_20240404`
WHERE
  event_name = 'show'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_category') = 'your_value';

このクエリでは、通常のWHERE句内でサブクエリを使用して、特定のイベントカテゴリに基づいてイベントをフィルタリングしている。WITH句を用いる方が可読性が向上しやすい。

BigQueryにおけるGA4 event_paramsのUNNNESTによる展開と分析に関する記事はこちら

【BigQuery】GA4 event_paramsのUNNNESTによる展開と分析：ダメなクエリと正しいクエリ

GA4をBigQueryにExport設定して、BigQuery上でGA4データを分析する際に、よくつまずくのがGA4のevent_paramsの解析の仕方。event_paramsの中に、さらにkey-value形式の配列でデータが入れ子になっているので、集計の際にはSQLのUNNEST演算子を使って、データ内の要素を展開して単一行に変換してから集計を行う。

kazulog.fun

2024.07.01

UNION / UNION ALL：複数のSELECT結果を一つにして表示

複数のSELECT文の結果を縦に連結して、一つの結果セットにする。

• UNION：重複削除→和集合
• UNION ALL：重複も含める

指定するカラムの数と順序、データ型は同じにすること。カラム名が一致している必要はない

UNIONを利用するSQL文では、ORDER BY句は全体の最後に一度だけしか利用できない

Booksテーブル

Magazinesテーブル

エラーが発生するクエリ

エラーケース1: カラムの数が異なる

SELECT BookID, Title FROM Books
UNION ALL
SELECT MagazineID, Title, Issue FROM Magazines;

このクエリはエラーになります。Booksからは2つのカラムを選択しているのに対し、Magazinesからは3つのカラムを選択しているため。

エラーケース2: UNION ALLとORDER BYの不正な使用

SELECT BookID, Title FROM Books
ORDER BY BookID
UNION ALL
SELECT MagazineID, Title FROM Magazines
ORDER BY MagazineID;

このクエリもエラーになる。UNION ALLを使う場合、ORDER BYは最後に1回だけ指定する必要がある。

エラーを回避するクエリ

SELECT BookID, Title FROM Books
UNION ALL
SELECT MagazineID, Title FROM Magazines;