Oracle的 MODEL 查询

Oracle的 MODEL 查询

注： 以下操作仅限于 Oracle 10g (含) 以上版本可用。

 

首先先造测试表/测试数据

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