I have something like that, i need to get "triangular" number - created by summing all natural ones from 0 to n. I have such code, what should i write so i could SUM(generate_series from generate_series(1,n))?

SELECT n,
    CASE 
      WHEN n > 0 THEN
        SUM value FROM generate_series(1,n)
      ELSE 0
    END
  AS res
FROM triangular

Result should be like this: (n-1 , res-1/ n-2, res-3/ n-3, res-6) or in other words: for(res=0;n>0;n--){res=res n;} I`m trying to generate_serise and sum it.

I tried to find "adecvate" PostgreSQL documentation wikilike to see returning table "attributes" of generate_sequence "class"

Now, i`ve got this:

SELECT n,
    SUM(generate_s::INTEGER)
  AS res
FROM triangular,
  LATERAL generate_series(0,CASE WHEN n<=0 THEN 0 ELSE triangular.n END) 
    AS generate_ser(generate_s)
GROUP BY n

How do i replace GROUP BY so its doesnt group it by any way it does it, only the way n value enters the processing code? Kinda how to group values, without ordering them?

CodePudding user response：

Are you searching for the cumulative SUM() window function?

demo:db<>fiddle

SELECT
    x,
    SUM(x) OVER (ORDER BY x) 
FROM generate_series(1, 10) AS x  -- second parameter is n

or a simple aggregation?

demo:db<>fiddle

SELECT
    SUM(x)
FROM generate_series(1, 10) AS x  -- second parameter is n

CodePudding user response：

If you don't supply an alias, the name is just "generate_series". I don't know where this is documented, but it is discoverable by using * and looking at the header:

select * from generate_series(1,1);
 generate_series
-----------------
               1

Or if you assign a column alias, then that is the name:

select * from generate_series(1,1) as foo(bar);
 bar
-----
   1

If you supply a table alias but not a column alias, then the column name is a copy of the table name.

select * from generate_series(1,1) as foo;
 foo
-----
   1