在 Postgres 中聚合多个字段时填充缺失的行

我每天使用 Postgres 汇总一组产品的销售额，不仅需要知道何时发生销售，还需要知道何时不发生销售以进行进一步处理。

SELECT 
sd.date, 
COUNT(sd.sale_id) AS sales, 
sd.product

FROM sales_data sd
-- sales per product, per day
GROUP BY sd.product, sd.date
ORDER BY sd.product, sd.date

这会产生以下结果：

    date    | sales |       product                           
------------+-------+-------------------
 2017-08-17 |  10   | soap
 2017-08-19 |   2   | soap
 2017-08-20 |   5   | soap
 2017-08-17 |   2   | shower gel
 2017-08-21 |   1   | shower gel

正如您所看到的 - 每个产品的日期范围并不连续，因为sales_data只是有时不包含这些产品的任何信息。

我的目标是添加一个sales = 0某个范围内任何一天均未销售的每种产品的行 - 例如此处，介于2017-08-17 and 2017-08-21给出如下内容：

    date    | sales |      product                           
------------+-------+-------------------
 2017-08-17 |  10   | soap
 2017-08-18 |   0   | soap
 2017-08-19 |   2   | soap
 2017-08-20 |   5   | soap
 2017-08-21 |   0   | soap
 2017-08-17 |   2   | shower gel
 2017-08-18 |   0   | shower gel
 2017-08-19 |   0   | shower gel
 2017-08-20 |   0   | shower gel
 2017-08-21 |   1   | shower gel

在只有一个产品的更简单的情况下，解决方案似乎是使用generate_series() i.e.:

• 使用generate_series创建完整的日期范围
• LEFT JOIN已聚合的销售数据到日期系列
• COALESCE any NULL缺失行计数为 0

我遇到的问题是，这种方法似乎无法在聚合数据中重复日期，因为我不仅对多个日期进行分组，而且还对多个产品进行分组。

It feels就像我应该能够在这里用窗口函数做一些狡猾的事情来解决这个问题，例如加入由产品名称定义的分区的完整日期范围 - 但我看不到真正让它发挥作用的方法。

你可以使用：

WITH cte AS (
   SELECT date, s.product
   FROM  ... -- some way to generate date series
   CROSS JOIN (SELECT DISTINCT product FROM sales_data) s
)
SELECT 
    c.date,
    c.product,
    COUNT(sd.sale_id) AS sales
FROM cte c
LEFT JOIN sales_data sd
  ON c.date = sd.date AND c.product= sd.product
GROUP BY c.date, c.product
ORDER BY c.date, c.product;

首先创建日期和产品的笛卡尔积，然后LEFT JOIN根据实际数据并进行计算。

Oracle 对于这种情况有一个很棒的功能，称为分区外连接:

SELECT times.time_id, product, quantity 
FROM inventory  PARTITION BY  (product) 
RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) 
WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') 
      AND TO_DATE('06/04/01', 'DD/MM/YY') 
ORDER BY  2,1;