如何将嵌套子查询SQL转换为Django ORM并实现单行求和？

问题说明 #

需要将以下嵌套子查询SQL转换为Django ORM，要求最终返回单行求和结果，用于子查询：

SELECT SUM(income)
FROM (
  SELECT COUNT(keyword)*
    CASE
        WHEN country='ca' THEN 390
        WHEN country='fi' THEN 290
        WHEN country='it' THEN 280
        WHEN country='nl' THEN 260
        ELSE 250 
    END AS income
    FROM analytics_conversions
    WHERE keyword = 'online'
    AND click_time BETWEEN '2022-06-01' AND '2022-06-30'
GROUP BY country) as _

当前已实现的ORM代码返回多行结果，无法直接用于子查询：

keywords_conversions_params = {
    'keyword': OuterRef('keyword'),
    'keyword_type': OuterRef('keyword_type')
}

keywords_conversions_value = Conversions.objects.filter(
    **keywords_conversions_params).order_by().values('keyword').annotate(
        value=Count('pk') * Case(
            When(country='ca', then=350),
            When(country='fi', then=290),
            When(country='it', then=280),
            When(country='nl', then=260),
            default=250
        )).values('value')

解决方案 #

方案一：用Subquery实现嵌套求和逻辑 #

先按country分组计算每个分组的金额，再对结果求和，最后用Subquery包装成单行结果：

from django.db.models import Subquery, Sum

# 内层查询：按country分组计算单个分组的金额
inner_query = Conversions.objects.filter(
    **keywords_conversions_params
).order_by().values('country').annotate(
    value=Count('pk') * Case(
        When(country='ca', then=390),
        When(country='fi', then=290),
        When(country='it', then=280),
        When(country='nl', then=260),
        default=250
    )
).values('value')

# 外层求和并包装为子查询
keywords_conversions_value = Subquery(
    inner_query.aggregate(total=Sum('value')).values('total')
)

方案二：直接合并聚合逻辑（更高效） #

无需显式嵌套子查询，直接用Sum包裹金额计算逻辑，Django会自动生成对应SQL：

# 直接计算总金额，返回单值
total_income = Conversions.objects.filter(
    **keywords_conversions_params
).annotate(
    country_value=Count('pk') * Case(
        When(country='ca', then=390),
        When(country='fi', then=290),
        When(country='it', then=280),
        When(country='nl', then=260),
        default=250
    )
).aggregate(total_income=Sum('country_value'))['total_income']

如果要作为其他查询的注释字段使用，调整为子查询格式：

from django.db.models import Subquery, Sum, IntegerField

total_income_subquery = Subquery(
    Conversions.objects.filter(
        **keywords_conversions_params
    ).annotate(
        country_value=Count('pk') * Case(
            When(country='ca', then=390),
            When(country='fi', then=290),
            When(country='it', then=280),
            When(country='nl', then=260),
            default=250
        )
    ).aggregate(total=Sum('country_value')).values('total'),
    output_field=IntegerField()
)

注意事项 #

• 原始SQL是按country分组，你之前的代码误用了values('keyword')，需要修正为values('country')
• 注意统一金额数值：原始SQL中加拿大对应的是390，你之前的代码写的是350，需按需调整

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