teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップBigQueryに関する質問日付毎にその日から一週間の間のアクティブユーザー数を集計したい

編集履歴

回答編集履歴

10

追記

2018/11/22 00:32

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -157,4 +157,34 @@
157
157
  select *
158
158
  from before_formating
159
159
  order by event_date asc
160
+ ```
161
+ 単にユーザーのIDをカウントするだけなので、シンプルになりそう。
162
+ ```SQL
163
+ with T as (
164
+ select
165
+ date(event_date) event_date
166
+ , date(date_add(event_date,interval 7 day)) wau_range_date
167
+ , date(date_add(event_date,interval 1 month)) mau_range_date
168
+ , user_id
169
+ , event_name
170
+ from `sample*`
171
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
172
+ and app_info.install_source = 'iTunes'
173
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
174
+ and user_id is not null
175
+ limit 10000000
176
+ )
177
+ select
178
+ dau.event_date
179
+ , count(distinct(dau.user_id)) as dau_count
180
+ , count(distinct(wau.user_id)) as wau_count
181
+ , count(distinct(mau.user_id)) as mau_count
182
+ from T as dau
183
+ inner join T as wau
184
+ on dau.event_date between wau.event_date and wau.wau_range_date
185
+ inner join T as mau
186
+ on dau.event_date between mau.event_date and mau.wau_range_date
187
+ group by dau.event_date
188
+ order by dau.event_date asc
189
+
160
190
  ```

9

削除

2018/11/22 00:32

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -157,37 +157,4 @@
157
157
  select *
158
158
  from before_formating
159
159
  order by event_date asc
160
- ```
161
- もっとシンプルに
162
- ```SQL
163
- with T as (
164
- select
165
- date(event_date) event_date
166
- , date(date_add(event_date,interval 7 day)) wau_range_date
167
- , date(date_add(event_date,interval 1 month)) mau_range_date
168
- , user_id
169
- , event_name
170
- from `sample*`
171
- where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
172
- and app_info.install_source = 'iTunes'
173
- and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
174
- and user_id is not null
175
- limit 10000000
176
- )
177
- , base_date as(
178
- select event_date from T group by event_date
179
- )
180
- select
181
- base_date.event_date
182
- , count(distinct(dau.user_id)) as dau_count
183
- , count(distinct(wau.user_id)) as wau_count
184
- , count(distinct(mau.user_id)) as mau_count
185
- from base_date inner join T as dau
186
- on base_date.event_date = dau.event_date
187
- inner join T as wau
188
- on base_date.event_date between wau.event_date and wau.wau_range_date
189
- inner join T as mau
190
- on base_date.event_date between mau.event_date and mau.wau_range_date
191
- group by base_date.event_date
192
- order by base_date.event_date asc
193
160
  ```

8

修正

2018/11/22 00:25

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -183,11 +183,11 @@
183
183
  , count(distinct(wau.user_id)) as wau_count
184
184
  , count(distinct(mau.user_id)) as mau_count
185
185
  from base_date inner join T as dau
186
- on base_date.event_date = dau.event_date
186
+ on base_date.event_date = dau.event_date
187
- left join T as wau
187
+ inner join T as wau
188
- on base_date.event_date between wau.event_date and wau.wau_range_date
188
+ on base_date.event_date between wau.event_date and wau.wau_range_date
189
- left join T as mau
189
+ inner join T as mau
190
- on base_date.event_date between mau.event_date and mau.wau_range_date
190
+ on base_date.event_date between mau.event_date and mau.wau_range_date
191
191
  group by base_date.event_date
192
192
  order by base_date.event_date asc
