teratail
回答率
85.48%
質問するログイン新規登録
トップBigQueryに関する質問SQL(bq)での出力について

編集履歴

質問編集履歴

1

失礼しました！質問内容を修正させていただきました。

2022/05/19 05:59

投稿

bqny
bqny

スコア26

test CHANGED
File without changes
test CHANGED
@@ -1,44 +1,67 @@
1
1
  SQL(bq)の処理について質問です。
2
- 以下のクエリであるjancodeの購入回数をposテーブルから期間ごとに取得しています。withのaテーブルの中である期間の購入回数が取得できなかった時は0と出力したいのですが何かいい手はありますでしょうか？
2
+ 以下のクエリであるjancodeの購入回数を期間ごとに取得しています。
3
+ 今回の例でいうと'2020/11'の場合の値がテーブルに無いので出力されないのですが
4
+ こちらを0として出力したいです
3
5
 
4
- sqlイメージ
6
+ 想定イメージ
7
+ period purchase_user trial_user repeat_user
8
+ 2020/05 6 6 0
9
+ 2020/08 6 6 0
10
+ 2020/11 0 0 0
11
+
12
+
13
+ ```ここに言語を入力
14
+ CREATE TABLE
15
+ [project_id].}[dataset_name].[table_name]
16
+ (
17
+ user_id STRING
18
+ , start_date DATE
19
+ , end_date DATE
20
+ , date DATE
21
+ , jancode STRING
22
+ )
23
+ ;
24
+
25
+ INSERT INTO [project_id].}[dataset_name].[table_name]
26
+ VALUES('00','2022-05-01','2022-05-31','2020-05-02','12345678')
27
+ , ('01','2022-05-01','2022-05-31','2020-05-12','12345678')
28
+ , ('02','2022-05-01','2022-05-31','2020-05-13','12345678')
29
+ , ('03','2022-05-01','2022-05-31','2020-08-02','12345678')
30
+ , ('04','2022-05-01','2022-05-31','2020-08-02','12345678')
31
+ , ('05','2022-05-01','2022-05-31','2020-08-01','12345678')
32
+ , ('06','2022-05-01','2022-05-31','2020-09-04','12345678')
33
+ , ('07','2022-05-01','2022-05-31','2020-05-02','12345678')
34
+ , ('08','2022-05-01','2022-05-31','2020-09-02','12345678')
35
+ , ('09','2022-05-01','2022-05-31','2020-05-02','12345678')
36
+ , ('10','2022-05-01','2022-05-31','2020-09-02','12345678')
37
+ , ('11','2022-05-01','2022-05-31','2020-05-02','12345678')
38
+ ;
39
+
5
- WITH a AS (
40
+ WITH test AS (
6
41
  SELECT
7
42
  CASE WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 24 MONTH) AND DATE_SUB(end_date, INTERVAL 22 MONTH) THEN '2020/05'
8
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 21 MONTH) AND DATE_SUB(end_date, INTERVAL 19 MONTH) THEN '2020/08'
43
+ WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 21 MONTH) AND DATE_SUB(end_date, INTERVAL 19 MONTH) THEN '2020/08'
9
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 18 MONTH) AND DATE_SUB(end_date, INTERVAL 16 MONTH) THEN '2020/11'
44
+ WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 18 MONTH) AND DATE_SUB(end_date, INTERVAL 16 MONTH) THEN '2020/11'
10
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 15 MONTH) AND DATE_SUB(end_date, INTERVAL 13 MONTH) THEN '2021/02'
11
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 12 MONTH) AND DATE_SUB(end_date, INTERVAL 10 MONTH) THEN '2021/05'
12
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 9 MONTH) AND DATE_SUB(end_date, INTERVAL 7 MONTH) THEN '2021/08'
13
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 6 MONTH) AND DATE_SUB(end_date, INTERVAL 4 MONTH) THEN '2021/11'
14
- WHEN date BETWEEN DATE_SUB(start_date, INTERVAL 3 MONTH) AND DATE_SUB(end_date, INTERVAL 1 MONTH) THEN '2022/02'
15
- WHEN date BETWEEN start_date AND end_date THEN '2022/05'
16
- END AS period
45
+ END AS period
17
- , pos.user_id
46
+ , user_id
18
47
  , COUNT(DISTINCT date) AS frequency
19
- FROM
48
+ FROM
20
- pos_table
49
+ [project_id].}[dataset_name].[table_name]
21
50
  WHERE
22
- jancode = 'zzzzzzzzzzzzz'
51
+ jancode = '12345678'
52
+ GROUP BY
53
+ user_id
54
+ , period
23
55
  )
24
- SELECT
56
+ SELECT
25
- member_type
26
- , period
57
+ period
27
58
  , COUNT(DISTINCT user_id) AS purchase_user
28
59
  , COUNT(DISTINCT IF(frequency = 1, user_id,NULL)) AS traial_user
29
- , COUNT(DISTINCT IF(frequency >1, user_id,NULL)) AS repeat_user
60
+ , COUNT(DISTINCT IF(frequency > 1, user_id,NULL)) AS repeat_user
30
61
  FROM
31
- a
62
+ test
32
63
  GROUP BY
33
64
  period
34
65
  ORDER BY
35
66
  period
36
-
37
-
38
- 出力イメージ
67
+ ```
39
-
40
- period purchase_user trial_user repeat_user
41
- 2020/05 n n n
42
- 2020/08 0 0 0
43
- 2020/11 n n n
44
- 〜〜〜〜〜〜〜
BigQuerySQL