group_byでsumしてquery - kuroの覚え書き

やりたいこと
テーブルTestで、カラムz_idの数値をカラムnameとgeneが同じサンプルで合計してtotal_pointsとして各サンプルに付与し、total_pointsが2以上になるサンプルをqueryしたい
作ったコード

            total_points = func.sum(Test.z_id).label('total_points')

            sample = session.query(Test.id, total_points).filter(Test.samp.like(form.name.data)).group_by(Test.samp, Test.gene)

これだとnameとgeneが同じサンプルは1サンプルに統一してtotal_pointsを付与してリストアップしてしまう。
合計は計算してもサンプルはまとめてしまわないでほしいんだけどな。
あと、total_pointsで

.filter(total_points >= 2)

とくっつけてもエラーになる。このままではtotal_pointsはTestにつながっていないからfilterがかからないのだよね。そこでやっぱりjoinがいるのだとは思う。
しかしjoinといえばリレーションを張ったテーブル間でつなぐ例しか見つからないんだが・・・

検索しまくった結果、さらにサブクエリーと併用する必要があることがわかってきた。
http://omake.accense.com/static/doc-ja/sqlalchemy/ormtutorial.html#select
http://symfoware.blog68.fc2.com/blog-entry-1379.html

　 stmt = session.query(Test.samp, func.sum(Test.z_id).label('total_points')).group_by(Test.samp).subquery()
　 sample = session.query(Test, stmt.c.total_points).outerjoin((stmt, Test.samp==stmt.c.samp)).filter(Test.samp.like(form.name.data)).filter(Test.gene.like(form.gene.data)).filter(Test.count<=form.count.data).filter(stmt.c.total_points >= 9)
return render_template('index.html', contents=sample, form=form)

このようにstmtに一旦z_idの合計とsampを対応付ける仮想テーブルを作り、outerjoinでTestの各sampに紐付けした上でクエリーをもう一度かけるらしい。

この結果を表示するにはindex.htmlの方に

        {% for samp, count in contents %}
        <tr>
          <td>{{ samp.id }}</td>
          <td>{{ samp.samp }}</td>
          <td>{{ samp.gene }}</td>
          <td>{{ samp.zygo }}</td>
          <td>{{ samp.count }}</td>
          <td>{{ samp.z_id }}</td>
          <td>{{ count }}</td>
        </tr>

こんな感じの受け皿を用意しておかないとならないようで、こっちも工夫がいる。

これでようやく一歩近づいたが、今度はgroup_byをsampとgeneの2つでやる方法を考えないとね。あとcaseによる数値化も

まずはgroup_byを2つにする件。これは案外簡単であった。

from sqlalchemy.sql.expression import and_

stmt = session.query(Test.samp, Test.gene, func.sum(Test.z_id).label('total_points')).group_by(Test.samp, Test.gene).subquery()
sample = session.query(Test, stmt.c.total_points).outerjoin((stmt, and_(Test.samp==stmt.c.samp, Test.gene==stmt.c.gene))).filter(Test.samp.like(form.name.data)).filter(Test.gene.like(form.gene.data)).filter(Test.count<=form.count.data).filter(stmt.c.total_points >= 2)

これだけ

次にcaseによる数値化。homなら２それ以外（het)なら１にしてsumすればいいので

stmt = session.query(Test.samp, Test.gene, func.sum(case([(Test.zygo=='hom', 2)], else_=1)).label('total_points')).filter(Test.count<=form.count.data).group_by(Test.samp, Test.gene).subquery()
sample = session.query(Test, stmt.c.total_points).outerjoin((stmt, and_(Test.samp==stmt.c.samp, Test.gene==stmt.c.gene))).filter(Test.count<=form.count.data).filter(Test.samp.like(form.name.data)).filter(Test.gene.like(form.gene.data)).filter(stmt.c.total_points >= 2)

これでちゃんと動作したよ。
最初homなら２、hetなら１それ以外０でやろうとしたらエラーが出た。homとhetしかないのだから２択に変えたらうまくいった。

しかしまあこの検索式で200,000行以上のデータベースをリアルタイム検索させてると時間は結構食うな。こりゃフロントエンドの問題じゃなくSQLのデータベースソフトウェアの問題だわ。

一旦打ち切られたプロジェクトながら、細々と手を入れていったところ、まあまあ役に立ちそうなものができたように思う。これで終わりにするのはもったいないような気もしてきたな。