ククログ

株式会社クリアコード > ククログ > Groongaである時間範囲のデータを集計する方法

Groongaである時間範囲のデータを集計する方法

月単位や日単位などある時間範囲で検索したい場面は多いです。 こんな時は月単位、日単位で日付情報を格納するカラムを作り、それを用いて検索したくなります。

しかし、検索要件が変わり、新たに年単位や週単位で検索する必要が出た場合、それらの情報を格納するカラムを増やさなければなりません。 日付情報は一つのカラムに格納し、その情報を年単位や週単位に丸めて検索できれば、要件の変更に追従してカラムを増やす必要がなくなります。

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の値がApplemonthカラムの値が 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関数の使用を検討してみてはいかがでしょうか?