2个星了,散分,发点东西大家探讨下, 写好SQL(2)

呵呵,用了6周升级到2星,估计今年最后次散分了,发上次散分的下半部分,满100个人结贴
===================================================
三、 对日期的操作
在SQL　Server 里的日期数据，我们经常可以用 字段<=’2008-5-20’这样的表达式，但在oracle却不可以，因为数据类型不一样 字段是date型，’2008-5-20’是字符型，需要用to_char 或者to_number转换成相同的类型才能进行比较。
刚开始这让我觉得不习惯，觉得不好，不过时间长了，觉得也有不少便利之处，尤其是配合trunc啊，to_char之类的，下面简单写一下常用的一些对日期的操作
SELECT SYSDATE FROM DUAL 取当前系统时间
Select trunc(sysdate) from dual 取当前日期
Select trunc(sysdate,’MM’) from dual 取当前月的第一天
　　 Select trunc(sysdate,’YYYY’) from dual取当年的元旦
Select to_char(sysdate,’ss’) from dual取当前时间秒部分
Select to_char(sysdate,’mi’) from dual取当前时间分钟部分
Select to_char(sysdate,’HH24’) from dual取当前时间秒小时部分
Select to_char(sysdate,’DD’) from dual取当前时间日期部分
Select to_char(sysdate,’MM’) from dual取当前时间月部分
Select to_char(sysdate,’YYYY’) from dual取当前时间年部分
Select to_char(sysdate,’w’) from dual取当前时间是一个月中的第几周(从1日开始算)
Select to_char(sysdate,’ww’) from dual取当前时间是一年中的第几周（从1.1开始算）
Select to_char(sysdate,’iw’) from dual取当前时间是一年中的第几周（按实际日历的）
Select to_char(sysdate,’d’) from dual取当前时间是一周的第几天，从星期天开始，周六结束
Select to_char(sysdate,'day') from dual　取当前日是星期几，和数据库设置的字符集有关，会输出’Tuesday’
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天

Select Add_months(sysdate,12) from dual 取一年后的今天

Select sysdate-(sysdate-100) from dual 取两个日期之间的天数
Select (sysdate-(sysdate-100))*1440 from dual 取两个日期之间的分钟数
Select (sysdate-(sysdate-100))*1440*60 from dual 取两个日期之间的秒数
Select months_between(sysdate,sysdate-100) from dual 取两个日期间隔的月份
Select last_day(sysdate) from dual 取当前月的最后天
Select next_day(sysdate,’1’) from dual 取当前日之后第一个星期天，里面的’1’表示取星期日，如果今天正好是星期日，则会显示下一个星期日

四、 DECODE和CASE…When…End
Decode是oracle提供的一个很强大的函数，使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，减少处理时间
Decdoe类似于if…then…else,基本语法:
Decode(判断条件，
等于条件一，输出结果1
等于条件二，输出结果2
…
…
[默认结果])
举例：
Decode(to_char(sysdate,’d’),
’1’,’周日’
’7’,’周六’
’工作日’)

DECODE常见用途用来输出根据特定条件输出不同的结果，
比如说，查看订单时，需要统计有效
如果不用decode，需要两条语句UNION出来才行

SELECT   dates, SUM (qty) qty, SUM (can_qty) can_qty

    FROM (SELECT   TRUNC (a.order_date) dates, SUM (syslast) qty, 0 can_qty

              FROM torderdetail a

             WHERE a.order_date >= TRUNC (SYSDATE-40) AND a.syslast >0

          GROUP BY TRUNC (a.order_date)

          UNION ALL

          SELECT   TRUNC (a.order_date) dates, 0 qty, SUM (syscancel) can_qty

              FROM torderdetail a

             WHERE a.order_date >= TRUNC (SYSDATE-40) AND a.syslast = 0

          GROUP BY TRUNC (a.order_date))

GROUP BY dates

执行计划

Plan

SELECT STATEMENT ALL_ROWS Cost: 9 Bytes: 70 Cardinality: 2 

 	9 HASH GROUP BY Cost: 9 Bytes: 70 Cardinality: 2 

 	 	8 VIEW EASYTVC_DEV. Cost: 8 Bytes: 70 Cardinality: 2 

 	 	 	7 UNION-ALL 

 	 	 	 	3 HASH GROUP BY Cost: 4 Bytes: 11 Cardinality: 1 

 	 	 	 	 	2 TABLE ACCESS BY INDEX ROWID TABLE EASYTVC_DEV.TORDERDETAIL Cost: 3 Bytes: 11 Cardinality: 1 

 	 	 	 	 	 	1 INDEX RANGE SCAN INDEX EASYTVC_DEV.IDX_TORDERDETAIL_02 Cost: 2 Cardinality: 1 

 	 	 	 	6 HASH GROUP BY Cost: 4 Bytes: 14 Cardinality: 1 

 	 	 	 	 	5 TABLE ACCESS BY INDEX ROWID TABLE EASYTVC_DEV.TORDERDETAIL Cost: 3 Bytes: 14 Cardinality: 1 

 	 	 	 	 	 	4 INDEX RANGE SCAN INDEX EASYTVC_DEV.IDX_TORDERDETAIL_02 Cost: 2 Cardinality: 1 

