LoginSignup
6
8

More than 5 years have passed since last update.

@doryokujin(Takahiro Inoue)

『Treasure Data でアクセスログ分析の限界に挑む』その① 〜「日次」「週次」「月次」の集計を正しく理解する〜

Last updated at Posted at 2018-11-26

はじめに

本シリーズは，Treasure Data，つまり基本SQLで実現可能な様々なアクセスログ分析事例を具体的なクエリと可視化を交えて紹介していくブログです。多くは当たり前のものですが，時には実に良く捻られたものと思って頂けるよう，私が知りうる限りの全ての事例を提供していく予定です。

(注)：基本SQLと書きましたが， ARM TREASURE DATA に所属する私は，Treasure Data（TD）プラットフォーム上でクエリを動かしているため，特に今回の様な日付に関する記述においては独自関数（TD_UDF）がたくさん登場することをご了承ください。ただ，この日付に関する記述以外は他のプラットフォーム（とりわけ Presto, Hive ユーザー）には多分に適用可能で参考になるものと考えております。

また，今回の記事紹介に当たって，登場する TD_UDF 関数をより理解してもらうため，本家ドキュメントをかみ砕いたページおよび理解をより深めるためのトレーニングページ

を併設しましたので併せて参照ください。

今回のテーマ

今回の『テーマ』は今後ほぼ全てのクエリで登場するであろう日付の範囲指定に関して，適切な設定方法およびテンプレートを解説していく事にあります。

前提とするデータ定義

本シリーズでは以下の様な pageview テーブルを想定していきますが，多くの場合では time カラムと td_client_id カラム（または何らかのユーザーを識別するカラム）しか使いませんので，アクセスログに限らず様々なログで応用可能です。

time td_client_id td_title td_ip
1542956154 c445a751288 Treasure Data 72.215.178.105
1542955952 6b5a0357b2f Treasure Data 103.206.191.199
1542955625 344503ae8cb td command line tool reference 185.3.22.17
1542954891 178e1a1d3e4 SQL & Performance Tuning Tips 103.250.170.50
1542954151 f556371264a Installing and Updating... 39.110.208.102
1542953658 c31baa01dfe Planning and Implementation... 203.132.82.75
1542953160 9894e8afc72 gtm-msr 66.102.8.8

要旨

  • 本ブログにおける「日次」「週次」「月次」とは，ある定まった基準日に対して，それに対する「先日」「先週」「先月」の範囲を意味します。
  • また， 基準日は TD_SCHEDULED_TIME を利用し，それをうまく利用する事で過去の任意の「日次」「週次」「月次」集計をやり直したい時でも，クエリを書き換える事無く，基準日を外側から変更することで対応できる柔軟なテンプレートクエリを紹介します。

例えば， 基準日 = '2018-11-23 11:11:00' であった場合には，各々の対象期間は

# 基準日 = '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) # 月次

に定める事ができ，以下に紹介するテンプレートがこの期間でレコードを抽出してきてくれます。

「日次」「週次」「月次」テンプレート

1. TD_TIME_RANGE & TD_DATE_TRUNC 型

以下の句を WHERE 句に指定すれば， TD_SCHEDULED_TIME における「日次」「週次」「月次」処理が可能になります。トレジャーデータユーザーは Presto でも Hive 環境でも実行できます。以降ほぼ全ての内容において， 基準日を '2018-11-23 11:11:00' とおいています。

# 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）

2. TD_INTERVAL 型

TD_INTERVAL を用いれば，より簡単な句で「日次」「週次」「月次」処理を実現します。ただし，現時点ではこの関数を使えるトレジャーデータユーザーは Presto 環境下のみとなります。

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

3. 正しい範囲であるかを確認するためのテンプレートクエリ

ここまでで書いた「日次」「週次」「月次」のテンプレートを，実際のデータに対して適切な範囲であるかを確認するには以下のクエリテンプレートを使って下さい。SELECT 句内のコメントアウト部分， target_day, target_week, target_month は適用する時間軸によってどれか1つを選んでください。これらは確認クエリの中では不要ですが，実現場でのバッチクエリなどでは，後で結果を見返すときに，どの日（週，月）の処理を行っていたのかを記録している重要な情報です。
また， WHERE 句以降に先ほどのテンプレートを挿入します。

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

月次の TIME_INTERVAL 型で例を1つ見てみます。また，本文の最後の方に全ての例を記述しています。
intval_month.png
min_date と max_date の値が正しい範囲内であるかを確認します。

4. TD_TIME_RANGE 型と TD_INTERVAL 型に優劣はない

