q言語基礎_6_テーブル

# 以下は2020年頃に執筆した過去ブログのアーカイブです。現在メンテしておらず、一部の情報が古い可能性があります

へんてこデータベースもどき「kdb」と、知る人ぞ知るその実装言語「q」の基本を解説する本シリーズ。
本連載の目的はq言語の基本的な情報、実務で用いられる実践的なテクニックを日本語で分かりやすく提供することである。細かい情報や定義を知りたい時は適宜「q for mortals」及び「q Tips」を参照されたい

q for mortals
https://code.kx.com/q4m3/2_Basic_Data_Types_Atoms/

q Tips
https://www.amazon.com/Tips-Fast-Scalable-Maintainable-Kdb-ebook/dp/B00UZ8OMME

テーブル

dictionaryからtableへの変換のおさらい

q言語基礎_4_Dictionary(Tableの誕生)で扱った通り、qにおけるテーブルの実体はdictionaryをflipしたものである。

q)`hoge`moge!(`waninoko`hinoarasi;20 30)
hoge| waninoko hinoarasi
moge| 20       30
q)t:flip `hoge`moge!(`waninoko`hinoarasi;20 30)
q)t
hoge      moge
--------------
waninoko  20
hinoarasi 30

テーブルの宣言

qにはdinctionaryをflipするのとは別に、テーブルを宣言する方法が用意されている。
([] *c1*:*L1*; ...; *cn*:*Ln*)
ここで、cnはカラム名、Lnはカラムに入るデータのリストである。当然、各カラムのリストは同じ長さである必要がある。bracketを省略すると単なるリストとして扱われてしまうので注意。

q)([] prod:`hoge`moge`fuga;price:20 30 21)
prod price
----------
hoge 20
moge 30
fuga 21

q)([] prod:`hoge`moge`fuga;price:20 30 21)  ~ flip `prod`price!(`hoge`moge`fuga;20 30 21) / flipで宣言した場合と等価
1b

以下のようなテクニックも利用可能。

q)prod:`hoge`moge`fuga
q)price:20 30 21
q)([] prod;price) / 先に配列を宣言しておくパターン。変数名が暗黙的にカラム名として使用されているのがポイント
prod price
----------
hoge 20
moge 30
fuga 21
q)([] num: 1 + til 5; 5#23) /関数で動的に生成
num x
------
1   23
2   23
3   23
4   23
5   23
q)([] prod:`hoge`moge`fuga;price:20) /カラムに同一のアトムを挿入するテクニック
prod price
----------
hoge 20
moge 20
fuga 20

テーブル情報の参照

cols

cols関数は任意のテーブルのカラムのリストを返す。

q)t
hoge      moge
--------------
waninoko  20
hinoarasi 30
q)cols t
`hoge`moge

tables

シンボリックネームスペースを引数として、当該ネームスペースに属するテーブルのリストを表示する。
は？シンボリックネームスペースってなんだ？？？となるだろうが、これは次章以降で述べる。
今の所は、”テーブルの一覧が見れるやつ”程度の認識で良い。

q)t:([] c1:100 200 300; c2: (100 200; 1 2 3; enlist 19 ))
q)t1:([] c1:100 200 300; c2: (100 200; 1 2 3; enlist 19 ))
q)t2:([] c1:100 200 300; c2: (100 200; 1 2 3; enlist 19 ))
q)tables[]
`s#`t`t1`t2
q)\a    / どういうわけか、\aもtables関数と同じ挙動を取る
`s#`t`t1`t2

count

レコード数を教えてくれるやつ。

q)t1:([] c1:100 200 300; c2: (100 200; 1 2 3; enlist 19 ))
q)t1
c1  c2
-----------
100 100 200
200 1 2 3
300 ,19
q)count t1
3

テーブルスキーマの宣言

以下のようにすることで、データが入っていない空のスキーマを宣言することが出来る。

q)t:([] name:(); iq:())
q)meta t
c   | t f a
----| -----
name|
iq  |

宣言時に型をキャストすることでカラムの型を指定することも可能。

q)t:([] name:`symbol$(); iq:`int$())
q)meta t
c   | t f a
----| -----
name| s
iq  | i

基本的なselect, update

以降、q for mortalsで頻繁に参照される以下のテーブルを説明に用いる。

t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)t
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126

select

テーブルに対するselectは以下のようにする。
select cols from table

q)select name from t
name
----------
Dent
Beeblebrox
Prefect

テーブルを全選択したい場合は以下となる。(アスタリスクは付かないので注意)

q)select from t
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126

selectの返り値はテーブル型の変数である。従って、結果に対してselectをチェーンすることも出来る。

