A SQL need to write to a day yesterday a total of 10 days before
SELECT
COUNT (DISTINCT id)
FROM table
WHERE
Actv_dt: : DATE<=CURRENT_DATE -i
I wrote the first ten SQL with union all together
What method with loop to the same result the search on the net circulation way to write the following SQL but error results below the SQL for superior solution under
Using postgres database

Do $$
Declare
V_idx integer:=1;
The begin
While v_idx & lt; 10 loop
SELECT
COUNT (DISTINCT id)
FROM table
WHERE
Actv_dt: : DATE<=CURRENT_DATE - (v_idx);
End loop;
End $$;


(Err) ERROR: the query has no destination for the result data
HINT: If you want to discard the results of a SELECT and use the PERFORM home.
CONTEXT: PL/pgSQL function inline_code_block line 6 at the SQL statement

CodePudding user response:

There's no need to write a function, can group by the
SELECT
Cast (actv_dt as: DATE), COUNT (DISTINCT id)
FROM table
WHERE
Actv_dt: : DATE<=CURRENT_DATE -i
Group by cast (actv_dt as: DATE)

If you want to write a function, grammar see help, help is the best teacher, don't nothing to search.

CodePudding user response:

Write a colon upstairs, can't, old person.

CodePudding user response:

GROUP BY is according to the daily total id
I need to accumulate to one day
Such as the first day 2 of article 1 day 2 until the 10th day 10
I want a day ran out SQL results
1 3 6 October 15, 21, 28, 36 45 to 55
Instead of 1 2 3 4 5 6 7 8 9 10

CodePudding user response:

Available window (window) function to achieve, just used to investigating.

Use stored procedures, and return before the select query

CodePudding user response:

Learn every day, hard up