『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜 ref: https://qiita.com/doryokujin/items/773f6008e420c7f3260d

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) # 月次

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）

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) # 月次

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

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

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

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)

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)

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)

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)

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 ...

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)

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) # 適切な月の始まりと終わり

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日から始まっていない

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

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

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) # 前月