SQLいろいろ

はじめに

だいぶ更新をサボってしまっていたのですが、今後は自分の学習記録として週一くらいで更新できればと思います。

今回は以下の書籍でSQLについて色々学んだので、書籍内の演習の一部に対する自分の解答をまとめようと思います。

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析（できるDigital Camp） - インプレスブックス

演習030

・メインクエリ
ランディングページと離脱ページの組み合わせ（concat(landing,'->',exit)）をセッションカウントの多い順に並べています。
ユニークなcid, session_countの組み合わせがセッションカウントになるので、count(distinct)でセッションカウントを集計しています。

・サブクエリ
ランディングページと離脱ページの組み合わせを集計しています。
ウィンドウ関数のfirst_value,last_valueにてセッションカウント毎のランディングページ・離脱ページを取得しています。

select 
  concat(landing,'->',exit) as landing_and_exit
  , count(distinct concat(cid, session_count)) as session 
from
    (
    select 
    *
    , first_value(page) over
        (
        partition by cid, session_count 
        order by date_time
        rows between unbounded preceding and unbounded following
        ) 
        as landing 
    , last_value(page) over
        (
        partition by cid, session_count 
        order by date_time
        rows between unbounded preceding and unbounded following
        ) 
        as exit 
    from sample.web_log
    )
where landing != exit
group by landing_and_exit
order by 2 desc 
limit 5