これら２種類のテンプレートを比べると， TD_INTERVAL 型一択と思われるかも知れませんが，必ずしもそう言い切れないと私は考えています。今後様々なアクセスログ集計クエリを紹介していきますが，その中でもケースバイケースである事を確認しています。

  • TD_TIME_RANGE 型は「始点」と「終点」が明確に置かれているので，クエリ作成者がどの範囲を取りたいのかが汲み取りやすい
  • TD_TIME_RANGE 型は「始点」と「終点」のどちらかを NULL とすることで，「始点以上」や「終点未満」という片側が無限大の範囲を取ることができる
  • TD_TIME_RANGE 型を真面目に理解しようとすると，様々なDATE関数における知識が深まる（深めたい方は本記事後半を読んでください）
  • TD_INTERVAL 型は新しい関数のため，応用事例がまだ出回っていない， Hive ユーザーは使えない（あくまで現状）
  • TD_INTERVAL 型は書式の意味やそれがどの範囲を取るのかについての正しい理解（勉強）が必要
  • TD_INTERVAL 型は「始点」や「終点」に絶対的な時間を入れれない
  • TD_INTERVAL 型はオフセット機能で TD_TIME_RANGE 以上の活用可能性を秘めている

5. （参考） Presto Date 関数で同様の再現

参考までに，純粋なPrestoの日付関数を使った事がない甘えの私が考えられうるテンプレートクエリが以下になります。
Prestoの date_add 関数にはなんと 'month' による加減算ができるので，月次に関しては 1. のそれよりも統一性の意味で良いかもしれません。トレジャーデータに格納されている time カラムや TD_SCHEDULED_TIME の値は unixtime のため，変換が必要になってしまいます。また， TD_SCHEDULED_TIME の代わりに NOW を使ってしまうと，挙動が異なってしまいます。（参照）

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

さて実はこの記事の前半のここまでで，コピペで使いたい人にとってはほぼ目的を果たしている事になります。次の定義の章は，このテンプレート導出のための理論的な事を述べた退屈な文章ですので，読み飛ばして頂いても構いません。

「日次」「週次」「月次」の定義

「日次」「週次」「月次」の集計をすると言ったときに，人によってもし様々な「日次」「週次」「月次」の概念が存在してしまうと，その概念毎に集計される値が異なってしまいます。ここではある基準日が決まったときに，「日次」「週次」「月次」を意味する範囲を一意に定める事を目標に，そのための条件を順番に指定していきます。

1. 範囲（秒単位）の適切さ

さて，まず「日次」「週次」「月次」の（あくまで本ブログ内での）定義をしておきましょう。まず，日（週,月）と聞くと，秒単位の意味での範囲（期間）が想定されます。

  • 1日の長さ = 60秒×60分×24時間=86400秒
  • 1週の長さ = 60秒×60分×24時間×7日=604800秒
  • 1月の長さ = 60秒×60分×24時間×(28〜31日)= 2419200秒〜2678400秒

それぞれの範囲で集計されていることが第一の条件になります。日と週の範囲については一意に定まるのに対して，月の範囲は各月によって範囲が異なる事に月次集計の難しさが垣間見えます。

2. 「始点」と「終点」の適切さ

さらに本ブログの定義では，単純に秒単位の意味での１日（週，月）分の範囲が適切である事に加えて，「始点」と「終点」の時間が厳密に日（週，月）の始まり・終わりを指していることが求められます。

さて，以下の時間の範囲は『端点が適切』と呼ぶことができます。

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

それに該当しないものとして，以下は範囲としては「1. 範囲（秒単位）の適切さ」の日（週，月）における範囲条件を満たしていますが，始まりと終わりが適切ではないので，この時点で「日次」「週次」「月次」の定義からは外れてしまいます。（もちろん，これらの範囲での集計が必要なケースも多々ありますが，このケースを認めると，一意に定められないので。）

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

3. 端点の適切さ

さて，ここまで不明瞭な点があるとすれば， [始点，終点) という数学的表現です。
[始点，終点)の表現は『始点を含み，終点を含まない範囲』を意味します。言い換えると『始点以上，終点未満』となります。これには以下の4種の範囲表現があります：

  • [始点，終点]: 始点を含む，終点を含む
  • (始点，終点): 始点を含まない，終点を含まない
  • [始点，終点): 始点を含む，終点を含まない
  • (始点，終点]: 始点を含まない，終点を含む

この表現の内，[始点，終点)以外は『1.範囲が適切』でかつ『2.「始点」と「終点」の適切』であっても端点の含有の意味で「日次」とは言えません。[始点，終点)のみのケースを『端点が適切』であると呼ぶ事にします。

