【SQL：PRESTO】完全自分用

ユーザ毎の閲覧順序・間隔やカテゴリページ毎の閲覧順序・ＵＵを集計

事前にユーザ別のアクセスカテゴリページと期間内初アクセス日を集計したテーブルを構築

--閲覧間隔日数を出す場合は適時クエリを修正
WITH sq_sort_now AS(
  SELECT
    tracking_data,
    node,
    min_accesstime
    --テーブル名変更
  FROM
    mezzo_min_access_time
  ORDER BY
    min_accesstime,
    tracking_data
),
sq_sort_next AS(
  SELECT
    tracking_data,
    node,
    min_accesstime
    --テーブル名変更
  FROM
    mezzo_min_access_time
),
sq_order AS(
  SELECT
    sq_sort_now.tracking_data,
    sq_sort_now.node AS now_node,
    sq_sort_now.min_accesstime AS now_accesstime,
    sq_sort_next.node AS next_node,
    sq_sort_next.min_accesstime AS next_accesstime
  FROM (sq_sort_now)
  JOIN
    (sq_sort_next)
    ON (
      sq_sort_now.tracking_data = sq_sort_next.tracking_data
      AND sq_sort_now.node != sq_sort_next.node
    )
  ORDER BY
    sq_sort_now.tracking_data
),
sq_order_diff AS(
  SELECT
    tracking_data,
    now_node,
    now_accesstime,
    next_node,
    next_accesstime,
    date_diff(
      'day',
      -- 他にも minute, hour, week, monthなども指定できる
      CAST(
        now_accesstime AS TIMESTAMP
      ),
      CAST(
        next_accesstime AS TIMESTAMP
      )
    ) AS time_diff
  FROM
    sq_order
  WHERE
    date_diff(
      'day',
      -- 他にも minute, hour, week, monthなども指定できる
      CAST(
        now_accesstime AS TIMESTAMP
      ),
      CAST(
        next_accesstime AS TIMESTAMP
      )
    )>= 0
  ORDER BY
    tracking_data
),
sq_uu AS(
--閲覧間隔日数を含むデータ
--ここのデータを出したい場合は本クエリを最終結果とする
  SELECT
    now_node,
    next_node,
    time_diff,
    COUNT(DISTINCT tracking_data) AS uu
  FROM (sq_order_diff)
  GROUP BY
    now_node,
    next_node,
    time_diff
  ORDER BY
    now_node,
    next_node,
    time_diff
)
--順序のみ出す ネットワーク作図用データ
SELECT
  now_node,
  next_node,
  sum(uu) sum_uu
FROM
  sq_uu
WHERE
 uu > 10
GROUP BY
  now_node,
  next_node
ORDER BY
  now_node,
  next_node