Oracle 10g 完全参考手册之DECODE实现交叉报表--数据行转换为报表的列

本文主要摘自并翻译于《Oracle Database 10g完全参考手册》英文版，并根据情况补充11g的内容，和自己的例子

（注：这里使用查询出来的数据结果是把源码中的数据建到本地使用，因为直接把书本中的查询出来的结果复制出来后格式乱，对不齐，原来格式请看书本）

 

The DECODEfunction is without doubt one of themost powerful in Oracle’s SQL. It is one of several extensions Oracle added to thestandard SQL language. This chapter will explore a number of ways thatDECODE can be used, including the generationof “crosstab” reports. You can also use theCASE function and theCOALESCE function to execute complex logicaltests within your SQL statements.

11g中补充：DECODE和 CASE常常用来旋转数据----也就是说将数据行转换为报表的列。

Here is DECODE’s format:

DECODE(value,if1,then1,if2,then2,if3,then3,.. . ,else)

 

Here, value represents any column in a table (regardless of datatype) orany result of a computation,

such as one date minus another, a SUBSTR of a character column, one numbertimes another,

and so on. value is tested for each row. If value equals if1, then theresult of theDECODE is

then1; if value equals if2, then the result of theDECODE is then2. This continues forvirtually

as many if-then pairs as you can construct. If value equals none of theifs, then the result of the

DECODE is else. Each of the ifs and thensas well as the else also can be a column or the result

of a function or computation. You can have up to 255 elements within theparentheses.

Let’s consider the checkout history for the bookshelf, as recorded in the BOOKSHELF_CHECKOUTtable:

select *

  from BOOKSHELF_CHECKOUT;

NAME

TITLE

CHECKOUTDATE

RETURNEDDATE

JED HOPKINS

INNUMERACY

2002-1-1

2002-1-22

GERHARDT KENTGEN

WONDERFUL LIFE

2002-1-2

2002-2-2

DORAH TALBOT

EITHER/OR

2002-1-2

2002-1-10

EMILY TALBOT

ANNE OF GREEN GABLES

2002-1-2

2002-1-20

PAT LAVAY

THE SHIPPING NEWS

2002-1-2

2002-1-12

ROLAND BRANDT

THE SHIPPING NEWS

2002-1-12

2002-3-12

ROLAND BRANDT

THE DISCOVERERS

2002-1-12

2002-3-1

ROLAND BRANDT

WEST WITH THE NIGHT

2002-1-12

2002-3-1

EMILY TALBOT

MIDNIGHT MAGIC

2002-1-20

2002-2-3

EMILY TALBOT

HARRY POTTER AND THE GOBLET OF FIRE

2002-2-3

2002-2-14

PAT LAVAY

THE MISMEASURE OF MAN

2002-1-12

2002-2-12

DORAH TALBOT

POLAR EXPRESS

2002-2-1

2002-2-15

DORAH TALBOT

GOOD DOG, CARL

2002-2-1

2002-2-15

GERHARDT KENTGEN

THE MISMEASURE OF MAN

2002-2-13

2002-3-5

FRED FULLER

JOHN ADAMS

2002-2-1

2002-3-1

FRED FULLER

TRUMAN

2002-3-1

2002-3-20

JED HOPKINS

TO KILL A MOCKINGBIRD

2002-2-15

2002-3-1

DORAH TALBOT

MY LEDGER

2002-2-15

2002-3-3

GERHARDT KENTGEN

MIDNIGHT MAGIC

2002-2-5

2002-2-10

 

NOTE

Your output may contain an extra blank line due to word wrapping.

Extra blank lines are not shown here for readability.

 

As you look through the checkout list, you realize that some of the bookswere checked out for a rather long time. You can order this list by the numberof days checked out, to highlight the readers who keep books for the longesttime:

selectname

      ,TITLE

      ,RETURNEDDATE - CHECKOUTDATE DAYSOUT

  from BOOKSHELF_CHECKOUT

 orderby DAYSOUTdesc;

NAME

TITLE

DAYSOUT

ROLAND BRANDT

THE SHIPPING NEWS

59

ROLAND BRANDT

WEST WITH THE NIGHT

48

ROLAND BRANDT

THE DISCOVERERS

48

GERHARDT KENTGEN

WONDERFUL LIFE

31

PAT LAVAY

