Flask SQLAlchemy中count函数未按预期聚合问题排查

我编写了如下Flask SQLAlchemy查询语句，希望统计每日各测试字段有数据的用户数量：

actests_daily = (
            db.session.query(
                func.count(AcTests.problem_solving_test_id),
                func.count(AcTests.personality_test_id),
                func.count(AcTests.spatial_ability_test_id),
                func.date(AcTests.last_test_submit_time),
            )
            .filter(AcUsers.last_test_submit_time != None)
            .group_by(
                AcTests.problem_solving_test_id,
                AcTests.personality_test_id,
                AcTests.spatial_ability_test_id,
                func.date(AcTests.last_test_submit_time),
            )
            .all()
        )

实际得到的结果：

[(1, 0, 1, datetime.date(2022, 8, 1)), (0, 0, 1, datetime.date(2022, 8, 1))]

预期结果应为：

[(1, 0, 2, datetime.date(2022, 8, 1))]

查询未按预期聚合，请问代码哪里出错了？

错误原因分析 #

1. 分组条件错误：GROUP BY中包含了三个测试ID字段，这会导致只要这三个字段的组合不同，就会被拆分为独立分组，无法实现按日期统一聚合的目标，这是同一日期出现多条结果的核心原因。
2. 表关联缺失：查询中使用AcUsers的字段作为过滤条件，但未显式关联AcTests和AcUsers表，可能导致过滤逻辑异常，甚至出现笛卡尔积数据。
3. 统计逻辑可选优化：若目标是统计去重的用户数量而非单纯的记录数，原代码的count(字段)会统计所有非空记录，无法避免同一用户多次提交的重复统计。

修正后的代码 #

场景1：统计每日各测试有数据的去重用户数 #

假设AcTests表通过user_id关联AcUsers表：

actests_daily = (
    db.session.query(
        # 统计problem_solving_test_id非空的用户数（去重）
        func.count(func.distinct(AcTests.user_id)).filter(AcTests.problem_solving_test_id.isnot(None)),
        # 统计personality_test_id非空的用户数（去重）
        func.count(func.distinct(AcTests.user_id)).filter(AcTests.personality_test_id.isnot(None)),
        # 统计spatial_ability_test_id非空的用户数（去重）
        func.count(func.distinct(AcTests.user_id)).filter(AcTests.spatial_ability_test_id.isnot(None)),
        func.date(AcTests.last_test_submit_time),
    )
    .join(AcUsers, AcTests.user_id == AcUsers.id)  # 显式关联两张表
    .filter(AcUsers.last_test_submit_time.isnot(None))
    .group_by(func.date(AcTests.last_test_submit_time))  # 仅按日期分组
    .all()
)

场景2：统计每日各测试非空的记录数（不去重） #

如果只需统计非空记录的数量，无需去重用户：

actests_daily = (
    db.session.query(
        func.count(AcTests.problem_solving_test_id),
        func.count(AcTests.personality_test_id),
        func.count(AcTests.spatial_ability_test_id),
        func.date(AcTests.last_test_submit_time),
    )
    .join(AcUsers, AcTests.user_id == AcUsers.id)
    .filter(AcUsers.last_test_submit_time.isnot(None))
    .group_by(func.date(AcTests.last_test_submit_time))
    .all()
)

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