ORACLE中一个简单的UNPIVOT例子

最新推荐文章于 2024-09-10 10:14:19 发布

原创最新推荐文章于 2024-09-10 10:14:19 发布 · 1.8w 阅读

17 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了Oracle 11g中UNPIVOT功能的使用，通过一个学生考试成绩的例子展示了如何将列转换为行以进行数据分析。利用UNPIVOT，可以方便地找到学生某学期得分最低的科目，并讨论了UNPIVOTClause的组成部分，包括INCLUDE/EXCLUDE NULLS选项、指定输出列名等。此外，还给出了一个将低于80分的成绩加12分的场景应用。

【Oracle 11g版本】

之前写过一个很简单的PIVOT的例子，这次补充一个UNPIVOT的例子，其实这个功能在日常应用中还是比较有用，以一个最简单的例子来说，数据库中有某个学生一学期的考试科目成绩，如下语句

SELECT '罗飞' STU_NAME,
       '2001-2002' TERM,
       '90' 微积分,
       '88' 线性代数,
       '85' 数据结构,
       '70' 操作系统
  FROM DUAL

执行之后是如下结果：

现在需要找出这个学生2001到2002学期考试成绩得分最低的科目，因为传统的关系数据库是按照行存储，而MAX()和MIN()仅仅对列生效，所以，必然要进行一个行列转换，否则，只能通过写过程的方式来实现，还好，Oracle 11g开始提供了这个函数，UNPIVOT（），稍微调整一下代码如下：

SELECT COURSE_MARK
  FROM (SELECT '罗飞' STU_NAME,
               '2001-2002' TERM,
               '90' 微积分,
               '88' 线性代数,
               '85' 数据结构,
               '70' 操作系统
          FROM DUAL) UNPIVOT(COURSE_MARK FOR TYPE IN(
                                                     微积分,
                                                     线性代数,
                                                     数据结构,
                                                     操作系统))

执行结果如下：

好了，剩下的事情就留给MAX()或者MIN()去完成，很方便

关于UNPIVOT，官方有如下的说法：

The unpivot_clause rotatescolumns into rows.

（1）The INCLUDE | EXCLUDE NULLS clausegives you the option of including or excluding null-valued rows. INCLUDE NULLS causesthe unpivot operation to include null-valued rows; EXCLUDE NULLS eliminatesnull-values rows from the return set. If you omit this clause, then the unpivotoperation excludes nulls.

（2）unpivot_clause: this clause specifies a name for a column to represent the unpivotedmeasure values.

（3）Inthe pivot_for_clause, specify a name for eachoutput column that will hold descriptor values, such as quarter or product.

（4）Inthe unpivot_in_clause, specify the input datacolumns whose names will become values in the output columns of the pivot_for_clause.These input data columns have names specifying a category value, such as Q1,Q2, Q3, Q4. The optional AS clause lets you map the input data columnnames to the specified literal values in the output columns.

剩下的，对上面的例子再改造一下，这个学生可能享受到了某种加分政策，于是，把所有低于80的成绩统一加12分，12分，听上去好豪华，可惜我从来没有过，语句如下：

SELECT CASE
         WHEN COURSE_MARK < 80 THEN
          COURSE_MARK + 12
         WHEN COURSE_MARK > 80 THEN
          TO_NUMBER(COURSE_MARK)
       END COURSE_MARK
  FROM (SELECT '罗飞' STU_NAME,
               '2001-2002' TERM,
               '90' 微积分,
               '88' 线性代数,
               '85' 数据结构,
               '70' 操作系统
          FROM DUAL) UNPIVOT(COURSE_MARK FOR TYPE IN(
                                                     
                                                     微积分,
                                                     线性代数,
                                                     数据结构,
                                                     操作系统))

其实，例子都很简单，原始语句也很简单，只要发挥想象力，会有很多的结果：）