デジタル関連

解答227『集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析』演習ドリル

投稿日:

解答

227

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

結果テーブル

結果テーブル

サポートページに戻る

<a href="">

<img src="static/img/dekiru-net.png" alt="" width="120" />

Source: できるネット
解答227『集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析』演習ドリル

-デジタル関連

Translate »

Copyright© ぽーたりーふ , 2021 All Rights Reserved Powered by STINGER.