2016年10月21日金曜日

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

前置き。。

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

title昨日_uu昨日_pv一昨日_uu一昨日_pvdiff_uudiff_pv
記事A1000150012001500-2000
記事B200300200250050
記事C100200200300-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

0 件のコメント :

コメントを投稿