Active User Extraction SQL Analysis

Active User Extraction SQL Analysis BigQueryにおけるデイリーアクティブユーザー（DAU）集計クエリの構造的解剖 For Data Analysts & Backend Engineers

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331'); WITH Active_UU AS ( SELECT ymd, COUNT(uuid) AS _active_uu FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id AS uuid FROM <project>.<dataset>.events_* WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to() AND is_active_user GROUP BY ymd, uuid ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM Active_UU クエリの全体像とミッション このクエリの目的は、「特定の期間における、日別の有効ユーザー数（DAU）を正確にカウントすること」です。単なる集計ではなく、BigQueryのパフォーマンスを最適化し、データの重複を防ぐ堅牢な構造を持っています。 次ページより、データがどのように処理されるか、パイプラインの順を追って解剖します。 1. 変数定義 2. 抽出・ フィルタ 3. 重複排除 4. 集計

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 1: 動的期間の定義（メンテナンス性の確保）
抽出対象の開始日と終了日をTEMP FUNCTIONとして定義。クエリ本体に日付をハードコード（直書き）することを避け、再利用時や自動化パイプラインに組み込む際の保守性を高めるモダンな設計アプローチです。
CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');
開始日
2026/03/01
終了日
2026/03/31
Global
Parameters

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 2: シャーディングテーブルの特定とコスト削減
日別に分割されたテーブル（events_*）に対し、先ほど定義した関数と_TABLE_SUFFIXを組み合わせて対象テーブルを限定します。これにより、不要なデータのフルスキャンを防ぎ、クエリの実行コストと時間を劇的に削減します。
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
events_20260228 events_20260301 events_20260302 events_20260303 events_20260401

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 3: 対象ユーザーの論理フィルタリング
抽出されたデータセットに対し、真偽値（Boolean）によるフィルターを適用。アクティブ条件を満たすイベントのみを通過させ、ノイズとなるデータ（非アクティブなセッションやボットなど）をこの段階で完全に弾きます。
AND is_active_user
Boolean Logic Gate
is_active_user = TRUE
Discarded

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 4: データの成形と型の最適化
集計キーとなる日付データを文字列型から正しい「DATE型（ymd）」へ変換（PARSE_DATE）。同時に、冗長なカラム名user_pseudo_idを扱いやすいuuidへとエイリアスし、後続の処理をシンプルにします。
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
Raw Data
event_date (String): '20260301'
user_pseudo_id: 'xyz123...'
PARSE_DATE
& Alias
Clean Data
ymd (Date): 2026-03-01
uuid: 'xyz123...'

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 5: 同一ユーザーの重複排除（最も重要なステップ）
同一ユーザー（uuid）が同じ日（ymd）に複数回のイベントを発生させた場合、そのままカウントするとUUが過剰に算出されてしまいます。ここで一度GROUP BYをかけることで、「1日につき1ユーザー=1行」へと圧縮し、一意性を担保します。
GROUP BY ymd, uuid
ymd: 2026-03-01 | uuid: UserA
ymd: 2026-03-01 | uuid: UserA
ymd: 2026-03-01 | uuid: UserA
GROUP BY
ymd, uuid
ymd: 2026-03-01 | uuid: UserA 1 Unique Row

CREATE TEMP FUNCTION date_from()
RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to()
RETURNS STRING AS ('20260331');

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 6: CTEによるロジックのカプセル化
これまでの工程で作成した「重複のない日別ユニークユーザーの束」を、Active_UUという名前の一時的な仮想テーブル（CTE）に格納します。ネストを深くせず、クエリの可読性とモジュール性を劇的に向上させる構造です。
WITH Active_UU AS (
-- (抽出・重複排除されたクリーンなデータ)
)
Virtual Table: Active_UU
ymd: 2026-03-01 | uuid: 'xyz123...'
ymd: 2026-03-01 | uuid: 'xyz123...'
ymd: 2026-03-01 | uuid: 'xyz123...'
ymd: 2026-03-01 | uuid: 'xyz123...'
ymd: 2026-03-01 | uuid: 'xyz123...'

SELECT ymd, COUNT(uuid) AS _active_uui SELECT ( ymd: 2026-03-01 WERE (dataset as .ymd(date) .scanner(datast) .ymd ==(uuid) AS COUNT(uuid) = 14 AS _active ) FROM concrate_mant = (uuid) AS _active_uu current_mates = (uuid) AS _active FDIT ymd (ymd_mates) WHER data = Active_uuid ) SELECT ymd, COUNT(uuid) AS _active_uu ... GROUP BY ymd SELECT select ymd date_ymd: 2026-03-01 SELECT regmm_cost フェーズ 7: 日別の最終集計（DAUの算出） カプセル化されたクリーンなデータセットに対し、日付（ymd）ごとにユーザー（uuid）の数をカウント（COUNT）します。事前の重複排除が完了しているため、ここで算出される数値は正確なデイリーアクティブユーザー数（DAU）となります。 SELECT ymd, COUNT(uuid) AS _active_uu ... GROUP BY ymd Active_UU Dataset ymd: 2026-03-01 _active_uu: 452

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS _active_uu
FROM (
SELECT PARSE_DATE('%Y%m%d', event_date)
AS ymd, user_pseudo_id AS uuid
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

フェーズ 8: 時系列順のソート
集計された結果は順序が保証されていないため、ORDER BY ymd ASC（昇順）を指定します。時系列分析やBIツールでの可視化において、日付順に整列されたデータを出力することは必須のプロセスです。
ORDER BY ymd ASC
03-15 | 390
03-02 | 412
03-30 | 501
03-01 | 452
03-02 | 412
03-03 | 420
ASC
(昇順)

SELECT erimmpmap SELECT * FROM Active_UU AS data_id, _active_uu FROM _active_uu AS (ymd, _active_uu) SELECT * FROM Active_UU FROM SELECT tmoc _active_uu ADD (ymd, _active_uu) WHER (ymd, active_uu) AND Amgu FROM Active_UU SELECT * FROM Active_UU FROM _active_uu AND _active_uu) = 20 SELECT * FROM Active_UU SELECT * FROM Active_UU フェーズ 9: 最終結果の出力 すべての処理を終えたActive_UUテーブルから、全カラム（ymd, _active_uu）を出力します。無駄なデータが削ぎ落とされた、極めてクリーンな時系列の集計データセットの完成です。 SELECT * FROM Active_UU ymd _active_uu 2026-03-01 452 2026-03-02 412 2026-03-03 420

WITH Active_UU AS (
SELECT ymd, COUNT(uuid) AS
_active_uu
FROM (
SELECT PARSE_DATE('%Y%m
%d', event_date) AS ymd,
user_pseudo_id AS uuid
FROM <project>.
<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
AND is_active_user
GROUP BY ymd, uuid
)
GROUP BY ymd
ORDER BY ymd ASC
)

SELECT * FROM Active_UU

シンセシス: データ処理パイプラインの全容
このクエリは単なる文字列ではなく、生データを「抽出」「フィルタ」「成形」「重複排除」「集計」へと順次加工していくシステマティックな製造ラインです。構造を論理的なパイプラインとして捉えることで、複雑なSQLも直感的に設計・解読することが可能になります。
1. Raw Data
2. Filter
3. Deduplicate
4. Aggregate
5. Output
ymd _active_uu
2026-03-01 452
2026-03-02 412
2026-03-03 420