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）：

 IDVALUE1aaa高2bbb低3aaa低4aaa高5bbb低6bbb高

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

 高低aaa21bbb12
写法：

<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 ttwhere value='高'group by idunion allselect id,0 高,count(1) 低from ttwhere 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业务13450
结果：
日期业务名指标名值20140812业务1指标13420140812业务1指标250

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

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

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

<span style="color:#33cc00;BACKGROUND-COLOR: #000000">select 日期,       业务名,       列_1,       列_2 from tpivot (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）