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

編集履歴

回答編集履歴

10

追記

2018/11/22 00:32

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -317,3 +317,63 @@
317
317
  order by event_date asc
318
318
 
319
319
  ```
320
+
321
+ 単にユーザーのIDをカウントするだけなので、シンプルになりそう。
322
+
323
+ ```SQL
324
+
325
+ with T as (
326
+
327
+ select
328
+
329
+ date(event_date) event_date
330
+
331
+ , date(date_add(event_date,interval 7 day)) wau_range_date
332
+
333
+ , date(date_add(event_date,interval 1 month)) mau_range_date
334
+
335
+ , user_id
336
+
337
+ , event_name
338
+
339
+ from `sample*`
340
+
341
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
342
+
343
+ and app_info.install_source = 'iTunes'
344
+
345
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
346
+
347
+ and user_id is not null
348
+
349
+ limit 10000000
350
+
351
+ )
352
+
353
+ select
354
+
355
+ dau.event_date
356
+
357
+ , count(distinct(dau.user_id)) as dau_count
358
+
359
+ , count(distinct(wau.user_id)) as wau_count
360
+
361
+ , count(distinct(mau.user_id)) as mau_count
362
+
363
+ from T as dau
364
+
365
+ inner join T as wau
366
+
367
+ on dau.event_date between wau.event_date and wau.wau_range_date
368
+
369
+ inner join T as mau
370
+
371
+ on dau.event_date between mau.event_date and mau.wau_range_date
372
+
373
+ group by dau.event_date
374
+
375
+ order by dau.event_date asc
376
+
377
+
378
+
379
+ ```

9

削除

2018/11/22 00:32

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -317,69 +317,3 @@
317
317
  order by event_date asc
318
318
 
319
319
  ```
320
-
321
- もっとシンプルに
322
-
323
- ```SQL
324
-
325
- with T as (
326
-
327
- select
328
-
329
- date(event_date) event_date
330
-
331
- , date(date_add(event_date,interval 7 day)) wau_range_date
332
-
333
- , date(date_add(event_date,interval 1 month)) mau_range_date
334
-
335
- , user_id
336
-
337
- , event_name
338
-
339
- from `sample*`
340
-
341
- where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
342
-
343
- and app_info.install_source = 'iTunes'
344
-
345
- and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
346
-
347
- and user_id is not null
348
-
349
- limit 10000000
350
-
351
- )
352
-
353
- , base_date as(
354
-
355
- select event_date from T group by event_date
356
-
357
- )
358
-
359
- select
360
-
361
- base_date.event_date
362
-
363
- , count(distinct(dau.user_id)) as dau_count
364
-
365
- , count(distinct(wau.user_id)) as wau_count
366
-
367
- , count(distinct(mau.user_id)) as mau_count
368
-
369
- from base_date inner join T as dau
370
-
371
- on base_date.event_date = dau.event_date
372
-
373
- inner join T as wau
374
-
375
- on base_date.event_date between wau.event_date and wau.wau_range_date
376
-
377
- inner join T as mau
378
-
379
- on base_date.event_date between mau.event_date and mau.wau_range_date
380
-
381
- group by base_date.event_date
382
-
383
- order by base_date.event_date asc
384
-
385
- ```

8

修正

2018/11/22 00:25

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -368,15 +368,15 @@
368
368
 
369
369
  from base_date inner join T as dau
370
370
 
371
- on base_date.event_date = dau.event_date
371
+ on base_date.event_date = dau.event_date
372
-
372
+
373
- left join T as wau
373
+ inner join T as wau
374
-
374
+
375
- on base_date.event_date between wau.event_date and wau.wau_range_date
375
+ on base_date.event_date between wau.event_date and wau.wau_range_date
376
-
376
+
377
- left join T as mau
377
+ inner join T as mau
378
-
378
+
379
- on base_date.event_date between mau.event_date and mau.wau_range_date
379
+ on base_date.event_date between mau.event_date and mau.wau_range_date
380
380
 
381
381
  group by base_date.event_date
382
382
 

7

追記

2018/11/22 00:23

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -317,3 +317,69 @@
317
317
  order by event_date asc
318
318
 
