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'))
-
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个字符
-
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本身不修改
-
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 日期 --将去年的日期按星期对应改为今年的日期,今年的日期保持不变
-
length/trim/translate
length/trim/translate都是处理字符串的函数
length() --计算长度
trim() --剔除空格
translate --将某字符翻译为某字符
length(trim(translate(flight_no,'0123456789',' '))) is null --flight_no字段有的含有字母,有的为纯数字,此语句将含有字母的行剔除
-
nullif(expression1 , expression2)
nullif用于判断expression1和expression2是否相等,相等时返回expression,否则返回null
sum(order_seat * segment_space) / nullif(sum(supply_seat * segment_space), 0) 客座 --判断除数是否为0,为0时替换为null,避免报错
-
to_char(to_date())
日期和字符串格式的转换
to_char(to_date(date,'yyyy-mm-dd')+364,'yyyy-mm-dd') --此处的date原字段类型为vachar2,先用to_date函数转为日期格式,即可用+364来调整日期,再用to_char函数转回字符串类型,保持字段类型不变
-
pivot
pivot 为行转列函数
SELECT * FROM (...) PIVOT(sum(折扣) 折扣, sum(客座) 客座 for 年 in ('2017', '2018')) --将变量年转置为列,原来的列将会由'客座','折扣'变为'2017_客座','2017_折扣','2018_客座','2018_折扣'. --所有用到聚集函数的变量都要写到pivot函数里,可重命名变量