Oracle的 MODEL 查询

欲忘树

　　首先先造测试表/测试数据
　　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