首先先造测试表/测试数据
SQL> CREATE TABLE SALE_REPORT (
2 SALE_DATE DATE NOT NULL ,
3 SALE_ITEM VARCHAR(2) NOT NULL ,
4 SALE_MONEY DECIMAL(10,2) NOT NULL
5 )
6 /
Table created.
SQL> DECLARE
2 v_begin_day DATE;
3 v_end_day DATE;
4 BEGIN
5 v_begin_day := TO_DATE('2009-01-01', 'YYYY-MM-DD');
6 v_end_day := TO_DATE('2010-01-01', 'YYYY-MM-DD');
7
8 WHILE v_begin_day < v_end_day LOOP
9 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'A', TO_NUMBER( TO_CHAR(v_begin_day, 'YYYY') ));
10 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'B', TO_NUMBER( TO_CHAR(v_begin_day, 'MM') ));
11 INSERT INTO SALE_REPORT VALUES(v_begin_day, 'C', TO_NUMBER( TO_CHAR(v_begin_day, 'DD') ));
12 v_begin_day := v_begin_day + 1;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
原有的 SQL，查询每个月的销售额.
With cte AS
(
SELECT
TO_CHAR(SALE_DATE, 'MM') AS month,
SUM(sale_money) AS sum_money
FROM
sale_report
GROUP BY
TO_CHAR(SALE_DATE, 'MM')
)
SELECT *
FROM cte
ORDER BY month;
MO SUM_MONEY
-- ----------
01 62806
02 56714
03 62868
04 60855
05 62930
06 60915
07 62992
08 63023
09 61005
10 63085
11 61065
MO SUM_MONEY
-- ----------
12 63147
已选择12行。
这里使用了 CTE， 如果对CTE不了解的话，也可以无视，就简单的认为本次的测试表只有12条记录就可以了。
使用 MODEL 语句，实现类似 UNION ALL 操作的处理。
-- 在原有SQL的 每个月的销售额 的基础上，进一步追加 每个季度 与 全年的销售额。
With cte AS
(
SELECT
TO_CHAR(SALE_DATE, 'MM') AS month,
SUM(sale_money) AS sum_money
FROM
sale_report
GROUP BY
TO_CHAR(SALE_DATE, 'MM')
)
SELECT *
FROM cte
MODEL
DIMENSION BY (month) -- 按照 月 为 维度
MEASURES(sum_money) -- 数组的数据为 sum_money
RULES(
sum_money['Q1'] = sum_money['01']+sum_money['02']+sum_money['03'],
sum_money['Q2'] = sum_money['04']+sum_money['05']+sum_money['06'],
sum_money['Q3'] = sum_money['07']+sum_money['08']+sum_money['09'],
sum_money['Q4'] = sum_money['10']+sum_money['11']+sum_money['12'],
sum_money['YY'] = sum_money['Q1']+sum_money['Q2']+sum_money['Q3']+sum_money['Q4']
)
ORDER BY
month;
MO SUM_MONEY
-- ----------
01 62806
02 56714
03 62868
04 60855
05 62930
06 60915
07 62992
08 63023
09 61005
10 63085
11 61065
MO SUM_MONEY
-- ----------
12 63147
Q1 182388
Q2 184700
Q3 187020
Q4 187297
YY 741405
已选择17行。
先把上面的 MODEL 的部分说明一下：
MODEL 关键字 必须
DIMENSION BY 维度 必须
MEASURES 指定作为数组的列 必须
RULES 对数组进行各种操作的描述 可选
-- 上一个SQL, 用 + 用的太多了。
-- 这里用 SUM()[BETWEEN和AND] 返回特定范围内的数据单元
-- 这里用 SUM()[ IN ] 返回特定范围内的数据单元
With cte AS
(
SELECT
TO_CHAR(SALE_DATE, 'MM') AS month,
SUM(sale_money) AS sum_money
FROM
sale_report
GROUP BY
TO_CHAR(SALE_DATE, 'MM')
)
SELECT *
FROM cte
MODEL
DIMENSION BY(month) -- 按照 月 为 维度
MEASURES(sum_money) -- 数组的数据为 sum_money
(
sum_money['Q1'] = SUM(sum_money)[month BETWEEN '01' AND'03'],
sum_money['Q2'] = SUM(sum_money)[month BETWEEN '04' AND'06'],
sum_money['Q3'] = SUM(sum_money)[month BETWEEN '07' AND'09'],
sum_money['Q4'] = SUM(sum_money)[month BETWEEN '10' AND'12'],
sum_money['YY'] = SUM(sum_money)[month IN ('Q1', 'Q2', 'Q3', 'Q4')]
)
ORDER BY
month;
结果同上，就不重复复制粘贴了。
增加一列数组的数据，通过 CURRENTV() 读取相邻数据的处理
-- 本查询用于在 每一行上，增加一列（上月销售合计）
-- 这里与前面不同点有以下几点：
-- 1.CTE 当中，增加了一列 0 AS prev_sum_money
-- 2.月的处理，由 TO_CHAR(SALE_DATE, 'MM') 变为 EXTRACT(MONTH FROM SALE_DATE)
-- 3.在于 MEASURES 中有 2 列数据.也就是可以有2个数组，分别为 sum_money 与 prev_sum_money
-- 4.使用了 FOR 语句，遍历数组从2月到12月
-- 5.使用了 CURRENTV() 函数，取得当前数组索引，然后用来-1。从而获取 上月销售合计.
With cte AS
(
SELECT
EXTRACT(MONTH FROM SALE_DATE) AS month,
SUM(sale_money) AS sum_money,
0 AS prev_sum_money
FROM
sale_report
GROUP BY
EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
*
FROM cte
MODEL
DIMENSION BY(month) -- 按照 月 为 维度
MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
(
prev_sum_money[FOR month FROM 2 TO 12 INCREMENT 1]
= sum_money[CURRENTV() - 1]
)
ORDER BY
month;
MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- --------------
1 62806 0
2 56714 62806
3 62868 56714
4 60855 62868
5 62930 60855
6 60915 62930
7 62992 60915
8 63023 62992
9 61005 63023
10 63085 61005
11 61065 63085
MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- --------------
12 63147 61065
已选择12行。
增加维度的处理
-- 在前面的基础上，增加年的字段.
-- 然后再维度中，增加 年的 维度.
With cte AS
(
SELECT
EXTRACT(YEAR FROM SALE_DATE) AS year,
EXTRACT(MONTH FROM SALE_DATE) AS month,
SUM(sale_money) AS sum_money,
0 AS prev_sum_money
FROM
sale_report
GROUP BY
EXTRACT(YEAR FROM SALE_DATE),
EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
*
FROM cte
MODEL
DIMENSION BY (year, month) -- 按照 年，月 为 维度
MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
(
prev_sum_money[2009, FOR month FROM 2 TO 12 INCREMENT 1]
= sum_money[CURRENTV(), CURRENTV() - 1]
)
ORDER BY
month;
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2009 1 62806 0
2009 2 56714 62806
2009 3 62868 56714
2009 4 60855 62868
2009 5 62930 60855
2009 6 60915 62930
2009 7 62992 60915
2009 8 63023 62992
2009 9 61005 63023
2009 10 63085 61005
2009 11 61065 63085
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2009 12 63147 61065
已选择12行。
IS PRESENT / PRESENTV() / PRESENTNNV()
-- 在前面的基础上，增加2010年1月的 上月 数据 （数据库中无2010年数据）
-- 这里的 IS PRESENT ：当数据单元指定的记录在MODEL子句执行之前存在，则IS PRESENT返回TRUE。
-- PRESENTV() 如果cell引用的记录在MODEL子句执行以前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。
-- PRESENTNNV() 如果cell引用的单元在MODEL子句执行之前已经存在，并且该单元的值不为空，则PRESENTNNV(cell,expr1,expr2)返回表达式expr1。如果记录不存在，或单元值为空值，则返回表达式expr2。
With cte AS
(
SELECT
EXTRACT(YEAR FROM SALE_DATE) AS year,
EXTRACT(MONTH FROM SALE_DATE) AS month,
SUM(sale_money) AS sum_money,
0 AS prev_sum_money
FROM
sale_report
GROUP BY
EXTRACT(YEAR FROM SALE_DATE),
EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
*
FROM cte
MODEL
DIMENSION BY(year, month) -- 按照 年，月 为 维度
MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
(
prev_sum_money[2009, FOR month FROM 2 TO 12 INCREMENT 1]
= sum_money[CURRENTV(), CURRENTV() - 1],
prev_sum_money[2010, 1] = sum_money[2009, 12],
prev_sum_money[2010, FOR month FROM 2 TO 3 INCREMENT 1]
= CASE WHEN sum_money[CURRENTV(), CURRENTV() - 1] IS PRESENTTHEN
sum_money[CURRENTV(), CURRENTV() - 1]
ELSE
0
END,
prev_sum_money[2010, FOR month FROM 4 TO 6 INCREMENT 1]
= PRESENTV(sum_money[CURRENTV(), CURRENTV() - 1],
sum_money[CURRENTV(), CURRENTV() - 1],
0),
prev_sum_money[2010, FOR month FROM 7 TO 9 INCREMENT 1]
= PRESENTNNV(sum_money[CURRENTV(), CURRENTV() - 1],
sum_money[CURRENTV(), CURRENTV() - 1],
0),
prev_sum_money[2010, FOR month FROM 10 TO 12 INCREMENT 1]
= sum_money[CURRENTV(), CURRENTV() - 1]
)
ORDER BY
year,
month;
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2009 1 62806 0
2009 2 56714 62806
2009 3 62868 56714
2009 4 60855 62868
2009 5 62930 60855
2009 6 60915 62930
2009 7 62992 60915
2009 8 63023 62992
2009 9 61005 63023
2009 10 63085 61005
2009 11 61065 63085
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2009 12 63147 61065
2010 1 63147
2010 2 0
2010 3 0
2010 4 0
2010 5 0
2010 6 0
2010 7 0
2010 8 0
2010 9 0
2010 10
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2010 11
2010 12
已选择24行。
测试插入几条 2010年的数据，用于验证结果.
INSERT INTO sale_report
SELECT TO_DATE('20100201', 'YYYYMMDD'), 'A', 20 FROM dual UNION ALL
SELECT TO_DATE('20100501', 'YYYYMMDD'), 'A', 50 FROM dual UNION ALL
SELECT TO_DATE('20100801', 'YYYYMMDD'), 'A', 80 FROM dual UNION ALL
SELECT TO_DATE('20101101', 'YYYYMMDD'), 'A', 110 FROM dual;
再次执行的结果：
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2009 1 62806 0
2009年数据相同的，忽略...
2009 12 63147 61065
2010 1 63147
2010 2 20 0
2010 3 20
2010 4 0
2010 5 50 0
2010 6 50
2010 7 0
2010 8 80 0
2010 9 80
2010 10
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2010 11 110
2010 12 110
已选择24行。
IGNORE NAV
-- 本例子用于演示 IGNORE NAV
-- IGNORE NAV的返回值如下：
-- 空值或缺失数字值时返回0。
-- 空值或缺失字符串值时返回空字符串。
-- 空值或缺失日期值时返回01-JAN-2000。
-- 其他所有数据库类型时返回空值。
-- KEEP NAV对空值或缺失数字值返回空值。注意默认条件下使用KEEP NAV。
With cte AS
(
SELECT
EXTRACT(YEAR FROM SALE_DATE) AS year,
EXTRACT(MONTH FROM SALE_DATE) AS month,
SUM(sale_money) AS sum_money,
0 AS prev_sum_money
FROM
sale_report
WHERE
EXTRACT(YEAR FROM SALE_DATE) = 2010
GROUP BY
EXTRACT(YEAR FROM SALE_DATE),
EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
*
FROM cte
MODEL IGNORE NAV
DIMENSION BY (year, month) -- 按照 年，月 为 维度
MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
(
prev_sum_money[2010, FOR month FROM 2 TO 12 INCREMENT 1]
= sum_money[CURRENTV(), CURRENTV() - 1]
)
ORDER BY
year,
month;
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2010 2 20 0
2010 3 20
2010 4 0
2010 5 50 0
2010 6 50
2010 7 0
2010 8 80 0
2010 9 80
2010 10 0
2010 11 110 0
2010 12 110
已选择11行。
RULES UPDATE
-- 本例子用于演示 更新已有的单元
-- 默认情况下，如果表达式左端的引用单元存在，则更新该单元。
-- 如果该单元不存在，就在数组中创建一条新的记录。
-- 可以用RULES UPDATE改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。
With cte AS
(
SELECT
EXTRACT(YEAR FROM SALE_DATE) AS year,
EXTRACT(MONTH FROM SALE_DATE) AS month,
SUM(sale_money) AS sum_money,
0 AS prev_sum_money
FROM
sale_report
WHERE
EXTRACT(YEAR FROM SALE_DATE) = 2010
GROUP BY
EXTRACT(YEAR FROM SALE_DATE),
EXTRACT(MONTH FROM SALE_DATE)
)
SELECT
*
FROM cte
MODEL
DIMENSION BY (year, month) -- 按照 年，月 为 维度
MEASURES(sum_money, prev_sum_money) -- 数组的数据为 sum_money, prev_sum_money
RULES UPDATE
(
prev_sum_money[2010, FOR month FROM 2 TO 12 INCREMENT 1]
= sum_money[CURRENTV(), CURRENTV() - 1]
)
ORDER BY
year,
month;
YEAR MONTH SUM_MONEY PREV_SUM_MONEY
---------- ---------- ---------- --------------
2010 2 20
2010 5 50
2010 8 80
2010 11 110
