Django ORM Raw MySQL关联查询语句逻辑解析及功能咨询

原查询语句 #

select 
    CONCAT(first_name,last_name)as broker_name,
    coupon_name,
    (select count(user_details.id)from user_details join user on user.id=user_details.user_id and user.role_id=3 where referral_coupon_id=coupons.id) as tenat_referrals,
    (select count(user_details.id) from user_details join user on user.id=user_details.user_id and user.role_id=2 where referral_coupon_id=coupons.id) as landloard_referrals 
from user 
LEFT JOIN coupons on coupons.user_id=user.id 
where role_id=4

功能逻辑说明 #

这是典型的房产租赁行业经纪人拉新业绩统计语句，具体逻辑如下：

• 数据筛选范围：从user表取role_id=4的用户，结合别名broker_name可知role_id=4对应角色为经纪人；通过左关联coupons表匹配经纪人名下的所有优惠券，左关联的特性保证即使经纪人没有关联优惠券也会出现在统计结果中，不会遗漏主体数据。
• 返回字段逐个说明：
  • broker_name：通过MySQL函数CONCAT(first_name,last_name)拼接经纪人的名和姓，生成完整的经纪人展示名称
  • coupon_name：当前行对应经纪人关联的优惠券名称
  • tenat_referrals：子查询统计结果，统计通过当前优惠券邀请注册的租客人数：关联user_details和user表，筛选角色为role_id=3（对应租客）、且注册时绑定的推荐优惠券ID为当前行优惠券ID的用户数（注：原SQL此处字段名tenat为tenant的拼写笔误）
  • landloard_referrals：子查询统计结果，统计通过当前优惠券邀请注册的房东人数：逻辑同上，仅角色筛选条件改为role_id=2（对应房东）（注：原SQL此处字段名landloard为landlord的拼写笔误）
• 实际业务用途：输出结果每行对应一个经纪人的单张优惠券的拉新转化数据，可直接用于经纪人绩效核算、优惠券引流效果评估、拉新活动效果复盘等场景。

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