将 LOOP 合并到 SQL 中 [英] Incorporating a LOOP into a SQL

问题描述

我对 SQL 还很陌生，以前没有将循环合并到 SQL 语句中.这个来自 elan.elig 的 SQL 查询返回的数据如下方网格所示.

I am fairly new at SQL and have not incorporated a Loop into a SQL statement previously. This SQL query from elan.elig returns data as shown in the grid below.

select  (extract(year from  age(case when terminationdate is null then 
CURRENT_DATE else terminationdate END ,effectivedate ))) *12 +
(extract(month from  age(case when terminationdate is null then 
CURRENT_DATE else terminationdate END ,effectivedate ))  +1)
as "mbrmonths" ,effectivedate
from elan.elig

Mbr Months  Effective Date
1   10/1/2018
10  11/1/2018
2   11/1/2018
8   11/1/2018
8   11/1/2018
8   11/1/2018
2   11/1/2018
2   11/1/2018
7   11/1/2018

对于查询中的每一行，我需要执行后续的 LOOP，将 memberMonth 计数传播到 Year/Month 存储桶中.下面的 Do LOOP 正是这样做的.我一直在尝试确定如何将循环合并到 SQL 语句中，以便对于读取的每一行，它将传递两个变量并执行循环，然后读取下一行并继续..

For each row from the query I need to execute the subsequent LOOP that spreads the memberMonth counts into the Year/Month buckets. The following Do LOOP does exactly this. I have been trying for some time now to determine how to incorporate the Loop into the SQL statement so that for each row read, it will pass the two variables and execute the Loop and then read the next row and continue on..

DO $$
declare 
nbr_mem_months integer=5;
effectivedate date ='20190401';
ym char(6) =to_char(effectivedate,'YYYYMM');
begin
for r in 1..nbr_mem_months loop
update elan.pmpm set mbrmonths=mbrmonths+1 where yyyyymm=ym;
effectivedate=effectivedate + interval '1 month';
ym=to_char(effectivedate,'YYYYMM');
end loop;
end;
$$;

PMPM 存储桶

yyyymm  mbrmonths
201901  0
201902  0
201903  0
201904  1
201905  1
201906  1
201907  1
201908  1
201909  0
201910  0
201911  0

CREATE FUNCTION "UpdatePMPM"() RETURNS boolean
    LANGUAGE plpgsql
AS
$$
DECLARE
    nbr_mem_months NUMERIC;
    effectivedate date;
    ym char(6);
BEGIN
        LOOP
            ym=to_char(effectivedate,'YYYYMM');
            nbr_mem_months=5;
            UPDATE elan.pmpm set mbrmonths=mbrmonths+1 where yyyyymm=ym;
         
            effectivedate=effectivedate + interval '1 month';
        END LOOP;
    RETURN TRUE;
END
$$;



*Response from the Select statement:

ERROR:  function public.UpdatePMPM(integer, date, text) does not exist

Select public."UpdatePMPM"(5,cast('20190101' as date),cast('...
               ^
HINT:  No function matches the given name and argument types.

推荐答案

问题是在创建函数时使用参数调用函数但未指定任何参数.所以你需要类似的东西(未测试):

The issue is calling the function with arguments but not specifying any when creating the function. So you need something like(not tested):

CREATE FUNCTION "UpdatePMPM"(nbr_mem_months integer, effectivdate date, some_arg varchar) RETURNS void
    LANGUAGE plpgsql
AS
$$
DECLARE
    ym varchar := to_char(effectivedate,'YYYYMM');
BEGIN
        FOR r IN  1..nbr_mem_months LOOP
            
            UPDATE elan.pmpm set mbrmonths = mbrmonths+1 where yyyyymm = ym;
            effectivedate = effectivedate + interval '1 month';
            ym=to_char(effectivedate,'YYYYMM');
        END LOOP;
    RETURN;
END
$$;

从错误中不清楚第三个参数应该是什么，所以你会澄清.

From the error it is not clear what the third argument is supposed to be, so that will clarification from you.

这篇关于将 LOOP 合并到 SQL 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！