SQL 使用Oracle的MODEL子句变换结果集|极客教程

SQL 使用Oracle的MODEL子句变换结果集，就像本章第一个实例一样，你希望于熟知的常规变换技巧之外另辟蹊径。你想尝试一下 Oracle 的 MODEL 子句。不同于 SQL Server 的 PIVOT 操作符，Oracle 的 MODEL 子句并不是用来做结果集变换的。说得准确一些，用 MODEL 子句做结果集变换其实算是误用，它并不符合 MODEL 子句的设计意图。

SQL 使用Oracle的MODEL子句变换结果集问题描述

就像本章第一个实例一样，你希望于熟知的常规变换技巧之外另辟蹊径。你想尝试一下 Oracle 的 MODEL 子句。不同于 SQL Server 的 PIVOT 操作符，Oracle 的 MODEL 子句并不是用来做结果集变换的。说得准确一些，用 MODEL 子句做结果集变换其实算是误用，它并不符合 MODEL 子句的设计意图。尽管如此，MODEL 子句还是为常见问题提供了一种有趣的思路。对于本例而言，你希望把下面的结果集：

select deptno, count(*) cnt
  from emp
 group by deptno
　
DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

做如下转换。

       D10        D20        D30
---------- ---------- ----------
         3          5          6

SQL 使用Oracle的MODEL子句变换结果集解决方案

和常规的变换技巧一样，要在 MODEL 子句中使用聚合和 CASE 表达式。不同之处在于我们使用数组来存储聚合运算的值，并返回结果集里的数组。

select max(d10) d10,
       max(d20) d20,
       max(d30) d30
  from (
select d10,d20,d30
  from ( select deptno, count(*) cnt from emp group by deptno )
 model
  dimension by(deptno d)
   measures(deptno, cnt d10, cnt d20, cnt d30)
   rules(
     d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
     d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,
     d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end
  )
  )

SQL 使用Oracle的MODEL子句变换结果集扩展知识

功能强大的 MODEL 子句是对 Oracle SQL 工具箱非常有益的补充。一旦开始使用 MODEL，我们一定会被它提供的各种实用的功能吸引住。比如迭代，以数组形式访问行值，向结果集 upsert1 行值的能力，以及构建参考模型的能力。虽然本实例不会用到 MODEL 子句的这些非常酷的功能，但（出于学习和研究的目的）从多种角度审视问题，并以出人意料的方式使用某些功能未尝不是一次有益的尝试。
理解本解决方案的第一步是，仔细观察 FROM 子句的内嵌视图。该内嵌视图用于统计 EMP 表中每个 DEPTNO 对应的员工总数。结果集显示如下。

select deptno, count(*) cnt
  from emp
 group by deptno
　
DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

以上结果集就是 MODEL 要处理的数据。仔细观察 MODEL 子句，可以发现它有 3 个组成部分：DIMENSION BY、MEASURES 和 RULES。先从 MEASURES 开始。
MEASURES 列表的项目就是我们为该查询声明的数组。该查询使用了 4 个数组：DEPTNO、D10、D20 和 D30。与 SELECT 列表类似，MEASURES 列表里的数组也可以指定别名。不难发现，上述 4 个数组中有 3 个实际上都来源于内嵌视图的 CNT。
如果说 MEASURES 列表包含了我们用到的数组，那么 DIMENSION BY 子句包含的项目则是数组的索引。试想一下，数组 D10 只是 CNT 的别名。再看一下上述内嵌视图的结果集，我们会发现 CNT 有 3 个值：3、5 和 6。当基于 CNT 值创建一个数组时，我们创建的是拥有 3 个元素的数组，即 3 个整数值：3、5 和 6。现在，我们该如何逐一地访问该数组的值呢？需要借助数组索引。由 DIMENSION BY 子句定义的索引值如下：10、20 和 30（来自上述结果集）。因此，以下面的表达式为例。

d10[10]

该表达式的评估结果为 3，该值就是数组 D10 中 DEPTNO 10 对应的 CNT 值（该值为 3）。
3 个数组（D10、D20、D30）都是基于 CNT 值创建的，因此它们都有相同的值。那么，如何把合适的统计值放入到正确的数组中呢？这是 RULES 子句要做的事。如果仔细观察上述内嵌视图的结果集的话，我们会看到 DEPTNO 的值分别为 10、20 和 30。RULES 子句里的 CASE 表达式只要逐一评估 DEPTNO 数组的每个值即可。

class= “第2级无序列表”>如果值为 10，为 D10[10] 存入 DEPTNO 10 对应的 CNT 值，否则存入 0。
如果值为 20，为 D20[20] 存入 DEPTNO 20 对应的 CNT 值，否则存入 0。
如果值为 30，为 D30[30] 存入 DEPTNO 30 对应的 CNT 值，否则存入 0。

如果你感到迷惑不解，请不要担心。我们接下来不妨先执行一下到目前为止讨论过的查询代码，下面是刚刚讨论过的查询代码对应的结果集。有时候读一段文字内容，然后实际执行一下对应的代码，最后再回过头去重新读一遍文字内容，这样理解起来会更容易。实际执行一下下面的代码之后，相信你就能迅速理解到目前为止我们讲过的内容。

select deptno, d10,d20,d30
  from ( select deptno, count(*) cnt from emp group by deptno )
 model
  dimension by(deptno d)
   measures(deptno, cnt d10, cnt d20, cnt d30)
   rules(
     d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
     d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,
     d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end
  )
　
DEPTNO        D10        D20        D30
------ ---------- ---------- ----------
    10          3          0          0
    20          0          5          0
    30          0          0          6

如上所示，正是 RULES 子句改变了每一个数组里的值。如果你仍然感到疑惑不解，不妨实际执行一下如下所示的查询语句，该查询注释掉了 RULES 子句里的表达式，其他部分与上述查询相同。

select deptno, d10,d20,d30
  from ( select deptno, count(*) cnt from emp group by deptno )
 model
  dimension by(deptno d)
   measures(deptno, cnt d10, cnt d20, cnt d30)
   rules(
    /*
     d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
     d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,
     d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end
    */
  )
　
DEPTNO        D10        D20        D30
------ ---------- ---------- ----------
    10          3          3          3
    20          5          5          5
    30          6          6          6

现在应该足够清楚明白了，上述 MODEL 子句的结果集和内嵌视图完全相同，只是 COUNT 函数的返回值被分别指定了别名 D10、D20 和 D30。如下所示的查询也能证明这一点。

select deptno, count(*) d10, count(*) d20, count(*) d30
  from emp
 group by deptno
　
DEPTNO        D10        D20        D30
------ ---------- ---------- ----------
    10          3          3          3
    20          5          5          5
    30          6          6          6

因此，MODEL 子句所做的事情就是取出 DEPTNO 和 CNT 的值，把它们放入数组，并确保每一个数组代表一个单独的 DEPTNO。现在，每一个数组 D10、D20 和 D30 都含有一个非零值，它们代表给定 DEPTNO 对应的 CNT。结果集变换已经完成，剩下的就是调用聚合函数 MAX 以返回单独的一行结果。（本书前几章已经多次使用 MIN 或 SUM，本实例的做法也出于同样的目的。）

select max(d10) d10,
       max(d20) d20,
       max(d30) d30
  from (
select d10,d20,d30
  from ( select deptno, count(*) cnt from emp group by deptno )
 model
  dimension by(deptno d)
   measures(deptno, cnt d10, cnt d20, cnt d30)
   rules(
    d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
    d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,
    d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end
  )
  )
　
       D10        D20        D30
---------- ---------- ----------
         3          5          6