質問編集履歴

3 saziさんの回答を参考に書いたコードの追記。

akm2929

akm2929 score 12

2018/11/21 15:02  投稿

日付毎にその日から一週間の間のアクティブユーザー数を集計したい
完成イメージ↓
|日付|WAU|MAU|
|:--|:--:|--:|
|2018/09/09|200|500|
|2018/09/10|180|480|
WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数
何を使ってどう集計すればいいのでしょうか?
ちなみにDAUは以下のように出しています。
```standardsql
with T as (
   select
     parse_date("%Y%m%d", event_date) event_date
     , user_id
     , event_name
   from
     `sample*`
   where(
     _TABLE_SUFFIX BETWEEN
      "20180707" AND
     FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
   )
   and
       app_info.install_source = 'iTunes'
   and
       date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
   and
       user_id is not null
)
,dau as (
 select
 event_date
 ,count(distinct(user_id)) as dau_count
 from
 T
 group by
 event_date
)
```
user_idはイベント毎に計測されるため、元データでは重複があります。
user_idはイベント毎に計測されるため、元データでは重複があります。
<↓saziさんの回答を自分なりに解釈して書いたコード(コピペだとエラーで、saziさんの書き方が高度でどう変えればいいかわかりませんでした。。。)>
エラーはないのですが、dau、wau、mauの数字がほぼ同じになってしまいました。
```standardsql
with T as (
   select
     parse_date("%Y%m%d", event_date) event_date
     , user_id
     , event_name
   from
     `sample*`
   where(
     _TABLE_SUFFIX BETWEEN
      "20180707" AND
     FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
   )
   and
       app_info.install_source = 'iTunes'
   and
       date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
   and
       user_id is not null
   limit 10000000
)
, dau as (
 select
   event_date
   ,count(distinct(user_id)) as dau_count
 from
   T
 group by
   event_date
)
, wau as (
 select
    event_date
   ,count(distinct(user_id)) as wau_count
 from
   T
 where
   event_date between t.event_date and date_add(t.event_date,interval 7 day)
 group by event_date
)
, mau as (
 select
   event_date
   ,count(distinct(user_id)) as mau_count
 from
   T
 where
   event_date between t.event_date and date_add(t.event_date,interval 1 month)
 group by event_date
)
, before_formating as (
 select
   dau.event_date
   ,dau_count
   ,wau_count
   ,mau_count
 from
   dau
 left join
   wau
 on
   dau.event_date = wau.event_date
 left join
   mau
 on
   dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc
```
  • SQL

    5747 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    218 questions

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

2 user_idの状態に関して追記

akm2929

akm2929 score 12

2018/11/19 18:01  投稿

日付毎にその日から一週間の間のアクティブユーザー数を集計したい
完成イメージ↓
|日付|WAU|MAU|
|:--|:--:|--:|
|2018/09/09|200|500|
|2018/09/10|180|480|
WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数
何を使ってどう集計すればいいのでしょうか?
ちなみにDAUは以下のように出しています。
```standardsql
with T as (
   select
     parse_date("%Y%m%d", event_date) event_date
     , user_id
     , event_name
   from
     `sample*`
   where(
     _TABLE_SUFFIX BETWEEN
      "20180707" AND
     FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
   )
   and
       app_info.install_source = 'iTunes'
   and
       date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
   and
       user_id is not null
)
,dau as (
 select
 event_date
 ,count(distinct(user_id)) as dau_count
 from
 T
 group by
 event_date
)
```
```
user_idはイベント毎に計測されるため、元データでは重複があります。
  • SQL

    5747 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    218 questions

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

1 Tテーブルの追加

akm2929

akm2929 score 12

2018/11/19 17:59  投稿

日付毎にその日から一週間の間のアクティブユーザー数を集計したい
完成イメージ↓
|日付|WAU|MAU|
|:--|:--:|--:|
|2018/09/09|200|500|
|2018/09/10|180|480|
WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数
何を使ってどう集計すればいいのでしょうか?
ちなみにDAUは以下のように出しています。
```standardsql
with T as (  
   select  
     parse_date("%Y%m%d", event_date) event_date  
     , user_id  
     , event_name  
   from  
     `sample*`  
   where(  
     _TABLE_SUFFIX BETWEEN  
      "20180707" AND  
     FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))  
   )  
   and  
       app_info.install_source = 'iTunes'  
   and  
       date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"  
   and  
       user_id is not null  
)  
 
,dau as (
 select
 event_date
 ,count(distinct(user_id)) as dau_count
 from
 T
 group by
 event_date
)
```
  • SQL

    5747 questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    218 questions

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

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る