SQL为具有相同ID的所有日期添加摘要行

Question

我们从该查询中得到了以下结果表，但是我们如何添加一个汇总行来汇总同一广告 ID 的所有天数，如所需结果表中所示？谢谢。

查询:

SELECT
  right(ad_id,6) AS ad_id,
  CAST(date_start AS DATE) AS "Day",
  objective,
  SUM(impressions) AS Impressions,
  sum(clicks) AS Clicks
FROM ads 
WHERE date_start >= '2018-05-01' AND date_start < '2018-06-01'
GROUP BY ad_id, CAST(date_start AS DATE), objective
Order by ad_id, CAST(date_start AS DATE) desc

结果表:

+--------+----------+-------------+-------------+--------+
| ad_id  |   day    |  objective  | impressions | clicks |
+--------+----------+-------------+-------------+--------+
|  36911 | 5/2/2018 | CONVERSIONS |       16689 |    160 |
|  36911 | 5/1/2018 | CONVERSIONS |        4223 |     59 |
|  37111 | 5/2/2018 | CONVERSIONS |        1964 |      9 |
|  37111 | 5/1/2018 | CONVERSIONS |        1409 |     19 |
| 279311 | 5/3/2018 | LINK_CLICKS |         309 |     10 |
| 279311 | 5/2/2018 | LINK_CLICKS |        2816 |     19 |
| 279311 | 5/1/2018 | LINK_CLICKS |        5876 |     66 |
| 279511 | 5/3/2018 | LINK_CLICKS |        3551 |     86 |
| 279511 | 5/2/2018 | LINK_CLICKS |        3334 |     76 |
| 279511 | 5/1/2018 | LINK_CLICKS |       17798 |    508 |
+--------+----------+-------------+-------------+--------+

带有摘要行的所需结果表:

+--------+----------+-------------+-------------+--------+
| ad_id  |   day    |  objective  | impressions | clicks |
+--------+----------+-------------+-------------+--------+
|  36911 | All      | CONVERSIONS |       20912 |    219 |
|  36911 | 5/2/2018 | CONVERSIONS |       16689 |    160 |
|  36911 | 5/1/2018 | CONVERSIONS |        4223 |     59 |
|  37111 | All      | CONVERSIONS |        3373 |     28 |
|  37111 | 5/2/2018 | CONVERSIONS |        1964 |      9 |
|  37111 | 5/1/2018 | CONVERSIONS |        1409 |     19 |
| 279311 | All      | LINK_CLICKS |        9001 |     95 |
| 279311 | 5/3/2018 | LINK_CLICKS |         309 |     10 |
| 279311 | 5/2/2018 | LINK_CLICKS |        2816 |     19 |
| 279311 | 5/1/2018 | LINK_CLICKS |        5876 |     66 |
| 279511 | All      | LINK_CLICKS |       24683 |    670 |
| 279511 | 5/3/2018 | LINK_CLICKS |        3551 |     86 |
| 279511 | 5/2/2018 | LINK_CLICKS |        3334 |     76 |
| 279511 | 5/1/2018 | LINK_CLICKS |       17798 |    508 |
+--------+----------+-------------+-------------+--------+

Answer 1

使用分组集:

SELECT COALESCE(right(ad_id, 6), 'All') AS ad_id,
       CAST(date_start AS DATE) AS "Day",
       objective,
       SUM(impressions) AS Impressions,
       sum(clicks) AS Clicks
FROM ads 
WHERE date_start >= '2018-05-01' AND date_start < '2018-06-01'
GROUP BY GROUPING SETS ( (ad_id), (ad_id, CAST(date_start AS DATE), objective) )
Order by ad_id, CAST(date_start AS DATE) desc;

在早期版本的 Postgres 中，使用 CTE 和union all:

with t as (
      SELECT right(ad_id, 6) AS ad_id,
             CAST(date_start AS DATE) AS "Day",
             objective,
             SUM(impressions) AS Impressions,
             sum(clicks) AS Clicks
      FROM ads 
      WHERE date_start >= '2018-05-01' AND date_start < '2018-06-01'
      GROUP BY GROUPING SETS (ad_id, CAST(date_start AS DATE), objective)
     )
select *
from t
union all
select ad_id, NULL, 'All', sum(impressions), sum(clicks)
from t
group by ad_id
order by 1, 2 desc;