如何在Postgresql中删除空值并将其设置为10行？ [英] How can I remove the null values and make it to 10 rows in Postgresql?

问题描述

我是Postgresql的新手。我有一个名为 sales的表。

I am new to Postgresql. I have a table called 'sales'.

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);

类似这样的东西：

something like this:

现在，我想找到最有利的月份（当时产品的大部分金额为
售出）和每种产品的最差月（当售出的产品数量最少时）。

Now I want to find the "most favorable" month (when most amount of the product was sold) and the "least favorable" month (when the least amount of the product was sold) for each product.

结果应如下所示：

The result should be like this:

我输入

SELECT
    prod product,
    MAX(CASE WHEN rn2 = 1 THEN month END) MOST_FAV_MO,

    MAX(CASE WHEN rn1 = 1 THEN month END) LEAST_FAV_MO
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant ) rn1,
        ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant DESC) rn2
    FROM sales
) x
WHERE  rn1 = 1 or rn2 = 1
GROUP BY prod,quant;

然后每种产品的值为空，总共有20行：
< img src = https://i.stack.imgur.com/YfFFQ.jpg alt =当前结果>

Then there are null values for each product and there are 20 rows in total:

那么如何删除空值在这些行中，使行总数达到10（总共有10种不同的产品）？

So how can I remove the null values in these rows and make the total number of rows to 10 (There are 10 distinct products in total)???

推荐答案

I应该说 GROUP BY 子句应该是

GROUP BY prod

否则，每个不同的量会得到一行，这不是您想要的。

Otherwise you get one line per different quant, which is not what you want.

这篇关于如何在Postgresql中删除空值并将其设置为10行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！