10 · 拿 SQL 审问一份数据

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE calls(
  id INTEGER, user_id TEXT, feature TEXT, city TEXT, cents REAL, ts TEXT)""")
db.executemany("INSERT INTO calls VALUES(?,?,?,?,?,?)", [
    (1, 'u1', 'chat',    'BJ',  0.056, '2026-07-01 09:12:00'),
    (2, 'u1', 'summary', 'BJ',  2.64,  '2026-07-01 10:03:00'),
    (3, 'u2', 'summary', None, 10.2,   '2026-07-02 22:41:00'),
    (4, 'u2', 'chat',    'sh',  0.31,  '2026-07-03 08:00:00'),
    (5, 'u3', 'chat',    'SH',  0.44,  '2026-07-03 08:30:00'),
    (5, 'u3', 'chat',    'SH',  0.44,  '2026-07-03 08:30:00'),
    (7, 'u4', 'rerank',  'GZ', -1.0,   '2026-07-04 12:00:00'),
    (8, 'u4', 'chat',    None,  0.07,  '2026-07-04 12:05:00'),
    (9, 'u5', 'summary', 'BJ',  3.10,  '2026-06-01 00:00:00'),
])

profile = """
SELECT '① 行数'          AS 问, CAST(COUNT(*) AS TEXT)                      AS 答 FROM calls
UNION ALL
SELECT '② id 唯一吗',   COUNT(*) || ' 行 / ' || COUNT(DISTINCT id) || ' 个 id' FROM calls
UNION ALL
SELECT '③ city 空多少',  (COUNT(*) - COUNT(city)) || ' 行为 NULL'  FROM calls   -- ⭐ 括号别省
UNION ALL
SELECT '④ cents 范围',   MIN(cents) || ' ~ ' || MAX(cents)          FROM calls
UNION ALL
SELECT '⑤ ts 范围',      MIN(ts)    || ' ~ ' || MAX(ts)             FROM calls
"""
for r in db.execute(profile):
    print("%-14s %s" % r)

print("\n枚举列到底有几个值：")
for r in db.execute(
        "SELECT city, COUNT(*) FROM calls GROUP BY city ORDER BY COUNT(*) DESC"):
    print("   ", r)

print("\n重复的 id 是哪几个：")
for r in db.execute(
        "SELECT id, COUNT(*) AS n FROM calls GROUP BY id HAVING COUNT(*) > 1"):
    print("   ", r)

① 行数           9
② id 唯一吗       9 行 / 8 个 id
③ city 空多少     2 行为 NULL
④ cents 范围     -1.0 ~ 10.2
⑤ ts 范围        2026-06-01 00:00:00 ~ 2026-07-04 12:05:00

枚举列到底有几个值：
    ('BJ', 3)
    ('SH', 2)
    (None, 2)
    ('sh', 1)
    ('GZ', 1)

重复的 id 是哪几个：
    (5, 2)

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE calls(
  id INTEGER, user_id TEXT, feature TEXT, city TEXT,
  cents REAL, created_at TEXT, paid_at TEXT)""")
db.executemany("INSERT INTO calls VALUES(?,?,?,?,?,?,?)", [
    (1, 'u1', 'chat',    'BJ',  0.056, '2026-07-01 09:12:00', '2026-07-01 09:12:30'),
    (2, 'u1', 'summary', 'BJ',  2.64,  '2026-07-01 10:03:00', '2026-07-01 10:03:10'),
    (3, 'u2', 'summary', None, 10.2,   '2026-07-02 22:41:00', '2026-07-02 22:41:05'),
    (4, 'u2', 'chat',    'sh',  0.31,  '2026-07-03 08:00:00', '2026-07-03 07:59:00'),
    (5, 'u3', 'chat',    'SH',  0.44,  '2026-07-03 08:30:00', '2026-07-03 08:30:02'),
    (5, 'u3', 'chat',    'SH',  0.44,  '2026-07-03 08:30:00', '2026-07-03 08:30:02'),
    (7, 'u4', 'rerank',  'GZ', -1.0,   '2026-07-04 12:00:00', '2026-07-04 12:00:01'),
    (8, 'u4', 'chat',    None,  0.07,  '2026-07-04 12:05:00', '2026-07-04 12:05:03'),
])

