07 · 窗口函数：不塌行的聚合

函数(参数) OVER ( PARTITION BY 分组列 ORDER BY 排序列 )

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (model TEXT, dt TEXT, auc REAL)")
db.executemany("INSERT INTO runs VALUES (?,?,?)", [
    ("rank_v3", "2026-03-01", 0.812), ("rank_v3", "2026-03-08", 0.831),
    ("rank_v3", "2026-03-15", 0.826), ("rank_v3", "2026-03-22", 0.831),
    ("recall_v1", "2026-03-01", 0.774), ("recall_v1", "2026-03-08", 0.769),
    ("recall_v1", "2026-03-15", 0.802),
])

print("原表行数 :", db.execute("SELECT COUNT(*) FROM runs").fetchone()[0])

print("\n-- GROUP BY：七行塌成两行 --")
for r in db.execute("""
    SELECT model, COUNT(*) AS n, ROUND(AVG(auc), 4) AS avg_auc
    FROM runs GROUP BY model ORDER BY model"""):
    print("   ", r)

print("\n-- 窗口函数：七行还是七行，每行多知道自己组里的情况 --")
for r in db.execute("""
    SELECT model, dt, auc,
           COUNT(*)             OVER (PARTITION BY model)      AS n,
           ROUND(AVG(auc)       OVER (PARTITION BY model), 4)   AS avg_auc,
           ROUND(auc - AVG(auc) OVER (PARTITION BY model), 4)   AS diff
    FROM runs ORDER BY model, dt"""):
    print("   ", r)

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (model TEXT, dt TEXT, auc REAL)")
db.executemany("INSERT INTO runs VALUES (?,?,?)", [
    ("rank_v3", "2026-03-01", 0.812), ("rank_v3", "2026-03-08", 0.831),
    ("rank_v3", "2026-03-15", 0.826), ("rank_v3", "2026-03-22", 0.831),  # ⭐ 0.831 出现两次
])

print("dt          auc     ROW_NUMBER  RANK  DENSE_RANK")
for r in db.execute("""
    SELECT dt, auc,
           ROW_NUMBER() OVER (ORDER BY auc DESC) AS rn,
           RANK()       OVER (ORDER BY auc DESC) AS rk,
           DENSE_RANK() OVER (ORDER BY auc DESC) AS dk
    FROM runs ORDER BY rn"""):
    print(f"{r[0]}  {r[1]:.3f}      {r[2]}          {r[3]}     {r[4]}")

print("\n三者各自的最大值 :", db.execute("""
    SELECT MAX(rn), MAX(rk), MAX(dk) FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY auc DESC) rn,
             RANK()       OVER (ORDER BY auc DESC) rk,
             DENSE_RANK() OVER (ORDER BY auc DESC) dk
      FROM runs)""").fetchone())

print("\n⭐ 加一个唯一的决胜列，结果就定死了：")
for r in db.execute("""
    SELECT dt, ROW_NUMBER() OVER (ORDER BY auc DESC, dt DESC) rn FROM runs ORDER BY rn"""):
    print("   ", r)

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (model TEXT, dt TEXT, auc REAL)")
db.executemany("INSERT INTO runs VALUES (?,?,?)", [
    ("rank_v3", "2026-03-01", 0.812), ("rank_v3", "2026-03-08", 0.831),
    ("rank_v3", "2026-03-15", 0.826), ("rank_v3", "2026-03-22", 0.831),
    ("recall_v1", "2026-03-01", 0.774), ("recall_v1", "2026-03-08", 0.769),
    ("recall_v1", "2026-03-15", 0.802),
])

q = """
SELECT model, dt, auc,
       LAG(auc)  OVER (PARTITION BY model ORDER BY dt) AS prev_auc,
       ROUND(auc - LAG(auc) OVER (PARTITION BY model ORDER BY dt), 4) AS delta
FROM runs ORDER BY model, dt
"""
for r in db.execute(q):
    print("  ", r)

print("\n⭐ 只看比上一次掉了的那几次重训：")
for r in db.execute(f"WITH x AS ({q}) SELECT model, dt, delta FROM x WHERE delta < 0"):
    print("  ", r)

print("\n每个 model 第一行的 prev_auc :",
      db.execute(f"SELECT COUNT(*) FROM ({q}) WHERE prev_auc IS NULL").fetchone()[0], "个 NULL")

print("\nLAG 的第三个参数可以给默认值（第一行不再是 NULL）：")
for r in db.execute("""
    SELECT model, dt, LAG(auc, 1, auc) OVER (PARTITION BY model ORDER BY dt) AS prev_or_self
    FROM runs ORDER BY model, dt LIMIT 3"""):
    print("  ", r)

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (model TEXT, dt TEXT, auc REAL)")
db.executemany("INSERT INTO runs VALUES (?,?,?)", [
    ("rank_v3", "2026-03-01", 0.812), ("rank_v3", "2026-03-08", 0.831),
    ("rank_v3", "2026-03-15", 0.826), ("rank_v3", "2026-03-22", 0.831),
    ("recall_v1", "2026-03-01", 0.774), ("recall_v1", "2026-03-08", 0.769),
    ("recall_v1", "2026-03-15", 0.802),
])

try:
    db.execute("""SELECT model, dt FROM runs
                  WHERE ROW_NUMBER() OVER (PARTITION BY model ORDER BY dt DESC) <= 2""").fetchall()
except Exception as e:
    print("❌ 写在 WHERE 里 :", type(e).__name__, "|", e)

try:
    db.execute("""SELECT model, dt,
                         ROW_NUMBER() OVER (PARTITION BY model ORDER BY dt DESC) AS rn
                  FROM runs WHERE rn <= 2""").fetchall()
except Exception as e:
    print("❌ 用别名过滤   :", type(e).__name__, "|", e)

print("\n✅ 先开窗再筛 —— 每个 model 最近 2 次：")
for r in db.execute("""
    WITH ranked AS (
      SELECT model, dt, auc,
             ROW_NUMBER() OVER (PARTITION BY model ORDER BY dt DESC) AS rn
      FROM runs
    )
    SELECT model, dt, auc FROM ranked WHERE rn <= 2 ORDER BY model, dt DESC"""):
    print("  ", r)

print("\n⭐ 聚合结果之上再开窗（每个 model 的均值，和它离总体的距离）：")
for r in db.execute("""
    SELECT model,
           ROUND(AVG(auc), 4) AS m,
           ROUND(AVG(auc) - AVG(AVG(auc)) OVER (), 4) AS vs_all
    FROM runs GROUP BY model"""):
    print("  ", r)