Oracle常用SQL查询

1、时间转换

（1）24小时格式

SELECT TO_CHAR(SYSDATE,'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

（2）SYSDATE转换

SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
       TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
  FROM DUAL;

（3）时间截取TRUNC

SELECT TRUNC(SYSDATE, 'year'),--截取到年（本年的第一天）
       TRUNC(SYSDATE, 'q'),--截取到季度（本季度的第一天）
       TRUNC(SYSDATE, 'month'),--截取到月（本月的第一天）
       TRUNC(SYSDATE, 'day'),--截取到周（本周第一天，即上周日）
       TRUNC(SYSDATE) --截取到当天，舍去时分秒
  FROM DUAL;

SELECT TRUNC(SYSDATE, 'ww'),--按年度1月1日的第一天为每周第一天
       TRUNC(SYSDATE, 'iw'),--每周一
       TRUNC(SYSDATE, 'w')--按月份1日的第一天作为每周第一天
  FROM DUAL;

（4）业务周
业务周是上周六到本周五

SELECT trunc(sysdate, 'iw') - 2, trunc(sysdate, 'iw') + 4
  FROM DUAL;

说明：

• sysdate所在的本周一前移2天，即是本业务周开始日期
• sysdate所在的本周一后移4天，即是本业务周结束日期
  

上一个业务周

SELECT trunc(sysdate - 7, 'iw') - 2, trunc(sysdate - 7, 'iw') + 4
  FROM DUAL;

2、获取每个分组中日期最大的一条数据

业务场景：在报表开发中，只需要每个分组最近的一条数据。例如：某天的明细数据中，可能存在多条记录，只需要该天最后更新的一条记录。

思路：对分组进行排序，并编号，然后select编号为1的记录即可。

SELECT A.VC_NAME，A.F_VALUE
  FROM (SELECT T.*,
               row_number() over(partition by T.VC_NAME,T.DT_BEGIN_DATE order by T.DT_UPDATETIME desc) rn
          FROM TABLE_NAME T) A
 WHERE A.rn = 1;

3、环比计算

环比 = 2021年10月/2021年09月(同一时期内不同时间段的比较)
环比增长率 = 2021年10月/2021年09月-1

计算环比值需要用到分析函数lag over()

lag(f,m,n)  以f为目标向上m位取数，当取不到时默认为 n

SELECT T.VC_DEPT_NAME,T.DT_END_DATE,T.F_KPI_VALUE,
       lag(T.F_KPI_VALUE) over(partition by T.VC_KPI_NAME order by T.DT_END_DATE) pre,
       nvl(round((T.F_KPI_VALUE / lag(T.F_KPI_VALUE) over(partition by T.VC_KPI_NAME order by T.DT_END_DATE)-1)*100, 2), '0') hb 
  FROM KPI_MANUAL_VALUES T
 WHERE T.VC_KPI_NAME in('A收入', 'B收入', 'C收入', 'D收入')
   AND vc_stat_caliber = '合计'
   AND vc_stat_period = '月'