◇各ユーザの曜日別アクセス率の集計イメージ
| 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