319
319
  ```
320
+
321
+ もっとシンプルに
322
+
323
+ ```SQL
324
+
325
+ with T as (
326
+
327
+ select
328
+
329
+ date(event_date) event_date
330
+
331
+ , date(date_add(event_date,interval 7 day)) wau_range_date
332
+
333
+ , date(date_add(event_date,interval 1 month)) mau_range_date
334
+
335
+ , user_id
336
+
337
+ , event_name
338
+
339
+ from `sample*`
340
+
341
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
342
+
343
+ and app_info.install_source = 'iTunes'
344
+
345
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
346
+
347
+ and user_id is not null
348
+
349
+ limit 10000000
350
+
351
+ )
352
+
353
+ , base_date as(
354
+
355
+ select event_date from T group by event_date
356
+
357
+ )
358
+
359
+ select
360
+
361
+ base_date.event_date
362
+
363
+ , count(distinct(dau.user_id)) as dau_count
364
+
365
+ , count(distinct(wau.user_id)) as wau_count
366
+
367
+ , count(distinct(mau.user_id)) as mau_count
368
+
369
+ from base_date inner join T as dau
370
+
371
+ on base_date.event_date = dau.event_date
372
+
373
+ left join T as wau
374
+
375
+ on base_date.event_date between wau.event_date and wau.wau_range_date
376
+
377
+ left join T as mau
378
+
379
+ on base_date.event_date between mau.event_date and mau.wau_range_date
380
+
381
+ group by base_date.event_date
382
+
383
+ order by base_date.event_date asc
384
+
385
+ ```

6

修正

2018/11/22 00:22

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -254,7 +254,7 @@
254
254
 
255
255
  from base_date inner join T
256
256
 
257
- on T.event_date between base_date.event_date and wau_range_date
257
+ on base_date.event_date between T.event_date and T.wau_range_date
258
258
 
259
259
  group by base_date.event_date
260
260
 
@@ -270,7 +270,7 @@
270
270
 
271
271
  from base_date inner join T
272
272
 
273
- on T.event_date between base_date.event_date and mau_range_date
273
+ on base_date.event_date between T.event_date and T.mau_range_date
274
274
 
275
275
  group by base_date.event_date
276
276
 

5

追記

2018/11/21 09:36

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -179,3 +179,141 @@
179
179
  BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
180
180
 
181
181
  [BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)
182
+
183
+
184
+
185
+ 追記
186
+
187
+ --
188
+
189
+ 予め、範囲となる日付を求めておくように修正
190
+
191
+ ```SQL
192
+
193
+ with T as (
194
+
195
+ select
196
+
197
+ date(event_date) event_date
198
+
199
+ , date(date_add(event_date,interval 7 day)) wau_range_date
200
+
201
+ , date(date_add(event_date,interval 1 month)) mau_range_date
202
+
203
+ , user_id
204
+
205
+ , event_name
206
+
207
+ from `sample*`
208
+
209
+ where (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
210
+
211
+ and app_info.install_source = 'iTunes'
212
+
213
+ and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
214
+
215
+ and user_id is not null
216
+
217
+ limit 10000000
218
+
219
+ )
220
+
221
+ , dau as (
222
+
223
+ select
224
+
225
+ event_date
226
+
227
+ ,count(distinct(user_id)) as dau_count
228
+
229
+ from
230
+
231
+ T
232
+
233
+ group by
234
+
235
+ event_date
236
+
237
+ )
238
+
239
+ , base_date as(
240
+
241
+ select event_date from T group by event_date
242
+
243
+ )
244
+
245
+
246
+
247
+ , wau as (
248
+
249
+ select
250
+
251
+ base_date.event_date
252
+
253
+ , count(distinct(T.user_id)) as wau_count
254
+
255
+ from base_date inner join T
256
+
257
+ on T.event_date between base_date.event_date and wau_range_date
258
+
259
+ group by base_date.event_date
260
+
261
+ )
262
+
263
+ , mau as (
264
+
265
+ select
266
+
267
+ base_date.event_date
268
+
269
+ , count(distinct(T.user_id)) as wau_count
270
+
271
+ from base_date inner join T
272
+
273
+ on T.event_date between base_date.event_date and mau_range_date
274
+
275
+ group by base_date.event_date
276
+
277
+ )
278
+
279
+ , before_formating as (
280
+
281
+ select
282
+
283
+ dau.event_date
284
+
285
+ ,dau_count
286
+
287
+ ,wau_count
288
+
289
+ ,mau_count
290
+
291
+ from
292
+
293
+ dau
294
+
295
+ left join
296
+
297
+ wau
298
+
299
+ on
300
+
301
+ dau.event_date = wau.event_date
302
+
303
+ left join
304
+
305
+ mau
306
+
307
+ on
308
+
309
+ dau.event_date = mau.event_date
310
+
311
+ )
312
+
313
+ select *
314
+
315
+ from before_formating
316
+
317
+ order by event_date asc
318
+
319
+ ```

4

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

2018/11/21 09:33

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -112,7 +112,7 @@
112
112
 
113
113
  from base_date inner join T
114
114
 
115
- on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 7 day)
115
+ on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 7 day))
116
116
 
117
117
  group by base_date.event_date
118
118
 
@@ -128,7 +128,7 @@
128
128
 
129
129
  from base_date inner join T
130
130
 
131
- on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 1 month)
131
+ on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 1 month))
132
132
 
133
133
  group by base_date.event_date
134
134
 

3

追記

2018/11/21 09:22

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -175,3 +175,7 @@
175
175
  order by event_date asc
176
176
 
177
177
  ```
178
+
179
+ BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
180
+
181
+ [BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)

2

修正

2018/11/21 09:11

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -40,7 +40,7 @@
40
40
 
41
41
  select
42
42
 
43
- parse_date("%Y%m%d", event_date) event_date
43
+ date(event_date) event_date
44
44
 
45
45
  , user_id
46
46
 
@@ -80,7 +80,7 @@
80
80
 
81
81
  select
82
82
 
83
- event_date
83
+ event_date
84
84
 
85
85
  ,count(distinct(user_id)) as dau_count
86
86
 
@@ -112,7 +112,7 @@
112
112
 
113
113
  from base_date inner join T
114
114
 
115
- on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 7 day)
115
+ on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 7 day)
116
116
 
