PostgreSQL无需CTE，能否实现优惠券关联多表聚合统计？

首先修正原CTE中的两处错误：

• accounts_by_coupon里的group by accounts.via应为group by accounts.coupon_id（需按优惠券ID分组统计注册数）
• orders_by_coupon里的sum(orders.amonut)应为sum(orders.amount)（字段名拼写错误）

子查询版本SQL #

可以将两个CTE转换为FROM子句嵌入子查询或关联子查询，以下是两种可行方案：

方案1：FROM子句嵌入子查询

select 
    coupons.id,
    coalesce(acc.signups, 0) as signups,
    coalesce(ord.sales, 0) as sales,
    coalesce(ord.revenue, 0.00) as revenue
from coupons
left join (
    select coupon_id, count(*) as signups
    from accounts
    where coupon_id is not null  -- 过滤未使用优惠券的记录
    group by coupon_id
) acc on coupons.id = acc.coupon_id
left join (
    select coupon_id, count(*) as sales, sum(amount) as revenue
    from orders
    where coupon_id is not null  -- 过滤未使用优惠券的记录
    group by coupon_id
) ord on coupons.id = ord.coupon_id;

方案2：关联子查询（SELECT子句中嵌入）

select 
    coupons.id,
    (select count(*) from accounts where accounts.coupon_id = coupons.id) as signups,
    (select count(*) from orders where orders.coupon_id = coupons.id) as sales,
    (select sum(amount) from orders where orders.coupon_id = coupons.id) as revenue
from coupons;

Rails ActiveRecord 实现示例 #

如果要适配Rails框架，推荐两种写法：

方式1：基于SQL子查询的ActiveRecord写法

Coupon.select(
  'coupons.id',
  'coalesce(acc.signups, 0) as signups',
  'coalesce(ord.sales, 0) as sales',
  'coalesce(ord.revenue, 0.00) as revenue'
).left_joins(
  'LEFT JOIN (SELECT coupon_id, count(*) as signups FROM accounts WHERE coupon_id IS NOT NULL GROUP BY coupon_id) acc ON coupons.id = acc.coupon_id',
  'LEFT JOIN (SELECT coupon_id, count(*) as sales, sum(amount) as revenue FROM orders WHERE coupon_id IS NOT NULL GROUP BY coupon_id) ord ON coupons.id = ord.coupon_id'
)

方式2：模型关联结合统计（需注意笛卡尔积问题）

假设已在模型中设置关联：

# app/models/coupon.rb
class Coupon < ApplicationRecord
  has_many :accounts
  has_many :orders
end

# 查询代码
Coupon.select(
  :id,
  'COUNT(DISTINCT accounts.id) as signups',
  'COUNT(DISTINCT orders.id) as sales',
  'SUM(orders.amount) as revenue'
).left_joins(:accounts, :orders)
 .group(:id)

注：这种方式需用DISTINCT避免多表关联导致的笛卡尔积计数错误，更推荐第一种子查询写法。

预期结果（基于提供的表数据） #

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