能否通过SQL分组集/ROLLUP实现按ad_id计算每千次展示成本？

现有如下数据表结构：

create table event_summary (
  created_at timestamp,
  ad_id integer,
  views integer,
  unique_views integer,
  clicks integer,
  unique_clicks integer,
  price integer
);
create index on event_summary using BRIN (created_at);

当前使用的SQL查询如下：

select
  sum(views) as total_views,
  sum(unique_views) as total_unique_views,
  sum(clicks) as total_clicks,
  sum(unique_clicks) as total_unique_clicks,
  price,
  ad_id
from event_summary
group by ad_id, price
order by total_views desc

该查询运行正常，之后我会在应用代码中针对每个ad_id计算每千次展示成本（CPM）：将各分组的total_views与price相乘后求和，再结合该ad_id的总展示量计算CPM。

想请教：是否可以/推荐使用GROUP BY分组集（Grouping Sets）或ROLLUP，直接在SQL中完成CPM的计算？

可以直接用分组集（Grouping Sets）或ROLLUP在SQL中完成CPM计算，这样能把明细数据和聚合后的CPM结果一次性查询出来，避免在应用层做二次计算，效率更高也更简洁。

方法一：使用GROUPING SETS同时获取明细与ad_id级汇总 #

通过分组集，我们可以同时得到(ad_id, price)维度的明细统计，以及ad_id维度的汇总统计（包括计算CPM）：

select
  ad_id,
  price,
  sum(views) as total_views,
  sum(unique_views) as total_unique_views,
  sum(clicks) as total_clicks,
  sum(unique_clicks) as total_unique_clicks,
  sum(views * price) as total_cost,
  -- 仅在ad_id级汇总行计算CPM，明细行显示NULL
  case when grouping(price) = 1 then
    round((sum(views * price)::numeric / sum(views)) * 1000, 2)
  end as cpm
from event_summary
group by grouping sets (
  (ad_id, price),  -- 原有的明细分组
  (ad_id)          -- ad_id级汇总分组
)
order by ad_id, grouping(price), total_views desc;

• grouping(price)函数用来判断当前行是否是汇总行：返回1表示price是被聚合的列（即当前行是ad_id级汇总），返回0表示是明细行。
• CPM的计算公式为(总花费 / 总展示量) * 1000，这里用round函数保留两位小数，你可以根据需求调整精度。

方法二：使用ROLLUP简化层级分组 #

如果你的分组是有层级关系的（先按ad_id，再按price），ROLLUP会自动生成所有可能的层级组合，效果和上面的分组集类似：

select
  ad_id,
  price,
  sum(views) as total_views,
  sum(unique_views) as total_unique_views,
  sum(clicks) as total_clicks,
  sum(unique_clicks) as total_unique_clicks,
  sum(views * price) as total_cost,
  case when grouping(price) = 1 then
    round((sum(views * price)::numeric / sum(views)) * 1000, 2)
  end as cpm
from event_summary
group by rollup(ad_id, price)
order by ad_id, grouping(price), total_views desc;

ROLLUP会生成(ad_id, price)、(ad_id)、()（全局汇总）这三个分组，如果你不需要全局汇总，可以在WHERE子句或者HAVING子句中过滤掉grouping(ad_id) = 1的行。

如果只需要ad_id级的CPM结果 #

如果不需要保留(ad_id, price)的明细数据，直接按ad_id分组即可，不需要复杂的分组集语法：

select
  ad_id,
  sum(views) as total_views,
  sum(unique_views) as total_unique_views,
  sum(clicks) as total_clicks,
  sum(unique_clicks) as total_unique_clicks,
  sum(views * price) as total_cost,
  round((sum(views * price)::numeric / sum(views)) * 1000, 2) as cpm
from event_summary
group by ad_id
order by total_views desc;

推荐场景 #

• 如果需要同时查看明细数据和ad_id级的CPM汇总，优先用GROUPING SETS或ROLLUP，一次查询拿到所有结果。
• 如果只需要ad_id级的CPM，直接简单分组即可，不需要复杂的分组集语法。

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