簡単関係照会言語 Erq で快適なデータベース分析生活を送る

Erq（アーク）は、SQLの代わりにアドホックなデータ分析に用いることを主目的とした、新しいデータベース言語です。リレーショナルデータベースは便利ですが、アドホックなデータ分析を行う上で、SQLの文法は面倒なものです。Erqは、SQLのセマンティクスは極力そのままに異なる文法を採用することで、簡単にクエリを書けるようになっています。

SQLクエリの実例

私はSQLiteデータベースに漢字の文字情報を入れて、複雑な検索や分析ができるようにしているのですが、実際にそのデータベースを使ったクエリ例を見てみましょう。使っているMojidataデータベースは、次のリポジトリからビルドできます。

• GitHub - mandel59/mojidata: Character Database for CJKV Text Processing

まず、漢字の読みを集めたmji_readingテーブルの内容を全部表示するために、SQLで次のように照会します。（末尾のセミコロン ; は、SQLite CLIにおける文の終端記号です。）

select * from mji_reading;

データは全部で122148件あるのですが、冒頭のデータはこんな感じになっています。MJ文字図形名は、文字情報基盤における図形番号です。

"MJ文字図形名","読み"
MJ000001,"おなじ"
MJ000001,"くりかえし"
MJ000001,"のま"
MJ000002,"しめ"
MJ000004,"キュウ"
MJ000004,"おか"
MJ000005,"テン"
MJ000006,"キ"
MJ000006,"よろこぶ"
MJ000007,"カ"

ここで、簡単な分析として、読みごとに件数をカウントし、多い順に10件表示してみましょう。

select 読み, count(*) from mji_reading group by 読み order by count(*) desc limit 10;

"読み",count(*)
"コウ",2775
"ショウ",1985
"ソウ",1732
"シ",1730
"トウ",1675
"キ",1536
"カン",1515
"セン",1476
"キョウ",1437
"ケン",1279

カラムを追加し、読みに対応する漢字の例をいくつか表示してみましょう。mji_readingに格納されているのはUnicodeではなくMJ文字図形名なので、Unicodeの漢字を表示するには、別のテーブル mji と結合して照会する必要があります。UnicodeとMJ文字図形名は1対多対応なので、重複するUnicodeを排除するために、select句にdistinctキーワードを使います。また、表示する漢字を最大5つに制限するために、サブクエリを二重に使って、limit句で制限をかけたデータに対してgroup_concat()集約関数で集約を行うことにします。そうすると、クエリはこのようになります。

select
  読み,
  count(*),
  (
    select group_concat(c)
    from (
      select distinct 対応するUCS as c
      from mji
      natural join mji_reading as r
      where r.読み = mji_reading.読み
      limit 5
    )
  ) as 例
from mji_reading
group by 読み
order by count(*) desc
limit 10;

"読み",count(*),"例"
"コウ",2775,"㐬,㒶,㓂,㓚,㓛"
"ショウ",1985,"㐮,㐮,㐼,㑱,㒉"
"ソウ",1732,"㐮,㑿,㒎,㔌,㔿"
"シ",1730,"㑥,㒋,㒾,㓨,㓼"
"トウ",1675,"㑽,㓊,㓱,㓸,㔁"
"キ",1536,"㐂,㑧,㑶,㒫,㔳"
"カン",1515,"㒈,㓧,㔋,㔶,㖤"
"セン",1476,"㑒,㒄,㒨,㒰,㔊"
"キョウ",1437,"㐩,㓋,㓏,㓙,㕳"
"ケン",1279,"㐸,㒽,㓩,㓺,㔓"

上記の例は単純ですが、SQLの冗長性・煩雑性がよく表れています。

• select句とgroup by句やorder by句に重複して書くことになる。
• select句はクエリの先頭、group by句やorder by句はクエリの末尾にあるので、カーソル移動が面倒くさい。
• サブクエリにも都度selectキーワードを書くので、多重のサブクエリは記述量がすごく多くなってしまう。
• 処理の流れ上は後にくるselect句が先頭にあるので、処理の流れがクエリ上で行ったり来たりしてしまう。
• テーブル名やカラム名の別名を式の後に書くので、後から読むとき、特に長い式の場合に、見づらい。

Erqクエリの実例