193
193
  ```

7

追記

2018/11/22 00:23

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -157,4 +157,37 @@
157
157
  select *
158
158
  from before_formating
159
159
  order by event_date asc
160
+ ```
161
+ もっとシンプルに
162
+ ```SQL
163
+ with T as (
164
+ select
165
+ date(event_date) event_date
166
+ , date(date_add(event_date,interval 7 day)) wau_range_date
167
+ , date(date_add(event_date,interval 1 month)) mau_range_date
168
+ , user_id
169
+ , event_name
170
+ from `sample*`
171
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
172
+ and app_info.install_source = 'iTunes'
173
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
174
+ and user_id is not null
175
+ limit 10000000
176
+ )
177
+ , base_date as(
178
+ select event_date from T group by event_date
179
+ )
180
+ select
181
+ base_date.event_date
182
+ , count(distinct(dau.user_id)) as dau_count
183
+ , count(distinct(wau.user_id)) as wau_count
184
+ , count(distinct(mau.user_id)) as mau_count
185
+ from base_date inner join T as dau
186
+ on base_date.event_date = dau.event_date
187
+ left join T as wau
188
+ on base_date.event_date between wau.event_date and wau.wau_range_date
189
+ left join T as mau
190
+ on base_date.event_date between mau.event_date and mau.wau_range_date
191
+ group by base_date.event_date
192
+ order by base_date.event_date asc
160
193
  ```

6

修正

2018/11/22 00:22

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -126,7 +126,7 @@
126
126
  base_date.event_date
127
127
  , count(distinct(T.user_id)) as wau_count
128
128
  from base_date inner join T
129
- on T.event_date between base_date.event_date and wau_range_date
129
+ on base_date.event_date between T.event_date and T.wau_range_date
130
130
  group by base_date.event_date
131
131
  )
132
132
  , mau as (
@@ -134,7 +134,7 @@
134
134
  base_date.event_date
135
135
  , count(distinct(T.user_id)) as wau_count
136
136
  from base_date inner join T
137
- on T.event_date between base_date.event_date and mau_range_date
137
+ on base_date.event_date between T.event_date and T.mau_range_date
138
138
  group by base_date.event_date
139
139
  )
140
140
  , before_formating as (

5

追記

2018/11/21 09:36

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -88,4 +88,73 @@
88
88
  order by event_date asc
89
89
  ```
90
90
  BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
91
- [BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)
91
+ [BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)
92
+
93
+ 追記
94
+ --
95
+ 予め、範囲となる日付を求めておくように修正
96
+ ```SQL
97
+ with T as (
98
+ select
99
+ date(event_date) event_date
100
+ , date(date_add(event_date,interval 7 day)) wau_range_date
101
+ , date(date_add(event_date,interval 1 month)) mau_range_date
102
+ , user_id
103
+ , event_name
104
+ from `sample*`
105
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
106
+ and app_info.install_source = 'iTunes'
107
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
108
+ and user_id is not null
109
+ limit 10000000
110
+ )
111
+ , dau as (
112
+ select
113
+ event_date
114
+ ,count(distinct(user_id)) as dau_count
115
+ from
116
+ T
117
+ group by
118
+ event_date
119
+ )
120
+ , base_date as(
121
+ select event_date from T group by event_date
122
+ )
123
+
124
+ , wau as (
125
+ select
126
+ base_date.event_date
127
+ , count(distinct(T.user_id)) as wau_count
128
+ from base_date inner join T
129
+ on T.event_date between base_date.event_date and wau_range_date
130
+ group by base_date.event_date
131
+ )
132
+ , mau as (
133
+ select
134
+ base_date.event_date
135
+ , count(distinct(T.user_id)) as wau_count
136
+ from base_date inner join T
137
+ on T.event_date between base_date.event_date and mau_range_date
138
+ group by base_date.event_date
139
+ )
140
+ , before_formating as (
141
+ select
142
+ dau.event_date
143
+ ,dau_count
144
+ ,wau_count
145
+ ,mau_count
146
+ from
147
+ dau
148
+ left join
149
+ wau
150
+ on
151
+ dau.event_date = wau.event_date
152
+ left join
153
+ mau
154
+ on
155
+ dau.event_date = mau.event_date
156
+ )
157
+ select *
158
+ from before_formating
159
+ order by event_date asc
160
+ ```

4

条件の日付の書式を合わせるように修正

2018/11/21 09:33

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -55,7 +55,7 @@
55
55
  base_date.event_date
56
56
  , count(distinct(T.user_id)) as wau_count
57
57
  from base_date inner join T
58
- on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 7 day)
58
+ on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 7 day))
59
59
  group by base_date.event_date
60
60
  )
61
61
  , mau as (
@@ -63,7 +63,7 @@
63
63
  base_date.event_date
64
64
  , count(distinct(T.user_id)) as wau_count
65
65
  from base_date inner join T
66
- on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 1 month)
66
+ on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 1 month))
67
67
  group by base_date.event_date
68
68
  )
69
69
  , before_formating as (

3

追記

2018/11/21 09:22

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -86,4 +86,6 @@
86
86
  select *
87
87
  from before_formating
88
88
  order by event_date asc
89
- ```
89
+ ```
90
+ BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
91
+ [BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)

2

修正

2018/11/21 09:11

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -19,7 +19,7 @@
19
19
  ```SQL
20
20
  with T as (
21
21
  select
22
- parse_date("%Y%m%d", event_date) event_date
22
+ date(event_date) event_date
23
23
  , user_id
24
24
  , event_name
25
25
  from
@@ -39,7 +39,7 @@
39
39
  )
40
40
  , dau as (
41
41
  select
42
- event_date
42
+ event_date
43
43
  ,count(distinct(user_id)) as dau_count
44
44
  from
45
45
  T
@@ -55,7 +55,7 @@
55
55
  base_date.event_date
56
56
  , count(distinct(T.user_id)) as wau_count
57
57
  from base_date inner join T
58
- on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 7 day)
58
+ on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 7 day)
59
59
  group by base_date.event_date
