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
More 0

CodePudding user response:

Try this:
Do $$
Declare
V_idx integer:=1;
V_cnt integer:=0;
The begin
While v_idx & lt; 10 loop
Select count (distinct id)
Into v_cnt
From table
Where
Actv_dt: : DATE<=CURRENT_DATE - (v_idx);
End loop;
End $$;

CodePudding user response:

reference 1st floor qq646748739 response:

try this:
Do $$
Declare
V_idx integer:=1;
V_cnt integer:=0;
The begin
While v_idx & lt; 10 loop
Select count (distinct id)
Into v_cnt
From table
Where
Actv_dt: : DATE<=CURRENT_DATE - (v_idx);
End loop;
End $$;

Grammar no problem but couldn't run the SQL to run the SQL 2000 seconds haven't result ten SQL even a few seconds out of the results

CodePudding user response:

End loop above added v_idx=v_idx + 1; Why didn't jump out of the loop can be the result..

CodePudding user response:

Changed to the following SQL to run out of the results yesterday, but only the value of the other nine days not cycle to the results,,,

The CREATE OR REPLACE FUNCTION count_sfz (INT) RETURNS an INT AS
$$
DECLARE v_idx INTEGER; V_cnt INTEGER:=0;
The BEGIN
FOR v_idx IN 1.. 10 loop
Select count (distinct id) INTO v_cnt
The FROM
Table
WHERE
Actv_dt: : DATE & lt;=CURRENT_DATE - (v_idx);
RETURN v_cnt;
END loop;
END $$

LANGUAGE PLPGSQL;
The SELECT count_sfz (10);

CodePudding user response:

Group by the date