# with句を利用したテーブル内ネスト構造の集計

## 概要
- ネスト構造のテーブル内のデータをバラして必要なデータのみを集計する方法の説明を説明します

## 詳細
### 目的
- 基本的なwith句の使い方の説明
- 赤枠をバラして横並びにする方法の説明をする

### SQL

WITH t1 AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_title") AS title,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS location,
    event_name
   FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211101'
    AND '20211110'
)
SELECT 
  title,
  location,
  COUNT(*) as count
FROM t1 
WHERE event_name = "first_visit"
GROUP BY location, title

### 結果例

|行|title|location|count|
|---|---|---|---|
|1|株式会社菊原web解析事務所| https://kikuhara.site/ | 13 |
|2|【GCPエンジニア監修】GA4のデータをBigQueryで分析するための方法 – 株式会社菊原web解析事務所| https://kikuhara.site/work/googleanalytics4-work/ga4-bigquery-01/ |4|
|3|【ＧＡ】固有の購入数と数量の意味 \| やる夫で学ぶGoogleAnalytics | https://kikuhara.site/yaruo/googleanalytics_kinou/ec_metrics/ |2|
|4 | googleタグマネージャーのみで重複ＵＲＬを仮想ページビュー化する方法 – 株式会社菊原web解析事務所| https://kikuhara.site/work/gtm-kasoupageview/ |2|
|5 |[どっちが人気？]欅坂46と乃木坂46の人気を分析してみた。 \| やる夫で学ぶGoogleAnalytics |https://kikuhara.site/yaruo/bunsekishuhou/keyakizaka_nogizaka/ |2|

### 日単位セッション数の取得
#### 目的
- with句を利用してテーブルをバラしてセッション数の集計をする方法の説明

#### SQL

WITH
  t1 AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS target_date,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211001'
    AND '20211101'
    AND event_name = 'session_start' )
SELECT
  hitdate,
  COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1
ORDER BY 1

#### 結果例

| 行 | target_date | sessions |
| -------- | -------- | -------- |
| 1 | 2021-10-01 | 14 |
| 2 | 2021-10-02 | 2 |
| 3 | 2021-10-03 | 4 |
| 4 | 2021-10-04 | 6 |
| 5 | 2021-10-05 | 3 |

### ページ別ランディング数・離脱数・直帰数
#### 目的
- with句を利用してテーブルをバラしてランディング数等を集計する方法の説明
#### SQL

WITH
  t1 AS (
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid
  FROM                    
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211001' and '20211031'
  AND event_name = 'page_view'
  )
  ,t2 AS (
  SELECT
    page_location,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp) = 1 THEN 1
      ELSE 0
    END AS is_entry,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS is_exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
      ELSE 0
    END AS is_single
  FROM
    t1
  )
SELECT
  t2.page_location AS page_location,
  SUM(t2.is_entry) AS entry,
  SUM(t2.is_exit) AS exit,
  SUM(t2.is_single) AS singlevisit,
  IFNULL(SAFE_DIVIDE(CAST(SUM(t2.is_entry) AS numeric), CAST(SUM(t2.is_exit) AS numeric)), 0) AS ratio
FROM t2
GROUP BY 1
ORDER BY 2 DESC

#### 結果例

|行|page_location|entry|exit|singlevisit | ratio |
| -------- | -------- | -------- |-------- |-------- |-------- |
|1|https://kikuhara.site/|70|61|54|1.147540984|
|2|https://kikuhara.site/private/|6|5|2|1.2|
|3|https://kikuhara.site/yaruo/googleanalytics_kinou/ec_metrics/|5|5|5|1|
|4|https://kikuhara.site/work/gtm-kasoupageview/|4|4|3|1|
|5|https://kikuhara.site/work/bouns-contents/|3|4|3|0.75|
|行|page_location|entry|exit|singlevisit |
| -------- | -------- | -------- |-------- |-------- |
|1| https://kikuhara.site/| 82| 73| 63|
|2| https://kikuhara.site/private/ |6 |5| 2|
|3| https://kikuhara.site/yaruo/googleanalytics_kinou/ec_metrics/ |5 |5 |5|
|4| https://kikuhara.site/work/gtm-kasoupageview/ |5 |5| 4
|5| https://kikuhara.site/yaruo/bunsekishuhou/keyakizaka_nogizaka/ |4| 4|4|