SQLAlchemyでサブクエリ
問題
SQLAlchemyから集約関数sumを使ったとき、
同じ列を重複してカウントしてしまった。
以下のようなSQLを実現したい。
sqlite> select l.lemma, lf.total, t.token, tf.total from lemma l join ( select lemma_id, sum(count) as total from lemma_in_file where file_id in (1,2) group by lemma_id ) lf on l.id == lf.lemma_id join token t on l.id == t.lemma_id join ( select token_id, sum(count) as total from token_in_file where file_id in (1,2) group by token_id ) tf on t.id == tf.token_id order by lf.count desc limit 10;
理解を助けるために、このSQLについて少しだけ補足する。
tokenというのは、テキストに出てくる語そのもののことで、
英語でいうとstudies, studyなど。
lemmaというのは、辞書に載っている語の形で、
英語でいうとstudy。
tokenはlemmaの変種にすぎないので、1対多のリレーションが貼られている。
lemma token
study -- study
|
|- studies
token, lemmaそれぞれのファイル内での出現数を記録しているテーブルが{token, lemma}_in_fileだ。
長いので、lf, tfとエイリアスをつけている。
取り出したい情報は、
l.lemma, lf.total, t.token, tf.total
totalというのはエイリアスで、
{token, lemma}.countが１ファイルの中の出現数であるのに対し、
複数ファイル中での出現数をsumで合計したものだ。
studyの例えでいうと、欲しい結果は以下のようなものだ。
l.lemma lf.total t.token tf.total study 50 study 25 study 50 studies 10 study 50 studied 10 study 50 studying 5
SQL文中で２回サブクエリを使っているのは、
sumの対象で、同じ列が複数回出現して重複してカウントするのを避けるだめだ。
あらかじめサブクエリ内で集約関数を使ったあとにjoinしている。
これをSQLAlchemyでやろうとしてちょっと手こずった。
解法
使うモジュールのインポート
まず今回使うものたちをimport。
from sqlalchemy.sql import func from sqlalchemy.sql.expression import desc from sqlalchemy.orm import aliased
サブクエリの構築
サブクエリから組み立てていく。
lf_subquery = session.query(
LemmaInFile.lemma_id,
func.sum(LemmaInFile.count).label("total")
).filter(
LemmaInFile.file_id.in_([1,2])
).group_by(
LemmaInFile.lemma_id
).subquery("lf")
queryメソッドにこのサブクエリに期待するカラムを指定する。
lemmaのidと、それのファイル1, 2中の合計出現数total。
labelでSUM(count) AS totalのようにエイリアスをつけることができる。
サブクエリ内の値は外からアクセスしやすい必要があるのでこれは重要。
subqueryメソッドでクエリ全体をサブクエリとしてエイリアスをつけることができる。
(SELECT ... FROM ...) lf
token側も同様にサブクエリを構築する。
tf_subquery = session.query(
TokenInFile.token_id,
func.sum(TokenInFile.count).label("total")
).filter(
TokenInFile.file_id.in_([1,2])
).group_by(
TokenInFile.token_id
).subquery("tf")
エイリアスを変数に束縛
lf = aliased(LemmaInFile, lf_subquery) tf = aliased(TokenInFile, tf_subquery)
これでlf, tfが使える。
クエリの構築
上で作ったサブクエリをjoinしていく。
query = session.query(
Lemma.lemma, Lemma.pos, "lf.total", Token.token, "tf.total"
).join(
lf, Lemma.id == lf.lemma_id
).join(
Token, Token.lemma_id == Lemma.id
).join(
tf, Token.id == tf.token_id
).order_by(
desc("lf.total")
)
SELECT(query())には、Lemma, lf, Token, tf がでてくるので、
それらをjoinする必要がある。
左から順番にjoinしている。
第一引数がテーブル（かサブクエリ）で、第二引数がjoinの条件だ。
そして最後にlemmaの出現数降順でソートしている。
まとめ
SQLが透けてみえるのがSQLAlchemyのよいところだ。
適宜生のSQL文を埋め込むことができる。
課題
lf.totalというアトリビュートが自動で作られるわけではないらしい。
