自己非等価結合２

自分メモ．




メディアンを求めるＳＱＬ．




母集合を上位と下位の２つの部分集合に分けて，その共通部分を探す．という考え方だとエレガントに書ける．

自己結合とCASE式，HAVING句の合わせ技．

import sqlite3

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

c.execute("CREATE TABLE 名簿(名前, 収入)");
c.executemany("INSERT INTO 名簿 VALUES(?, ?)", [
    ("A", 400),
    ("B", 30),
    ("C", 20),
    ("D", 20),
    ("E", 20),
    ("F", 15),
    ("G", 15),
    ("H", 10),
    ("I", 10),
    ("J", 10),
]);


sql = "SELECT org.収入 AS median FROM 名簿 org, 名簿 cp 
        GROUP BY org.収入 
       HAVING SUM(CASE WHEN cp.収入 >= org.収入 THEN 1 ELSE 0 END) >= COUNT(*) / 2 
          AND SUM(CASE WHEN cp.収入 <= org.収入 THEN 1 ELSE 0 END) >= COUNT(*) / 2";

c.execute("SELECT AVG(DISTINCT median) FROM (" + sql + ")");

for row in c:
    print(row);

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

結果は 17.5．





これを全く同じ考え方でPythonで書くとこんな感じになる．


Python組込みのset型を使った集合演算とリスト内包表記で４行．

income = {("A", 400),
          ("B", 30),
          ("C", 20),
          ("D", 20),
          ("E", 20),
          ("F", 15),
          ("G", 15),
          ("H", 10),
          ("I", 10),
          ("J", 10)
};


s0 = set([org for org in income if len([1 for cp in income if cp[1] >= org[1]]) >= len(income) / 2]);
s1 = set([org for org in income if len([1 for cp in income if cp[1] <= org[1]]) >= len(income) / 2]);

median = set([v[1] for v in s0.intersection(s1)]);

print(sum(median) / len(median));

ちなみに，極めて素朴な方法で手続き的にメディアンを求めるPythonコードはこんな．

s = [org[1] for org in income];
s.sort();

idx = len(s) // 2;
print((s[idx] + s[idx - 1 + (len(s) % 2)]) / 2);