Python中使用sqlite3如何按两列同值分组计算指定列平均值

核心思路 #

你需要先把分散在id1、id2两列的食材ID统一拆成单列，再按ID分组计算平均值即可，用SQL的UNION ALL就能快速完成列转行的操作。

完整代码示例 #

import sqlite3

# 1. 连接数据库（这里示例用内存数据库，你可以替换为自己的db文件路径）
conn = sqlite3.connect(':memory:')
cur = conn.cursor()

# --------------- 以下是初始化测试数据的代码，你如果已有表可以跳过 ---------------
# 创建测试表
cur.execute('''
CREATE TABLE food_pair (
    id1 INTEGER,
    id2 INTEGER,
    cnt INTEGER,
    avg_score INTEGER
)
''')
# 插入你给的样本数据
test_data = [
    (2,3,3,10),
    (3,4,3,11),
    (2,5,3,12),
    (4,5,3,13)
]
cur.executemany('INSERT INTO food_pair VALUES (?,?,?,?)', test_data)
conn.commit()
# ---------------------------------------------------------------------------

# 2. 执行查询SQL
query_sql = '''
SELECT id, AVG(avg_score) AS avg_avg_score
FROM (
    -- 把id1列的食材和对应得分查出来
    SELECT id1 AS id, avg_score FROM food_pair
    UNION ALL
    -- 把id2列的食材和对应得分查出来
    SELECT id2 AS id, avg_score FROM food_pair
) AS all_food_score
GROUP BY id
ORDER BY id
'''
cur.execute(query_sql)

# 3. 获取输出结果
result = cur.fetchall()
print("id | avg_avg_score")
print("-----------------")
for row in result:
    print(f"{row[0]:<2} | {row[1]:<13}")

# 关闭连接
conn.close()

输出验证 #

运行上述代码后输出结果和你预期完全一致：

id | avg_avg_score
-----------------
2  | 11.0         
3  | 10.5         
4  | 12.0         
5  | 12.5  

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