成本9
而用

decode

SELECT   TRUNC (order_date) dates, SUM (DECODE (syslast, 0, 0, syslast)) qty,

         SUM (DECODE (syslast, 0, syscancel, 0)) can_qty

    FROM torderdetail a

   WHERE a.order_date >= TRUNC (SYSDATE - 40)

GROUP BY TRUNC (order_date)

执行计划

Plan

SELECT STATEMENT ALL_ROWS Cost: 4 Bytes: 14 Cardinality: 1 

 	3 HASH GROUP BY Cost: 4 Bytes: 14 Cardinality: 1 

 	 	2 TABLE ACCESS BY INDEX ROWID TABLE EASYTVC_DEV.TORDERDETAIL Cost: 3 Bytes: 14 Cardinality: 1 

 	 	 	1 INDEX RANGE SCAN INDEX EASYTVC_DEV.IDX_TORDERDETAIL_02 Cost: 2 Cardinality: 1 

成本只有4

效率明显高了许多，语句也简炼

另外个最常见的用途，就是做固定列的行列转换
比如说有数据
Name class score
小明 语文 80
小强 数学 90
小明 英语 89
小强 语文 70
小明 数学 95
小强 英语 85

要求出来
姓名 语文 数学 英语
小明 80 95 89
小强 70 90 85

这时候就以用decode来做

SELECT   NAME 姓名, SUM (DECODE (CLASS, '语文', score, 0)) 语文,

         SUM (DECODE (CLASS, '数学', score, 0)) 数学,

         SUM (DECODE (CLASS, '英语', score, 0)) 英语

    FROM table

GROUP BY NAME

CASE…When…End 和Decode差不多，语法
Case
When 条件1 符合 then 结果1
When 条件2 符合 then 结果2
Else 默认结果
End
和decode 的差别在于，decode的判断条件只能是一个，并且只能做等于或者不等于判断，case 可以做>、<、>=、<=之类的判断，并且可以同时有多个判断条件。
不过case语法要比decode显得复杂，所以一般判断条件不复杂的话，用Decode显得简洁点,不过case属于标准sql,decode只有oracle提供。

五、 分析函数
Oracle提供一个强大的函数系列――分析函数，上面写到的row_number() over()就是其中之一，特征就在于over()
Over()里面三类参数
Partition by 相当于group by 后面跟相应的字段名
Order by 排序条件
Rows between 窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。
一般常用的参数就是partition by 和order by
Over() 有些时候里面可以不写参数，默认就是对于所有数据，例如sum(字段)　over()　就是对于所有纪录范围求和，而有些时候order by是必须的，如row_number() over() 就是的

分析函数的优点在于，不受语句里的group by 的限制(注,经研究,SUM,AVG等的结果还是受GROUP影响)

这里顺便说几个关于SUM的
sum(字段) 这样写,除非是求所有纪录的某字段的和,并且select只显示sum字段,否则需要和group一起用

sum(字段) over(),也是对所有纪录集范围内字段求和,不需要GROUP,可以和其它字段同行显示

比如说你可以写这样的

select a.*,sum(b) over()

      from a

sum(字段)over(partition by 分组字段)按分组字段求和,不需要group by

sum(字段1) over (order by 字段2) 按排序顺序求和到当前行

比如表a
字段1 字段2
1 1
1 2
2 3
2 4
4 5
5 6
用

select a.*

 sum(字段1) over (order by 字段2)  c

from a

出来结果
字段1 字段2 c
1 1 1
1 2 2
2 3 4
2 4 6
4 5 10
5 6 15


大家知道写group 　by时，select里的字段除聚合函数（如sum,avg,count之类）外，其余都要和group by 里对应，也不能写*
比如说上面row_number() over() 里的例子，要求知道每天最后张订单的所有信息，传统的作法就需要按订单日期每天分组，然后找最大的订单时间，然后再和原表关联查询，也就是说要对同张表，做两次扫描查询
而用row_number() over(partition by trunc(order_date) order by order_date desc)一次可以产生分组编号，最后只要取结果为1的就行了

常用的分析函数
Row_number() over 按分组按排序编号
Rank() over 按分组按排序编号，有并列的，编号相同，并跳过相同数目下几个编号
Dense_rank() over按分组按排序编号，有并列的，编号相同，不跳号
Lag(字段名，偏移量，默认值) over 按分组排序，取当前纪录字段的往上+偏移量条纪录的同一字段的值，取不到的话，显示默认值
lead(字段名，偏移量，默认值) over 基本同上，差别在于往后取纪录
first_value(字段) over(　窗口函数)　取窗口函数范围内的第一条纪录，需要写开窗函数
last_value 同上，只不过是取结果集中最后条纪录
ratio_to_report(字段) over() 字段所占 over结果集的百分比，像统计当天各地区销量的占比，传统做法要写两个语句关联，一个统计各地区的的销量和，一个统计所有销量和，然后再关联计算各地方的百分比，而用这个分析函数，一条语句就够了，如

SELECT   br_area_gb, SUM (syslast) qty,

         ratio_to_report (SUM (syslast)) OVER () ratio

    FROM torderdetail a

   WHERE syslast > 0

GROUP BY br_area_gb