个人笔记——SQL里面的编程

例题A

把12345五个数组通过存储过程随即排列插入一张表里面
提取关键信息，5个数字，存储过程，排列，插入表

实现

1. 先获取五个数字，建立一张数据表，先插入五个数字

CREATE COLUMN TABLE "HAODONG"."TEST_NUMBER" (
"NUMBER" INTEGER CS_INT
) UNLOAD PRIORITY 5 AUTO MERGE 

2. 建立再建立一张存储排列好数字的表

CREATE COLUMN TABLE "HAODONG"."TEST_ARRAY" (
"ARRAY" VARCHAR(5)
) UNLOAD PRIORITY 5 AUTO MERGE 

3. 建立存储过程

CREATE PROCEDURE ADD_NUMBER_FHD()
AS 
V_NUM VARCHAR(5); --存放组合后的五位数
V_COUNT1 INTEGER; --存放第一张表的数字个数
V_COUNT2 INTEGER; --存放是否有重复数字的个数
V_COUNT3 INTEGER DEFAULT 0;  --组合了多少个不同的五位数
BEGIN

--声明变量
DECLARE INT_RESULT INTEGER DEFAULT 1; --循环条件数据之一
DECLARE I INTEGER;--循环计算组成的五位数个数
SELECT COUNT(*) INTO V_COUNT1 FROM "HAODONG"."TEST_NUMBER";--通过查询表里面数据的个数，然后into赋值给V_COUNT1变量

--计算有多少种排列方式（排列方式共有1*2*3*4*5=120种）
FOR I IN 1..V_COUNT1 DO   --循环，这里其实可以直接赋值一个变量为120，不需要计算
INT_RESULT := I * INT_RESULT;
END FOR;

--查询并插入所有随机排序的数据（去重）
WHILE V_COUNT3 < INT_RESULT DO  --循环

SELECT COUNT(*) INTO V_COUNT3 FROM "HAODONG"."TEST_ARRAY";--V_COUNT3++，每次查询表里面的数据，在个数上面增加，来控制循环
SELECT STRING_AGG("NUMBER" ORDER BY RAND()) INTO V_NUM FROM "HAODONG"."TEST_NUMBER";--随机组合并且赋值给变量V_NUM
SELECT COUNT(*) INTO V_COUNT2 FROM "HAODONG"."TEST_ARRAY" B WHERE B.ARRAY = V_NUM;--和表里的数据进行比较，返回1或者0给V_COUNT2

--如果随机排列后的数据未在表中查询到，返回0，数据插入
IF(V_COUNT2 = 0)
THEN
INSERT INTO "HAODONG"."TEST_ARRAY" VALUES(V_NUM);--插入数据
END IF;
END WHILE;
END;

语法

• IF THEN ELSE

   IF  条件
   THEN 
   为真执行
   ELSE
   否则执行
   END IF;
  

• WHILE LOOP

   WHILE	条件 DO
   	满足执行
   END WHILE
  

• FOR LOOP

    FOR IN start_value .. end_value DO
    	满足执行
    END FOR
  

start_value：开始变量
end_value ：结束变量

例题B

表格HANABI.RECEIVED_PAYMENTS中存的是2014，2015年的回款数据，根据财务要求计算YTD（YTD概念百度查）。其中，每月25日之后算作下一月的回款（11月26日-12月31日算作12月的回款）。建立sql script 和graghical的计算视图查询201401-201503每个月的YTD值。

实现

/******* Begin Procedure Script ************/ 
 BEGIN 

--获取数据
LT_1=
SELECT
AMOUNT,
LEFT(PERIOD_ID,4) YEAR,--截取年份
CASE WHEN 
RIGHT(PERIOD_ID,2) >25 
AND MONTH(PERIOD_ID) <12 
THEN LEFT((TO_DATS(ADD_MONTHS(PERIOD_ID,1))),6) 
ELSE LEFT(PERIOD_ID,6) END YEARMONTH,--对月份进行要求上的偏离
PERIOD_ID
FROM
HAODONG.RECEIVED_PAYMENTS
WHERE PERIOD_ID BETWEEN '20140101' AND '20150301'--对日期上面进行限制
;

