累計UU数を時系列グラフで表現

仕様ツール

Firebase
BigQuery
データポータル

実現したい事

アプリの起動UUをリリースからの積上推移を日次で出したい

日時	ユーザー（uu)
3/1のアプリ起動ユーザー	Aさん、Bさん
3/1のアプリ起動ユーザー	Aさん、Cさん、Dさん
3/1のアプリ起動ユーザー	Aさん、Eさん

というデータがある場合、

日時	ユーザー（uu)
3/1	2uu
3/1	4uu
3/1	5uu

のように、「該当日まで何人が個別カウントされたか」という表現。

データポータルでのグラフイメージ
日々の積み上げ推移が分かるように。※データポータルのみの機能では現在実現不可

元々使用していたクエリだとレコード数がかなり多かった為、書き直し。
前後比較して頂けたらと。

修正前クエリ

qiita.sql

with mst_session_users as (
    select
        date(timestamp_micros(event_timestamp), 'Asia/Tokyo') as session_date
        ,user_pseudo_id
    from `テーブル名`
        where _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    and event_name = "session_start"
    group by 1,2
)
, mst_base_date as (
    select
        date(timestamp_micros(event_timestamp), 'Asia/Tokyo') as base_date
        ,user_pseudo_id
    from `テーブル名`
    where _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    and event_name = "session_start"
    group by 1,2
)
, mst_cumulative as (
    select
        a.base_date
        , b.session_date
        , b.user_pseudo_id
    from mst_base_date as a
    inner join mst_session_users as b
    on a.base_date >= b.session_date
)
select
    base_date
    , count(distinct user_pseudo_id) as users
from mst_cumulative
group by 1
order by 1

修正後クエリ

qiita.sql

with mst_session_users as (
    SELECT
        user_pseudo_id
        ,MIN(date(timestamp_micros(event_timestamp), 'Asia/Tokyo')) AS start_date -- 期間内の最初の日付
    FROM `テーブル名`
    WHERE 
        _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
        AND 
        event_name="session_start"
    GROUP BY 1

), tmp AS (
    SELECT
        start_date,
        COUNT(user_pseudo_id) AS uu_tmp
    FROM mst_session_users    
    GROUP BY 1
)

SELECT
    *,
    SUM(uu_tmp) OVER (ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS users
FROM tmp
ORDER BY 1

修正部分

修正前WITH句のmst_cumulativeでの処理に相当な負荷がかかっていた
※このWITH句で行数が一気に増えることでデータポータル反映時に処理落ちしてしまっていた。