Groongaのウインドウ関数を使って特定範囲の値を集計する - 2020-08-03 - ククログ

資料ダウンロード

ククログ

株式会社クリアコード > ククログ > Groongaのウインドウ関数を使って特定範囲の値を集計する

Groongaのウインドウ関数を使って特定範囲の値を集計する

データベースなどでまとまったデータを取り扱っていると、ある特定の範囲の値を集計したいことがあります。

例えば、全国展開している商店で、全店舗の売上を合計して一ヶ月の売上を算出するケースを考えてみましょう。 この場合は、PoatgreSQL等のRDBMSではGROUP BY句を、Groongaではドリルダウンを使えば集計できます。 ですが、例えば、各店舗が全体の売上に対してどの程度寄与しているかを知りたい場合、RDBMSのGROUP BY句やGroongaのドリルダウンでは集計できません。

こういったケースでは、 "各店舗の売上/全体の売上" を計算する必要があります。

具体例として、各店舗の月の売上を集計したテーブルを考えます。このテーブルを使って、各店舗の売上が全体の売上に対してどの程度寄与しているかを算出します。

この例では、各店舗の売上は既にカラムとして用意されているので、その値を使えば良いですが、全体の売上は集計する必要があります。 この時、RDBMSのGROUP BY句や、Groongaのドリルダウンでは、集計結果のみを結果として返すので、集計結果と既存の他のカラムの値を使って計算するということができません。

こういった時に使えるのがウインドウ関数と呼ばれる関数群です。 ウインドウ関数も、RDBMSのGROUP BY句や、Groongaのドリルダウンと同様、ある特定範囲の値を集計します。 異なるのは、結果を全てのレコードに追加する点です。

つまり、ウインドウ関数を使うと各レコードに全体の売上を格納するカラムが追加されます。 全体の売上がカラムとして追加されるので、レコード毎に既存のカラムと組み合わせて "各店舗の売上/全体の売上" も計算できます。

では、実際に各店舗の売上が全体の売上に対してどのくらいの割合なのかをGroongaで計算してみましょう。

まずは、各店舗の月の売上を集計したテーブルを用意します。

table_create ReportPerMonth TABLE_NO_KEY
column_create ReportPerMonth branch_name COLUMN_SCALAR ShortText
column_create ReportPerMonth sales COLUMN_SCALAR UInt32
column_create ReportPerMonth month COLUMN_SCALAR Time

load --table ReportPerMonth
[
{"branch_name": "Sayama"   , "sales": 256125  , "month": "2020-04-30 00:00:00.000000"},
{"branch_name": "Iruma"    , "sales": 211546  , "month": "2020-04-30 00:00:00.000000"},
{"branch_name": "Hakodate" , "sales": 122928  , "month": "2020-04-30 00:00:00.000000"},
{"branch_name": "Otaru"    , "sales": 1132001 , "month": "2020-04-30 00:00:00.000000"},
{"branch_name": "Umeda"    , "sales": 188122  , "month": "2020-04-30 00:00:00.000000"},
{"branch_name": "Kure"     , "sales": 120160  , "month": "2020-04-30 00:00:00.000000"},

{"branch_name": "Sayama"   , "sales": 156125  , "month": "2020-05-30 00:00:00.000000"},
{"branch_name": "Iruma"    , "sales": 71546   , "month": "2020-05-30 00:00:00.000000"},
{"branch_name": "Hakodate" , "sales": 111928  , "month": "2020-05-30 00:00:00.000000"},
{"branch_name": "Otaru"    , "sales": 1332001 , "month": "2020-05-30 00:00:00.000000"},
{"branch_name": "Umeda"    , "sales": 117122  , "month": "2020-05-30 00:00:00.000000"},
{"branch_name": "Kure"     , "sales": 1120160 , "month": "2020-05-30 00:00:00.000000"},

{"branch_name": "Sayama"   , "sales": 1561250 , "month": "2020-06-30 00:00:00.000000"},
{"branch_name": "Iruma"    , "sales": 211546  , "month": "2020-06-30 00:00:00.000000"},
{"branch_name": "Hakodate" , "sales": 199928  , "month": "2020-06-30 00:00:00.000000"},
{"branch_name": "Otaru"    , "sales": 1452001 , "month": "2020-06-30 00:00:00.000000"},
{"branch_name": "Umeda"    , "sales": 111122  , "month": "2020-06-30 00:00:00.000000"},
{"branch_name": "Kure"     , "sales": 100160  , "month": "2020-06-30 00:00:00.000000"},
]

