基于SQLite3的Python脚本：中位数与众数计算问题求助

1. 中位数计算修复（支持奇数/偶数数量的数据集） #

当前代码仅在数据项数量为奇数时正确，原因是只取了中间的单个行。对于偶数数量，需要取中间两个值并计算平均值。以下是使用SQLite窗口函数的优化实现（SQLite 3.25+支持）：

def select_median(conn):
    print("Median")
    cur = conn.cursor()
    cur.execute("""
        WITH ranked_data AS (
            SELECT columnC, columnF,
                   ROW_NUMBER() OVER (PARTITION BY columnC ORDER BY columnF) AS row_num,
                   COUNT(*) OVER (PARTITION BY columnC) AS total_count
            FROM myTable
        )
        SELECT columnC,
               CASE 
                   WHEN total_count % 2 = 1 THEN columnF 
                   ELSE AVG(columnF) 
               END AS median
        FROM ranked_data
        WHERE row_num IN ((total_count + 1) // 2, (total_count // 2) + 1)
        GROUP BY columnC
        ORDER BY columnC
    """)
    rows = cur.fetchall()
    for row in rows:
        print(row)

逻辑说明： #

• 使用ROW_NUMBER()为每个columnC分组内的columnF值排序并分配行号
• COUNT()窗口函数获取每个分组的总数据量
• 根据总数量奇偶性，选择中间1行（奇数）或中间2行（偶数），偶数时计算平均值作为中位数

2. 众数逻辑修复（无唯一众数时返回"no mode"） #

原代码仅返回出现次数最多的第一个值，未处理多个值出现次数相同的情况。以下实现会检查是否存在唯一众数，不存在则返回"no mode"：

def select_mode(conn):
    print("Mode")
    cur = conn.cursor()
    cur.execute("""
        WITH frequency AS (
            SELECT columnC, columnF, COUNT(*) AS occurrence
            FROM myTable
            GROUP BY columnC, columnF
        ),
        max_frequency AS (
            SELECT columnC, MAX(occurrence) AS max_occur
            FROM frequency
            GROUP BY columnC
        ),
        mode_candidates AS (
            SELECT f.columnC, f.columnF,
                   COUNT(*) OVER (PARTITION BY f.columnC) AS candidate_count
            FROM frequency f
            JOIN max_frequency mf ON f.columnC = mf.columnC AND f.occurrence = mf.max_occur
        )
        SELECT columnC,
               CASE WHEN candidate_count > 1 THEN 'no mode' ELSE columnF END AS mode
        FROM mode_candidates
        GROUP BY columnC
        ORDER BY columnC
    """)
    rows = cur.fetchall()
    for row in rows:
        print(row)

逻辑说明： #

• frequency表统计每个columnC分组内各columnF值的出现次数
• max_frequency表获取每个分组的最高出现次数
• mode_candidates表筛选出所有达到最高出现次数的值，并统计每个分组的候选众数数量
• 若候选数量大于1，返回"no mode"；否则返回该众数值

内容的提问来源于stack exchange，提问作者Alison Hoy