postgresql 分组按集进行子聚合?

j13ufse2  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(161)

我有一个数据汇总表,看起来像这样:

  1. create table event_summary (
  2. created_at timestamp,
  3. ad_id integer,
  4. views integer,
  5. unique_views integer,
  6. clicks integer,
  7. unique_clicks integer,
  8. price integer
  9. );
  10. create index on event_summary using BRIN (created_at);

我试图只在SQL中表达自己,但很难理解如何在组内聚合集合。我有以下查询:

  1. select
  2. sum(views),
  3. sum(unique_views),
  4. sum(clicks),
  5. sum(unique_clicks),
  6. price
  7. from event_summary
  8. group by ad_id, price
  9. order by views desc

查询工作得很好。然后,在我的应用程序代码中,我可以计算每个ad_id的每英里成本,因为price已经分离。所以我把views的值乘以price。然后我把所有的值加起来,我就得到了每个ad_id的最终每英里成本。
但我对分组集合,甚至是汇总感到好奇。在SQL上进行这种计算是可能的,甚至是推荐的吗?

z2acfund

z2acfund1#

听起来你实际上可以用一个window function和一个子查询来解决问题:demo

  1. select *,
  2. sum(views_sum*price)over(partition by ad_id) as cost_per_mile
  3. from(select ad_id,
  4. sum(views) as views_sum,
  5. sum(unique_views) as unique_views_sum,
  6. sum(clicks) as clicks_sum,
  7. sum(unique_clicks) as unique_clicks_sum,
  8. price
  9. from event_summary
  10. group by ad_id, price
  11. ) AS subquery;

| ad_id| views_sum| unique_views_sum| clicks_sum| unique_clicks_sum|价格|每英里费用|
| --|--|--|--|--|--|--|
| 1 | 115109 | 36892 | 12166 | 8555 | 1760 | 364890640 |
| 1 | 90166 | 37656 | 13606 | 8683 | 1800 | 364890640 |
| 2 | 112170 | 36716 | 13889 | 6330 | 1760 | 409178400 |
| 2 | 117644 | 40753 | 11531 | 6793 | 1800 | 409178400 |
| 3 | 103802 | 35926 | 13843 | 7130 | 1800 | 381687920 |
| 3 | 110707 | 43738 | 12581 | 6989 | 1760 | 381687920 |
如果按price分组的唯一原因是计算数据库外的数据,那么可以摆脱它,然后继续使用聚合函数中的算术:demo

  1. select ad_id,
  2. sum(views) as views_sum,
  3. sum(unique_views) as unique_views_sum,
  4. sum(clicks) as clicks_sum,
  5. sum(unique_clicks) as unique_clicks_sum,
  6. sum(views*price) as cost_per_mile
  7. from event_summary
  8. group by ad_id
  9. order by ad_id;

| ad_id| views_sum| unique_views_sum| clicks_sum| unique_clicks_sum|每英里费用|
| --|--|--|--|--|--|
| 1 | 205275 | 74548 | 25772 | 17238 | 364890640 |
| 2 | 229814 | 77469 | 25420 | 13123 | 409178400 |
| 3 | 214509 | 79664 | 26424 | 14119 | 381687920 |
这是讨论完整聚合函数调用语法的文档-注意它说的是 expression 而不是 column,这意味着有相当多的灵活性,包括上面的算术。在页面的顶部,你可以看到15种其他类型的表达式,其中的列表并不详尽。

展开查看全部

相关问题