checks = """
SELECT '完整性 · city 非空率' AS 维度,
       ROUND(100.0 * COUNT(city) / COUNT(*), 1) || '%' AS 实测,
       '>= 99.5%'                                      AS 期望,
       CASE WHEN 100.0*COUNT(city)/COUNT(*) >= 99.5
            THEN 'PASS' ELSE 'FAIL' END                AS 结论 FROM calls
UNION ALL
SELECT '唯一性 · id 无重复',
       CAST(COUNT(*) - COUNT(DISTINCT id) AS TEXT) || ' 个多余行', '= 0',
       CASE WHEN COUNT(*) = COUNT(DISTINCT id) THEN 'PASS' ELSE 'FAIL' END FROM calls
UNION ALL
SELECT '有效性 · cents >= 0',
       CAST(SUM(CASE WHEN cents < 0 THEN 1 ELSE 0 END) AS TEXT) || ' 行为负', '= 0',
       CASE WHEN SUM(CASE WHEN cents < 0 THEN 1 ELSE 0 END) = 0
            THEN 'PASS' ELSE 'FAIL' END FROM calls
UNION ALL
SELECT '有效性 · feature 在白名单里',
       CAST(SUM(CASE WHEN feature NOT IN ('chat','summary') THEN 1 ELSE 0 END) AS TEXT)
       || ' 行越界', '= 0',
       CASE WHEN SUM(CASE WHEN feature NOT IN ('chat','summary') THEN 1 ELSE 0 END) = 0
            THEN 'PASS' ELSE 'FAIL' END FROM calls
UNION ALL
SELECT '一致性 · paid_at >= created_at',
       CAST(SUM(CASE WHEN paid_at < created_at THEN 1 ELSE 0 END) AS TEXT) || ' 行倒挂', '= 0',
       CASE WHEN SUM(CASE WHEN paid_at < created_at THEN 1 ELSE 0 END) = 0
            THEN 'PASS' ELSE 'FAIL' END FROM calls
UNION ALL
SELECT '时效性 · 最新一行离 07-05 多久',
       CAST(ROUND(julianday('2026-07-05 00:00:00') - julianday(MAX(created_at)), 2) AS TEXT)
       || ' 天', '< 1 天',
       CASE WHEN julianday('2026-07-05 00:00:00') - julianday(MAX(created_at)) < 1
            THEN 'PASS' ELSE 'FAIL' END FROM calls
"""
for r in db.execute(checks):
    print("%-28s %-14s %-10s %s" % r)

完整性 · city 非空率               75.0%          >= 99.5%   FAIL
唯一性 · id 无重复                 1 个多余行         = 0        FAIL
有效性 · cents >= 0             1 行为负          = 0        FAIL
有效性 · feature 在白名单里          1 行越界          = 0        FAIL
一致性 · paid_at >= created_at  1 行倒挂          = 0        FAIL
时效性 · 最新一行离 07-05 多久         0.5 天          < 1 天      PASS

SELECT uid FROM traffic
WHERE click_7d > 0        -- 过滤掉无行为用户
  AND is_new = 0          -- 排除新用户
  AND region <> 'C'       -- 去掉 C 区

import sqlite3, random
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE traffic (uid INTEGER PRIMARY KEY, click_7d INT, is_new INT, region TEXT)")
random.seed(3)                                   # ⭐ 固定种子，你跑出来的数和这里一样
rows = []
for uid in range(1, 100001):
    c = 0 if random.random() < 0.66 else random.randint(1, 30)
    rows.append((uid, c, 1 if random.random() < 0.2 else 0, random.choice("ABC")))
db.executemany("INSERT INTO traffic VALUES (?,?,?,?)", rows)

