最大値の平均を取得するクエリ

Question

前提・実現したいこと

session_idのスクロール深度から最大値を取得し、URLごとにスクロール深度の平均を取得する。
以下のDBが合った場合に、
//aaa.html：30と20の平均
//bbb.html：20と40の平均
を出せるようにしたいです。

|timestamp|url|session_id|scroll_depth|
|:--|:--:|--:|
|2021-01-01|//aaa.html|abcde|10|
|2021-01-01|//aaa.html|abcde|20|
|2021-01-01|//aaa.html|abcde|30|
|2021-01-01|//aaa.html|12345|10|
|2021-01-01|//aaa.html|12345|20|
|2021-01-01|//bbb.html|fghij|10|
|2021-01-01|//bbb.html|fghij|20|
|2021-01-01|//bbb.html|klmno|10|
|2021-01-01|//bbb.html|klmno|20|
|2021-01-01|//bbb.html|klmno|30|
|2021-01-01|/***/bbb.html|klmno|40|

試したこと

session_idごとにscroll_depthの最大値を取得するクエリを書いたのですが、ここからdate/url/scroll_depthの平均を出すクエリにたどり着けずにいます。

select
    FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date,
    url,
    session_id,
    max(scroll_depth) as scroll_depth
FROM DB名
group by date, session_id,url

ご教授のほどよろしくお願いします。

Comments

[sazi]

group byが、「session_idのスクロール深度から最大値」では、(date, session_id,url)ですが、「URLごとにスクロール深度の平均」は、(date,url)になるかと思います。
それを一度に取得すると、(date, session_id,url)に合わせるしかありませんけど、どのようなselectの結果にしたいのですか？

[sazi]

質問では、それぞれを求めたいという事ではありませんでしたね。

Answer 1

集計の単位が違うものは別々に集計するしかありません。

select  date, url, avg(scroll_depth) as scroll_depth
from (
  select  FORMAT_TIMESTAMP("%Y-%m-%d", timestamp, "Asia/Tokyo") AS date
        , url
        , session_id
        , max(scroll_depth) as scroll_depth
  FROM DB名
  group by date, url, session_id
) session_Max
group by date, url

Comments

[MeMeMeMeMeMe]

こちらの回答がドンピシャで得たいものでした。ありがとうございます。