q)type select from select from t
98h
q)select name from select from select from t
name
----------
Dent
Beeblebrox
Prefect
||
取得したデータを以下のように新たな変数にアサインすることで、新しい変数に入ったデータを得られる。
>|q|
q)select from select from t
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126
q)select hoge:name, moge:iq from select from t
hoge       moge
---------------
Dent       98
Beeblebrox 42
Prefect    126

update

値の更新は、select句と同様の理屈で取得したデータに対して新たな値をアサインすることで実現される。
ただし、ここでのアップデートは元のテーブルを破壊的に変更はしない点に注意。あくまで、新たな値が入ったテーブルデータが返るだけである。

q)update name:10 from t
name iq
--------
10   98
10   42
10   126
q)t
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126

元のテーブルを破壊的に変更したい場合は、テーブル名を参照渡しする。

q)update name:10 from `t
`t
q)t
name iq
--------
10   98
10   42
10   126

insert

レコードのinsertは考え方としてはdictionaryの配列のjoinであり、syntaxとしては,を用いる。

q)t:([] hoge:`a`b`c;moge:100 200 300)
q)t
hoge moge
---------
a    100
b    200
c    300
q)t,:`hoge`moge!(`d;200)
q)t,:`moge`hoge!(300;`e)
q)t,:(`f;11) / カラム名を省略することも可能
q)t
hoge moge
---------
a    100
b    200
c    300
d    200
e    300
f    11
q)t,`moge`hoge!(300;`e) / なお、,:はjoinした値の再アサインであり、代入演算子+:や-:と考え方は同じ
hoge moge
---------
a    100
b    200
c    300
d    200
d    200
e    300
f    11
e    300

Primary Keyの指定とKeyed Tables

Keyed Table

テーブルの内、一つのカラムを索引とすることで、検索効率を向上することが出来る。
以下のように、keyとなる単一の要素を持つテーブルと、同一の要素数を持つテーブルのDictionaryである。これをkeyed Tableという。
keyとなるカラムのテーブル ! 対応するデータのテーブル

q)v:flip `name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
q)v
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126
q)k:flip (enlist `eid)!enlist 1001 1002 1003 /keyとなるカラムのテーブル
q)k
eid
----
1001
1002
1003
q)kt:k!t / keyed tableの作成
q)kt
eid | name iq
----| --------
1001| 10   98
1002| 10   42
1003| 10   126
q)select eid, name from kt / keyed tableは原則通常のテーブルと透過的に操作可能
eid  name
---------
1001 10
1002 10
1003 10
q)cols kt
`eid`name`iq
q)key kt  / テーブルであると同時にdictionaryでもあるため、keyによる値の取出しも可能
eid
----
1001
1002
1003
q)value kt
name       iq
--------------
Dent       98
Beeblebrox 42
Prefect    126
q)kt[1001]
name| `Dent
iq  | 98
q)kt[([] eid :1001 1002)] 
name       iq
-------------
Dent       98
Beeblebrox 42
q)kt?([] name:enlist `Prefect; iq:enlist 126) / Valueからkeyを逆引き
eid
----
1003

keyed tableは以下のように宣言することも出来る。（テーブル宣言のシンタックス内の括弧の用途はこれだったのだ！）

q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)kt
eid | name       iq
----| --------------
1001| Dent       98
1002| Beeblebrox 42
1003| Prefect    126

空のkeyed tableは以下となる。