今度は同じ分析をErqで行ってみましょう。テーブルの全件取得は、Erqではテーブル名を書くだけです。（Erq CLIでは、文の終端記号に";;"を使っています。）

mji_reading;;

読みごとに件数をカウントし、多い順に10件表示するには、次のように書きます。

mji_reading {読み => count(*) desc} limit 10;;

ブレース・アロー記法 { ... => ... } はErqにおける集約クエリの書き方で、アローの左側にグループに使うカラムを、アローの右側に集約関数のカラムを書きます。また、カラムの後に asc/desc を指定することもできます。この記法によって、SQLのselect句・group by句・order by句の指定を一度に行えるので、Erqでは集約を書くのが簡単になっています。

サブクエリはどうでしょうか。SQLのときと同様に、漢字の例のカラムを追加してみます。

mji_reading
{
  読み =>
  count(*) desc,
  例:
    from mji
    natural join r: mji_reading
    [r.読み = mji_reading.読み]
    {c: 対応するUCS}
    distinct
    limit 5
    {group_concat(c)}
}
limit 10;;

• Erqでは、サブクエリの先頭にfromを書きます。サブクエリを括弧で括る必要はありません。（トップレベルのクエリにもfromをつけて良いのですが、省略できます。サブクエリではテーブル名とカラム名の区別のため、基本的にはfromキーワードが必要です。）
• カラム名やテーブル名の別名は、式の前に書きます。
• ブラケット記法 [...] はwhere句・having句に相当します。
• ブレース記法 {...} はselect句に相当しますが、from句の後に書きます。
• distinctキーワードは、Erqでは独立したdistinct句です。
• ブラケット記法やブレース記法は、クエリに複数書いても問題ありません。

Erqのこれらの特徴により、SQLでは二重のサブクエリとして書いていたクエリを、すっきりとした直列的なサブクエリとして記述できました。

そのほかのクエリ例

他にもいくつかクエリ例を載せてみます。Erq CLIではErqクエリから変換されたSQLを出力するので、どういう変換が行われるか分かるようになっています。

ブラケット記法がhaving句に変換される例

erq> unihan_variant[property='kTraditionalVariant']{s: UCS => t: group_concat(value, '')}[count(*)>1] limit 10;;
select UCS as s, group_concat(value, '') as t from unihan_variant where (property = 'kTraditionalVariant') group by (UCS) having (count(*) > 1) limit 10
["s","t"]
["䴘","鷈鷉"]
["䴙","鷿鸊"]
["么","幺麼麽"]
["云","云雲"]
["伪","偽僞"]
["余","余餘"]
["冲","沖衝"]
["出","出齣"]
["历","曆歷"]
["发","發髮"]
10 rows (0.015s)

共通テーブル式とユニオン

erq> with t(a, b) as (`kdpv_cjkvi/non-cognate`{subject, object}) (t{a, b}; t{b, a}) join unihan_kTotalStrokes on a = UCS {a, b, s: cast(value as integer) asc}[s = 1];;
with t(a, b) as (select subject, object from `kdpv_cjkvi/non-cognate`) select a, b, cast(value as integer) as s from (select a, b from t union all select b, a from t) join unihan_kTotalStrokes on a = UCS where (s = 1) order by (cast(value as integer)) asc
["a","b","s"]
["乀","乁",1]
["乀","乁",1]
["乁","乁",1]
["乙","𠃉",1]
["乁","乀",1]
["𠃉","乙",1]
["乁","乀",1]
["乁","乁",1]
8 rows (0.015s)

共通テーブル式を使った再帰クエリ

erq> with g(i) as ({i: 1}; g{i + 1} limit 10) g;;
with g(i) as (select 1 as i union all select i + 1 from g limit 10) select * from g
["i"]
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
10 rows (0.000s)

再帰クエリを使ってグラフを辿る

