日付毎にその日から一週間の間のアクティブユーザー数を集計したい

Question

完成イメージ↓

日付	WAU	MAU
2018/09/09	200	500
2018/09/10	180	480

WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数

何を使ってどう集計すればいいのでしょうか？
ちなみにDAUは以下のように出しています。

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はイベント毎に計測されるため、元データでは重複があります。

＜↓saziさんの回答を自分なりに解釈して書いたコード（コピペだとエラーで、saziさんの書き方が高度でどう変えればいいかわかりませんでした。。。）＞
エラーはないのですが、dau、wau、mauの数字がほぼ同じになってしまいました。

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

Comments

[yambejp]

Tテーブルのサンプルデータを提示して下さい。とくにuser_idはevent_date毎にユニークなのかそれとも1日何度もログインできるのか提示してください

[akm2929]

すいません、追記させていただきました。user_idは一日に複数回カウントされます。不足失礼しました。

Answer 1

試せてませんが、DATE_ADDを使用すると以下のようになるかと。

  select event_date
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -7 DAY)) 
         ) as wau_count 
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -1 MONTH)) 
         ) as mau_count 
from (
  select event_date
  from  T
  group by event_date
) t1

追記

追加された記述より展開

with T as (
    select
        date(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
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 7 day))
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 1 month))
  group by base_date.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

BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
BigQueryで相関サブクエリがうまく動かなかった話

追記

予め、範囲となる日付を求めておくように修正

with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_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
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.wau_range_date
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.mau_range_date
  group by base_date.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

単にユーザーのIDをカウントするだけなので、シンプルになりそう。

with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_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
)
select
    dau.event_date
  , count(distinct(dau.user_id)) as dau_count
  , count(distinct(wau.user_id)) as wau_count
  , count(distinct(mau.user_id)) as mau_count
from  T as dau
      inner join T as wau
      on  dau.event_date between wau.event_date and wau.wau_range_date
      inner join T as mau
      on  dau.event_date between mau.event_date and mau.wau_range_date
group by dau.event_date
order by dau.event_date asc

Comments

[akm2929]

ご回答ありがとうございます。
初心者でして、saziさんの書き方がどういうロジックになっているのかわからず、自分なりにやってみたのですが（質問文内に追記させていただきました）、エラーはでないのですが、dau、wau、mauがほぼ同じ数字になってしまいました。
どこがだめでどうすればいいのかがわかりません…
よろしければご指導いただけると幸いです。
（saziさんの書き方が高度すぎたので、一応コピペは試してはみたのですが、コピペだとやはりエラーでした。）

[sazi]

最終的なbefore_formatingのwau_countやmau_countはそのevent_dateを基準にした範囲の集計である必要があります。
withでそれぞれ計算されているのは、範囲は指定されていますが、結局event_date毎にしか集計されていませんね。

[sazi]

コピペした時のエラーはどういった内容ですか？
DATE_ADDはTIMESTAMP型でないと駄目なので、そのエラーでしたら変換を掛ければ良いと思います。

[akm2929]

```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
)

select
event_date
, (select count(distinct(user_id)) from T
where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -7 DAY))
as wau_count
, (select count(distinct(user_id)) from T
where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -1 MONTH))
as mau_count
from (
select event_date
from T
group by event_date
) t1```
このコードで試し、
Error running query: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
というエラーがでてきました…

[sazi]

そのSQLは全文じゃないでしょう？
エラーであるleft join はどこにも使用されていませんから

[sazi]

あ、サブクエリーで出ているみたいですね。
ちょっと待って下さいね。

[akm2929]

私もそう思ったのですが、withTから始まるこれが全文になります…
実物と変えた点は、
`sample*`
の部分のみになります。

[akm2929]

ありがとうございます…！

[akm2929]

詳細な回答ありがとうございました！！

[sazi]

最後ので、上手くいきましたか？