代码之家 › 专栏 › 技术社区 › Rogue258

在DB2SQL中使用存储过程/宏进行声明和循环

declare db2 macros stored-procedures sql

0

Rogue258 · 技术社区 · 2 年前

我有一张桌子,上面有名字和交货日期,如下所示

名称	德鲁特
做记号	2022-04-01
做记号	2022-04-03
做记号	2022-04-05
做记号	2022-04-07
做记号	2022-04-09
戴夫	2022-04-02
戴夫	2022-04-04
戴夫	2022-04-06
戴夫	2022-04-08
戴夫	2022-04-10

等等我想要一个到某个特定日期的累计姓名计数。例如,2022-04-07至2022-04-10日期的输出样本

日期	名称	尽头
2022-04-07	做记号	4.
2022-04-08	做记号	4.
2022-04-09	做记号	5.
2022-04-10	做记号	5.
2022-04-07	戴夫	3.
2022-04-08	戴夫	4.
2022-04-09	戴夫	4.
2022-04-10	戴夫	5.

Ofc对于不同的名字,日期可能是相同的。我正在尝试使用动态SQL(因为我是初学者,希望探索更多)。我根据动态SQL方法生成了以下代码。我也对其他方法持开放态度。

begin
    declare cur_date date;
    declare og_date date;
    declare Cust_Name varchar2(4);
    declare cul_cnt number;
    set cur_date = current_date; /*today's date*/
    set og_date = cur_date - 10; /*calculating cumulative counts for last 10 days*/
loop
    select Name,count(Name) into Cust_Name,cul_cnt,og_date
    from table_A
    where prod_type like 'SHOES' /*Another column in the table just for filter*/
    and Del_dt <= og_date
    group by Name;
    og_date = og_date + 1;
exit when (og_date > cur_date);
end;
end;

目前我得到了这个错误

Error report -
Error starting at line : 1 in command -
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CUR_DATE;SECTION, DRIVER=3.69.49

不过,我相信代码中会有更多错误。如何获得输出表中所示的所需输出。此外,如何获取og_date(输出中的日期列),因为它不在原始表_A中,而是一个定义的变量

0 回复 | 直到 2 年前

1

0

Mark Barinstein 2 年前

试试这个:

 WITH 
-- Table of 10 dates generation starting from 2022-04-10
-- CURRENT_DATE can be used instead   
  D (I, DT) AS 
(
  SELECT 1, DATE ('2022-04-10') FROM SYSIBM.SYSDUMMY1
    UNION ALL
  SELECT I + 1, DT - 1 DAY FROM D WHERE I < 10
)
/*
, table_A (Name, Del_dt) AS 
(
          SELECT 'Mark', DATE ('2022-04-01') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-03') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-05') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-07') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-09') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-02') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-04') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-06') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-08') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-10') FROM SYSIBM.SYSDUMMY1
)
*/
SELECT D.DT, A.Name, A.cul_cnt
FROM D
CROSS JOIN TABLE 
(
  SELECT A.Name, COUNT (1) cul_cnt
  FROM table_A A
  WHERE A.Del_dt <= D.DT
  GROUP BY A.Name
) A
WHERE DT BETWEEN DATE ('2022-04-07') AND DATE ('2022-04-10')
ORDER BY A.Name DESC, D.DT

如果取消注释注释掉的块,可以按原样运行语句。本例的结果与您的相同:

DT	名称	尽头
2022-04-07	做记号	4.
2022-04-08	做记号	4.
2022-04-09	做记号	5.
2022-04-10	做记号	5.
2022-04-07	戴夫	3.
2022-04-08	戴夫	4.
2022-04-09	戴夫	4.
2022-04-10	戴夫	5.

Oracle中的相同查询:

WITH
  D (I, DT) AS 
(
  SELECT 1, TO_DATE ('2022-04-10', 'YYYY-MM-DD') FROM DUAL
    UNION ALL
 SELECT I + 1, DT - INTERVAL '1' DAY FROM D WHERE I < 10
)
/*
, table_A (Name, Del_dt) AS 
(
          SELECT 'Mark', TO_DATE ('2022-04-01', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-03', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-05', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-07', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-09', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-02', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-04', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-06', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-08', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-10', 'YYYY-MM-DD') FROM DUAL
)
*/
SELECT TO_CHAR (D.DT, 'YYYY-MM-DD') DT, A.Name, A.cul_cnt
FROM D
CROSS JOIN LATERAL 
(
  SELECT A.Name, COUNT (1) cul_cnt
  FROM table_A A
  WHERE A.Del_dt <= D.DT
  GROUP BY A.Name
) A
WHERE D.DT BETWEEN TO_DATE ('2022-04-07', 'YYYY-MM-DD') AND TO_DATE ('2022-04-10', 'YYYY-MM-DD')
ORDER BY A.Name DESC, D.DT