月単位や日単位などある時間範囲で検索したい場面は多いです。 こんな時は月単位、日単位で日付情報を格納するカラムを作り、それを用いて検索したくなります。
しかし、検索要件が変わり、新たに年単位や週単位で検索する必要が出た場合、それらの情報を格納するカラムを増やさなければなりません。 日付情報は一つのカラムに格納し、その情報を年単位や週単位に丸めて検索できれば、要件の変更に追従してカラムを増やす必要がなくなります。
Groongaでは、time_classify関数を使って日付情報をある期間で丸めることができます。
time_classify関数には以下の関数が存在し、それぞれ、年、月、週、日、曜日、時間、分、秒単位で時間を丸めることができます。
-
time_classify_year -
time_classify_month -
time_classify_week -
time_classify_day -
time_classify_day_of_week -
time_classify_hour -
time_classify_minute -
time_classify_second
例えば、time_classify_yearなら同じ年の時間を丸めることができます。
つまり、2020年1月13日と2020年5月21日が、2020年1月1日 00:00:00に丸められます。
これらの関数の値は以下のようにドリルダウンのキーとして使用できるので、ある範囲の時間のデータを集計できます。 例えば以下の例では、売上台帳から、ある製品が月単位でどのくらい売れたかを集計しています。
plugin_register functions/time
table_create Sales TABLE_NO_KEY
column_create Sales name COLUMN_SCALAR ShortText
column_create Sales price COLUMN_SCALAR UInt32
column_create Sales timestamp COLUMN_SCALAR Time
load --table Sales
[
{"name": "Apple" , "price": "256", "timestamp": "2020-01-30 11:50:11.000000"},
{"name": "Apple" , "price": "256", "timestamp": "2020-05-01 10:20:00.000000"},
{"name": "Orange", "price": "122", "timestamp": "2020-05-02 11:44:12.000001"},
{"name": "Apple" , "price": "256", "timestamp": "2020-01-07 19:50:23.000020"},
{"name": "banana", "price": "88" , "timestamp": "2020-05-08 11:00:02.000000"},
{"name": "banana", "price": "88" , "timestamp": "2020-05-08 21:34:12.000001"}
]
select Sales \
--limit -1 \
--columns[month].stage initial \
--columns[month].type Time \
--columns[month].value "time_classify_month(timestamp)" \
--drilldowns[sales_per_month_name].keys "month, name" \
--drilldowns[sales_per_month_name].output_columns "_value.name, _nsubrecs, time_format_iso8601(_value.month)" \
--drilldowns[sales_per_month_name].limit -1
[
[
0,
1590035787.669446,
0.003034353256225586
],
[
[
[
6
],
[
[
"_id",
"UInt32"
],
[
"month",
"Time"
],
[
"name",
"ShortText"
],
[
"price",
"UInt32"
],
[
"timestamp",
"Time"
]
],
[
1,
1577804400.0,
"Apple",
256,
1580352611.0
],
[
2,
1588258800.0,
"Apple",
256,
1588296000.0
],
[
3,
1588258800.0,
"Orange",
122,
1588387452.000001
],
[
4,
1577804400.0,
"Apple",
256,
1578394223.00002
],
[
5,
1588258800.0,
"banana",
88,
1588903202.0
],
[
6,
1588258800.0,
"banana",
88,
1588941252.000001
]
],
{
"sales_per_month_name":
[
[
4
],
[
[
"name",
"ShortText"
],
[
"_nsubrecs",
"Int32"
],
[
"time_format_iso8601",
null
]
],
[
"Apple",
2,
"2020-01-01T00:00:00.000000+09:00"
],
[
"Apple",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"Orange",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"banana",
2,
"2020-05-01T00:00:00.000000+09:00"
]
]
}
]
]
上記の例では、--columns[month]を使って動的にカラムを生成しています。
Groongaには動的カラムというクエリー実行時に一時的に作成できるカラムがあります。
この動的カラムを使って、time_classify_month(timestamp)の結果をmonthカラムに格納しています。
次に--drilldowns[sales_per_month_name]を使って、同じ期間に売れた製品をグループ化します。
ポイントは、--drilldowns[sales_per_month_name].keys "month, name" のところです。
ここでは、どのカラムの値を使ってグループ化するかを指定しています。
monthを指定することで、同じ月のレコードをグループ化しています。また、nameを指定することで、製品名でもグループ化しています。
このように、monthカラムの値と、nameカラムの値をグループ化すると以下の結果になります。
{
"sales_per_month_name":
[
[
4
],
[
[
"name",
"ShortText"
],
[
"_nsubrecs",
"Int32"
],
[
"time_format_iso8601",
null
]
],
[
"Apple",
2,
"2020-01-01T00:00:00.000000+09:00"
],
[
"Apple",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"Orange",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"banana",
2,
"2020-05-01T00:00:00.000000+09:00"
]
]
}
_nsubrecsの値がグループ化したレコードの数を表します。
したがって、例えば["Apple",2,"2020-01-01T00:00:00.000000+09:00"]という結果なら、nameの値がAppleでmonthカラムの値が
2020-01-01T00:00:00.000000+09:00というレコードが2件あると解釈します。
(Timeの値は通常だとUNIX時間で表示されますが、わかりにくいのでこの例では、人間に読みやすい形式に変換して出力しています。)
つまり、2020年1月にりんごは2個売れたと解釈できます。
もう少し高度な使い方として、以下のようにドリルダウンを使ってある期間のデータを計算して加工できます。 以下の例では月毎の売上の合計値を出力しています。
select Sales \
--limit -1 \
--columns[month].stage initial \
--columns[month].type Time \
--columns[month].value "time_classify_month(timestamp)" \
--drilldowns[sales_per_month_name].keys "month, name" \
--drilldowns[sales_per_month_name].output_columns "_value.name, _nsubrecs, time_format_iso8601(_value.month)" \
--drilldowns[sales_per_month_name].limit -1 \
--drilldowns[sum_sales_per_month].keys month \
--drilldowns[sum_sales_per_month].calc_target price \
--drilldowns[sum_sales_per_month].calc_types SUM \
--drilldowns[sum_sales_per_month].output_columns "time_format_iso8601(_key), _sum"
[
[
0,
1590116735.057712,
0.0004277229309082031
],
[
[
[
6
],
[
[
"_id",
"UInt32"
],
[
"month",
"Time"
],
[
"name",
"ShortText"
],
[
"price",
"UInt32"
],
[
"timestamp",
"Time"
]
],
[
1,
1577804400.0,
"Apple",
256,1
580352611.0
],
[
2,
1588258800.0,
"Apple",
256,
1588296000.0
],
[
3,
1588258800.0,
"Orange",
122,
1588387452.000001
],
[
4,
1577804400.0,
"Apple",
256,
1578394223.00002
],
[
5,
1588258800.0,
"banana",
88,
1588903202.0
],
[
6,
1588258800.0,
"banana",
88,
1588941252.000001
]
],
{
"sales_per_month_name":
[
[
4
],
[
[
"name",
"ShortText"
],
[
"_nsubrecs",
"Int32"
],
[
"time_format_iso8601",
null
]
],
[
"Apple",
2,
"2020-01-01T00:00:00.000000+09:00"
],
[
"Apple",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"Orange",
1,
"2020-05-01T00:00:00.000000+09:00"
],
[
"banana",
2,
"2020-05-01T00:00:00.000000+09:00"
]
],
"sum_sales_per_month":
[
[
2
],
[
[
"time_format_iso8601",
null
],
[
"_sum",
"Int64"
]
],
[
"2020-01-01T00:00:00.000000+09:00",
512
],
[
"2020-05-01T00:00:00.000000+09:00",
554
]
]
}
]
]
ドリルダウンは、同一グループのカラムの値を計算できます。 具体的には、合計値と最大値、最小値、平均値を計算できます。
上記の例では、月ごとの売上を計算したいので、合計値を計算します。
どんな計算をするかは、--drilldowns[sum_sales_per_month].calc_typesで指定しています。
合計値を計算する場合はSUMを指定します。
最大値の場合はMAX、最小値の場合はMIN、平均値の場合はAVGを指定します。
計算した値は、それぞれ_sum、_max、_min、_avgというカラムに格納されます。(これらのカラムは自動的に作成されるので、ユーザーが用意する必要はありません。)
計算対象のカラムは、--drilldowns[sum_sales_per_month].calc_targetで指定しています。
上記の例では売上を集計したいので、priceカラムを指定しています。
最後に--drilldowns[sum_sales_per_month].output_columnsで出力する情報を指定しています。
月毎の売上がわかれば良いので、月と売上の合計値が出力されれば良いことになります。
したがって、--drilldowns[sum_sales_per_month].output_columnsには、_keyと_sumを指定しています。
_keyはドリルダウンのキーを表すのでmonthカラムの値を出力します。
_sumはグループ内のpriceカラムの値の合計値を出力します。
結果は以下のようになり、この店舗では、2020年1月の売上は512円で2020年5月の売上は554円であることがわかります。
[
"2020-01-01T00:00:00.000000+09:00",
512
],
[
"2020-05-01T00:00:00.000000+09:00",
554
]
このようにして、月毎の売上の合計値を出力できました。
月単位や日単位などある時間範囲で集計したい場面に遭遇したら、time_classify関数の使用を検討してみてはいかがでしょうか？
