Postgresql how do I aggregate the data in a table to produce a summarized table with unique values i-CodePudding

So, I am new to postgresql. my data is as follows:

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

I would then like an outcome like the below:

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

Where "BEST_CONSEC_4WEEKS" is a character string that tells the actual week numbers with the highest sum of sales over a consecutive 4 week period. And "BEST_CONSEC_4WEEKS_SLS" provides the total units sold for that product during the weeks identified above. Database-specific functions must be avoided.

The following piece of code was my attempt, but it is not working as I expected.

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

Can anyone please tell me what I am doing wrong? Thanks

CodePudding user response：

You can use window functions with frames as follows:

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