如何使用Python查询PostgreSQL计算Bloom按州平均销量并按指定格式展示

现有PostgreSQL的sales表结构及样例数据如下：

需求为统计客户Bloom在各个指定州的平均销量，输出格式要求如下：

CUST   AVG_NY  AVG_CT AVG_NJ
Bloom  28923   3241   1873

现有代码全量拉取表数据到本地后，多次遍历列表分别计算每个州的均值，重复遍历会导致效率随数据量增长快速下降，且没有利用数据库原生的聚合计算能力，性能损耗极大。

方案1：直接通过PostgreSQL查询实现（最高效） #

直接在查询阶段完成过滤、聚合计算，不需要拉取全量数据到本地，性能最优，查询语句如下：

SELECT 
    cust AS "CUST",
    ROUND(AVG(CASE WHEN state = 'NY' THEN quant END)) AS "AVG_NY",
    ROUND(AVG(CASE WHEN state = 'CT' THEN quant END)) AS "AVG_CT",
    ROUND(AVG(CASE WHEN state = 'NJ' THEN quant END)) AS "AVG_NJ"
FROM sales
WHERE cust = 'Bloom'
GROUP BY cust;

对应的Python调用代码简化为：

import psycopg2

connection = psycopg2.connect(
    user="postgres",
    password="ss",
    host="127.0.0.1",
    port="8800",
    database="postgres"
)
cursor = connection.cursor()
# 直接执行聚合查询
postgreSQL_select_Query = """
SELECT 
    cust AS "CUST",
    ROUND(AVG(CASE WHEN state = 'NY' THEN quant END)) AS "AVG_NY",
    ROUND(AVG(CASE WHEN state = 'CT' THEN quant END)) AS "AVG_CT",
    ROUND(AVG(CASE WHEN state = 'NJ' THEN quant END)) AS "AVG_NJ"
FROM sales
WHERE cust = 'Bloom'
GROUP BY cust;
"""
cursor.execute(postgreSQL_select_Query)
result = cursor.fetchone()
# 输出结果
print("CUST   AVG_NY  AVG_CT AVG_NJ")
print(f"{result[0]}  {int(result[1])}   {int(result[2])}   {int(result[3])}")

# 关闭连接
cursor.close()
connection.close()

方案2：Python侧本地计算优化（仅针对已拉取全量数据的场景） #

如果已经拉取了全量数据到本地，只需一次遍历即可完成所有州的均值计算，不需要多次循环：

# 初始化统计字典，分别存每个州的销量总和、记录条数
state_stats = {
    "NY": {"sum": 0, "count": 0},
    "CT": {"sum": 0, "count": 0},
    "NJ": {"sum": 0, "count": 0}
}
cust_name = "Bloom"

for record in mobile_records:
    if record[0] == cust_name and record[5] in state_stats:
        state = record[5]
        quant = record[6]
        state_stats[state]["sum"] += quant
        state_stats[state]["count"] += 1

# 计算均值，避免除零异常
avg_ny = state_stats["NY"]["sum"] / state_stats["NY"]["count"] if state_stats["NY"]["count"] > 0 else 0
avg_ct = state_stats["CT"]["sum"] / state_stats["CT"]["count"] if state_stats["CT"]["count"] > 0 else 0
avg_nj = state_stats["NJ"]["sum"] / state_stats["NJ"]["count"] if state_stats["NJ"]["count"] > 0 else 0

# 输出结果
print("CUST   AVG_NY  AVG_CT AVG_NJ")
print(f"{cust_name}  {int(round(avg_ny))}   {int(round(avg_ct))}   {int(round(avg_nj))}")

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