THE MISMEASURE OF MAN

31

FRED FULLER

JOHN ADAMS

28

JED HOPKINS

INNUMERACY

21

GERHARDT KENTGEN

THE MISMEASURE OF MAN

20

FRED FULLER

TRUMAN

19

EMILY TALBOT

ANNE OF GREEN GABLES

18

DORAH TALBOT

MY LEDGER

16

DORAH TALBOT

POLAR EXPRESS

14

DORAH TALBOT

GOOD DOG, CARL

14

EMILY TALBOT

MIDNIGHT MAGIC

14

JED HOPKINS

TO KILL A MOCKINGBIRD

14

EMILY TALBOT

HARRY POTTER AND THE GOBLET OF FIRE

11

PAT LAVAY

THE SHIPPING NEWS

10

DORAH TALBOT

EITHER/OR

8

GERHARDT KENTGEN

MIDNIGHT MAGIC

5

that take longer to read? Multiple variables are involved, and looking atthem in isolation may lead

to incorrect decisions. What is the average number of days out for thebooks in each category?

 

select B.CATEGORYNAME

      ,min(BC.RETURNEDDATE - BC.CHECKOUTDATE) MINOUT

      ,max(BC.RETURNEDDATE - BC.CHECKOUTDATE) MAXOUT

      ,avg(BC.RETURNEDDATE - BC.CHECKOUTDATE) AVGOUT

  from BOOKSHELF_CHECKOUT BC

      ,BOOKSHELF          B

 where BC.TITLE = B.TITLE

 groupby B.CATEGORYNAME

 orderby B.CATEGORYNAME

CATEGORYNAME

MINOUT

MAXOUT

AVGOUT

ADULTFIC

10

59

27.66666667

ADULTNF

16

48

29.11111111

ADULTREF

8

8

8

CHILDRENFIC

5

18

12

CHILDRENPIC

14

14

14

This is more useful, but it doesn’t factor in the impact of the differentpeople checking out books in these categories. To accomplish that, you coulduse an additional level of grouping, but the results will be easier to use ifyou create a crosstab report. The following listing generates a report thatshows the minimum, maximum, and average days out by person, by category. This queryuses theDECODE function to perform thecalculations. Three borrowers are used for the purposes of this example.

