回答編集履歴
4
訂正
answer
CHANGED
|
@@ -1,6 +1,5 @@
|
|
|
1
1
|
集計の単位が違うものは別々に集計するしかありません。
|
|
2
2
|
```SQL
|
|
3
|
-
with session_Max as (
|
|
4
3
|
select date, url, avg(scroll_depth) as scroll_depth
|
|
5
4
|
from (
|
|
6
5
|
select FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date
|
3
訂正
answer
CHANGED
|
@@ -1,20 +1,14 @@
|
|
|
1
1
|
集計の単位が違うものは別々に集計するしかありません。
|
|
2
|
-
これらを一度に取得する場合は、「小計」のような行が必要で、unionでこれを追加します。
|
|
3
2
|
```SQL
|
|
4
3
|
with session_Max as (
|
|
4
|
+
select date, url, avg(scroll_depth) as scroll_depth
|
|
5
|
+
from (
|
|
5
6
|
select FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date
|
|
6
|
-
, url
|
|
7
|
+
, url
|
|
7
8
|
, session_id
|
|
8
9
|
, max(scroll_depth) as scroll_depth
|
|
9
10
|
FROM DB名
|
|
10
11
|
group by date, url, session_id
|
|
11
|
-
)
|
|
12
|
-
select * from session_unit
|
|
13
|
-
union all
|
|
14
|
-
select date
|
|
15
|
-
, url, 'url_Avg'
|
|
16
|
-
, Null
|
|
17
|
-
, avg(scroll_depth) as scroll_depth
|
|
18
|
-
|
|
12
|
+
) session_Max
|
|
19
|
-
|
|
13
|
+
group by date, url
|
|
20
14
|
```
|
2
推敲
answer
CHANGED
|
@@ -1,7 +1,7 @@
|
|
|
1
1
|
集計の単位が違うものは別々に集計するしかありません。
|
|
2
2
|
これらを一度に取得する場合は、「小計」のような行が必要で、unionでこれを追加します。
|
|
3
3
|
```SQL
|
|
4
|
-
with
|
|
4
|
+
with session_Max as (
|
|
5
5
|
select FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date
|
|
6
6
|
, url, 'session_Max' as total_type
|
|
7
7
|
, session_id
|
|
@@ -15,6 +15,6 @@
|
|
|
15
15
|
, url, 'url_Avg'
|
|
16
16
|
, Null
|
|
17
17
|
, avg(scroll_depth) as scroll_depth
|
|
18
|
-
FROM
|
|
18
|
+
FROM session_Max
|
|
19
19
|
group by date, url
|
|
20
20
|
```
|
1
推敲
answer
CHANGED
|
@@ -1,17 +1,20 @@
|
|
|
1
1
|
集計の単位が違うものは別々に集計するしかありません。
|
|
2
2
|
これらを一度に取得する場合は、「小計」のような行が必要で、unionでこれを追加します。
|
|
3
3
|
```SQL
|
|
4
|
+
with session_unit as (
|
|
4
5
|
select FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date
|
|
5
6
|
, url, 'session_Max' as total_type
|
|
6
7
|
, session_id
|
|
7
8
|
, max(scroll_depth) as scroll_depth
|
|
8
9
|
FROM DB名
|
|
9
10
|
group by date, url, session_id
|
|
11
|
+
)
|
|
12
|
+
select * from session_unit
|
|
10
13
|
union all
|
|
11
|
-
select
|
|
14
|
+
select date
|
|
12
|
-
, url, '
|
|
15
|
+
, url, 'url_Avg'
|
|
13
16
|
, Null
|
|
14
17
|
, avg(scroll_depth) as scroll_depth
|
|
15
|
-
FROM
|
|
18
|
+
FROM session_unit
|
|
16
19
|
group by date, url
|
|
17
20
|
```
|