【トレジャーデータ：Presto】各ユーザの曜日別アクセス率を集計してライフスタイルに合ったアプローチをする

ユーザ一人ひとりのライフスタイルに合ったアプローチをしたいと考えたとき、まずは、各ユーザの曜日別のアクセス率を把握することが有効。各ユーザの曜日別アクセス率が分かれば、一人ひとりのユーザに適切なタイミングでコンテンツを提供できる可能性を高めることができる。

◇各ユーザの曜日別アクセス率の集計イメージ

user_id	月	火	水	木	金	土	日
10001	0.5	0	0	0	0	0	0.5
10002	0	0	0.8	0.2	0	0	0
10003	0.1	0.1	0.1	0.2	0.1	0.1	0.3
10004	0	0	0	0	1	0	0

◇集計例（Presto）

WITH sq_data AS(
  SELECT
    DOW(
      --DOWで日付から曜日（1~7:月~日）を得る
      FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time,
            'yyyy-MM-dd',
            'jst')))
    ) AS weekly,
    user_id,
    CAST(--後で比率を計算するため浮動小数点型に変換する
      COUNT(0) AS DOUBLE
    ) AS pv
  FROM
    access_log
  WHERE
    AND TD_TIME_RANGE(time,
      '2017-01-01',
      '2017-02-01',
      'jst')
  GROUP BY
    DOW(
      FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time,
            'yyyy-MM-dd',
            'jst')))
    ),
    user_id
  ORDER BY
    weekly,
    user_id
),
sq_week_pv AS(
  SELECT
    user_id,
    COALESCE(
      --nullの場合は0とする
      SUM(CASE
          WHEN weekly = 1 THEN pv END),
      0
    ) AS Mon,
    COALESCE(
      SUM(CASE
          WHEN weekly = 2 THEN pv END),
      0
    ) AS Tue,
    COALESCE(
      SUM(CASE
          WHEN weekly = 3 THEN pv END),
      0
    ) AS Wed,
    COALESCE(
      SUM(CASE
          WHEN weekly = 4 THEN pv END),
      0
    ) AS Thurs,
    COALESCE(
      SUM(CASE
          WHEN weekly = 5 THEN pv END),
      0
    ) AS Fri,
    COALESCE(
      SUM(CASE
          WHEN weekly = 6 THEN pv END),
      0
    ) AS Sat,
    COALESCE(
      SUM(CASE
          WHEN weekly = 7 THEN pv END),
      0
    ) AS Sun,
    SUM(pv) AS pv
  FROM
    sq_data
  GROUP BY
    user_id
)
SELECT
  user_id,
  --比率の計算
  Mon / pv AS Mon,
  Tue / pv AS Tue,
  Wed / pv AS Wed,
  Thurs / pv AS Thurs,
  Fri / pv AS Fri,
  Sat / pv AS Sat,
  Sun / pv AS Sun
FROM
  sq_week_pv