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が1ファイルの中の出現数であるのに対し、

複数ファイル中での出現数を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文中で2回サブクエリを使っているのは、

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のようにエイリアスをつけることができる。

サブクエリ内の値は外からアクセスしやすい必要があるのでこれは重要。

Column.label

subqueryメソッドでクエリ全体をサブクエリとしてエイリアスをつけることができる。

(SELECT ... FROM ...) lf

Query.subquery

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が使える。

sqlalchemy.orm.aliased

クエリの構築

上で作ったサブクエリを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の条件だ。

Query.join

そして最後にlemmaの出現数降順でソートしている。

まとめ

SQLが透けてみえるのがSQLAlchemyのよいところだ。

SQLを知っていれば柔軟にメソッドチェーンを使えるし、

適宜生のSQL文を埋め込むことができる。

課題

lf.totalというアトリビュートが自動で作られるわけではないらしい。

queryメソッド内、desc関数内で、"lf.total"などとSQLをじかに書いている。