質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
BigQuery

BigQueryは、Google Cloud Platformが提供しているビッグデータ解析サービス。数TB(テラバイト)またはPB(ペタバイト)の膨大なデータに対し、SQL風のクエリを実行し、高速で集計・分析を行うサービスです。

Q&A

解決済

2回答

917閲覧

BigQueryで日毎のユニークユーザ数と通算のユニークユーザ数を集計したい

sysder

総合スコア25

BigQuery

BigQueryは、Google Cloud Platformが提供しているビッグデータ解析サービス。数TB(テラバイト)またはPB(ペタバイト)の膨大なデータに対し、SQL風のクエリを実行し、高速で集計・分析を行うサービスです。

0グッド

0クリップ

投稿2023/04/10 09:39

編集2023/04/11 20:49

実現したいこと

下記のtable_Aから、

user_iddate
12023-01-01
22023-01-01
12023-01-02
32023-01-02
12023-01-03
32023-01-03

下記のように集計したいです。

date日毎のUU通算のUU
2023-01-0122
2023-01-0223
2023-01-0323

クエリを書いてみたのですが下記ですと毎日のUUの積み重ねになってしまいます。

select date, count(distinct user_id) as 毎日のUU, sum(count(distinct user_id)) over(order by date rows unbounded preceding) as 通算のUU from table_A group by date

table_Aに初めて登場したuser_idに1が入っているカラム「first_flag」を追加してそのカラムを集計するようにしてみました。

table_A_added_first_flag

user_iddatefirst_flag
12023-01-011
22023-01-011
12023-01-020
32023-01-021
12023-01-030
32023-01-030
select date, count(distinct user_id), sum(first_flag) over(order by date rows unbounded preceding) from table_A_added_first_flag group by date

しかしfirst_flagでgroup byしなさいというエラーが出ます。
group byにfirst_flagを追加すると、集計がばらけてしまいます。

毎日のUUと通算のUUを別々に集計して、dateでinner joinして一つのテーブルにするしか方法はないでしょうか。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

自己解決

select date, count(distinct user_id), sum(sum(first_flag)) over(order by date rows unbounded preceding) from table_A_added_first_flag group by date

投稿2023/04/11 11:49

sysder

総合スコア25

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

累計する為にはuser_idの重複を省かないと駄目なので、一度に取得する為にはwindow関数ではなくサブクエリーの方が簡潔だと思います。

SQL

1select date 2 , count(distinct user_id) 3 , (select count(distinct user_id) from table_a where date<=ta.date) 4from table_A ta 5group by date

投稿2023/04/11 00:36

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sysder

2023/04/11 11:17 編集

ご回答ありがとうございます。 クエリを試してみたところ、小さいデータでは動くのですが、大きいデータになると下記のエラーになりました。 Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN. そこで相関サブクエリを使わないように、table_Aに初めて登場したuser_idに1が入っているカラム「first_flag」を追加して下記のように書き換えたところ、first_flagでgroup byしなさいというエラーが出ます。 これはなぜなんでしょうか?countするために必要ということでしょうか? 当たり前なのですがgroup byにfirst_flagを追加すると、集計がばらけてしまいます。 select date, count(distinct user_id), sum(first_flag) over(order by date rows unbounded preceding) from table_A_added_first_flag group by date
sysder

2023/04/11 11:48

下記のクエリで解決いたしました。 select date, count(distinct user_id), sum(sum(first_flag)) over(order by date rows unbounded preceding) from table_A_added_first_flag group by date 改めましてご回答ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問