teratail
回答率
85.36%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問[SQL][Google Analytics] NEST されたカラムで複数のキーと値を取得したい

編集履歴

質問編集履歴

4

実現したい状態を追記

2020/11/29 11:29

投稿

coffeesleep
coffeesleep

スコア5

test CHANGED
File without changes
test CHANGED
@@ -8,6 +8,44 @@
8
8
 
9
9
 
10
10
 
11
+ ## 出力したいデータのイメージ
12
+
13
+
14
+
15
+ - article_name の閲覧数を集計して cnt を出し、降順に並べる
16
+
17
+ - どの著者が書いた記事かをわかりやすくするために、author_name カラムを横に出す
18
+
19
+ - article_name と author_name は同一レコードの同じカラムの中にネストされて入っていて、それを取り出したい
20
+
21
+
22
+
23
+ ```
24
+
25
+ ↓集計対象 ↓補助データ
26
+
27
+ +-----+--------------+-------------+-----+
28
+
29
+ | Row | article_name | author_name | cnt |
30
+
31
+ +-----+--------------+-------------+-----+
32
+
33
+ | 1 | My Article A | Author A | 20 |
34
+
35
+ | 2 | My Article D | Author A | 18 |
36
+
37
+ | 3 | My Article C | Author C | 11 |
38
+
39
+ | 4 | My Article B | Author B | 9 |
40
+
41
+ ...
42
+
43
+ +-----+--------------+-------------+-----+
44
+
45
+ ```
46
+
47
+
48
+
11
49
  ## 環境
12
50
 
13
51
  Google Analytics 4
@@ -214,13 +252,13 @@
214
252
 
215
253
  +-----+--------------+-------------+-----+
216
254
 
217
- | 1 | My Article A |null | 20 |
255
+ | 1 | My Article A | null | 20 |
218
-
256
+
219
- | 2 | My Article D |null | 18 |
257
+ | 2 | My Article D | null | 18 |
220
-
258
+
221
- | 3 | My Article C |null | 11 |
259
+ | 3 | My Article C | null | 11 |
222
-
260
+
223
- | 4 | My Article B |null | 9 |
261
+ | 4 | My Article B | null | 9 |
224
262
 
225
263
  ...
226
264
 

3

追加で試したことを追記

2020/11/29 11:29

投稿

coffeesleep
coffeesleep

スコア5

test CHANGED
File without changes
test CHANGED
@@ -88,6 +88,8 @@
88
88
 
89
89
  key = 'article_name'
90
90
 
91
+ AND event_name = 'view_article'
92
+
91
93
  )
92
94
 
93
95
 
@@ -172,7 +174,7 @@
172
174
 
173
175
  WHERE
174
176
 
175
- key = 'article_name'
177
+ event_name = 'view_article'
176
178
 
177
179
  )
178
180
 
@@ -229,3 +231,39 @@
229
231
 
230
232
 
231
233
  追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・
234
+
235
+
236
+
237
+ ---
238
+
239
+ 追記２：各レコードのいずれかのカラムが null になってしまうのは UNNEST してしまってるからなと思い、このようなクエリでただカラムとキーを指定できるかなと試してみました。
240
+
241
+
242
+
243
+ ```SQL
244
+
245
+ SELECT
246
+
247
+ CASE WHEN e.event_params.key = 'article_name' THEN e.event_params.value.string_value
248
+
249
+ END AS article_name,
250
+
251
+ CASE WHEN e.event_params.key = 'author_name' THEN e.event_params.value.string_value
252
+
253
+ END AS author_name
254
+
255
+ FROM
256
+
257
+ `my-new-project.analytics_000000000.events_*` as e
258
+
259
+ WHERE
260
+
261
+ event_name = 'view_article'
262
+
263
+ ```
264
+
265
+ しかし、BigQuery でこのようなエラーになり、クエリを回せませんでした。
266
+
267
+
268
+
269
+ > Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [5:24]

2

テーブル例を追加

2020/11/29 11:16

投稿

coffeesleep
coffeesleep

スコア5

test CHANGED
File without changes
test CHANGED
@@ -24,6 +24,40 @@
24
24
 
25
25
 
26
26
 
27
+ テーブルの中身はこのようになっています。
28
+
29
+ ```
30
+
31
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
32
+
33
+ | Row | event_date | event_timestamp | event_name | event_params.key | event_params.value.string_value |
34
+
35
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
36
+
37
+ | 1 | 20201127 | 160394324324231 | view_article | article_name | My Article A |
38
+
39
+ | | | | | author_name | Author A |
40
+
41
+ | | | | | hogehoge key1 | hogehoge value1 |
42
+
43
+ | | | | | hogehoge key2 | hogehoge value2 |
44
+
45
+ | 2 | 20201127 | 160394324324112 | view_article | article_name | My Article B |
46
+
47
+ | | | | | author_name | Author B |
48
+
49
+ | | | | | hogehoge key1 | hogehoge value1 |
50
+
51
+ | | | | | hogehoge key2 | hogehoge value2 |
52
+
53
+ ...
54
+
55
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
56
+
57
+ ```
58
+
59
+
60
+
27
61
  ## 試したこと
28
62
 
29
63
 

1

試したことを追記

2020/11/28 09:22

投稿

coffeesleep
coffeesleep

スコア5

test CHANGED
File without changes
test CHANGED
@@ -94,9 +94,9 @@
94
94
 
95
95
  | 2 | My Article D | 18 |
96
96
 
97
- | 2 | My Article C | 11 |
97
+ | 3 | My Article C | 11 |
98
98
 
99
- | 2 | My Article B | 9 |
99
+ | 4 | My Article B | 9 |
100
100
 
101
101
  ...
102
102
 
@@ -182,12 +182,16 @@
182
182
 
183
183
  | 2 | My Article D |null | 18 |
184
184
 
185
- | 2 | My Article C |null | 11 |
185
+ | 3 | My Article C |null | 11 |
186
186
 
187
- | 2 | My Article B |null | 9 |
187
+ | 4 | My Article B |null | 9 |
188
188
 
189
189
  ...
190
190
 
191
191
  +-----+--------------+-------------+-----+
192
192
 
193
193
  ```
194
+
195
+
196
+
197
+ 追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・