branch_name が各店舗の名前です。 sales はその月の売上です。 month は売上月です。

データをロードしたら次にウインドウ関数を使って、月毎の全体の売上を集計しましょう。 まずは、 --columns[LABEL].window.group_keys に month を指定して月毎にグループ化します。

全体の売上は、月毎の売上の総和なので、 --columns[LABEL].value に window_sum(sales) を 使って計算します。 window_sum は window.group_keys でグループ化したグループ毎に指定したカラムの値の総和をとります。

つまり以下のようなクエリーを実行します。

plugin_register functions/time

select ReportPerMonth \
  --limit -1 \
  --columns[sales_sum_per_month].stage initial \
  --columns[sales_sum_per_month].type UInt32 \
  --columns[sales_sum_per_month].value 'window_sum(sales)' \
  --columns[sales_sum_per_month].window.group_keys month \
  --output_columns '_id, branch_name, sales, sales_sum_per_month, time_format_iso8601(month)'

上記のクエリーの結果は以下の通りです。 ポイントは、月毎の全体の売上を表す sales_sum_per_month が全てのレコードに追加されているところです。 月毎に同じ値がsales_sum_per_monthに設定されていることが確認できるかと思います。

例えば、2020年4月の全体の売上は、2030882となっているので、売上月が2020-04-01T00:00:00.000000+09:00 のレコードのsales_sum_per_monthは、全て2030882が設定されています。

[
  [
    0,
    1594109666.415102,
    0.002141475677490234
  ],
  [
    [
      [
        18
      ],
      [
        [
          "_id",
          "UInt32"
        ],
        [
          "branch_name",
          "ShortText"
        ],
        [
          "sales",
          "UInt32"
        ],
        [
          "sales_sum_per_month",
          "UInt32"
        ],
        [
          "time_format_iso8601",
          null
        ]
      ],
      [
        1,
        "Sayama",
        256125,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        2,
        "Iruma",
        211546,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        3,
        "Hakodate",
        122928,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        4,
        "Otaru",
        1132001,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        5,
        "Umeda",
        188122,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        6,
        "Kure",
        120160,
        2030882,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        7,
        "Sayama",
        156125,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        8,
        "Iruma",
        71546,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        9,
        "Hakodate",
        111928,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        10,
        "Otaru",
        1332001,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        11,
        "Umeda",
        117122,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        12,
        "Kure",
        1120160,
        2908882,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        13,
        "Sayama",
        1561250,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        14,
        "Iruma",
        211546,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        15,
        "Hakodate",
        199928,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        16,
        "Otaru",
        1452001,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        17,
        "Umeda",
        111122,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        18,
        "Kure",
        100160,
        3636007,
        "2020-06-01T00:00:00.000000+09:00"
      ]
    ]
  ]
]

これで、月毎の全体の売上が集計できました。 次は、各店舗の売上が全体の売上の何％なのかを算出します。

月毎の全体の売上は、前述の通り sales_sum_per_month カラムに格納されています。 このカラムは全レコードに追加されているので、このカラムの値と、各店舗の売上 sales を sales / sales_sum_per_month のように除算することで、各店舗の売上が全体の売上の何％なのかを算出できます。

結果は、each_sales_per_allカラムに格納されます。

sales / sales_sum_per_monthの値は実数になる可能性があるので、実際のクエリーでは、sales * 1.0 / sales_sum_per_monthのようにsalesの値に* 1.0して、計算結果の型を整数から実数にしています。