WHERES = [("click_7d > 0", "过滤掉无行为用户"),
          ("is_new = 0",   "排除新用户"),
          ("region <> 'C'","去掉 C 区")]
full = "SELECT uid FROM traffic WHERE " + " AND ".join(w for w, _ in WHERES)
n_all  = db.execute("SELECT COUNT(*) FROM traffic").fetchone()[0]
n_eval = db.execute("SELECT COUNT(*) FROM (%s)" % full).fetchone()[0]
print("① 差集核对：全量 %d，评估集 %d，被这几个 WHERE 挡掉 %d（%.1f%%）"
      % (n_all, n_eval, n_all-n_eval, (n_all-n_eval)*100/n_all))

print("② 每个 WHERE 单独负责多少 —— 去掉这一条会多出多少行：")
for w, why in WHERES:                            # ⭐ 留一法：每次只摘掉一条
    rest = [x for x, _ in WHERES if x != w]
    sql = "SELECT COUNT(*) FROM traffic" + (" WHERE " + " AND ".join(rest) if rest else "")
    n = db.execute(sql).fetchone()[0]
    print("   去掉 `%-14s`(%s) → %6d 行，多出 %6d" % (w, why, n, n - n_eval))

print("③ 按人群分桶对比：评估集里各桶占比 vs 全量各桶占比")
q = """
WITH e AS (%s)
SELECT t.region,
       COUNT(*)                                           AS 全量,
       SUM(CASE WHEN e.uid IS NOT NULL THEN 1 ELSE 0 END) AS 评估集,
       ROUND(100.0*COUNT(*)/(SELECT COUNT(*) FROM traffic), 1)                       AS 全量占比,
       ROUND(100.0*SUM(CASE WHEN e.uid IS NOT NULL THEN 1 ELSE 0 END)
             /(SELECT COUNT(*) FROM (%s)), 1)                                        AS 评估占比
FROM traffic t LEFT JOIN e ON e.uid = t.uid GROUP BY t.region ORDER BY t.region
""" % (full, full)
for r in db.execute(q): print("   ", r)

① 差集核对：全量 100000，评估集 18119，被这几个 WHERE 挡掉 81881（81.9%）
② 每个 WHERE 单独负责多少 —— 去掉这一条会多出多少行：
   去掉 `click_7d > 0  `(过滤掉无行为用户) →  53731 行，多出  35612
   去掉 `is_new = 0    `(排除新用户) →  22636 行，多出   4517
   去掉 `region <> 'C' `(去掉 C 区) →  27122 行，多出   9003
③ 按人群分桶对比：评估集里各桶占比 vs 全量各桶占比
    ('A', 33424, 9027, 33.4, 49.8)
    ('B', 33686, 9092, 33.7, 50.2)
    ('C', 32890, 0, 32.9, 0.0)

import sqlite3, random
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE traffic (uid INTEGER PRIMARY KEY, click_7d INT, is_new INT, region TEXT)")
random.seed(3)
rows = []
for uid in range(1, 100001):
    c = 0 if random.random() < 0.66 else random.randint(1, 30)
    rows.append((uid, c, 1 if random.random() < 0.2 else 0, random.choice("ABC")))
db.executemany("INSERT INTO traffic VALUES (?,?,?,?)", rows)

KEEP = "click_7d > 0 AND is_new = 0 AND region <> 'C'"

q = """
WITH tagged AS (
  SELECT *, CASE WHEN {keep} THEN '留下' ELSE '被挡掉' END AS 分组 FROM traffic
)
SELECT 分组,
       COUNT(*)                                    AS 人数,
       ROUND(AVG(click_7d), 2)                     AS 平均点击,
       ROUND(100.0*AVG(is_new), 1)                 AS 新用户占比,
       SUM(CASE WHEN region='A' THEN 1 ELSE 0 END) AS A,
       SUM(CASE WHEN region='B' THEN 1 ELSE 0 END) AS B,
       SUM(CASE WHEN region='C' THEN 1 ELSE 0 END) AS C
FROM tagged GROUP BY 分组 ORDER BY 分组 DESC
""".format(keep=KEEP)
print("留下 vs 被挡掉，两个人群长什么样：")
for r in db.execute(q):
    print("   ", r)

