Django基于分组计算占比：如何用PostgreSQL窗口函数优化查询？

嘿，这个需求完全可以通过Django ORM结合PostgreSQL的窗口函数来实现，比把全量数据拉去pandas处理高效太多了！我来给你详细讲讲怎么做：

首先，假设你的Django模型大概是这样的（我用一个通用的例子，你可以替换成自己的字段）：

from django.db import models

class DataRecord(models.Model):
    main_group = models.CharField(max_length=100)  # 第一个分组字段（父分组）
    sub_group = models.CharField(max_length=100)   # 第二个分组字段（子分组）
    count = models.IntegerField()                  # 用来计算占比的数值字段（比如数量、金额等）

接下来，我们可以用Django的Window函数、Sum聚合以及F表达式，直接在数据库层面完成分组和占比计算：

核心查询代码 #

from django.db.models import Sum, F, Window, Case, When, Value
from django.db.models.functions import Cast
from django.db.models.fields import FloatField

# 构建查询
result = DataRecord.objects.values(
    'main_group', 'sub_group'
).annotate(
    # 计算每个子分组的数值总和
    sub_total=Sum('count'),
    # 用窗口函数计算对应父分组的总合（按main_group分区）
    main_total=Window(
        expression=Sum('count'),
        partition_by=['main_group']
    )
).annotate(
    # 计算占比，同时处理父分组总合为0的情况避免除零错误
    percentage=Case(
        When(main_total=0, then=Value(0.0)),
        default=Cast(F('sub_total') / F('main_total'), output_field=FloatField()),
        output_field=FloatField()
    )
).order_by('main_group', 'sub_group')

代码解释 #

• values('main_group', 'sub_group')：告诉ORM按这两个字段进行分组，返回的结果会按这两个字段去重聚合
• 第一个annotate：
  • sub_total：计算每个(main_group, sub_group)子分组的count总和
  • main_total：通过窗口函数Window，按main_group分区，每个子分组都会拿到所属父分组的总count值
• 第二个annotate：
  • 用F表达式做除法计算占比，Cast用来把整数除法的结果转换成浮点类型，避免得到整数结果
  • 用Case和When处理父分组总合为0的情况，防止出现数据库除零错误
• order_by：让结果按父分组和子分组排序，可读性更强

示例结果 #

假设你的数据是这样的：

那么查询返回的结果会是：

# 遍历result可以看到类似这样的数据
{
    'main_group': 'Electronics',
    'sub_group': 'Phones',
    'sub_total': 100,
    'main_total': 300,
    'percentage': 0.3333333333333333
},
{
    'main_group': 'Electronics',
    'sub_group': 'Laptops',
    'sub_total': 200,
    'main_total': 300,
    'percentage': 0.6666666666666666
},
{
    'main_group': 'Clothing',
    'sub_group': 'Shirts',
    'sub_total': 150,
    'main_total': 300,
    'percentage': 0.5
},
{
    'main_group': 'Clothing',
    'sub_group': 'Pants',
    'sub_total': 150,
    'main_total': 300,
    'percentage': 0.5
}

注意事项 #

• 你的Django版本2.0.5和PostgreSQL9.6.8都完全支持窗口函数，所以这个方案可以直接用
• 这种方式把计算逻辑放在数据库层面，避免了将全量数据加载到内存中处理，数据量越大，性能提升越明显
• 如果你的数值字段是浮点类型，可以去掉Cast，直接用F('sub_total') / F('main_total')即可

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