ORM实现group by

前言

记录一次使用Django ORM实现分组聚合查询踩过的坑。需求是按照项目维度统计每个项目的人力工时，这不就是普通的分组聚合吗？按照之前记忆，对queryset对象使用聚合函数（SUM、MIN、MAX等），使用values指定分组的字段示例如下：

>>> from time_sheet.models import FvrTimeSheetItem
... from django.db.models import Sum
... from django.db.models.functions import Coalesce
... print(FvrTimeSheetItem.objects.filter(is_active=True, project_number__isnull=False).values(
...     'project_number').annotate(
...     hours=Sum(Coalesce('office1', 0))))
<QuerySet [{'project_number': 'B-CS-22-014', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': 'S-CN-21-112', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': '3-22-045', 'hours': 0.0}, {'project_number': 'B-CS-22-014', 'hours': 0.0}, {'project_number': '1-22-053', 'hours': 0.0}, {'project_number': 'B-CS-22-014', 'hours': 0.0}, {'project_number': '1-22-049', 'hours': 0.0}, {'project_number': 'B-CS-22-014', 'hours': 0.0}, {'project_number': '1-22-049', 'hours': 0.0}, {'project_number': '1-22-049', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': 'S-CN-22-104', 'hours': 0.0}, {'project_number': '1-22-023', 'hours': 0.0}, {'project_number': '1-22-048', 'hours': 0.0}, {'project_number': 'P-CN-22-116', 'hours': 7.0}, {'project_number': 'S-CN-21-121', 'hours': 0.0}, '...(remaining elements truncated)...']>

分组聚合

• 其实聚合查询和分组查询是两个不同的概念。
  • 聚合查询是指在数据库中进行计算，例如对某一列进行求和、计数、平均值等，以便对数据进行汇总和分析。而分组查询则是将数据库中的数据按照某一列或多列进行分类，以便更好地进行分析和比较。
• 那为啥分组查询和聚合查询经常放到一起讨论呢？举个栗子有个学生成绩表记录所有学生（不同班级）的成绩，如果不分组，聚合查询的对象是所有学生，只能统计总成绩。但真实场景是需要分班级统计，那此时就必须先分组，再在每个分组内进行聚合计算。

ORM为啥没有group_by方法sql却有？

• sql中GROUP BY的使用限制：GROUP BY中指定的字段必须在查询的 SELECT列表中出现，或者是一个聚合函数的参数。如果不符合该规则，在不同的数据库中会有不同的行为，有些数据库会报错，有些数据库则会自动进行字段的聚合或者默认使用第一个值。
• django ORM没有group_by方法是怎么进行分组查询？首先通过values方法指定字段（该方法会将字段添加到select列表中），然后再对该字段使用annotate+Sum方法进行聚合查询。

>>>from cost import models as cost_models
>>>from django.db.models.functions import Coalesce;from django.db.models import Sum
>>>print(cost_models.FvrAliBusinessTravelCost.objects.filter(project_code="S-CN-22-104").extra(select={'dep_year_month': "left(dep_date, 7)"}).values("dep_year_month").annotate(
    total=Coalesce(Sum('total_price'), 0)).query)

SELECT (left(dep_date, 7)) AS `dep_year_month`, COALESCE(SUM(`fvr_ali_business_travel_cost`.`total_price`), 0) AS `total` FROM `fvr_ali_business_travel_cost` WHERE `fvr_ali_business_travel_cost`.`project_code` = S-CN-22-104 GROUP BY (left(dep_date, 7)) ORDER BY NULL

解决过程

之后测试妹子找我反馈，这值不对，得到的求和值远小于实际呀。

这下丢脸了，赶紧找问题吧，先print出queryset.query看看对应的sql是啥吧，发现分组确实是分组了，可这GROUP BY的字段为啥会多出个create_datetime，就是多出 的字段导致即使project number一样的列，create_datetime不一致也会导致分到不同的组。

>>> print(FvrTimeSheetItem.objects.filter(is_active=True, project_number__isnull=False).values(
...     'project_number').annotate(
...     hours=Sum(Coalesce('office1', 0))).query)
SELECT `fvr_timesheet_item`.`project_number`, SUM(COALESCE(`fvr_timesheet_item`.`office1`, 0)) AS `hours` FROM `fvr_timesheet_item` WHERE (`fvr_timesheet_item`.`is_active` = True AND `fvr_timesheet_item`.`project_number` IS NOT NULL) 
GROUP BY `fvr_timesheet_item`.`project_number`, `fvr_timesheet_item`.`create_datetime` ORDER BY `fvr_timesheet_item`.`create_datetime` DESC

奇怪的是为啥会多出个create_datetime呢，queryset后面也没加order_by呀，最后在该model的元类中不晓得谁加个ordering属性-_-||，默认每次queryset查询时按照create_datetime倒序排。而这个在你使用分组时按照某个字段排序，这个字段便会加到group by。

# model如下：
class FvrTimeSheetItem(FvrBaseModel):
    timesheet = models.ForeignKey(FvrTimeSheet,
                                  null=True,
                                  blank=True,
                                  on_delete=models.SET_NULL,
                                  verbose_name="TimeSheet")
    project_number = models.CharField(max_length=48,
                                      null=True,
                                      blank=True,
                                      verbose_name="project number")
    office1 = models.FloatField(null=True, blank=True, verbose_name="Office1")
    is_active = models.BooleanField(default=True, verbose_name="Active?")

    def __str__(self):
        return ''

    class Meta:
        db_table = "fvr_timesheet_item"
        app_label = "time_sheet"
        ordering = ("-create_datetime", )
        verbose_name = "Timesheet Item"
        verbose_name_plural = verbose_name

在删除ordering后，再print一下queryset.query得到预期的结果。

>>> from time_sheet.models import FvrTimeSheetItem
... from django.db.models import Sum
... from django.db.models.functions import Coalesce
... print(FvrTimeSheetItem.objects.filter(is_active=True, project_number__isnull=False).values(
...     'project_number').annotate(
...     hours=Sum(Coalesce('office1', 0))).query)
SELECT `fvr_timesheet_item`.`project_number`, SUM(COALESCE(`fvr_timesheet_item`.`office1`, 0)) AS `hours` FROM `fvr_timesheet_item` WHERE (`fvr_timesheet_item`.`is_active` = True AND `fvr_timesheet_item`.`project_number` IS NOT NULL) GROUP BY `fvr_timesheet_item`.`project_number` ORDER BY NULL

最后建议不要在model的Meta类中使用ordering默认排序，一个会影响数据库查询速度，另外是在协同开发时会导致一些难预见的结果，就像我踩的那个坑一样-_-||。如果使用最好优先字段带索引的字段（如id等），提高查询速度。