SQL文（クエリ）

WITH master AS (
  SELECT
    *
    , REGEXP_EXTRACT(page, r"[^\?]+") AS page_wo_parameter
  FROM
    sample.web_log
)
SELECT
  *
  , round(bounce / session, 4) AS bounce_rate
FROM
  (
    SELECT
      landing_page
      , COUNT(DISTINCT session_id) AS session
      , SUM(bounce) AS bounce
    FROM
      (
        SELECT
          CONCAT(cid, session_count) AS session_id
          , FIRST_VALUE(page_wo_parameter)
          OVER (PARTITION BY cid, session_count ORDER BY date_time)
          AS landing_page
          , COUNT(*) OVER (PARTITION BY cid, session_count)
          AS pv_per_session
          , IF(COUNT(*) OVER (PARTITION BY cid, session_count) = 1, 1, 0)
          AS bounce
        FROM
          master
      )
    GROUP BY
      landing_page
  )
ORDER BY
  4 DESC
LIMIT 3

結果テーブル

サポートページに戻る