SQL工作常用函数(Oracle/PLSQL)

SQL工作常用函数(Oracle/PLSQL)

--整体代码示例
SELECT * FROM(
SELECT airline,
       substr(date,1,4) 年,
       case when date between '2018-01-01' and  '2018-08-31'  then date
         else to_char(to_date(date,'yyyy-mm-dd')+364,'yyyy-mm-dd') end 日期,
       decode(新航班号,null,flight_no,新航班号) 航班号,
       sum(ticket_income_cki + account_fuel) / nullif(sum(order_seat * standard_price), 0) 折扣,
       sum(order_seat * segment_space) / nullif(sum(supply_seat * segment_space), 0) 客座, 
FROM   database0 t
WHERE  (dep_date between '2018-01-01' and '2018-08-31'
        or dep_date between '2017-01-01' and '2017-08-31')
       and length(trim(translate(flight_no,'0123456789',' '))) is null  
GROUP BY airline,
         substr(flight_date,1,4),
         case when date between '2018-01-01' and  '2018-08-31'  then date
              else to_char(to_date(date,'yyyy-mm-dd')+364,'yyyy-mm-dd') end,
                                                           --注意这里没有'日期'
         decode(新航班号,null,flight_no,新航班号))
         PIVOT(sum(折扣) 折扣, sum(客座) 客座 for 年 in ('2017', '2018'))

1. substr(variable, start_num, num_chars)

   substr相当于excel里的MID函数,substr的语句必须要在group by中分组(有使用到聚集函数如sum时)

   substr(date,1,4) 年 --获取年份,date格式为20181105
   substr(flight_no,-1) --相当于excel的RIGHT函数,截取最右边的n个字符
   

2. decode(variable, value_A, ‘A’, value_B, ‘B’, ‘Other’)

   重编码函数,当variable=a时,改为b(功能较弱,只能用于判断等于某值时修改,如要对于在某个范围时重编码,使用case when),decode的语句必须要在group by中分组

   decode(sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) --将不同的价格重编码为不同等级,其他改为'Other'
   decode(value,1000,'D',2000,'C',3000,'B',4000,'A', value) --只修改几个值,其他保留为value本身不修改
   

3. case…when…end

   case when 语句一般用于重新编码生成新变量,case when的语句必须要在group by中分组

   case when date between '2018-01-01' and  '2018-08-31'  then '今年'
        when date between '2017-01-01' and  '2017-08-31'  then '去年'
        else '前年' end 年份 --将日期重编码生成年份变量
   
   case when date between '2018-01-01' and  '2018-08-31'  then date
        else to_char(to_date(date,'yyyy-mm-dd')+364,'yyyy-mm-dd') end 日期
        --将去年的日期按星期对应改为今年的日期,今年的日期保持不变
   

4. length/trim/translate

   length/trim/translate都是处理字符串的函数

   length() --计算长度

   trim() --剔除空格

   translate --将某字符翻译为某字符

   length(trim(translate(flight_no,'0123456789',' '))) is null
   --flight_no字段有的含有字母,有的为纯数字,此语句将含有字母的行剔除
   

5. nullif(expression1 , expression2)

   nullif用于判断expression1和expression2是否相等,相等时返回expression,否则返回null

   sum(order_seat * segment_space) / nullif(sum(supply_seat * segment_space), 0) 客座 
   --判断除数是否为0,为0时替换为null,避免报错
   

6. to_char(to_date())

   日期和字符串格式的转换

   to_char(to_date(date,'yyyy-mm-dd')+364,'yyyy-mm-dd')
   --此处的date原字段类型为vachar2,先用to_date函数转为日期格式,即可用+364来调整日期,再用to_char函数转回字符串类型,保持字段类型不变
   

7. pivot

   pivot 为行转列函数

   SELECT * FROM (...)
       PIVOT(sum(折扣) 折扣, sum(客座) 客座 for 年 in ('2017', '2018'))
   --将变量年转置为列,原来的列将会由'客座','折扣'变为'2017_客座','2017_折扣','2018_客座','2018_折扣'.
   --所有用到聚集函数的变量都要写到pivot函数里,可重命名变量