117
117
  group by base_date.event_date
118
118
 
@@ -128,7 +128,7 @@
128
128
 
129
129
  from base_date inner join T
130
130
 
131
- on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 1 month)
131
+ on T.event_date between base_date.event_date and date_add(date base_date.event_date,interval 1 month)
132
132
 
133
133
  group by base_date.event_date
134
134
 

1

追記

2018/11/21 09:09

投稿

sazi
sazi

スコア25188

test CHANGED
@@ -27,3 +27,151 @@
27
27
  ) t1
28
28
 
29
29
  ```
30
+
31
+ 追記
32
+
33
+ --
34
+
35
+ 追加された記述より展開
36
+
37
+ ```SQL
38
+
39
+ with T as (
40
+
41
+ select
42
+
43
+ parse_date("%Y%m%d", event_date) event_date
44
+
45
+ , user_id
46
+
47
+ , event_name
48
+
49
+ from
50
+
51
+ `sample*`
52
+
53
+ where(
54
+
55
+ _TABLE_SUFFIX BETWEEN
56
+
57
+ "20180707" AND
58
+
59
+ FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
60
+
61
+ )
62
+
63
+ and
64
+
65
+ app_info.install_source = 'iTunes'
66
+
67
+ and
68
+
69
+ date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
70
+
71
+ and
72
+
73
+ user_id is not null
74
+
75
+ limit 10000000
76
+
77
+ )
78
+
79
+ , dau as (
80
+
81
+ select
82
+
83
+ event_date
84
+
85
+ ,count(distinct(user_id)) as dau_count
86
+
87
+ from
88
+
89
+ T
90
+
91
+ group by
92
+
93
+ event_date
94
+
95
+ )
96
+
97
+ , base_date as(
98
+
99
+ select event_date from T group by event_date
100
+
101
+ )
102
+
103
+
104
+
105
+ , wau as (
106
+
107
+ select
108
+
109
+ base_date.event_date
110
+
111
+ , count(distinct(T.user_id)) as wau_count
112
+
113
+ from base_date inner join T
114
+
115
+ on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 7 day)
116
+
117
+ group by base_date.event_date
118
+
119
+ )
120
+
121
+ , mau as (
122
+
123
+ select
124
+
125
+ base_date.event_date
126
+
127
+ , count(distinct(T.user_id)) as wau_count
128
+
129
+ from base_date inner join T
130
+
131
+ on T.event_date between base_date.event_date and date_add(base_date.event_date,interval 1 month)
132
+
133
+ group by base_date.event_date
134
+
135
+ )
136
+
137
+ , before_formating as (
138
+
139
+ select
140
+
141
+ dau.event_date
142
+
143
+ ,dau_count
144
+
145
+ ,wau_count
146
+
147
+ ,mau_count
148
+
149
+ from
150
+
151
+ dau
152
+
153
+ left join
154
+
155
+ wau
156
+
157
+ on
158
+
159
+ dau.event_date = wau.event_date
160
+
161
+ left join
162
+
163
+ mau
164
+
165
+ on
166
+
167
+ dau.event_date = mau.event_date
168
+
169
+ )
170
+
171
+ select *
172
+
173
+ from before_formating
174
+
175
+ order by event_date asc
176
+
177
+ ```