元派遣プログラマの自称技術系ブログです。雑記とか自作のオープンソースプロジェクトの話とか。
Javaとか組込とかできます。お仕事ください。

sqlite3でROW_NUMBER()が使えるらしい。

SQLiteでもVersion3.25.0からROW_NUMBERが使えるようになっていた。

How to use ROW_NUMBER in sqlite - Stack Overflow
SQLite ROW_NUMBER() Window Function By Practical Examples

グループ分けされたスコア情報から、グループごとにN個のデータをサンプリングする時、検索結果をUNIONでくっつけてもいいけど、グループ数が可変の場合に大変困る。

例えばこんなテーブル

g p v
A 1 0
A 2 0
A 3 0
B 4 0
B 6 0
C 6 0
C 7 0
C 7 0

ROW_NUMBER()を使えば、各gから2つづつデータ取得できる。

SELECT g,p,v FROM (SELECT g,p,v,ROW_NUMBER() OVER(PARTITION BY g) gn
FROM TEST) WHERE gn<=2

f:id:nyatla:20200105132931p:plain


SQliteのバージョンが古いときはこのへんをみながらソースから作るとよい。

qiita.com

$wget https://www.sqlite.org/2018/sqlite-autoconf-3300100.tar.gz
$tar xvzf sqlite-autoconf-3300100.tar.gz

$./configure --prefix=/opt/sqlite/sqlite3.30.0/
$make -j
$sudo make install

Pythonで使いたいときは、libsqlite3のシンボリックリンクを書き換える。

$cd /usr/lib/x86_64-linux-gnu/
$sudo mv libsqlite3.so.0 libsqlite3.so.0.old                                      
$sudo ln -s /opt/sqlite/sqlite3.30.1/lib/libsqlite3.so.0.8.6 libsqlite3.so.0      

Pythonでのバージョンが切り替わってればたぶんOK

>>> import sqlite3
>>> con = sqlite3.connect(':memory:')
>>> con.execute('select sqlite_version()').fetchone()
('3.30.1',)
>>>