name test1 test2 id count ---------------------------------------- A001 x a 1 A001 x a 2 A001 x b 3 A001 x b 4 A001 x b 5 A001 y b 6 A001 y b 7 A001 y c 8 A001 y c 9 A001 y c 10 A002 x a 11 A002 y a 12 A002 x b 13 A002 y b 14 A002 x b 15 A002 y b 16 A002 x b 17 A002 y c 18 A002 x c 19 A002 y c 20 A003 y a 21 A003 y a 22 A003 y b 23 A003 y b 24 A003 y b 25 A003 x b 26 A003 x b 27 A003 x c 28 A003 x c 29 A003 x c 30
こんなテーブルがあったとして
sqlite> SELECT name, data1, data2, COUNT(*) from update_test GROUP BY name, data1, data2; name data1 data2 count1 ---------- ---------- ---------- ---------- A001 x a 2 A001 x b 3 A001 y b 2 A001 y c 3 A002 x a 1 A002 x b 3 A002 x c 1 A002 y a 1 A002 y b 2 A002 y c 2 A003 x b 2 A003 x c 3 A003 y a 2 A003 y b 3
これで、すべての組み合わせのリストができる。これが1st step
次に
sqlite> SELECT * from update_test LEFT OUTER JOIN (SELECT name, data1, data2, id, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2; name data1 data2 id count name data1 data2 id count1 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- A001 x a 1 A001 x a 2 2 A001 x a 2 A001 x a 2 2 A001 x b 3 A001 x b 5 3 A001 x b 4 A001 x b 5 3 A001 x b 5 A001 x b 5 3 A001 y b 6 A001 y b 7 2 A001 y b 7 A001 y b 7 2 A001 y c 8 A001 y c 10 3 A001 y c 9 A001 y c 10 3 A001 y c 10 A001 y c 10 3 A002 x a 11 A002 x a 11 1 A002 y a 12 A002 y a 12 1 A002 x b 13 A002 x b 17 3 A002 y b 14 A002 y b 16 2 A002 x b 15 A002 x b 17 3 A002 y b 16 A002 y b 16 2 A002 x b 17 A002 x b 17 3 A002 y c 18 A002 y c 20 2 A002 x c 19 A002 x c 19 1 A002 y c 20 A002 y c 20 2 A003 y a 21 A003 y a 22 2 A003 y a 22 A003 y a 22 2 A003 y b 23 A003 y b 25 3 A003 y b 24 A003 y b 25 3 A003 y b 25 A003 y b 25 3 A003 x b 26 A003 x b 27 2 A003 x b 27 A003 x b 27 2 A003 x c 28 A003 x c 30 3 A003 x c 29 A003 x c 30 3 A003 x c 30 A003 x c 30 3
これで全行に対応するcountがcount1としてjoinされている。ただしjoinされるidは各グループの最後のidで書かれるらしい。
sqlite> SELECT * from update_test LEFT OUTER JOIN (SELECT COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2; Error: no such column: var_count.name
joinする際にON以下で元のupdate_testのカラムとvar_countのカラムが一致しているところにつなげるよう指示しているので、joinしたテーブルのカラムがダブるからといってnameやtest1を省くことはできない。
sqlite> SELECT var_count.count1 from update_test LEFT OUTER JOIN (SELECT name, data1, data2, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2; count1 ---------- 2 2 3 3 3 2 2 3 3 3 1 1 3 2 3 2 3 2 1 2 2 2 3 3 3 2 2 3 3 3
このselect結果を使ってupdate update_test set count = var_count.count1みたいにカラムをアップデートしたいんだけどうまくいかないのね。mysqlなんかだと
mysql> UPDATE update_test t1, (SELECT var_count.count1 from update_test LEFT OUTER JOIN (SELECT name, data1, data2, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2) t2 set t1.count = t2.var_count.count1;
のようにt1, t2という別名を付けられるのだが、SQLiteでは使えないらしい。
sqlite> UPDATE update_test t1, (SELECT var_count.count1 from update_test LEFT OUTER JOIN (SELECT name, data1, data2, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2) t2 set t1.count = t2.var_count.count1; Error: near "t1": syntax error
かといって別名無しでやろうとすると
sqlite> update update_test set count = (SELECT var_count.count1 from update_test LEFT OUTER JOIN (SELECT name, data1, data2, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count ON update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2); sqlite> select * from update_test; name data1 data2 id count ---------- ---------- ---------- ---------- ---------- A001 x a 1 2 A001 x a 2 2 A001 x b 3 2 A001 x b 4 2 A001 x b 5 2 A001 y b 6 2 A001 y b 7 2 A001 y c 8 2 A001 y c 9 2 A001 y c 10 2 A002 x a 11 2 A002 y a 12 2 A002 x b 13 2 A002 y b 14 2 A002 x b 15 2 A002 y b 16 2 A002 x b 17 2 A002 y c 18 2 A002 x c 19 2 A002 y c 20 2 A003 y a 21 2 A003 y a 22 2 A003 y b 23 2 A003 y b 24 2 A003 y b 25 2 A003 x b 26 2 A003 x b 27 2 A003 x c 28 2 A003 x c 29 2 A003 x c 30 2
これもおかしい
sqlite> update update_test set count = (SELECT var_count.count1 from (SELECT name, data1, data2, COUNT(*) as count1 from update_test GROUP BY name, data1, data2) AS var_count where update_test.name=var_count.name and update_test.data1=var_count.data1 and update_test.data2=var_count.data2); sqlite> select * from update_test; name data1 data2 id count ---------- ---------- ---------- ---------- ---------- A001 x a 1 2 A001 x a 2 2 A001 x b 3 3 A001 x b 4 3 A001 x b 5 3 A001 y b 6 2 A001 y b 7 2 A001 y c 8 3 A001 y c 9 3 A001 y c 10 3 A002 x a 11 1 A002 y a 12 1 A002 x b 13 3 A002 y b 14 2 A002 x b 15 3 A002 y b 16 2 A002 x b 17 3 A002 y c 18 2 A002 x c 19 1 A002 y c 20 2 A003 y a 21 2 A003 y a 22 2 A003 y b 23 3 A003 y b 24 3 A003 y b 25 3 A003 x b 26 2 A003 x b 27 2 A003 x c 28 3 A003 x c 29 3 A003 x c 30 3
お！コレだ！できた。そうかupdateするならjoinせずに条件式が合致するものをupdateすればいいだけだったのか。
これで、上でややこしいことをしていたのがSQLiteの操作だけで完結できるようになった。
次はtsvをアップロードして自動的に登録できるようにしたいところだが、それよりはUIの改善が先かな。
name data1 data2 id start end count pos pos2 A001 x a 1 100 100 2 A001 x a 2 105 105 2 A001 x b 3 110 110 3 A001 x b 4 115 115 3 A001 x b 5 120 121 3 A001 y b 6 125 127 2 A001 y b 7 130 133 2 A001 y c 8 135 139 3 A001 y c 9 140 145 3 A001 y c 10 145 145 3 A002 x a 11 150 150 1 A002 y a 12 155 155 1 A002 x b 13 160 160 3 A002 y b 14 165 165 2 A002 x b 15 170 170 3 A002 y b 16 175 175 2 A002 x b 17 180 180 3 A002 y c 18 185 185 2 A002 x c 19 190 190 1 A002 y c 20 195 195 2 A003 y a 21 200 200 2 A003 y a 22 205 205 2 A003 y b 23 210 210 3 A003 y b 24 215 215 3 A003 y b 25 220 220 3 A003 x b 26 225 225 2 A003 x b 27 230 230 2 A003 x c 28 235 235 3 A003 x c 29 240 240 3 A003 x c 30 245 245 3
こんなテーブルを用意してカラムの数値を計算させるなら
sqlite> update update_test set pos = (select A.pos from (select id, start + 100 as pos from update_test) as A where update_test.id = A.id); sqlite> select * from update_test; name data1 data2 id start end count pos pos2 A001 x a 1 100 100 2 200 A001 x a 2 105 105 2 205 A001 x b 3 110 110 3 210 A001 x b 4 115 115 3 215 A001 x b 5 120 121 3 220 A001 y b 6 125 127 2 225 A001 y b 7 130 133 2 230 A001 y c 8 135 139 3 235 A001 y c 9 140 145 3 240 A001 y c 10 145 145 3 245 A002 x a 11 150 150 1 250 A002 y a 12 155 155 1 255 A002 x b 13 160 160 3 260 A002 y b 14 165 165 2 265 A002 x b 15 170 170 3 270 A002 y b 16 175 175 2 275 A002 x b 17 180 180 3 280 A002 y c 18 185 185 2 285 A002 x c 19 190 190 1 290 A002 y c 20 195 195 2 295 A003 y a 21 200 200 2 300 A003 y a 22 205 205 2 305 A003 y b 23 210 210 3 310 A003 y b 24 215 215 3 315 A003 y b 25 220 220 3 320 A003 x b 26 225 225 2 325 A003 x b 27 230 230 2 330 A003 x c 28 235 235 3 335 A003 x c 29 240 240 3 340 A003 x c 30 245 245 3 345
こんな感じにもできる。
さらに文字列を連結して
sqlite> update update_test set pos2 = (select A.pos2 from (select id, start || "-" || end as pos2 from update_test) as A where update_test.id = A.id); sqlite> select * from update_test; name data1 data2 id start end count pos pos2 A001 x a 1 100 100 2 200 100-100 A001 x a 2 105 105 2 205 105-105 A001 x b 3 110 110 3 210 110-110 A001 x b 4 115 115 3 215 115-115 A001 x b 5 120 121 3 220 120-121 A001 y b 6 125 127 2 225 125-127 A001 y b 7 130 133 2 230 130-133 A001 y c 8 135 139 3 235 135-139 A001 y c 9 140 145 3 240 140-145 A001 y c 10 145 145 3 245 145-145 A002 x a 11 150 150 1 250 150-150 A002 y a 12 155 155 1 255 155-155 A002 x b 13 160 160 3 260 160-160 A002 y b 14 165 165 2 265 165-165 A002 x b 15 170 170 3 270 170-170 A002 y b 16 175 175 2 275 175-175 A002 x b 17 180 180 3 280 180-180 A002 y c 18 185 185 2 285 185-185 A002 x c 19 190 190 1 290 190-190 A002 y c 20 195 195 2 295 195-195 A003 y a 21 200 200 2 300 200-200 A003 y a 22 205 205 2 305 205-205 A003 y b 23 210 210 3 310 210-210 A003 y b 24 215 215 3 315 215-215 A003 y b 25 220 220 3 320 220-220 A003 x b 26 225 225 2 325 225-225 A003 x b 27 230 230 2 330 230-230 A003 x c 28 235 235 3 335 235-235 A003 x c 29 240 240 3 340 240-240 A003 x c 30 245 245 3 345 245-245
色々できるな。
これでデータベースのカラムの用意がすべてSQLiteで完結した。
・・・んだけど、結構致命的な欠陥が。
SQLiteのUPDATEは絶望的に動作が遅い。
webで調べたところ、SELECTにかかる時間が1msecの環境でUPDATEは60msecもかかるということ。
100件で6秒、1000件で1分、31万件だと実に5時間はかかる計算になる。とても待ってられないよ。
対策としてはINSERTで別のTABLEに新たに書き込んで、出来上がったらALTERでテーブル名を上書きしてしまうといいとか。
しかしなあ、それにしてもカラム数も200以上あるテーブルにそれはきついよな。
結局のところ時間のかかる部分は単純なテキスト処理でやってしまったほうが早いかもしれない。
