PostgreSQL: Group value every n rows-CodePudding

I have a simple table with date and budget values

budget	date
10	2022-05-01
5	2022-05-02
15	2022-05-03
16	2022-05-04
22	2022-05-05
13	2022-05-06
23	2022-05-07
56	2022-05-08

I want to group every N rows in one with PostgreSQL statement. I expect to see something like this, if N = 2 (for example, it also can be 3 or 5, it depends)

budget	date
15	2022-05-01 - 2022-05-02
31	2022-05-03 - 2022-05-04
35	2022-05-05 - 2022-05-06
79	2022-05-07 - 2022-05-08

If it more that 2, the date should be formatted as start_row_date - end_row_date (2022-05-01 - 2022-05-03 for N = 3)

CodePudding user response：

We can try to use ROW_NUMBER window function with some math which to represent N if your "date" might be datatype we can try to use TO_CHAR to get the format as your expected.

SELECT SUM(budget) budget,
       CONCAT(MIN("date"),'-',MAX("date")) date
FROM (
  SELECT *,ROW_NUMBER() OVER(ORDER BY "date") rn
  FROM T 
) t1
GROUP BY (rn - 1)/2
ORDER BY (rn - 1)/2

sqlfiddle

CodePudding user response：

This is the solution provided by @D-Shih. He is the owner of the logic and i convert it into a DB function for easy access for other viewers

Note: T = budget_table , Date = bud_date

CREATE or replace FUNCTION budget_function(num integer)
RETURNS TABLE(budget bigint, dub_date text)
LANGUAGE plpgsql
AS $function$
begin

    return query
        SELECT SUM(t1.budget) budget,
               string_agg(TO_CHAR("bud_date",'YYYY-MM-DD'),'-') date
        FROM (
          SELECT *, ROW_NUMBER() OVER(ORDER BY "bud_date") rn
          FROM budget_table
        ) t1
        GROUP BY (rn - 1)/num
        ORDER BY (rn - 1)/num;
    
end
$function$
;

Try

select * from budget_function(2);
select * from budget_function(3);