如何优雅实现按客户聚合MySQL数据并统计所有子类型（含0值）

嘿，我有几个更优雅的思路来解决这个统计需求，不管是从数据库层面直接处理，还是在Python代码里优化，都能让逻辑更清晰简洁，一起来看看：

一、数据库层面直接生成结果（推荐） #

其实这个需求完全可以在SQL层面实现，数据库做聚合统计通常比Python循环更高效，尤其是数据量大的时候。核心思路是先构造包含所有sub_type的集合，再和客户做交叉连接，最后左连接原表统计数量：

WITH all_sub_types AS (
    SELECT 'cat' AS sub_type UNION ALL
    SELECT 'dog' UNION ALL
    SELECT 'fish' UNION ALL
    SELECT 'bird'
),
all_customers AS (
    SELECT DISTINCT customer FROM your_table
)
SELECT 
    ac.customer,
    ast.sub_type AS name,
    COUNT(yt.id) AS count
FROM all_customers ac
CROSS JOIN all_sub_types ast
LEFT JOIN your_table yt 
    ON ac.customer = yt.customer 
    AND ast.sub_type = yt.sub_type
    AND yt.type = 'animal'
GROUP BY ac.customer, ast.sub_type
ORDER BY ac.customer, ast.sub_type;

这样执行后，直接就能得到每个客户对应4种sub_type的统计结果，没有数据的count自动为0，后端只需要把结果转换成需要的格式即可，省去了大量Python循环处理的代码。

二、Python代码层面优化 #

如果必须在Python里处理数据，这里有两种更优雅的写法：

1. 用collections.defaultdict简化逻辑 #

利用defaultdict的特性，提前定义好每个客户的初始统计结构，避免重复的setdefault操作：

from collections import defaultdict

# 定义所有sub_type的初始计数模板
base_counts = {'cat': 0, 'dog': 0, 'fish': 0, 'bird': 0}
# 用defaultdict自动为新客户初始化统计模板（注意要copy模板，避免共享引用）
customer_stats = defaultdict(lambda: base_counts.copy())

for row in queryset:
    # 直接累加对应sub_type的计数，无需判断
    if row.type == 'animal':  # 记得筛选animal类型
        customer_stats[row.customer][row.sub_type] += 1

# 转换为题目要求的格式，比如获取John的结果
john_result = [{'name': sub_type, 'count': customer_stats['John'][sub_type]} 
               for sub_type in base_counts.keys()]

2. 用Pandas批量处理（适合大数据量） #

如果你的数据量较大，用Pandas可以更简洁地完成分组统计和补全缺失值：

import pandas as pd

# 将queryset转换为DataFrame
df = pd.DataFrame(queryset, columns=['id', 'type', 'sub_type', 'customer'])
# 筛选出animal类型的数据
animal_df = df[df['type'] == 'animal']

# 生成所有sub_type和客户的全组合
all_sub_types = pd.DataFrame({'sub_type': ['cat', 'dog', 'fish', 'bird']})
all_customers = pd.DataFrame({'customer': animal_df['customer'].unique()})
full_combinations = all_customers.merge(all_sub_types, how='cross')

# 统计原数据的各组合计数
counts = animal_df.groupby(['customer', 'sub_type']).size().reset_index(name='count')

# 左连接补全缺失值，将空值填充为0
result_df = full_combinations.merge(counts, on=['customer', 'sub_type'], how='left').fillna(0)
result_df['count'] = result_df['count'].astype(int)

# 转换为题目要求的格式，比如获取Marry的结果
marry_result = result_df[result_df['customer'] == 'Marry'] \
               .rename(columns={'sub_type': 'name'}) \
               [['name', 'count']].to_dict('records')

补充说明 #

原代码里的循环只处理了cat类型的累加，应该是笔误，上面的优化方案都默认处理所有animal下的sub_type，确保统计逻辑正确。

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