q)ktempty:([eid:()] name:(); iq:())
q)ktempty:([eid:`int$()] `symbol$name:(); iq:`int$()) / 型を宣言する場合
q)meta ktempty
c   | t f a
----| -----
eid | i
name| s
iq  | i

xkeyを用いることで、通常のtableを任意のカラムをkeyとしたkeyed tableに変換することが出来る。

q)t:([] id:10 11 12 13; area:`TKY`LDN`NYK`PAR; pl:1000 900 200 1000)
q)t
id area pl
------------
10 TKY  1000
11 LDN  900
12 NYK  200
13 PAR  1000
q)`id xkey t
id| area pl
--| ---------
10| TKY  1000
11| LDN  900
12| NYK  200
13| PAR  1000

逆にkeyed tableを普通のtableに戻したい時は、引数にempty gemeral list()を渡してやればよい。

q)() xkey t
id area pl
------------
10 TKY  1000
11 LDN  900
12 NYK  200
13 PAR  1000
q)t
id area pl
------------
10 TKY  1000
11 LDN  900
12 NYK  200
13 PAR  1000
q)`id xkey `t /破壊的に変えたい場合は名前渡し
`t
q)t
id| area pl
--| ---------
10| TKY  1000
11| LDN  900
12| NYK  200
13| PAR  1000

左から数えて幾つのカラムをkeyにしたいかを指定することでkeyedテーブルを作るsyntaxも存在する。使いどころは分からないが…

q)t
id area pl
------------
10 TKY  1000
11 LDN  900
12 NYK  200
13 PAR  1000
q)0!t
id area pl
------------
10 TKY  1000
11 LDN  900
12 NYK  200
13 PAR  1000
q)1!t
id| area pl
--| ---------
10| TKY  1000
11| LDN  900
12| NYK  200
13| PAR  1000
q)2!t
id area| pl
-------| ----
10 TKY | 1000
11 LDN | 900
12 NYK | 200
13 PAR | 1000

Foreign Key

Foreign Keyの宣言

qの世界にもforeign keyの概念が存在する。ただし、いわゆるRDBのforeign keyとは扱い方や振舞が異なる。
先ず、あるテーブルのforeign keyは、別のテーブルの対応するカラムをenumurationしたものとして宣言する。
以下ではeidがforeign keyになっている。

q)kt
eid | name       iq
----| --------------
1001| Dent       98
1002| Beeblebrox 42
1003| Prefect    126
q)tdetails:([] eid:`kt$1003 1001 1002 1001 1002 1001; sc:126 36 92 39 98 42)
q)tdetails
eid  sc
--------
1003 126
1001 36
1002 92
1001 39
1002 98
1001 42
q)meta tdetails /metaからは、foregin keyの参照先が確認できる
c  | t f  a
---| ------
eid| j kt
sc | j