select ReportPerMonth \
  --limit -1 \
  --columns[sales_month].stage initial \
  --columns[sales_month].type Time \
  --columns[sales_month].value 'time_classify_month(month)' \
  --columns[sales_sum_per_month].stage initial \
  --columns[sales_sum_per_month].type UInt32 \
  --columns[sales_sum_per_month].value 'window_sum(sales)' \
  --columns[sales_sum_per_month].window.group_keys sales_month \
  --columns[each_sales_per_all].stage output \
  --columns[each_sales_per_all].type Float \
  --columns[each_sales_per_all].value 'sales * 1.0 / sales_sum_per_month' \
  --output_columns '_id, branch_name, sales, sales_sum_per_month, each_sales_per_all, time_format_iso8601(sales_month)'
[
  [
    0,
    1594109970.315803,
    0.003779888153076172
  ],
  [
    [
      [
        18
      ],
      [
        [
          "_id",
          "UInt32"
        ],
        [
          "branch_name",
          "ShortText"
        ],
        [
          "sales",
          "UInt32"
        ],
        [
          "sales_sum_per_month",
          "UInt32"
        ],
        [
          "each_sales_per_all",
          "Float"
        ],
        [
          "time_format_iso8601",
          null
        ]
      ],
      [
        1,
        "Sayama",
        256125,
        2030882,
        0.1261151558780865,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        2,
        "Iruma",
        211546,
        2030882,
        0.1041645944963814,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        3,
        "Hakodate",
        122928,
        2030882,
        0.0605293660586878,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        4,
        "Otaru",
        1132001,
        2030882,
        0.5573937826028297,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        5,
        "Umeda",
        188122,
        2030882,
        0.09263068952307421,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        6,
        "Kure",
        120160,
        2030882,
        0.05916641144094044,
        "2020-04-01T00:00:00.000000+09:00"
      ],
      [
        7,
        "Sayama",
        156125,
        2908882,
        0.05367182305779334,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        8,
        "Iruma",
        71546,
        2908882,
        0.02459570377897763,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        9,
        "Hakodate",
        111928,
        2908882,
        0.03847801320232309,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        10,
        "Otaru",
        1332001,
        2908882,
        0.4579082272845719,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        11,
        "Umeda",
        117122,
        2908882,
        0.04026357892826179,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        12,
        "Kure",
        1120160,
        2908882,
        0.3850826537480723,
        "2020-05-01T00:00:00.000000+09:00"
      ],
      [
        13,
        "Sayama",
        1561250,
        3636007,
        0.4293858620184174,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        14,
        "Iruma",
        211546,
        3636007,
        0.05818085608746078,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        15,
        "Hakodate",
        199928,
        3636007,
        0.05498559271200523,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        16,
        "Otaru",
        1452001,
        3636007,
        0.3993394402155991,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        17,
        "Umeda",
        111122,
        3636007,
        0.03056154732375378,
        "2020-06-01T00:00:00.000000+09:00"
      ],
      [
        18,
        "Kure",
        100160,
        3636007,
        0.02754670164276361,
        "2020-06-01T00:00:00.000000+09:00"
      ]
    ]
  ]
]

このようにして、各店舗の売上が全体の売上のどのくらいなのかを計算できました。

ウインドウ関数を使って、ある特定範囲の値を算出し、その値を既存のカラムの値と組み合わせて計算する例を紹介しました。 この例のように、集計した値と、既存のカラムの値を組み合わせてなにか計算したい場合にウインドウ関数の使用を検討してみてはいかがでしょうか？

関連情報

現在クリアコードではソフトウェア開発者を募集しています！ フリーソフトウェア開発での開発の仕方をベースに、成果や知識を公開し共有しながら業務に携われます。

クリアコードのYouTubeチャンネルです。メンバーの登壇動画や、開発プロダクトに関する動画を公開しています。

Groongaについて開発者に質問できる「グルカイ！」ほぼ毎週火曜日12:15-12:45　Groongaチャンネルで開催中です。 Groongaについて知りたい人、ぜひご参加ください。