大した話じゃないんだけどややわかりにくいちゃぁわかりにくいので、実例の形でメモしとこうと。
データベースのテーブルは当たり前だが多少であれ正規化されていればこそ、管理がしやすいわけだね。どんなに「オレのためにだけなればいい」という雑なプロジェクトであってさえ、ちょっとは正規化する。無論製品品質になるほどちゃんと正規化するだろう。だけど性能やそもそもの扱いやすさから、「欲張り join」なんて良くやるだろう。この結果セットは、テーブル構造が正規化された綺麗なツリーになってようが、二次元の表にしかならない………って、何言ってるか通じる?
例えばこんなね:
1 # -*- coding: utf-8 -*-
2 from __future__ import unicode_literals
3
4 import sqlite3
5
6
7 _DB_NAME = 'mal_stats.db'
8 conn = sqlite3.connect(_DB_NAME)
9 c = conn.cursor()
10 c.execute("""
11 SELECT a.name, b.japanese, d.members, c.dropped, a.score, a.votes
12 FROM t_score_stats_snapshot a
13 INNER JOIN t_anime_baseinfo b
14 ON (a.name = b.name)
15 INNER JOIN t_summary_stats_snapshot c
16 ON (a.name = c.name AND a.datetime = c.datetime)
17 INNER JOIN t_airing_snapshot d
18 ON (a.name = d.name AND a.datetime = d.datetime)
19 WHERE a.datetime = (SELECT MAX(datetime) FROM t_score_stats_snapshot)
20 ORDER BY a.name, a.score
21 """)
22 for row in c.fetchall():
23 print(row)
24 conn.close()
「性能」と言ってるのは、DBMS との往復がコストがかかるので、出来るだけ一回のクエリで出来るだけ多くの情報を取ろうとするマインドの話。まぁ SQLite の場合はインプロセスでしかないんで、そんなこと気にしてもしょうがないんだけれど、ちゃんとした DBMS のノリに慣れちゃってるとさ、こういうことしちゃうよね、フツー。そして扱いやすさと言ってるのは、複数のクエリを何度も叩かずともいっしょくたに取っちまえ、ということが「出来てしまう」ということよね。後者の良し悪しはケースバイケースだが、SQL に慣れているほどこういった「一撃 SQL」を書きたくなるだろ、ってことね。
問題はここから。データベースアプリケーションの開発経験があるなら、このジレンマは誰でも心当たりがあると思う。上の実行結果は例えばこんななのだ:
1 ...(snip)...
2 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 1, 49)
3 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 2, 30)
4 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 3, 60)
5 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 4, 176)
6 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 5, 281)
7 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 6, 516)
8 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 7, 1254)
9 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 8, 2212)
10 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 9, 2064)
11 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592, 10, 1708)
12 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 3, 2)
13 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 4, 4)
14 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 5, 5)
15 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 6, 6)
16 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 7, 29)
17 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 8, 24)
18 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 9, 17)
19 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17, 10, 15)
20 ...(snip)...
「(‘Houseki no Kuni (TV)’, ‘宝石の国’, 48823, 1592)」という親情報と、それにぶら下がる「(1, 49), …, (10, 1700)」という木構造が望みのものなわけだけれど、当たり前だがこの結果セットからその構造に仕立て上げるのは自己責任となり、これが普通はダルい。
なので itertools.groupby、て話。
上の実例に対してそのまま itertools.groupby たもの:
1 # -*- coding: utf-8 -*-
2 from __future__ import unicode_literals
3
4 import itertools
5 import sqlite3
6
7
8 _DB_NAME = 'mal_stats.db'
9 conn = sqlite3.connect(_DB_NAME)
10 c = conn.cursor()
11 c.execute("""
12 SELECT a.name, b.japanese, d.members, c.dropped, a.score, a.votes
13 FROM t_score_stats_snapshot a
14 INNER JOIN t_anime_baseinfo b
15 ON (a.name = b.name)
16 INNER JOIN t_summary_stats_snapshot c
17 ON (a.name = c.name AND a.datetime = c.datetime)
18 INNER JOIN t_airing_snapshot d
19 ON (a.name = d.name AND a.datetime = d.datetime)
20 WHERE a.datetime = (SELECT MAX(datetime) FROM t_score_stats_snapshot)
21 ORDER BY a.name, a.score
22 """)
23 #for row in c.fetchall():
24 # print(row)
25 for it in itertools.groupby(c.fetchall(), key=lambda row: row[:4]):
26 outer, inner = it[0], it[1]
27 print(outer)
28 print([x[4:] for x in inner])
29 print("")
30 conn.close()
SQL で「a.name, b.japanese, d.members, c.dropped, a.score, a.votes」というふうに 6列取っていて、4列目(c.dropped)までが親となる情報、以降が子となる情報、ということを踏まえて key と [x[4:] for x in inner] を理解してちょ。
結果:
1 ...(snip)...
2 ('Houseki no Kuni (TV)', '宝石の国', 48823, 1592)
3 [(1, 49), (2, 30), (3, 60), (4, 176), (5, 281), (6, 516), (7, 1254), (8, 2212), (9, 2064), (10, 1708)]
4
5 ('Huyao Xiao Hongniang: Nan Guo', '狐妖小红娘 南国', 743, 17)
6 [(3, 2), (4, 4), (5, 5), (6, 6), (7, 29), (8, 24), (9, 17), (10, 15)]
7 ...(snip)...
