SQLで理解する！Groongaのドリルダウン入門

select max(_id) from table; なことをGroongaでしたい阿部です。

冒頭から余談です。 SQLは大文字で書く方がよくある書き方なんですが、小文字で書く派もそれなりいて私は小文字で書くのが好きです。大文字で書くのがめんどくさいからです。ということで、本記事ではSQLは小文字で書きます。

閑話休題。

Groongaは集計もできます。ただちょっとややこしいので、SQLで書くとこんな感じになりますよ、ということを示して世に多いであろうSQL好きにGroongaをPRするための記事です。

おことわり書き

コマンドの省略

例示するGroongaコマンドが長くなるので --output_pretty yes は省略して掲載しています。

レスポンス例の省略

Groongaのレスポンスは [HEADER, BODY] の形式です。

参考: https://groonga.org/ja/docs/reference/command/output_format.html

まずこの HEADER の部分は省略します。また BODY の中には「何も加工していない検索結果部」と「集計結果部」の2つになります。今回は集計に焦点を当てるので、「何も加工していない検索結果部」は省略します。

説明に使うデータ

_key	tag	priority
Groonga v13	Groonga	10
Groonga v14	Groonga	60
Mroonga v13	Mroonga	61
Mroonga v14	Mroonga	24
Mroonga v15	Mroonga	68
Rroonga v12	Rroonga	3
Rroonga v13	Rroonga	-9
Rroonga v14	Rroonga	29
Rroonga v15	Rroonga	0

Groongaにデータを投入するには次のコマンドを実行します。

table_create Tags TABLE_PAT_KEY ShortText

table_create Memos TABLE_HASH_KEY ShortText
column_create Memos tag COLUMN_SCALAR Tags
column_create Memos priority COLUMN_SCALAR Int64

load --table Memos
[
{"_key": "Groonga v13", "tag": "Groonga", "priority": 10},
{"_key": "Groonga v14", "tag": "Groonga", "priority": 60},
{"_key": "Mroonga v13", "tag": "Mroonga", "priority": 61},
{"_key": "Mroonga v14", "tag": "Mroonga", "priority": 24},
{"_key": "Mroonga v15", "tag": "Mroonga", "priority": 68},
{"_key": "Rroonga v12", "tag": "Rroonga", "priority": 3},
{"_key": "Rroonga v13", "tag": "Rroonga", "priority": -9},
{"_key": "Rroonga v14", "tag": "Rroonga", "priority": 29},
{"_key": "Rroonga v15", "tag": "Rroonga", "priority": 0}
]

説明すること

6つのSQLに相当するGroongaコマンドを例に説明します。

select tag, count(*) from Memos group by tag;
select tag, avg(priority) from Memos group by tag;
select tag, avg(priority) from Memos group by tag order by tag desc;
select tag, avg(priority) from Memos group by tag order by avg(priority);
select tag, max(priority) from Memos where tag != 'Rroonga' group by tag;
select tag, max(priority) from Memos where tag != 'Rroonga' group by tag having max(priority) > 65;

`select tag, count(*) from Memos group by tag;`

シンプルにgroup byして、count()する例です。

Groongaコマンド例:

select Memos \
  --drilldown tag \
  --drilldown_calc_types COUNT \
  --drilldown_output_columns _key,_nsubrecs

--drilldown
- SQLでいうところのgroup byに相当するのものです
- group byと同様に集計したいカラムを指定します
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#select-drilldown
--drilldown_calc_types
- 集計関数を指定します。SQLでいうところのcount()に相当するものです
- （例ということでCOUNTを指定しましたが常に有効なので、COUNTは省略できます）
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#drilldown-calc-types
--drilldown_output_columns
- SQLでいうところのselect col1, col2に相当するものです。出力したいカラム名を指定します
- ちょっと違うのが集計結果は「疑似カラム」と呼ばれるものを指定することです
  - _nsubrecsはcount()の結果です
  - drilldown_output_columnsの_keyはdrilldownで指定したカラムの別名です
    - この例だとdrilldown_output_columns._keyはMemos.tagです。Memos._keyのことではないので注意です
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#drilldown-output-columns

実行結果例:

[
  [HEADER],
  [
    [何も加工していない検索結果部],
    [
      [
        3
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_nsubrecs",
          "Int32"
        ]
      ],
      [
        "Groonga",
        2
      ],
      [
        "Mroonga",
        3
      ],
      [
        "Rroonga",
        4
      ]
    ]
  ]
]

以下の配列でレコード数分の結果が得られます。最初のレコードはGroongaというtagのレコードが2件あったという意味です。

[
  _key （Memos.tag）,
  _nsubrecs（count()の結果）
]

`select tag, avg(priority) from Memos group by tag;`

シンプルにgroup byして、avg()する例です。

Groongaコマンド例:

select Memos \
  --drilldown tag \
  --drilldown_calc_types AVG \
  --drilldown_calc_target priority \
  --drilldown_output_columns _key,_avg

--drilldown_calc_target
- count()の場合は行数を数えるので引数はなしでも良いですが、avg()の場合は列を指定する必要があります。ということでこのオプションです
- この例ではpriorityを指定しているので、SQLではavg(priority)相当です
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#drilldown-calc-target

実行結果例（集計結果部のみ記載）:

    [
      [
        3
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_avg",
          "Float"
        ]
      ],
      [
        "Groonga",
        35.0
      ],
      [
        "Mroonga",
        51.0
      ],
      [
        "Rroonga",
        5.75
      ]
    ]

`select tag, avg(priority) from Memos group by tag order by tag desc;`

group byして、avg()した結果をtagの降順でソートする例です。

Groongaコマンド例:

select Memos \
  --drilldown tag \
  --drilldown_calc_types AVG \
  --drilldown_calc_target priority \
  --drilldown_output_columns _key,_avg \
  --drilldown_sort_keys -_key

--drilldown_sort_keys
- 集計した結果でソートできます。drilldown_output_columnsと同様に疑似カラムで指定します
- SQLで言うところのdescの場合はカラム名の前にハイフン-をつけます（ascの場合はカラム名のみです）
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#drilldown-sort-keys

実行結果例（集計結果部のみ記載）:

    [
      [
        3
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_avg",
          "Float"
        ]
      ],
      [
        "Rroonga",
        5.75
      ],
      [
        "Mroonga",
        51.0
      ],
      [
        "Groonga",
        35.0
      ]
    ]

`select tag, avg(priority) from Memos group by tag order by avg(priority);`

group byして、avg()した結果をavg()の集計結果を使って昇順ソートする例です。

Groongaコマンド例:

select Memos \
  --drilldown tag \
  --drilldown_calc_types AVG \
  --drilldown_calc_target priority \
  --drilldown_output_columns _key,_avg \
  --drilldown_sort_keys _avg

--drilldown_sort_keys
- 昇順（asc）なので、ハイフン-なしの_avgを指定しています
- _avgも「疑似カラム」で平均を計算した結果です

実行結果例（集計結果部のみ記載）:

    [
      [
        3
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_avg",
          "Float"
        ]
      ],
      [
        "Rroonga",
        5.75
      ],
      [
        "Groonga",
        35.0
      ],
      [
        "Mroonga",
        51.0
      ]
    ]

`select tag, max(priority) from Memos where tag != 'Rroonga' group by tag;`

対象レコードをフィルターして、group byして、max()する例です。

Groongaコマンド例:

select Memos \
  --filter 'tag != "Rroonga"' \
  --drilldown tag \
  --drilldown_calc_types MAX \
  --drilldown_calc_target priority \
  --drilldown_output_columns _key,_max

--filter
- whereに相当する処理もできます。この例ではfilterを使いました
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#search-condition-filter
- 他にもmatch_columnsとqueryが利用できます
  - filterとの違いは参考URLをご覧ください
  - 参考: https://groonga.org/ja/docs/reference/commands/select.html#search-condition-query

実行結果例（集計結果部のみ記載）:

    [
      [
        2
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_max",
          "Int64"
        ]
      ],
      [
        "Groonga",
        60
      ],
      [
        "Mroonga",
        68
      ]
    ]

`select tag, max(priority) from Memos where tag != 'Rroonga' group by tag having max(priority) > 65;`

対象レコードをフィルターして、group byして、max()して、max()の結果でフィルターする例です。

Groongaコマンド例:

select Memos \
  --filter 'tag != "Rroonga"' \
  --drilldown tag \
  --drilldown_calc_types MAX \
  --drilldown_calc_target priority \
  --drilldown_output_columns _key,_max \
  --drilldown_filter '_max > 65'

--drilldown_filter
- SQLのhaving相当のことができます。集計結果を条件にフィルターできます
- ここで登場している_maxも「疑似カラム」で最大の値を指しています
- 参考: https://groonga.org/ja/docs/reference/commands/select.html#drilldown-filter

実行結果例（集計結果部のみ記載）:

    [
      [
        1
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_max",
          "Int64"
        ]
      ],
      [
        "Mroonga",
        68
      ]
    ]

補足: 複数のカラムで`drilldown`

SQLのselect _key, tag, count(*) from Memos group by _key, tag;みたいなことはdrilldownではできません。 --drilldownに複数のカラムを指定できるのですが、結果がSQLから想像するものとは違うはずです。

Groongaコマンド例:

select Memos \
  --drilldown _key,tag \
  --drilldown_calc_types COUNT \
  --drilldown_output_columns _key,_nsubrecs

実行結果例（集計結果部のみ記載）:

    [
      [
        9
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_nsubrecs",
          "Int32"
        ]
      ],
      [
        "Groonga v13",
        1
      ],
      [
        "Groonga v14",
        1
      ],
      [
        "Mroonga v13",
        1
      ],
      [
        "Mroonga v14",
        1
      ],
      [
        "Mroonga v15",
        1
      ],
      [
        "Rroonga v12",
        1
      ],
      [
        "Rroonga v13",
        1
      ],
      [
        "Rroonga v14",
        1
      ],
      [
        "Rroonga v15",
        1
      ]
    ],
    [
      [
        3
      ],
      [
        [
          "_key",
          "ShortText"
        ],
        [
          "_nsubrecs",
          "Int32"
        ]
      ],
      [
        "Groonga",
        2
      ],
      [
        "Mroonga",
        3
      ],
      [
        "Rroonga",
        4
      ]
    ]

複数のカラムを指定した場合はselect _key, count(*) from Memos group by _key;とselect tag, count(*) from Memos group by tag;の2つのクエリを実行したような結果が得られます。

参考: https://groonga.org/ja/docs/tutorial/drilldown.html#drilldown-with-multiple-column

朗報！

数行前にできません！と書きましたが、諦めるのは早いです。 drilldowns[${LABEL}]を含むより高度なドリルダウンができるパラメーターもあって、それを使うと実現できます。

参考: https://groonga.org/ja/docs/reference/commands/select.html#advanced-drilldown-related-parameters

このパラメーターについての説明も含めると記事がだいぶ長くなってしまうので別記事にて紹介します。

おまけ: `select max(_id) from table;`

冒頭のあいさつに登場したselect max(_id) from table;はdrilldownでは得られません。 --drilldownオプション（SQLでいうところのgroup by）が必須のためです。

次のようなコマンドで欲しい情報は得られます。

Groongaコマンド例:

select Memos \
  --output_columns _id \
  --sortby -_id \
  --limit 1

こちらも朗報！

数行前に得られません！と書きましたが、こちらも諦めるのは早いです。このクエリもdrilldowns[${LABEL}]を含むよりパラメーターで実現できます。別記事にて紹介します。

まとめ

SQLの集計クエリをGroongaのドリルダウンに置き換えて、Groongaでの集計について説明しました。ぜひGroongaで集計してください！

Groongaのドリルダウンをもっと有効活用したい場合はGroongaサポートサービスを提供していますのでお問い合わせください。

ドリルダウンについてを解説する動画もあるので、よかったら見てみてください！

ククログ

おことわり書き

コマンドの省略

レスポンス例の省略

説明に使うデータ

説明すること

`select tag, count(*) from Memos group by tag;`

`select tag, avg(priority) from Memos group by tag;`

`select tag, avg(priority) from Memos group by tag order by tag desc;`

`select tag, avg(priority) from Memos group by tag order by avg(priority);`

`select tag, max(priority) from Memos where tag != 'Rroonga' group by tag;`

`select tag, max(priority) from Memos where tag != 'Rroonga' group by tag having max(priority) > 65;`

補足: 複数のカラムで`drilldown`

朗報！

おまけ: `select max(_id) from table;`

こちらも朗報！

まとめ

関連情報

ククログ