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

編集履歴

質問編集履歴

4

実現したい状態を追記

2020/11/29 11:29

投稿

coffeesleep
coffeesleep

スコア5

title CHANGED
File without changes
body CHANGED
@@ -3,6 +3,25 @@
3
3
 
4
4
  具体的なイメージで言うと、__記事名でアクセス数ランキングをだして、その補助データとして著者名を別カラムで出したい（著者名は同じレコードの中に別のキーで格納されている）__といったような用途です。
5
5
 
6
+ ## 出力したいデータのイメージ
7
+
8
+ - article_name の閲覧数を集計して cnt を出し、降順に並べる
9
+ - どの著者が書いた記事かをわかりやすくするために、author_name カラムを横に出す
10
+ - article_name と author_name は同一レコードの同じカラムの中にネストされて入っていて、それを取り出したい
11
+
12
+ ```
13
+ ↓集計対象 ↓補助データ
14
+ +-----+--------------+-------------+-----+
15
+ | Row | article_name | author_name | cnt |
16
+ +-----+--------------+-------------+-----+
17
+ | 1 | My Article A | Author A | 20 |
18
+ | 2 | My Article D | Author A | 18 |
19
+ | 3 | My Article C | Author C | 11 |
20
+ | 4 | My Article B | Author B | 9 |
21
+ ...
22
+ +-----+--------------+-------------+-----+
23
+ ```
24
+
6
25
  ## 環境
7
26
  Google Analytics 4
8
27
  Google Tag Manager でイベントを設定
@@ -106,10 +125,10 @@
106
125
  +-----+--------------+-------------+-----+
107
126
  | Row | article_name | author_name | cnt |
108
127
  +-----+--------------+-------------+-----+
109
- | 1 | My Article A |null | 20 |
128
+ | 1 | My Article A | null | 20 |
110
- | 2 | My Article D |null | 18 |
129
+ | 2 | My Article D | null | 18 |
111
- | 3 | My Article C |null | 11 |
130
+ | 3 | My Article C | null | 11 |
112
- | 4 | My Article B |null | 9 |
131
+ | 4 | My Article B | null | 9 |
113
132
  ...
114
133
  +-----+--------------+-------------+-----+
115
134
  ```

3

追加で試したことを追記

2020/11/29 11:29

投稿

coffeesleep
coffeesleep

スコア5

title CHANGED
File without changes
body CHANGED
@@ -43,6 +43,7 @@
43
43
  UNNEST(event_params)
44
44
  WHERE
45
45
  key = 'article_name'
46
+ AND event_name = 'view_article'
46
47
  )
47
48
 
48
49
  SELECT
@@ -85,7 +86,7 @@
85
86
  `my-new-project.analytics_000000000.events_*`,
86
87
  UNNEST(event_params)
87
88
  WHERE
88
- key = 'article_name'
89
+ event_name = 'view_article'
89
90
  )
90
91
 
91
92
  SELECT
@@ -113,4 +114,22 @@
113
114
  +-----+--------------+-------------+-----+
114
115
  ```
115
116
 
116
- 追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・
117
+ 追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・
118
+
119
+ ---
120
+ 追記２：各レコードのいずれかのカラムが null になってしまうのは UNNEST してしまってるからなと思い、このようなクエリでただカラムとキーを指定できるかなと試してみました。
121
+
122
+ ```SQL
123
+ SELECT
124
+ CASE WHEN e.event_params.key = 'article_name' THEN e.event_params.value.string_value
125
+ END AS article_name,
126
+ CASE WHEN e.event_params.key = 'author_name' THEN e.event_params.value.string_value
127
+ END AS author_name
128
+ FROM
129
+ `my-new-project.analytics_000000000.events_*` as e
130
+ WHERE
131
+ event_name = 'view_article'
132
+ ```
133
+ しかし、BigQuery でこのようなエラーになり、クエリを回せませんでした。
134
+
135
+ > 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

title CHANGED
File without changes
body CHANGED
@@ -11,6 +11,23 @@
11
11
 
12
12
  ![イメージ説明](f94b3bfa93f63d3309a7b5e627e83bc2.png)
13
13
 
14
+ テーブルの中身はこのようになっています。
15
+ ```
16
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
17
+ | Row | event_date | event_timestamp | event_name | event_params.key | event_params.value.string_value |
18
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
19
+ | 1 | 20201127 | 160394324324231 | view_article | article_name | My Article A |
20
+ | | | | | author_name | Author A |
21
+ | | | | | hogehoge key1 | hogehoge value1 |
22
+ | | | | | hogehoge key2 | hogehoge value2 |
23
+ | 2 | 20201127 | 160394324324112 | view_article | article_name | My Article B |
24
+ | | | | | author_name | Author B |
25
+ | | | | | hogehoge key1 | hogehoge value1 |
26
+ | | | | | hogehoge key2 | hogehoge value2 |
27
+ ...
28
+ +-----+------------+-----------------+--------------+------------------+---------------------------------+
29
+ ```
30
+
14
31
  ## 試したこと
15
32
 
16
33
  記事名をもとにしたアクセス数ランキングは出すことができました。

1

試したことを追記

2020/11/28 09:22

投稿

coffeesleep
coffeesleep

スコア5

title CHANGED
File without changes
body CHANGED
@@ -46,8 +46,8 @@
46
46
  +-----+--------------+-----+
47
47
  | 1 | My Article A | 20 |
48
48
  | 2 | My Article D | 18 |
49
- | 2 | My Article C | 11 |
49
+ | 3 | My Article C | 11 |
50
- | 2 | My Article B | 9 |
50
+ | 4 | My Article B | 9 |
51
51
  ...
52
52
  +-----+--------------+-----+
53
53
  ```
@@ -90,8 +90,10 @@
90
90
  +-----+--------------+-------------+-----+
91
91
  | 1 | My Article A |null | 20 |
92
92
  | 2 | My Article D |null | 18 |
93
- | 2 | My Article C |null | 11 |
93
+ | 3 | My Article C |null | 11 |
94
- | 2 | My Article B |null | 9 |
94
+ | 4 | My Article B |null | 9 |
95
95
  ...
96
96
  +-----+--------------+-------------+-----+
97
- ```
97
+ ```
98
+
99
+ 追記：author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・