【トレジャーデータ】昨日及び一昨日の各記事のuu,pvを集計して比較する

前置き。。

デイリーで記事別UUランキング（昨日分）を集計している場合、その前日（一昨日）からどれだけ増減があるかは気になるところ。そこで、下記の集計をおこなうSQLを考える。

title	昨日_uu	昨日_pv	一昨日_uu	一昨日_pv	diff_uu	diff_pv
記事A	1000	1500	1200	1500	-200	0
記事B	200	300	200	250	0	50
記事C	100	200	200	300	-100	-100

・・・

WITH yesterday AS(
  SELECT
    MAX(title) AS title,
    COUNT(1) AS pv,
    COUNT(DISTINCT user_id) AS uu,
    page_id,
    page_type
  FROM
    access_log
  WHERE
--毎日自動で集計するためにTD_SCHEDULED_TIMEを使う
    TD_TIME_RANGE(time,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-1d'),
      TD_SCHEDULED_TIME(),
      'jst')
  GROUP BY
    page_id,
    page_type
  ORDER BY
    uu DESC
),
day_before_yesterday AS(
  SELECT
    MAX(title) AS title,
    COUNT(1) AS pv,
    COUNT(DISTINCT user_id) AS uu,
    page_id,
    page_type
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(time,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-2d'),
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-1d'),
      'jst')
  GROUP BY
    page_id,
    page_type
  ORDER BY
    uu DESC
) SELECT
  yesterday.title,
  yesterday.uu AS yesterday_uu,
  yesterday.pv AS yesterday_pv,
  day_before_yesterday.uu AS day_before_yesterday_uu,
  day_before_yesterday.pv AS day_before_yesterday_pv,
  yesterday.uu - day_before_yesterday.uu AS diff_uu,
  yesterday.pv - day_before_yesterday.pv AS diff_pv
FROM (yesterday) LEFT
JOIN (day_before_yesterday)
  ON (
-- 記事を一意に識別するにはpage_typeとpage_idが必要なため下記の条件を設定
    yesterday.page_type = day_before_yesterday.page_type
    AND yesterday.page_id = day_before_yesterday.page_id
  )
ORDER BY
  yesterday.uu DESC