代码之家 › 专栏 › 技术社区 › Vondoe79

Postgresql如何聚合表中的数据以生成一个具有一定范围内唯一值的汇总表

postgresql

Vondoe79 · 技术社区 · 4 年前

所以,我对postgresql是新手。我的数据如下:

PRODUCT WKnum SALES
HAMMER  1   17
HAMMER  2   20
HAMMER  3   17
HAMMER  4   10
HAMMER  5   12
HAMMER  6   13
HAMMER  7   2
HAMMER  8   25
SINK    1   25
SINK    2   20
SINK    3   9
SINK    4   7
SINK    5   24
SINK    6   16
SINK    7   10
SINK    8   16
BUCKET  1   22
BUCKET  2   2
BUCKET  3   10
BUCKET  4   24
BUCKET  5   9
BUCKET  6   20
BUCKET  7   9
BUCKET  8   21

然后,我希望得到如下结果:

PRODUCT BEST_CONSEC_4WEEKS  BEST_CONSEC_4WEEKS_SLS
HAMMER    1-4                       64
SINK      5-8                       66
BUCKET    3-6                       63

其中,“BEST_Concer_4WEEKS”是一个字符串,它表示连续4周内销售额最高的实际周数。“BEST_Concer_4WEEKS_SLS”提供了上述周内该产品的总销量。必须避免使用特定于数据库的功能。

下面的代码是我的尝试,但它并没有像我预期的那样工作。

SELECT PRODUCT, 
CASE WHEN Wknum IN â1 - 4â:: INTETER AS BEST_CONSEC_4WEEKS, 
SUM (CASE WHEN Wknum IS BETWEEN â1 AND 4â::INTEGER THEN SALES ELSE 0 END) OR 
SUM (CASE WHEN Wknum IS BETWEEN â5 AND 8â::INTEGER THEN SALES ELSE 0 END) 
SUM (CASE WHEN Wknum IS BETWEEN â3 AND 6â::INTEGER THEN SALES ELSE 0 END) AS BEST_CONSEC_4WEEKS_SLS
FROM TABLE1

谁能告诉我我做错了什么吗?谢谢

0 回复 | 直到 4 年前

Zakaria 4 年前

您可以将窗口函数与框架一起使用,如下所示:

with u as
(select PRODUCT, 
WKnum,
sum(SALES) over(partition by PRODUCT order by WKnum rows between current row and 3 following) as BEST_CONSEC_4WEEKS_SLS
from TABLE1),
v as
(select *, rank() over(partition by PRODUCT order by BEST_CONSEC_4WEEKS_SLS desc) as r
from u)
select PRODUCT,
concat(wknum, '-', wknum + 3) as BEST_CONSEC_4WEEKS,
BEST_CONSEC_4WEEKS_SLS
from v where r = 1

fiddle