Oracle中decode 以及ROW_NUMBER() OVER() 函数等其它相关函数用法

1：含义解释：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

可以这么理解注意：decode相当于：case when then else end语句

SELECT id,
DECODE(id,1,'第一个',2,'第二个','其它id') newId 
FROM   BRAIN_BAK.TEST_DW_ITEMS_DETA


select 字段1,decode(字段2,条件1,结果1,条件2,结果2,其它) from 表名

如果字段2 等于 条件一 则显示结果1.
如果字段2 等于 条件二 则显示结果2.
否则就显示其它

2：分组排序

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

例子：

SELECT RANGE_CODE,
                NEXTMONTH_PALN_VISITORS_NUM,
                NEXTMONTH_PALN_LAUNCHES_NUM,
                MONTH,
                DATA_TIME,
                CREATE_TIME,
                ROW_NUMBER() OVER (PARTITION BY RANGE_CODE,MONTH ORDER BY
 DATA_TIME DESC, CREATE_TIME DESC) RN
         FROM DS_AIR_MONTHLY_PLAN

3：如何查看同年数据如期的最新值

SELECT CONCAT(BigYear, BigMonth) 当前年数据最新值 FROM
 (SELECT MAX(年) BigYear,MAX(月) BigMonth FROM (
SELECT SUBSTR(TO_CHAR(DATA_TIME, 'yyyyMM'), 0, 4) AS 年 ,
SUBSTR(TO_CHAR(DATA_TIME, 'yyyyMM'), 5, 6) AS 月
FROM BRAIN.DWS_HR_TITLE_STATISTICS)
 GROUP BY 年)


--第二种查询结果方式
SELECT *
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ORG_NO,TO_CHAR(DATA_TIME, 'YYYY') 
ORDER BY DATA_TIME DESC, STAT_TIME DESC) rn,
               ORG_NO 机构编号,
               EMP_NUM 总人数,
               TO_CHAR(DATA_TIME, 'YYYYMM') AS 数据日期
          FROM DWS_HR_EMPLOYEE_STAT

         WHERE IS_SUM = 1) a
 where a.rn = 1


--
根据年进行分组然后根据 DATA_TIME,STAT_TIME  进行降序排序，
这时 会出现一个新的名为 rn 的列，
类似与下面那个排序函数中的新出来的那一列 ，然后取第一列为 1 的最新数据

输出结果：

排名函数 row_number()over(order by)用法

select * ,ROW_NUMBER() OVER(ORDER BY distId desc) as num from districtInfo

【查询语句说明】首先明确查询的结果为 *，num ，而 ROW_NUMBER() OVER(ORDER BY distId desc) 的作用是将查询的结果按照disId降序排列，给每条记录添加一个排名序号num（从1开始排）。

查询结果如下：

 4 ：Oracle之行转列pivot函数

语法：pivot(任一聚合函数 for 需要转列的值所在列名 in (需转为列名的值))

--行转列
select 
	*
from 
  (SELECT 
		rspfd.FORECAST_YEAR , --年份
		rspfd.INDEX_CODE ,    --指标代码
		rspfd.INDEX_VALUE     --指标值
	FROM RPT_STK_PROFIT_FORECAST_DATA rspfd  
	WHERE rspfd.FORECAST_ID = 250717)
  pivot(
    max(INDEX_VALUE) for INDEX_CODE in (      --INDEX_CODE 即要转成列的字段
        '1000001' as INDEX_O1,                --max(INDEX_VALUE) 此处必须为聚合函数，
        '1000003' as INDEX_O2,                --in () 对要转成列的每一个值指定一个列名
        '1000008' as INDEX_O3,
        '1000009' as INDEX_O4
    )
  );          

5:  使用MAX DECODE 实现 列转行函数

    SELECT 
 		T.FORECAST_YEAR,
 		max(decode(T.INDEX_CODE,'1000001',T.INDEX_VALUE,'')) AS INDEX_O1,
 		max(decode(T.INDEX_CODE,'1000003',T.INDEX_VALUE,'')) AS INDEX_O2,
 		max(decode(T.INDEX_CODE,'1000008',T.INDEX_VALUE,'')) AS INDEX_O3,
 		max(decode(T.INDEX_CODE,'1000009',T.INDEX_VALUE,'')) AS INDEX_O4
 	FROM (
	SELECT 
		rspfd.FORECAST_YEAR , --年份
		rspfd.INDEX_CODE ,    --指标代码
		rspfd.INDEX_VALUE     --指标值
	FROM RPT_STK_PROFIT_FORECAST_DATA rspfd  
	WHERE rspfd.FORECAST_ID = 111) T GROUP BY T.FORECAST_YEAR;

6：查询当前年时间，并往后推后俩年

select to_char(years) 年份 
from(select TO_CHAR(Sysdate, 'YYYY') - level + 1 years 
from dual connect by level <= 3)

 

持续更新中。。。。