自己非等値結合

ＳＱＬ自分メモ




例えば記録に基づく順位表を作るときに，同記録の人を同順位として次の順位は飛び石にするケース．


下はPythonからSQLite3を使った場合の例．

import sqlite3

db = sqlite3.connect(":memory:");
c = db.cursor();

c.execute("CREATE TABLE 走高跳(記録, 名前)");
c.executemany("INSERT INTO 走高跳 VALUES(?, ?)", [
    (1.98, "tatsushi"),
    (1.85, "tatsushi"),
    (1.90, "ok"),
    (1.85, "ok"),
    (1.85, "ok"),
    (1.85, "ok"),
    (1.80, "ok"),
    (1.80, "ok"),
    (1.80, "ok"),
    (1.86, "F"),
    (1.80, "F"),
    (1.80, "masato"),
    (1.80, "masato"),
    (1.75, "masato"),
    (1.75, "masato"),
    (1.75, "masato"),
    (1.60, "showhey"),
    (1.50, "sbt"),
    (1.35, "ed"),
]);

sql = "SELECT  (SELECT COUNT(記録) FROM 走高跳 WHERE 記録 > cp.記録) + 1 AS rank, \
               記録, \
               名前 \
         FROM  走高跳 cp \
        ORDER BY rank";

c.execute(sql);

for row in c:
    print(row);

c.close();
db.close();

実行結果

1 1.98 tatsushi
2 1.9 ok
3 1.86 F
4 1.85 tatsushi
4 1.85 ok
4 1.85 ok
4 1.85 ok
8 1.8 ok
8 1.8 ok
8 1.8 ok
8 1.8 F
8 1.8 masato
8 1.8 masato
14 1.75 masato
14 1.75 masato
14 1.75 masato
17 1.6 showhey
18 1.5 sbt
19 1.35 ed

このＳＱＬ文がミソ↓．

sql = "SELECT  (SELECT COUNT(記録) FROM 走高跳 WHERE 記録 > cp.記録) + 1 AS rank, \
               記録, \
               名前 \
         FROM  走高跳 cp \
        ORDER BY rank";

こういう集合指向的な書き方には萌えますなぁ．


再帰集合を使う "SELECT COUNT(記録) FROM 走高跳 WHERE 記録 > cp.記録" の部分とか，これだけでご飯三杯いける．





ＳＱＬを書くときは，Ｃみたいな手続き型言語の世界とは違う思考に頭を切り替えないといけない．


staticな書き方をする関数型言語ＭＬの考え方と根っこが共通している．