联接中不存在行的SQL聚合函数

Question

我正在尝试为不存在的行找到SQL聚合为0，在特定年份使用以下表的联接。

Customers -> customerid, Name
product -> prodcutid, price, name
Sales -> productid,customerid,date, orders

create table product(productid int,name char(20), price int,PRIMARY KEY(productid));
create table Customers(customerid int,name char(20),PRIMARY KEY(customerid));
create table sale(productid int,customerid int,orders int, date date);

INSERT INTO product (productid,name ,price) 
VALUES (1, 'TV', 3200);

INSERT INTO product (productid,name ,price) 
VALUES (2, 'fridge', 4200);

INSERT INTO Customers (customerid,name) 
VALUES (1, 'US');

INSERT INTO Customers (customerid,name) 
VALUES (2, 'Aus');

INSERT INTO sale (productid,customerid,orders,date) 
VALUES (2, 2, 1,'2018-10-2');

INSERT INTO sale (productid,customerid,orders,date) 
VALUES (1, 2, 10,'2018-10-13');

INSERT INTO sale (productid,customerid,orders,date) 
VALUES (1, 1, 100,'2018-10-2');

INSERT INTO sale (productid,customerid,orders,date) 
VALUES (1, 1, 100,'2019-10-1');

我获得了这个查询，以查找某一年产品的现有订单的聚合，但我也希望获得在同一年内没有与其他产品订购的产品：

  with cte as (select producid,customerid,sum(orders) as s from sales 
  where date_part('year', date) = 2018
  group by productid,customerid)
  select a.name,b.name,(c.s * b.price) as sales
  from customers as a 
  join cte as c on a.prodcutid=c.productid
  join product as b on b.customerid=c.customerid; 

通过上述查询可以获得的数据：

 -----------------------------------------------
 Product.name | Customer.name | Total_sale Value
 -----------------------------------------------
      TV       |   AUS        |  32000 (orders * price per product) 
      Fridge   |   AUS        |  4200 
      TV       |   US         |  320,000 

我想要的是2018年的数据：

 -----------------------------------------------
 Product.name | Customer.name | Total_sale Value
 -----------------------------------------------
      TV       |   AUS        |  32000 (orders * price per product) (if there was a sale then you should get a value else then it should be 0)
      Fridge   |   AUS        |  4200 
      Fridge   |   US         |  0 (as Customer US didnt order fridge at all)
      TV       |   US         |  320,000 

我尝试遵循相同的解决方案提供的SQL Get aggregate as 0 for non existing row using inner joins，但无法得到答案，原谅我的重复，但请让我知道我做错了什么。我是新来的sql，所以请原谅我的重复。

Answer 1

with cte as (
    select productid, customerid, sum(orders) as s from sale
    where year(date) = 2018
    group by productid, customerid
)
select p.name, c.name, coalesce(s.s * p.price, 0) as sales
from product as p
    cross join customer as c
    left outer join cte as s on s.productid = p.productid
                           and s.customerid = c.customerid

Answer 2

注意:自从这个答案发布后，这个问题就被编辑过了，我不再100%肯定有人问了什么，

我正在尝试使用联接为非现有行找到SQL聚合为0。

从概念上讲，销售表将产品与客户联系起来。

• 有一种产品，而且总是有一种产品(因为你这样看，就好像“我对每一种产品都感兴趣，不管它卖不卖”)

• 也许有一个客户(如果他们注册了，从来没有买过任何东西，他们可能是一个没有销售的客户)

• ，只有当某一产品已被客户购买时才有销售(因此，从未购买任何东西的客户在销售中没有提及，而且我们只对已经或尚未售出的产品感兴趣)

因此，回答问题的关系图需要如下所示：

products -> sales -> customer

如果一个产品从未订购过，那么products -> sales关系就会中断，因此，只有在存在匹配销售的情况下，它才需要是一个左连接，才能保持所有产品的销售并与产品关联。

sales -> customer不应该中断，可以是内部连接(如果您愿意首先加入它)，或者如果您不作为子查询(如果您使它成为immer连接，则由于产品从未售出而使其为空)的话，则可以退出join，因此未售出的产品将从结果中消失。

因此，您需要这样的查询：

SELECT ...
FROM
  products p
  LEFT JOIN sales s ON ...
  LEFT JOIN customer c ON ...

或者你需要

SELECT ...
FROM
  products p
  LEFT JOIN 
  (SELECT * FROM sales s INNER JOIN customer c ON ...) sc

如果使用第二种形式并在子查询中执行分组，可能会更简单，但在任何地方执行这些操作都很简单

对于从未销售过的产品，为禁止销售而引入的空值将参与SUM/AVG等，就好像它们是0一样。

附带注意，我不太确定customer表对您所声明的任何需求有什么支持，而且它可能可以省略