将表结果与来自另一个表的信息进行分组

Question

我试图执行一个查询，该查询通过外键返回来自同一表的值的聚合，其中包含来自其他表的信息，但我不能。在下面的示例中，我想返回2020-01-01和2021-01-01按州划分的总销售额，并显示州名。

表脚本:

CREATE TABLE IF NOT EXISTS estado (
id SERIAL PRIMARY KEY,
estado VARCHAR(100)
)
CREATE TABLE IF NOT EXISTS municipio (
id SERIAL PRIMARY KEY,
estado integer REFERENCES estado(id),
municipio VARCHAR(100)
)
CREATE TABLE IF NOT EXISTS vendas (
id SERIAL PRIMARY KEY,
municipio integer REFERENCES municipio(id),
valor numeric,
data_venda date
)
INSERT INTO estado VALUES (1, 'PR');
INSERT INTO estado VALUES (2, 'SC');
INSERT INTO estado VALUES (3, 'RS');
INSERT INTO municipio VALUES (1, 1, 'Pelotas');
INSERT INTO municipio VALUES (2, 1, 'Caxias do Sul');
INSERT INTO municipio VALUES (3, 1, 'Porto Alegre');
INSERT INTO municipio VALUES (4, 2, 'Florianopolis');
INSERT INTO municipio VALUES (5, 2, 'Chapeco');
INSERT INTO municipio VALUES (6, 2, 'Itajai');
INSERT INTO municipio VALUES (7, 3, 'Curitiba');
INSERT INTO municipio VALUES (8, 3, 'Maringa');
INSERT INTO municipio VALUES (9, 3, 'Foz do Iguaçu');
INSERT INTO vendas VALUES (1, 6, 5, '2020-01-01');
INSERT INTO vendas VALUES (2, 5, 10, '2021-01-01');
INSERT INTO vendas VALUES (3, 5, 5, '2020-01-01');
INSERT INTO vendas VALUES (4, 4, 2, '2020-01-01');
INSERT INTO vendas VALUES (5, 3, 10, '2021-01-01');
INSERT INTO vendas VALUES (6, 3, 12, '2020-01-01');
INSERT INTO vendas VALUES (7, 3, 20, '2020-01-01');
INSERT INTO vendas VALUES (8, 2, 10, '2020-01-01');
INSERT INTO vendas VALUES (9, 1, 11, '2021-01-01');
INSERT INTO vendas VALUES (10, 9, 4, '2020-01-01');

我的尝试(不显示荒谬的值和RS值):

SELECT 
e.estado, SUM(v.valor) as sum2021, SUM(v2.valor) as sum2020
FROM vendas v 
CROSS JOIN vendas v2 
INNER JOIN municipio m ON v.municipio = m.id
INNER JOIN estado e ON m.estado = e.id
WHERE v.data_venda = '2021-01-01' 
AND v2.data_venda = '2020-01-01' 
GROUP BY 1;

翻译一些术语:

município = city 
estado = state 
vendas = sales
valor = value 
data_venda = date of sale

Answer 1

您将vendas与自身交叉连接(如v1和v2)，这意味着它的每一行将与其他行匹配(即笛卡尔积)，这将产生您所看到的意想不到的结果。

好消息是你不需要这个连接。您可以使用filter子句对来自查询的行子集使用聚合函数(在本例中为sum):

SELECT 
e.estado, 
SUM(v.valor) FILTER (WHERE data_venda = '2021-01-01') AS sum2021, 
SUM(v.valor) FILTER (WHERE data_venda = '2020-01-01') AS sum2020
FROM vendas v 
INNER JOIN municipio m ON v.municipio = m.id
INNER JOIN estado e ON m.estado = e.id
GROUP BY 
e.estado;

<<p>SQLFiddle演示/strong>