ここで、`kt$1003 1001 1002 1001 1002 1001として宣言されたデータはenumであり、その実体はktのkeyカラムであるeidに対するindexのリストである。
enumはenum先のデータそのものと普段は透過的に扱うことが出来るが、intへキャストすることでその正体を垣間見ることが出来る。

q)select `int$eid from tdetails
eid
---
2
0
1
0
1
0
q)1002 = `kt$1003 1001 1002 1001 1002 1001 / 普段は元のデータと透過的に扱える
001010b

ここからが重要なことだが、ここで宣言したforeign keyには、その実体であるktテーブルのeidカラムに存在しない値を追加することができない。
これによって、いわゆるRDBにおけるforeign keyとしての振舞を実現できるというわけだ。

q)select eid from kt
eid
----
1001
1002
1003
q)`kt$1003 1001 1002 1001 1002 1001,1002
`kt$1003 1001 1002 1001 1002 1001 1002
q)`kt$1003 1001 1002 1001 1002 1001,1007
'cast
  [0]  `kt$1003 1001 1002 1001 1002 1001,1007
q)update eid:1003, sc:1000 from `tdetails where eid = 1003
`tdetails
q)update eid:1010, sc:1000 from `tdetails where eid = 1003
'cast
  [0]  update eid:1010, sc:1000 from `tdetails where eid = 1003

foreign keyの使い道はこれだけではない。なんと、foreign keyを持つテーブルは、foreign keyを経由することで参照先のテーブルのカラムを取出すことが出来る。
syntaxはkey.カラム名。

q)kt
eid | name       iq
----| --------------
1001| Dent       98
1002| Beeblebrox 42
1003| Prefect    126
q)select eid.iq from tdetails
iq
---
126
98
42
98
42
98
q)select eid.name from tdetails
name
----------
Prefect
Dent
Beeblebrox
Dent
Beeblebrox
Dent

foreign keyの参照を解除したい場合は、value関数によってupdateしてやればよい。

q)update value eid from `tdetails
`tdetails
q)meta tdetails
c  | t f a
---| -----
eid| j
sc | j

複数のテーブル、カラムの操作

join(,)

同じmeta情報を持つテーブル同士であれば、joinによってマージ出来る。

q)t1:([] hoge:`a`b`c; moge:10 20 30)
q)t2:([] hoge:`d`e`f; moge:40 50 60)
q)t1,t2
hoge moge
---------
a    10
b    20
c    30
d    40
e    50
f    60

Coalesce(^)

Coalesceは2つのkeyed tableをマージするのに使える。
left operandとreft operandを比較して、どちらかにしかないレコードは追加、keyが重複するレコードはreft operandの値を優先して上書きされる。

q)t1:([ hoge:`a`b`c] moge:10 20 30)
q)t2:([hoge:`a`e`f] moge:400 50 60)
q)t1 ^ t2
hoge| moge
----| ----
a   | 400
b   | 20
c   | 30
e   | 50
f   | 60

カラムの追加 (,')

Join Each (,') を用いることで、任意のテーブルに新たなカラムを追加することが出来る。
既に存在するカラムと同名のテーブルを追加した場合、上書きすることになる。

q)([] c1:`a`b`c),'([] c2:100 200 300)
c1 c2
------
a  100
b  200
c  300
q)([] c1:`a`b`c; c2:1 2 3),'([] c2:100 200 300)
c1 c2
------
a  100
b  200
c  300
q)([k:1 2 3] v1:10 20 30),'([k:3 4 5] v2:1000 2000 3000)
k| v1 v2
-| -------
1| 10
2| 20
3| 30 1000
4|    2000
5|    3000
q)([] c1:`a`b`c),'([] c2:100 200 300 400) / dictionaryのアイテム数が整合しない場合当然エラーになる
'length
  [0]  ([] c1:`a`b`c),'([] c2:100 200 300 400)

Attributes

qのテーブルのカラムには、検索効率を高めるための4種類のattributeが用意されている。
keyの付与は[attribute#対象データ]とする。
テーブルを作成する際には、テーブルが保持するデータや、テーブルに対するワークロードの特性に応じて適切なattributeを適用する必要がある。Kxによれば、attributeが性能向上に寄与するのは、少なくとも100万レコード以上のデータを扱う場合である。
そして、重要なこととして、いくつかのAttributeは、そのデータ形式を規定するものではなく、単に説明する者に過ぎない。つまり、あるカラムに対してあるAttributeを付与することは、「このカラムはこのAttributeに沿う形式ですよ」という属性の説明を添えているだけで、それ自体がカラムのデータ形式を変質させるものではないということだ。一応、付与可能かのチェックはしてくれるが。
q for mortalsはこれを以下のように説明している。

Attributes (other than `g#) are descriptive rather than prescriptive. By this we mean that by applying an attribute you are asserting that the list has a special form, which q will check. It does not instruct q to (re)make the list into the special form; that is your job.

Sorted `s#

当該データが昇順にsortされていることを示すのがSorted Attributeである。
qはデータがSortedである場合、liner searchの代わりにbinary searchを用いるため、Find(?), Equal(=), in, within等の処理が速くなる。

q)`s#1 2 4 8
`s#1 2 4 8 / sorted attrubuteが付く場合、その旨表示される
q)`s#2 1 3 4 / 昇順じゃないのに付与しようとすると怒られる
's-fail
  [0]  `s#2 1 3 4
         ^
q)L:`s#1 2 3 4 5
q)L,:0
q)L
1 2 3 4 5 6 0 / 昇順じゃない値が付与されたので、attributeが外れた
q)t:([] ti:`s#00:00:00 00:00:01 00:00:03; v:98 98 100.)
q)t
ti       v
------------
00:00:00 98
00:00:01 98
00:00:03 100
q)meta t / 実はmetaで出てくるaはattributeの略だったのだ！！！
c | t f a
--| -----
ti| v   s  
v | f

Unique `u#

Unique `u#はデータがdistinctであること、つまり一意であることを示す。
これが付与されている場合、qはデータを探す類の操作において、１つ目のデータを見つけた時点で仕事を終えるので、速くなるというわけだ。

q)`u#1 2 3 4
`u#1 2 3 4
q)`u#1 2 3 3
'u-fail
  [0]  `u#1 2 3 3
q)t:([] hoge:`u#`a`b`c;moge:10 20 30)
q)meta t
c   | t f a
----| -----
hoge| s   u
moge| j

Parted `p#

Parted `p#はデータ構造を形成する各要素の内、同一のものが全て隣接していることを示すものである。

q)`p#2 2 2 1 1 4 4 4 4 3 3
`p#2 2 2 1 1 4 4 4 4 3 3
q)`p#2 2 2 1 1 4 4 4 4 3 3 2 /隣接していないので怒られる
'u-fail
  [0]  `p#2 2 2 1 1 4 4 4 4 3 3 2

Grouped `g#

Grouped `g#はあるデータの集合が一纏まりに扱われるべきものであることを示すもので、どんなデータ構造にも適用することが出来る。これは一般的なRDBにおけるindexに対応する。

q)L:`g#10?10
q)L
`g#9 2 5 5 1 2 4 1 3 5
q)L,:1 1
q)
q)L
`g#9 2 5 5 1 2 4 1 3 5 1 1

Attributeの削除(`#)

Attributeの削除は以下のように行う。

q)L:`s#til 10
q)L
`s#0 1 2 3 4 5 6 7 8 9
q)`#L
0 1 2 3 4 5 6 7 8

*1:general listではない

流沙河鎮

情報技術系のこと書きます。