如何将指定SQL内连接查询转换为Django ORM查询？

原始SQL查询 #

SELECT business_id
FROM
  (SELECT business_id,
          event_type,
          avg(occurences) AS avgo
   FROM EVENTS
   GROUP BY business_id,
            event_type) AS t1
INNER JOIN
  (SELECT event_type,
          avg(occurences) AS avgo
   FROM EVENTS
   GROUP BY event_type) AS t2 ON t1.event_type = t2.event_type
WHERE t1.avgo > t2.avgo
GROUP BY business_id;

活跃商家定义 #

活跃商家定义：拥有不止一种事件类型，其发生次数高于该事件类型在所有商家中的平均发生次数的商家。

相关说明 #

• 模型示意图
• 表结构示意图

Django ORM实现 #

假设你的Django模型名为Event，对应数据库EVENTS表，字段包含business_id、event_type、occurences，可以通过以下代码实现需求：

from django.db.models import Avg, Subquery, OuterRef, Count

# 子查询：计算每种事件类型的全局平均发生次数
global_event_avg = Event.objects.filter(
    event_type=OuterRef('event_type')
).values('event_type').annotate(
    global_avg=Avg('occurences')
).values('global_avg')

# 筛选活跃商家
active_business_ids = Event.objects.values('business_id', 'event_type').annotate(
    business_event_avg=Avg('occurences')
).filter(
    business_event_avg__gt=Subquery(global_event_avg)
).values('business_id').annotate(
    qualified_event_count=Count('event_type')
).filter(qualified_event_count__gt=1).values_list('business_id', flat=True)

代码逻辑说明 #

1. 子查询global_event_avg：针对每条记录的event_type，获取该事件类型在所有商家中的平均发生次数；
2. 第一步annotate：计算每个商家每种事件类型的平均发生次数，并用filter筛选出高于对应事件全局平均的记录；
3. 第二次分组统计：对符合条件的记录按商家ID分组，统计每个商家拥有的符合条件的事件类型数量，最终筛选出数量大于1的商家ID。

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