60
60
  )
61
61
  , mau as (
@@ -63,7 +63,7 @@
63
63
  base_date.event_date
64
64
  , count(distinct(T.user_id)) as wau_count
65
65
  from base_date inner join T
66
- on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 1 month)
66
+ on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 1 month)
67
67
  group by base_date.event_date
68
68
  )
69
69
  , before_formating as (

1

追記

2018/11/21 09:09

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -12,4 +12,78 @@
12
12
  from T
13
13
  group by event_date
14
14
  ) t1
15
+ ```
16
+ 追記
17
+ --
18
+ 追加された記述より展開
19
+ ```SQL
20
+ with T as (
21
+ select
22
+ parse_date("%Y%m%d", event_date) event_date
23
+ , user_id
24
+ , event_name
25
+ from
26
+ `sample*`
27
+ where(
28
+ _TABLE_SUFFIX BETWEEN
29
+ "20180707" AND
30
+ FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
31
+ )
32
+ and
33
+ app_info.install_source = 'iTunes'
34
+ and
35
+ date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
36
+ and
37
+ user_id is not null
38
+ limit 10000000
39
+ )
40
+ , dau as (
41
+ select
42
+ event_date
43
+ ,count(distinct(user_id)) as dau_count
44
+ from
45
+ T
46
+ group by
47
+ event_date
48
+ )
49
+ , base_date as(
50
+ select event_date from T group by event_date
51
+ )
52
+
53
+ , wau as (
54
+ select
55
+ base_date.event_date
56
+ , count(distinct(T.user_id)) as wau_count
57
+ from base_date inner join T
58
+ on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 7 day)
59
+ group by base_date.event_date
60
+ )
61
+ , mau as (
62
+ select
63
+ base_date.event_date
64
+ , count(distinct(T.user_id)) as wau_count
65
+ from base_date inner join T
66
+ on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 1 month)
67
+ group by base_date.event_date
68
+ )
69
+ , before_formating as (
70
+ select
71
+ dau.event_date
72
+ ,dau_count
73
+ ,wau_count
74
+ ,mau_count
75
+ from
76
+ dau
77
+ left join
78
+ wau
79
+ on
80
+ dau.event_date = wau.event_date
81
+ left join
82
+ mau
83
+ on
84
+ dau.event_date = mau.event_date
85
+ )
86
+ select *
87
+ from before_formating
88
+ order by event_date asc
15
89
  ```