ORACLE SQL学习(最后更新：2014-08-12)

 

——题记：本文章记录一些常用函数的“新鲜”使用用法，或者此前未学习过的，能用于提高SQL效率的ORACLE sql语法。

 

目录：

一. count(decode)......group by 语法

二. 使用connect by 生成伪列（常用于生成一年的12个月份，一个月31日等）

三. 使用unpivot函数产生列转行（pivot进行行转列）

 

正文：

一. count(decode)......group by 语法：

该语法用于条件汇总计数，可以避免过多使用where从句和union。

示例：

表内容（tt）：

	ID	VALUE
1	aaa	高
2	bbb	低
3	aaa	低
4	aaa	高
5	bbb	低
6	bbb	高

需求结果（按id汇总，并计数各自高低的个数）：

	高	低
aaa	2	1
bbb	1	2

写法：

<span style="color:#33cc00;BACKGROUND-COLOR: #000000">select id,count(decode(value,'高',1)) 高,count(decode(value,'低',1)) 低
  from tt
 group by id</span>

当然复杂点也可以这么写，效果一致：

<span style="color:#33cc00;BACKGROUND-COLOR: #000000">select id,sum(高) 高,sum(低) 低 from (
select id,count(1) 高,0 低
from tt
where value='高'
group by id
union all
select id,0 高,count(1) 低
from tt
where value='低'
group by id
)group by id</span>

二. 使用connect by 生成伪列

说明：常用于生成一年的12个月份，一个月31日等。

由于在计算全量用户计算环比、同比的时候，需要用到某个用户全部月份的数据，如果某个月没有数据，统计的时候通常很头疼~

select '2014'||lpad(level,2,0) month from dual connect by level<=12;

    MONTH
1 201401
2 201402
3 201403
4 201404
5 201405
6 201406
7 201407
8 201408
9 201409
10 201410
11 201411
12 201412

connect by解释：

这是生成一个循环的，一般配合使用的语句是start with，用来产生层级关系的，这里不详细介绍这个语法了。

有兴趣可以看看这个文章：connect by...start with 用法

level是循环层级。

三. 使用unpivot函数产生列转行（pivot进行行转列）

列转行和行转列是老生常谈的问题了，一般就是考考你decode用得怎么样，而列转行主要就是写多一个外层的sql，把数据select..union出来之后sum起来。

这里使用for...in语句，可以避免多次select表来union，一次性产生行数据。先上语句：

<span style="BACKGROUND-COLOR: #000000"> </span><span style="color:#33ff33;BACKGROUND-COLOR: #000000">   select '20140812' as 日期, 
           app_id as 业务名, 
           lower(ind_id) as 指标名, 
           ind_values as 指标值
      from ( 
            select '业务1'as 业务名, 
                   34 as 指标1, 
                   50 as 指标2 
               from dual 
           ) 
       unpivot ( ind_values
                  for ind_id 
                   in (指标1,指标2) 
               );</span>

 

源数据（临时生成）：

业务名	指标1	指标2
业务1	34	50

结果：

日期	业务名	指标名	值
20140812	业务1	指标1	34
20140812	业务1	指标2	50

unpivot函数的使用方法可以参考文献1。

另外，如果需要行转列，也可以使用pivot函数。

假设上面的这个结果表我们保存成 t 表，那么语句就是：

<span style="color:#33cc00;BACKGROUND-COLOR: #000000">select 日期,
       业务名,
       列_1,
       列_2
 from t
pivot (sum(指标值)
       for 指标名
        in ('指标1' as 列_1,'指标2' as 列_2)
)</span>

结果大家自己YY一下了，我就不列出来了。

 

--------------------------------------------

参考文献：

1. Pivot 和 Unpivot（http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html）