q2 = """
SELECT SUM(CASE WHEN click_7d = 0 THEN 1 ELSE 0 END) AS 挨了_无点击,
       SUM(CASE WHEN is_new   = 1 THEN 1 ELSE 0 END) AS 挨了_新用户,
       SUM(CASE WHEN region = 'C' THEN 1 ELSE 0 END) AS 挨了_C区,
       COUNT(*)                                      AS 被挡掉合计
FROM traffic WHERE NOT ({keep})
""".format(keep=KEEP)
print("\n被挡掉的人各自挨了哪几刀（会重复计数，一个人可能同时中三刀）：")
print("   ", db.execute(q2).fetchone())

print("\n随手抽 5 行被挡掉的原始记录：")     # ⭐ 别只看聚合，肉眼看几行
for r in db.execute("SELECT * FROM traffic WHERE NOT (%s) LIMIT 5" % KEEP):
    print("   ", r)

留下 vs 被挡掉，两个人群长什么样：
    ('被挡掉', 81881, 2.99, 24.3, 24397, 24594, 32890)
    ('留下', 18119, 15.52, 0.0, 9027, 9092, 0)

被挡掉的人各自挨了哪几刀（会重复计数，一个人可能同时中三刀）：
    (66113, 19937, 32890, 81881)

随手抽 5 行被挡掉的原始记录：
    (1, 0, 0, 'B')
    (3, 0, 0, 'B')
    (4, 0, 0, 'C')
    (5, 0, 0, 'B')
    (6, 0, 0, 'A')

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders(oid INTEGER, uid TEXT, amount REAL)")
db.execute("CREATE TABLE items (oid INTEGER, sku TEXT, qty INT)")
db.executemany("INSERT INTO orders VALUES(?,?,?)",
               [(1, 'u1', 80.0), (2, 'u1', 45.0), (3, 'u2', 30.0)])
db.executemany("INSERT INTO items VALUES(?,?,?)",
               [(1, 'a', 1), (1, 'b', 2), (2, 'c', 1)])   # oid=1 有两行；oid=3 一行都没有

# ① 行数守恒：JOIN 之前多少行，JOIN 之后多少行
n_before = db.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
n_after  = db.execute(
    "SELECT COUNT(*) FROM orders o JOIN items i ON i.oid = o.oid").fetchone()[0]
print("① 行数守恒：orders %d 行 → JOIN 之后 %d 行（%s）"
      % (n_before, n_after, "一样" if n_before == n_after else "⚠️ 变了，去查扇出和丢行"))

# ② 同一个数，两种写法对拍
a = db.execute("SELECT SUM(o.amount) FROM orders o JOIN items i ON i.oid = o.oid").fetchone()[0]
b = db.execute("SELECT SUM(amount) FROM orders o "
               "WHERE EXISTS (SELECT 1 FROM items i WHERE i.oid = o.oid)").fetchone()[0]
print("② 对拍：JOIN 写法 = %s，EXISTS 写法 = %s → %s"
      % (a, b, "一致" if a == b else "⚠️ 不一致，两个口径至少有一个是错的"))

# ③ 双向对账：两边各自有多少行对不上
q = """
SELECT (SELECT COUNT(*) FROM orders o
        WHERE NOT EXISTS (SELECT 1 FROM items i WHERE i.oid = o.oid)) AS 订单没明细,
       (SELECT COUNT(*) FROM items i
        WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.oid = i.oid)) AS 明细没订单
"""
print("③ 双向对账：", db.execute(q).fetchone())

① 行数守恒：orders 3 行 → JOIN 之后 3 行（一样）
② 对拍：JOIN 写法 = 205.0，EXISTS 写法 = 125.0 → ⚠️ 不一致，两个口径至少有一个是错的
③ 双向对账： (1, 0)