erq> with v(a, b) as (mjsm natural join mji {対応するUCS, 縮退UCS})
...> with w(a, b) as (v; v{b, a})
...> with g(a, b) as ({null, '刈'}; g join w on g.b = w.a {w.a, w.b} distinct)
...> g {a => group_concat(b)};;
with v(a, b) as (select 対応するUCS, 縮退UCS from mjsm natural join mji), w(a, b) as (select * from v union all select b, a from v), g(a, b) as (select distinct null, '刈' union select distinct w.a, w.b from g join w on g.b = w.a) select a, group_concat(b) from g group by (a)
["a","group_concat(b)"]
[null,"刈"]
["㓼","刹"]
["㔑","刹"]
["䒳","䒳,朵,朶,𣎾,𣎿,𣏻"]
["䓭","刹,苅"]
["刈","刈,苅,𠚫,𠛄,𭃅,𭃆"]
["刴","刹,朶"]
["刹","㓼,㔑,䓭,刴,刹,剎,𠛴,𠞻"]
["剎","刹"]
["朵","䒳,朶"]
["朶","䒳,刴,朵,朶,𣎾,𣎿,𣏻"]
["苅","䓭,刈,苅,𠛄,𫟌"]
["𠚫","刈"]
["𠛄","刈,苅"]
["𠛴","刹"]
["𠞻","刹"]
["𣎾","䒳,朶"]
["𣎿","䒳,朶"]
["𣏻","䒳,朶"]
["𫟌","苅"]
["𭃅","刈"]
["𭃆","刈"]
22 rows (0.123s)

in演算子とorder by句の例

erq> mji natural join mji_reading[対応するUCS in joyo{漢字}]{漢字: 対応するUCS => 読み: group_concat(distinct 読み)} order by count(distinct 読み) desc limit 10;;
select 対応するUCS as 漢字, group_concat(distinct 読み) as 読み from mji natural join mji_reading where (対応するUCS in (select 漢字 from joyo)) group by (対応するUCS) order by count(distinct 読み) desc limit 10
["漢字","読み"]
["明","メイ,ミョウ,ミン,ベイ,ボウ,あかり,あかるい,あかるむ,あからむ,あきらか,あける,あく,あくる,あかす,ひかり"]
["生","セイ,ショウ,ソウ,いきる,いかす,いける,うまれる,うむ,おう,はえる,はやす,き,なま,うぶ"]
["行","コウ,ギョウ,アン,ゴウ,カン,ガン,いく,ゆく,おこなう,まさに,みち,めぐる,やる,ゆくゆく"]
["上","ジョウ,ショウ,うえ,うわ,かみ,あげる,あがる,のぼる,のぼせる,のぼす,たっとぶ,たてまつる,ほとり"]
["下","カ,ゲ,ア,した,しも,もと,さげる,さがる,くだる,くだす,くださる,おろす,おりる"]
["白","ハク,ビャク,ベ,ハ,ヒャク,シ,ジ,しろ,しら,しろい,しらげる,しらむ,もうす"]
["薄","ハク,ヘキ,ホ,うすい,うすめる,うすまる,うすらぐ,うすれる,せまる,すすき,バク,ビャク,ブ"]
["重","ジュウ,チョウ,ジュ,ズ,トウ,シュウ,シュ,え,おもい,かさねる,かさなる,おもんじる,はばかる"]
["反","ハン,ホン,タン,ヘン,ベン,そる,そらす,かえす,かえって,かえる,そむく,たん"]
["懐","カイ,エ,ふところ,なつかしい,なつかしむ,なつく,なつける,いだく,おもい,こころ,おもう,ふところにする"]
10 rows (0.021s)

ローバリュー演算

erq> with u(s, t) as (unihan_variant[property='kTraditionalVariant']{UCS, value})
...> u[{s, t} not in tghb_variants{规范字, 繁体字}] limit 10;;
with u(s, t) as (select UCS, value from unihan_variant where (property = 'kTraditionalVariant')) select * from u where ((s, t) not in (select 规范字, 繁体字 from tghb_variants)) limit 10
["s","t"]
["㐷","傌"]
["㐹","㑶"]
["㐽","偑"]
["㑈","倲"]
["㑔","㑯"]
["㑩","儸"]
["㑺","儁"]
["㓥","劏"]
["㔉","劚"]
["㖊","噚"]
10 rows (0.006s)

Erq実装について

現状はNode.js/JavaScriptでSQLiteのErqクライアントを実装し、個人的に利用しています。

• GitHub - mandel59/erq

将来的にはRustなどで実装しなおすかもしれませんが、現状でもそれなりに便利に使えています。リポジトリには公開していないので、GitHubからインストールしてください。次のコマンドを実行すると、erqコマンドがインストールされます。

npm install -g github:mandel59/erq