--根据年月对金额进行汇总
LT_2=
SELECT
SUM(AMOUNT) AMOUNT,
YEAR(YEARMONTH) YEAR,
MONTH(YEARMONTH) MONTH
FROM :LT_1
GROUP BY
YEAR(YEARMONTH),
MONTH(YEARMONTH)
;

--进行偏离汇总，得出YTD
VAR_OUT = 
SELECT
B.YEAR,
B.MONTH,
SUM(A.AMOUNT) AMOUNT
FROM :LT_2 AS A 
JOIN :LT_2 AS B 
ON A.YEAR = B.YEAR 
AND A.MONTH <= B.MONTH
GROUP BY
B.YEAR,
B.MONTH
;

END /********* End Procedure Script ************/

窗口函数

SELECT
YEAR,
YEARMONTH,
SUM(AMOUNT) OVER(PARTITION BY YEAR ORDER BY  YEARMONTH) as Z_AMOUNT
--窗口函数，对amount进行聚合，年份分组，月份排序
FROM
(
SELECT
LEFT(PERIOD_ID,4) AS YEAR,
LEFT(PERIOD_ID,6) AS YEARMONTH,
SUM(AMOUNT) AS AMOUNT
FROM HAODONG.RECEIVED_PAYMENTS
GROUP BY
LEFT(PERIOD_ID,4) ,
LEFT(PERIOD_ID,6) 
ORDER BY 
LEFT(PERIOD_ID,4) ,
LEFT(PERIOD_ID,6) 
)

SELECT
YEAR,
YEARMONTH,
LAG(YEARMONTH,1,0) OVER(PARTITION BY YEAR ORDER BY  YEARMONTH) AS Z_DATA
--窗口函数，对日期进行偏离
FROM
(
SELECT
LEFT(PERIOD_ID,4) AS YEAR,
LEFT(PERIOD_ID,6) AS YEARMONTH,
SUM(AMOUNT) AS AMOUNT
FROM HAODONG.RECEIVED_PAYMENTS
GROUP BY
LEFT(PERIOD_ID,4) ,
LEFT(PERIOD_ID,6) 
ORDER BY 
LEFT(PERIOD_ID,4) ,
LEFT(PERIOD_ID,6) 
)

逻辑bug

2019/7/15 更新
如果按照现在的代码，进行日期上面的选择，那么它就会从选择的日期开始YTD计算

会得出这样的结果，所以在逻辑上面，还是不对的，需要对代码进行优化，我们需要的是选择某一个日期，会得出这个日期的YTD值，而不是从这个日期进行YTD的运算。
我们可以得到最终的数据，所以我们将数据存储到表中，在下一步进行日期的选择操作，通过筛选，将数据进行输出

/******* Begin Procedure Script ************/ 
 BEGIN 
 
 --取消这里的日期判断
 LT_1=
SELECT
AMOUNT,
LEFT(PERIOD_ID,4) YEAR,
CASE WHEN RIGHT(PERIOD_ID,2) >25 AND MONTH(PERIOD_ID) <12 THEN LEFT((TO_DATS(ADD_MONTHS(PERIOD_ID,1))),6) ELSE LEFT(PERIOD_ID,6) END YEARMONTH,
PERIOD_ID
FROM
HAODONG.RECEIVED_PAYMENTS
;
--将已经计算的YTD数据存储在LT_2表中
LT_2 = 
SELECT DISTINCT
YEAR(YEARMONTH) YEAR,
MONTH(YEARMONTH) MONTH,
SUM(AMOUNT) OVER(PARTITION BY YEAR(YEARMONTH) ORDER BY MONTH(YEARMONTH)) AS AMOUNT
FROM :LT_1
;

--通过组合日期，然后和输入的日期进行比较，筛选数据
VAR_OUT = 
SELECT 
YEAR,
MONTH,
AMOUNT
FROM :LT_2
WHERE TO_DATE(CONCAT(YEAR,MONTH),'YYYYMM') BETWEEN :STADATE AND :ENDDATE
;
END /********* End Procedure Script ************/