Skip to content

Instantly share code, notes, and snippets.

@doryokujin

doryokujin/file0.sql

Last active November 27, 2018 02:23
Show Gist options
  • Save doryokujin/9f493d13efc7d0e5ed22f8c129c3ba44 to your computer and use it in GitHub Desktop.
Save doryokujin/9f493d13efc7d0e5ed22f8c129c3ba44 to your computer and use it in GitHub Desktop.
Download ZIP
『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜 ref: https://qiita.com/doryokujin/items/773f6008e420c7f3260d
Raw
file0.sql
# 基準日 = '2018-11-23 11:11:00'
[2018-11-22 00:00:00, 2018-11-23 00:00:00) # 日次
[2018-11-12 00:00:00, 2018-11-19 00:00:00) # 週次
[2018-10-01 00:00:00, 2018-11-01 00:00:00) # 月次
Raw
file1.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の unixtime
TD_TIME_RANGE(
time,
TD_TIME_ADD(TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST'),'-1d'),
TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST')
) # 日次 [2018-11-22 00:00:00, 2018-11-23 00:00:00）
TD_TIME_RANGE(
time,
TD_TIME_ADD(TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST'),'-1w'),
TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST')
) # 週次 [2018-11-12 00:00:00, 2018-11-19 00:00:00）
TD_TIME_RANGE(
time,
TD_DATE_TRUNC(
'month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST'
),
TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(), 'JST')
) # 月次 [2018-10-01 00:00:00, 2018-11-00 00:00:00）
Raw
file10.sql
基準日 = '2018-11-23 11:11:00'
[2018-11-22 00:00:00, 2018-11-23 00:00:00) # 日次
[2018-11-12 00:00:00, 2018-11-19 00:00:00) # 週次
[2018-10-01 00:00:00, 2018-11-01 00:00:00) # 月次
Raw
file11.sql
基準日 = '2018-11-23 11:11:00'
TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d')
# 正しく 1 日前の値： 2018-11-22 11:11:00
TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1w')
# 正しく 1 週間前の値： 2018-11-22 11:11:002018-11-16 11:11:00
TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1M') # NG
# Presto Date 関数
date_add('month', -1, from_unixtime(TD_SCHEDULED_TIME())
# 正しく 1 ヶ月分前の値： 2018-10-23 11:11:00
Raw
file12.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の unixtime
TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST') # 今日の始まり： 2018-11-23 00:00:00
TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST') # 今週の始まり： 2018-11-19 00:00:00
TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(), 'JST') # 今月の始まり： 2018-11-01 00:00:00
Raw
file13.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の unixtime
TD_TIME_ADD(TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST'),'-1d')
# 前日の始まり： 2018-11-22 00:00:00
TD_TIME_ADD(TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST'),'-1w')
# 前週の始まり： 2018-11-12 00:00:00
TD_DATE_TRUNC(
'month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST'
) # 前月の始まり： 2018-10-01 00:00:00
Raw
file14.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00'
TD_TIME_RANGE(
time,
TD_TIME_ADD(TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST'),'-1d'),
TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST')
) # 日次 [2018-11-22 00:00:00, 2018-11-23 00:00:00）
TD_TIME_RANGE(
time,
TD_TIME_ADD(TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST'),'-1w'),
TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST')
) # 週次 [2018-11-12 00:00:00, 2018-11-19 00:00:00）
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00'
TD_TIME_RANGE(
time,
TD_DATE_TRUNC(
'month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST'
), # 月の始点： 2018-10-01 00:00:00
TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(), 'JST') # 月の終点： 2018-11-01 00:00:00
) # 月次 [2018-10-01 00:00:00, 2018-11-01 00:00:00)
Raw
file15.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00'
# !SELECT 句に TD_SCHEDULED_TIME() を入れる事を忘れない!
TD_INTERVAL(time, '-1d', 'JST') # 日次 [2018-11-22 00:00:00, 2018-11-23 00:00:00)
TD_INTERVAL(time, '-1w', 'JST') # 週次 [2018-11-12 00:00:00, 2018-11-19 00:00:00)
TD_INTERVAL(time, '-1M', 'JST') # 月次 [2018-10-01 00:00:00, 2018-11-01 00:00:00)
Raw
file16.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00'
# !SELECT 句に TD_SCHEDULED_TIME() を入れる事を忘れない!
TD_INTERVAL(time, '-1d/-1d', 'JST') # 1日前の日次 [2018-11-21 00:00:00, 2018-11-22 00:00:00)
TD_INTERVAL(time, '-1d/-2d', 'JST') # 2日前の日次 [2018-11-20 00:00:00, 2018-11-21 00:00:00)
TD_INTERVAL(time, '-1w/-1w', 'JST') # 1週前の週次 [2018-11-05 00:00:00, 2018-11-12 00:00:00)
TD_INTERVAL(time, '-1w/-2w', 'JST') # 2週前の週次 [2018-10-29 00:00:00, 2018-11-05 00:00:00)
TD_INTERVAL(time, '-1M/-1M', 'JST') # 1月前の月次 [2018-09-01 00:00:00, 2018-10-01 00:00:00)
TD_INTERVAL(time, '-1M/-2M', 'JST') # 2月前の月次 [2018-08-01 00:00:00, 2018-09-01 00:00:00)
TD_INTERVAL(time, '-2M/-1M', 'JST') # 1月前の2ヶ月間 [2018-08-01 00:00:00, 2018-10-01 00:00:00)
Raw
file2.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の unixtime
# !SELECT 句に TD_SCHEDULED_TIME() を入れる事を忘れない!
TD_INTERVAL(time, '-1d', 'JST') # 日次 [2018-11-22 00:00:00, 2018-11-23 00:00:00)
TD_INTERVAL(time, '-1w', 'JST') # 週次 [2018-11-12 00:00:00, 2018-11-19 00:00:00)
TD_INTERVAL(time, '-1M', 'JST') # 月次 [2018-10-01 00:00:00, 2018-11-01 00:00:00)
Raw
file3.sql
/* TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' */
SELECT
TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-dd HH:mm:ss', 'JST') AS scheduled_date,
/* TD_TIME_FORMAT(TD_TIME_ADD(TD_DATE_TRUNC( 'day', TD_SCHEDULED_TIME(), 'JST'),'-1d'),'yyyy-MM-dd','JST') AS target_day, */
/* TD_TIME_FORMAT(TD_TIME_ADD(TD_DATE_TRUNC('week', TD_SCHEDULED_TIME(), 'JST'),'-1w'),'yyyy-MM-dd','JST') AS target_week, */
/* TD_TIME_FORMAT(TD_DATE_TRUNC('month', TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(),'JST')-1, 'JST'),'yyyy-MM-dd','JST') AS target_month, */
TD_TIME_FORMAT(MIN(time), 'yyyy-MM-dd HH:mm:ss', 'JST') AS min_date,
TD_TIME_FORMAT(MAX(time), 'yyyy-MM-dd HH:mm:ss', 'JST') AS max_date
FROM pageviews
WHERE ...
Raw
file4.sql
# TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' の unixtime
WHERE
date_add('day', -1, date_trunc('day', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo')))
<= from_unixtime(time,'Asia/Tokyo')
AND from_unixtime(time,'Asia/Tokyo')
< date_trunc('day', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo'))
# 日次 [2018-11-22 00:00:00, 2018-11-23 00:00:00)
WHERE
date_add('week', -1, date_trunc('week', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo')))
<= from_unixtime(time,'Asia/Tokyo')
AND from_unixtime(time,'Asia/Tokyo')
< date_trunc('week', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo'))
# 週次 [2018-11-12 00:00:00, 2018-11-19 00:00:00)
WHERE
date_add('month', -1, date_trunc('month', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo')))
<= from_unixtime(time,'Asia/Tokyo')
AND from_unixtime(time,'Asia/Tokyo')
< date_trunc('month', from_unixtime(TD_SCHEDULED_TIME(),'Asia/Tokyo'))
# 月次 [2018-10-01 00:00:00, 2018-11-01 00:00:00)
Raw
file5.sql
[2018-11-23 00:00:00, 2018-11-24 00:00:00) # 適切な日の始まりと終わり
[2018-11-19 00:00:00, 2018-11-26 00:00:00) # 適切な週の始まりと終わり（月曜始まり日曜終わり）
[2018-11-01 00:00:00, 2018-12-01 00:00:00) # 適切な月の始まりと終わり
Raw
file6.sql
[2018-11-23 11:11:00, 2018-11-24 11:11:00) # 00:00:00 から始まっていない
[2018-11-20 00:00:00, 2018-11-27 00:00:00) # 週初の月曜日から始まっていない
[2018-11-11 00:00:00, 2018-12-11 00:00:00) # 月初の1日から始まっていない
Raw
file7.sql
[2018-11-23 00:00:00, 2018-11-24 00:00:00] # 24日の 00:00:00 のレコードが含まれるのでNG
(2018-11-23 00:00:00, 2018-11-24 00:00:00) # 23日の 00:00:00 のレコードが含まれるのでNG
(2018-11-23 00:00:00, 2018-11-24 00:00:00] # 23日の 00:00:00 のレコードが含まれず，24日の 00:00:00 のレコードが含まれるのでダブルNG
Raw
file8.sql
# start_date = '2018-11-23 00:00:00' の unixtime
# end_date = '2018-11-24 00:00:00' の unixtime
WHERE start_date <= time AND time <= end_date
# end_date の 00:00:00 のレコードが含まれるのでNG
WHERE start_date < time AND time < end_date
# start_date の 00:00:00 のレコードが含まれないのでNG
WHERE start_date < time AND time <= end_date
# start_date の 00:00:00 のレコードが含まれず， end_date の 00:00:00 のレコードが含まれるのでダブルNG
Raw
file9.sql
基準日 = '2018-11-23 11:11:00'
[2018-11-23 00:00:00, 2018-11-24 00:00:00) # 今日
[2018-11-22 00:00:00, 2018-11-23 00:00:00) # 前日
[2018-11-19 00:00:00, 2018-11-26 00:00:00) # 今週
[2018-11-12 00:00:00, 2018-11-19 00:00:00) # 前週
[2018-11-01 00:00:00, 2018-12-01 00:00:00) # 今月
[2018-10-01 00:00:00, 2018-11-01 00:00:00) # 前月
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment