# groonga - An open-source fulltext search engine and column store.

- 日本語版はこちら

### Navigation

- index
- next |
- previous |
- groonga v2.0.5-154-g8f85693 documentation »
- 4. Tutorial »

# 4.5. ドリルダウン

groongaでは、特定のカラム値で検索結果をグループ化することができます。これをドリルダウンと呼びます。

Siteテーブルに２つのカラムを追加します。TLDドメイン名を格納するdomainカラムと、国名を格納するcountryカラムです。これらのカラムの型は、それぞれドメイン名を主キーとするSiteDomainテーブルと、国名を主キーとするSiteCountryテーブルとします。

Execution example:

    table_create --name SiteDomain --flags TABLE_HASH_KEY --key_type ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    table_create --name SiteCountry --flags TABLE_HASH_KEY --key_type ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create --table Site --name domain --flags COLUMN_SCALAR --type SiteDomain
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create --table Site --name country --flags COLUMN_SCALAR --type SiteCountry
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    load --table Site
    [
    {"_key":"http://example.org/","domain":".org","country":"japan"},
    {"_key":"http://example.net/","domain":".net","country":"brazil"},
    {"_key":"http://example.com/","domain":".com","country":"japan"},
    {"_key":"http://example.net/afr","domain":".net","country":"usa"},
    {"_key":"http://example.org/aba","domain":".org","country":"korea"},
    {"_key":"http://example.com/rab","domain":".com","country":"china"},
    {"_key":"http://example.net/atv","domain":".net","country":"china"},
    {"_key":"http://example.org/gat","domain":".org","country":"usa"},
    {"_key":"http://example.com/vdw","domain":".com","country":"japan"}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 9]

domainカラムとcountryカラムでドリルダウンを行う例を以下に示します。

Execution example:

    select --table Site --limit 0 --drilldown domain
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         9
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "country",
    #           "SiteCountry"
    #         ],
    #         [
    #           "domain",
    #           "SiteDomain"
    #         ],
    #         [
    #           "link",
    #           "Site"
    #         ],
    #         [
    #           "links",
    #           "Site"
    #         ],
    #         [
    #           "location",
    #           "WGS84GeoPoint"
    #         ],
    #         [
    #           "title",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         ".org",
    #         3
    #       ],
    #       [
    #         ".net",
    #         3
    #       ],
    #       [
    #         ".com",
    #         3
    #       ]
    #     ]
    #   ]
    # ]

テーブル型を持つカラムに対してドリルダウンを行った場合、参照先のテーブルに存在するカラム値を取得することもできます。ドリルダウンを行ったテーブルには、_nsubrecsという仮想的なカラムが追加されます。このカラムには、グループ化されたレコード数が入ります。

Execution example:

    select --table Site --limit 0 --drilldown domain --drilldown_output_columns _id,_key,_nsubrecs
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         9
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "country",
    #           "SiteCountry"
    #         ],
    #         [
    #           "domain",
    #           "SiteDomain"
    #         ],
    #         [
    #           "link",
    #           "Site"
    #         ],
    #         [
    #           "links",
    #           "Site"
    #         ],
    #         [
    #           "location",
    #           "WGS84GeoPoint"
    #         ],
    #         [
    #           "title",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         1,
    #         ".org",
    #         3
    #       ],
    #       [
    #         2,
    #         ".net",
    #         3
    #       ],
    #       [
    #         3,
    #         ".com",
    #         3
    #       ]
    #     ]
    #   ]
    # ]

複数のカラムに対してドリルダウンを行うことができます。複数のカラムに対してドリルダウンを行う場合には、drilldownパラメータにカラム名をカンマ区切りで与えます。

Execution example:

    select --table Site --limit 0 --drilldown domain,country
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         9
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "country",
    #           "SiteCountry"
    #         ],
    #         [
    #           "domain",
    #           "SiteDomain"
    #         ],
    #         [
    #           "link",
    #           "Site"
    #         ],
    #         [
    #           "links",
    #           "Site"
    #         ],
    #         [
    #           "location",
    #           "WGS84GeoPoint"
    #         ],
    #         [
    #           "title",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         ".org",
    #         3
    #       ],
    #       [
    #         ".net",
    #         3
    #       ],
    #       [
    #         ".com",
    #         3
    #       ]
    #     ],
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "japan",
    #         3
    #       ],
    #       [
    #         "brazil",
    #         1
    #       ],
    #       [
    #         "usa",
    #         2
    #       ],
    #       [
    #         "korea",
    #         1
    #       ],
    #       [
    #         "china",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

ドリルダウン結果を並びかえることができます。例えば、_nsubrecsパラメータの降順で並び替えることができます。

Execution example:

    select --table Site --limit 0 --drilldown country --drilldown_sortby _nsubrecs
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         9
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "country",
    #           "SiteCountry"
    #         ],
    #         [
    #           "domain",
    #           "SiteDomain"
    #         ],
    #         [
    #           "link",
    #           "Site"
    #         ],
    #         [
    #           "links",
    #           "Site"
    #         ],
    #         [
    #           "location",
    #           "WGS84GeoPoint"
    #         ],
    #         [
    #           "title",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "brazil",
    #         1
    #       ],
    #       [
    #         "korea",
    #         1
    #       ],
    #       [
    #         "usa",
    #         2
    #       ],
    #       [
    #         "china",
    #         2
    #       ],
    #       [
    #         "japan",
    #         3
    #       ]
    #     ]
    #   ]
    # ]

ドリルダウン結果は、デフォルトでは10件のみ表示されます。drilldown_offsetパラメータと、drilldown_limitパラメータによって、offsetとlimitを指定することができます。

Execution example:

    select --table Site --limit 0 --drilldown country --drilldown_sortby _nsubrecs --drilldown_limit 2 --drilldown_offset 2
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         9
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "country",
    #           "SiteCountry"
    #         ],
    #         [
    #           "domain",
    #           "SiteDomain"
    #         ],
    #         [
    #           "link",
    #           "Site"
    #         ],
    #         [
    #           "links",
    #           "Site"
    #         ],
    #         [
    #           "location",
    #           "WGS84GeoPoint"
    #         ],
    #         [
    #           "title",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "usa",
    #         2
    #       ],
    #       [
    #         "china",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

文字列型のカラムに対するドリルダウンは、他の型でのドリルダウンに比べて低速です。文字列でのドリルダウンを行いたい場合には、このチュートリアルのように、文字列型を主キーとするテーブルを別途作成し、そのテーブルを型とするカラムを作成します。

### Navigation

- index
- next |
- previous |
- groonga v2.0.5-154-g8f85693 documentation »
- 4. Tutorial »