postgresql - 如何在 Postgresql 中水平组契约(Contract)一个表上的两个选择查询？

Question

大家。我是 Postgresql 的初学者。最近我遇到了一个问题。我有一张名为“销售”的表。

create table sales
    (
        cust    varchar(20),
        prod    varchar(20),
        day integer,
        month   integer,
        year    integer,
        state   char(2),
        quant   integer
    );
insert into sales values ('Bloom', 'Pepsi', 2, 12, 2001, 'NY', 4232);
insert into sales values ('Knuth', 'Bread', 23, 5, 2005, 'PA', 4167);
insert into sales values ('Emily', 'Pepsi', 22, 1, 2006, 'CT', 4404);
insert into sales values ('Emily', 'Fruits', 11, 1, 2000, 'NJ', 4369);
insert into sales values ('Helen', 'Milk', 7, 11, 2006, 'CT', 210);
insert into sales values ('Emily', 'Soap', 2, 4, 2002, 'CT', 2549);
insert into sales values ('Bloom', 'Eggs', 30, 11, 2000, 'NJ', 559);

....总共有 498 行。以下是此表的概述:

现在我想计算每种产品的最大和最小销售量，以及它们对应的客户(购买产品的人)、日期(即最大和最小销售量的日期)和销售状态交易发生。以及相应产品的平均销售数量。

合并后应该是这样的:

它应该有 10 行，因为总共有 10 种不同的产品。

我试过:

select prod,
       max(quant),
       cust as MAX_CUST
from sales
group by prod;

但它返回了一个错误，并说客户应该在分组依据中。但我只想按产品类型分类。

此外，我如何横向组合 ma​​x_q 及其客户、日期、状态 与 min_q 及其客户、日期、状态 以及 AVG_Q 通过他们的产品名称？我真的很困惑!

Answer 1

您可以使用分析函数 ROW_NUMBER 通过增加/减少子查询中每个产品的销售额来对记录进行排名，然后进行条件聚合:

SELECT
    prod product,
    MAX(CASE WHEN rn2 = 1 THEN quant END) max_quant,
    MAX(CASE WHEN rn2 = 1 THEN cust END) max_cust,
    MAX(CASE WHEN rn2 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) max_date,
    MAX(CASE WHEN rn2 = 1 THEN state END) max_state,
    MAX(CASE WHEN rn1 = 1 THEN quant END) min_quant,
    MAX(CASE WHEN rn1 = 1 THEN cust END) min_cust,
    MAX(CASE WHEN rn1 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) min_date,
    MAX(CASE WHEN rn1 = 1 THEN state END) min_state,
    avg_quant
FROM (
    SELECT
        s.*,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant) rn1,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant DESC) rn2,
        AVG(quant) OVER(PARTITION BY prod) avg_quant
    FROM sales s
) x
WHERE rn1 = 1 OR rn2 = 1
GROUP BY prod, avg_quant