---
title: Active User Extraction SQL Analysis
tags: 
author: [渋谷TAIZI](https://image.docswell.com/user/4514287294)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/47MYYPVP7W.jpg?width=480
description: Active User Extraction SQL Analysis by 渋谷TAIZI
published: May 04, 26
canonical: https://image.docswell.com/s/4514287294/KY8EVV-2026-05-04-123103
---
# Page. 1

![Page Image](https://bcdn.docswell.com/page/47MYYPVP7W.jpg)

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

# Page. 2

![Page Image](https://bcdn.docswell.com/page/P7R9912YE9.jpg)

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. 集計

# Page. 3

![Page Image](https://bcdn.docswell.com/page/PJXQQYD47X.jpg)

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

# Page. 4

![Page Image](https://bcdn.docswell.com/page/3JK993XPJD.jpg)

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

# Page. 5

![Page Image](https://bcdn.docswell.com/page/LE3WWG54E5.jpg)

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

# Page. 6

![Page Image](https://bcdn.docswell.com/page/8EDKK1D57G.jpg)

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...'

# Page. 7

![Page Image](https://bcdn.docswell.com/page/V7PKKQXDJ8.jpg)

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

# Page. 8

![Page Image](https://bcdn.docswell.com/page/2JVVV5LGJQ.jpg)

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...'

# Page. 9

![Page Image](https://bcdn.docswell.com/page/5EGLL63DJL.jpg)

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

# Page. 10

![Page Image](https://bcdn.docswell.com/page/4JQYY45X7P.jpg)

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
(昇順)

# Page. 11

![Page Image](https://bcdn.docswell.com/page/K74WWQ92E1.jpg)

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

# Page. 12

![Page Image](https://bcdn.docswell.com/page/LJ1YYPKKEG.jpg)

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