select B.CATEGORYNAME

      ,max(DECODE(BC.NAME,'FRED FULLER', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) MAXFF

      ,avg(DECODE(BC.NAME,'FRED FULLER', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) AVGFF

      ,max(DECODE(BC.NAME,'DORAH TALBOT', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) MAXDT

      ,avg(DECODE(BC.NAME,'DORAH TALBOT', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) AVGDT

      ,max(DECODE(BC.NAME,'GERHARDT KENTGEN', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) MAXGK

      ,avg(DECODE(BC.NAME,'GERHARDT KENTGEN', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) AVGGK

  from BOOKSHELF_CHECKOUT BC

      ,BOOKSHELF          B

 where BC.TITLE = B.TITLE

 groupby B.CATEGORYNAME

 orderby B.CATEGORYNAME;

CATEGORYNAME

MAXFF

AVGFF

MAXDT

AVGDT

MAXGK

AVGGK

ADULTFIC

      

ADULTNF

28

23.5

16

16

31

25.5

ADULTREF

  

8

8

  

CHILDRENFIC

    

5

5

CHILDRENPIC

  

14

14

  

The output now shows the borrowers across the top—Fred Fuller’s maximumcheckout time is the MaxFF column, Dorah Talbot’s is the MaxDT column, andGerhardt Kentgen’s is the MaxGK column. The output shows that the AdultNFcategory has the longest average checkout time for

each of the borrowers shown, and that in Gerhardt Kentgen’s case it issignificantly longer than the average checkout time in the other category hechecked out.

 

How was this report generated? In the query, the grouping is byCategoryName. The MaxFF column query is shown here:

（这个报表是如何生成的？在该查询中是通过CategoryName进行分组的。MaxFF列的查询如下所示：）

selectB.CategoryName,

MAX(DECODE(BC.Name, 'FRED FULLER',

BC.ReturnedDate-BC.CheckOutDate,NULL))MaxFF,

DECODE is performing an if-then check onthe data: If the BC.Name column value in a row is ‘FRED FULLER’, then calculatethe difference between the ReturnedDate and CheckOutDate;

else, return a NULL. That list of values is thenevaluated and the maximum value is returned.

A similar set of operations returns the average checkout time for FredFuller:

（DECODE正对数据执行if-then测试。如果某行的BC.NAME列的值为 FRED FULLER，则计算ReturnedDate and CheckOutDate之差；否则返回一个NULL值。然后计算该列表的值并返回最大值。返回Fred Fuller的平均借出时间的操作方法也类似）

AVG(DECODE(BC.Name,'FRED FULLER',

BC.ReturnedDate-BC.CheckOutDate,NULL))AvgFF,

 

自己补充：

一开始不是太明白为什么decode能够实现行转列，最后想起来sql的执行顺序以及中间虚拟的结果表，对理解这个还是有很大的帮助的。

这里加了一个中间过渡的结果集:

select BC.NAME

      ,B.CATEGORYNAME

      ,max(DECODE(BC.NAME,'FRED FULLER', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) MAXFF

      ,avg(DECODE(BC.NAME,'FRED FULLER', BC.RETURNEDDATE - BC.CHECKOUTDATE,null)) AVGFF

      ,max(DECODE(BC.NAME,'DORAH TALBOT', BC.RETURNEDDATE -BC.CHECKOUTDATE,null)) MAXDT

      ,avg(DECODE(BC.NAME,'DORAH TALBOT', BC.RETURNEDDATE -BC.CHECKOUTDATE,null)) AVGDT

      ,max(DECODE(BC.NAME,'GERHARDT KENTGEN', BC.RETURNEDDATE -BC.CHECKOUTDATE,null)) MAXGK

      ,avg(DECODE(BC.NAME,'GERHARDT KENTGEN', BC.RETURNEDDATE -BC.CHECKOUTDATE,null)) AVGGK

  from BOOKSHELF_CHECKOUT BC

      ,BOOKSHELF          B

 where BC.TITLE = B.TITLE

 groupby B.CATEGORYNAME

         ,BC.NAME

 orderby B.CATEGORYNAME;

 

NAME

CATEGORYNAME

MAXFF

AVGFF

MAXDT

AVGDT

MAXGK

AVGGK

JED HOPKINS

ADULTFIC

      

PAT LAVAY

ADULTFIC

      

ROLAND BRANDT

ADULTFIC

      

DORAH TALBOT

ADULTNF

  

16

16

  

FRED FULLER

ADULTNF

28

23.5

    

GERHARDT KENTGEN

ADULTNF

    

31

25.5

JED HOPKINS

ADULTNF

      

PAT LAVAY

ADULTNF

      

ROLAND BRANDT

ADULTNF

      

DORAH TALBOT

ADULTREF

  

8

8

  

EMILY TALBOT

CHILDRENFIC

      

GERHARDT KENTGEN

CHILDRENFIC

    

5

5

DORAH TALBOT

CHILDRENPIC

  

14

14

  

注：用黄色标记的是在书中没有用decode进行转换显示的

附：

SQL Select语句完整的执行顺序： 

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算，这里执行max()，avg()聚集函数；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；

8、使用order by对结果集进行排序。

SQL语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在SQL语句中，第一个被处理的子句式FROM，而不是第一出现的SELECT。SQL查询处理的步骤序号：

(8) 

1 SELECT  (9) DISTINCT (11)<TOP_specification> <select_list> 

2  (1)  FROM <left_table>  

3  (3) <join_type> JOIN<right_table> 

4  (2) ON <join_condition> 

5  (4) WHERE <where_condition> 

6  (5) GROUP BY <group_by_list> 

7  (6) WITH {CUBE | ROLLUP} 

8  (7) HAVING <having_condition> 

9 (10) ORDER BY <order_by_list>

　　以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

　　逻辑查询处理阶段简介：

　　1、 FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。

　　2、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。

　　3、 OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。

　　4、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。

　　5、 GROUP BY：按GROUPBY子句中的列列表对TV4中的行进行分组，生成TV5。

　　6、 CUTE|ROLLUP：把超组插入VT5，生成VT6。

　　7、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。

　　8、 SELECT：处理SELECT列表，产生VT8。

　　9、 DISTINCT：将重复的行从VT8中删除，产品VT9。

　　10、ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)。

    11、TOP：从VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。