[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

これが実際の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

4. 『今』と『前』，どちらも適切か？

基準日はクエリを実行した時間（またはスケジュールされた時間）で，全てのレコードで一意に定まっている絶対時間を意味します。基準日が定まると，考えられうる「日次」「週次」「月次」というのは『今』と『前』の2通りある事に気付きます。
例えば，基準日を「2018-11-23 11:11:00」とします。この時点で以下の綺麗な範囲を定める事ができます。

基準日 = '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) # 前月

ここから気付くことは，集計を実行する基準日に対して，「今日」「今週」「今月」の集計範囲は，まだ得られていない未来の時間範囲を含んでいることになり，基準日を変えて（時間経過によって）改めて同じクエリの集計実行すれば得られる値が異なってしまいます。時間経過によって異なる範囲となるものは，過去の不変な範囲で求められた集計値との正確な比較ができないということを意味します。この不平等を加味して，本ブログでは『今』における「日次」「週次」「月次」は不採用とします。

5. まとめ

改めてポイントをまとめると，
1. 『範囲が適切』：　（秒数の意味で）正しい時間範囲となっている
2. 『始点と終点が適切』：　正しい日（週，月）の始まりと終わりになっている
3. 『端点が適切』： 始点を含み，終点を含まない
4. 『今でなく前』：将来同じクエリの実行しても値が変わらない「前日」「前週」「前月」の集計である
ここまで決めると，基準日が決まると「日次」「週次」「月次」の各々の範囲は一意に定まる事になります。

「日次」「週次」「月次」 の定義（再掲）

基準日 = '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) # 月次

さて，基準日に従って「日次」「週次」「月次」を定める事ができるようになった今，それを実クエリに反映していくことが可能になります。

実クエリにおける正しい「日次」「週次」「月次」の記述

この章での目標は，トレジャーデータの実クエリにおいて，基準日に連動して柔軟に「日次」「週次」「月次」での集計が行えるクエリテンプレートを作成することです。

基準日を TD_SCHEDULED_TIME で表現する

今まで述べていた基準日は，トレジャーデータ環境においては TD_SCHEDULED_TIME という関数で表現することができ，クエリを書き換える事なしにこの日付を変更することができるものとなります。
adohoc_query.png
↑ TD_SCHEDULED_TIME() が仕込まれたクエリでは，実行ボタンを押すと，その値として定める日付をカレンダーから簡単に指定できます。（実際に値として入るのはその日付の unixtime です。）

ここから定める基準日 = TD_SCHEDULED_TIME は，このカレンダーで指定した任意の日付（の unixtime ）であり，全レコードにおいて固定な時間です（各レコードに含まれる「time」カラムなどのレコード毎に可変な値ではありません）。
トレジャーデータでは， TD_SCHEDULED_TIME を活用することによって，「前月」の月次処理にとどまらず，「3ヶ月前」の月次処理（正確に言えば，基準日を2ヶ月前の日付に設定して，前月の月次処理をすることで現時点からは3ヶ月前となる）を，いつでもクエリに手を加えずに実行できることができます。

さて，基準日を TD_SCHEDULED_TIME で自由にで表現できたならば，それに対応した「日次」「週次」「月次」集計のための範囲のテンプレートクエリが書けるはずです。ここからは，定義と同じ順番でクエリを築き上げていきます。

1. 範囲（秒単位）の適切なクエリ

とりあえずは， TD_SCHEDULED_TIME を，（秒の意味で）適切に1日分，1週間分などを減算することができないかを考えます。なぜ加算ではなくて減算であるかというと， TD_SCHEDULED_TIME が「今」の値を表すとすれば，時間を加算することは未来の時間を得ることになり，先ほどの定義の章の4.で触れた「今」の問題に抵触しそうだからです。
TD_SCHEDULED_TIME の返値は unixtime ですから，「日」や「週」の範囲を考えるだけなら単純に必要な秒数だけを減算すれば良いのですが，可読性を挙げる意味でも TD_TIME_ADD 関数を多く採用しています。以下で挙動を確認しましょう。

基準日 = '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

TD_TIME_ADD(time, duration, 'JST') は，第一引数で与えられた time の値に， duration 分だけ加減算してくれる便利な関数です。例えば '1d' なら1日分を加算し， '-1w' ならば1週間分を減算します。そしてこの関数には '1M','-1M'（１ヶ月分の加減算）が存在しません。なぜなら月によって28日〜31日と可変となってしまうためです。（ただ， Presto の同様の関数では「月」の計算が可能になっているようですが，ここでは活用しないことにします。）

少なくとも「日次」と「週次」に関しては [TD_TIME_ADDで減算した時間, 元の時間)とすれば，範囲の意味での適切さは保たれている事がわかりました。

2. 「始点」と「終点」の適切なクエリ

次に「始点」と「終点」が適切なクエリであると思います。 TD_DATE_TRUNC 関数は，与えられた任意の時間に対して「日」「週」「月」それぞれで，適切な始まりの時間（unixtime）に『切り捨て』てくれる大変便利な関数です。以下で挙動を確認します。

# 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

「日」「週」については，この TD_DATE_TRUNC によって切り捨てたれた時間に対して1日，1週間を引くことによって，「日次」「週次」を満たしそうな範囲を得られそうです。一方，「月」については以下のアイデアで前の月の始まりを得ることにします。

  • まず， '2018-11-23 11:11:00' を月で DATE_TRUNC して今月の始まり： '2018-11-01 00:00:00' を得る
  • そこから1秒引いて '2018-10-31 23:59:59' と前月の時間を得る
  • それをさらに月で DATE_TRUNC して前月の始まり： '2018-10-01 00:00:00' を得る
# 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

クエリ表現に置いても適切な「始点」と「終点」が得られましたね。

3. 端点の適切なクエリ

ここまで，「始点」と「終点」が適切な時間を計算することができています。次に今まで数学的記号で表していた[始点，終点)を表現するクエリを書きます。 TD_TIME_RANGE 関数は，端点の適切さを満たしつつも，最良のパフォーマンスを発揮してくれるベストな選択肢です。

4. テンプレートクエリの完成

TD_TIME_RANGE 型のテンプレート

これでやっと，「日次」「週次」「月次」のクエリでの表現が可能になりました。

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

いつも通り TD_SCHEDULED_TIME() = '2018-11-23 11:11:00' のクエリの正しさを確認してみましょう。また，月次に関しては28日しか無い2月でも正しい範囲となっているかも見てみます。

  • 日次 range_day.png
  • 週次 range_week.png
  • 月次 range_month.png
  • 月次(2月) range_month_feb.png

TD_INTERVAL 型のより簡潔なテンプレート

近年登場した TD_INTERVAL という関数は，これまでの「日次」「週次」「月次」の処理を記述する複雑さを解消してくれる救世主的な関数です。先ほど苦労した「日次」「週次」「月次」の記述は，以下の統一フォーマットでうまくいきます。
ただし， TD_TINTERVAL の中で TD_SCHEDULED_TIME は使えませんので， SELECT 句の中で入れておくようにしてください。

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

実際のクエリを実行してみます。

  • 日次 intval_day.png
  • 週次 intval_week.png
  • 月次 intval_month.png
  • 月次(2月) intval_month_feb.png

どれも望む結果を得られていますね。

（応用）TD_INTERVAL 型でオフセットで過去の1つ前の「日次」「週次」「月次」を得る

さらにこの関数のオフセットを利用する事によって，（基準日を変えること無く）ここからn前の月次を求める事ができます。ここで覚えたいフォーマットとしては， '-1d/-Nd', '-1w/-Nw', '-1M/-NM' で， これらは N日（週，月）前の1日（週，月）の「日次」（「週次」，「月次」）を表現してくれます。

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

ここでは，1つの基準日に対して，一度に4ヶ月前までの月次が取れるクエリを紹介します。
offset.png
ポイントは，9月は30日までしか日数がありませんが，その場合でも適切な範囲のレコードを取得できていることです。
また，上の例の最後の '-2M/-1M' は1月前の2ヶ月分の範囲を指定してしまう事になることに注意が必要です。（つまり，本ブログの趣旨には即していない記述になります。）
2m_range.png
更に応用例として，例えば１０月と０９月のページ毎のPVを１つのクエリで比較することができ，前月比の増加率が高い順に列挙するようなクエリが書けます。
1.png

まとめ

既に，多くの人が「日次」「週次」「月次」での集計クエリを既に利用している状況ですので，本記事のインパクトはそれほど大きいものではありません。一方で本記事の意義を述べるならば，

  • 最近登場した関数： TD_INTERVEL によるテンプレートを紹介できたこと
  • 「日次」「週次」「月次」について，体系化して説明できたこと
  • これからトレジャーデータを活用する人にはどんどんこのテンプレートを活用して欲しいこと
  • 今後の記事ではこのテンプレートが当たり前に使われていること

だと感じています。次回以降の記事はより具体性のあるクエリが紹介できるので是非ともご期待下さい。
また，何かご指摘，ご要望，就職，セミナー依頼などがある方はお気軽に SNS 等を通じて私にご連絡下さい！

引き続き，本ブログをよろしくお願いします。

6
8
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
8