Oracle获取当前自然周，当前周的起始和结束日期
1 2 3 4 5 6 7 | SELECT to_char(sysdate,'iw') from dual; --本周是第几个自然周SELECT to_char(sysdate,'yyyy') into v_sbzq_nf from dual; -- 当前年份SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),'IW'),'yyyy-MM-dd') FROM DUAL;--本周的起始时间（本周周一日期）SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'YYYY-MM-DD'),'IW') + 6,'yyyy-MM-dd') FROM DUAL;--本周的结束时间（本周周日日期） |
下面是使用存储过程向数据库中插入一条数据
数据格式 ： 上报周期 起始时间 结束时间
xxxx年第xx周 xx.xx xx.xx
存储过程如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | create or replace procedure PRC_T_SJSB_ZYGYPJG is v_sbzq_zs varchar2(20);--上报周期周数 v_sbzq_nf varchar2(10); --上报周期年份 v_start varchar2(10); --起始月份 v_end varchar2(10); --结束月份 v_sbzq varchar2(20); --上报周期begin SELECT to_char(sysdate,'iw') into v_sbzq_zs from dual; --本周是第几个自然周 SELECT to_char(sysdate,'yyyy') into v_sbzq_nf from dual; SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),'IW'),'yyyy-MM-dd') into v_start FROM DUAL;--本周的起始时间（本周周一日期） SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'YYYY-MM-DD'),'IW') + 6,'yyyy-MM-dd') into v_end FROM DUAL;--本周的结束时间（本周周日日期） v_start:=substr(v_start,6); v_end :=substr(v_end,6); v_start :=replace(v_start,'/','.'); v_start :=replace(v_start,'-','.'); v_end :=replace(v_end, '/','.'); v_end :=replace(v_end, '-','.'); v_sbzq :=v_sbzq_nf||'年第'||v_sbzq_zs||'周'; insert into T_SJSB_ZYGYPJG (sbzq,sbzt,startdate,enddate) values(v_sbzq,'0',v_start,v_end); commit;end PRC_T_SJSB_ZYGYPJG; |
Oracle获取自然周数
在Oracle中，Mysql中以及Hive中，SQL实现同样的功能有时候可能要遵循不同的语法结构，尤其在日期操作方面区别较为明显。接下来，将Oracle中常用的周期统计梳理出来。
1. 按天统计
Oracle中通过to_char()函数来操作日期变量，通过其中的格式参数配置输出日期的格式。
格式参数值为’yyyymmdd’时，将日期统一转换为yyyymmdd（年月日）的方式输出。
1 2 3 4 5 | select to_char(created_time,'yyyymmdd') as day,count(mobile_no)from tablewhere to_char(created_time,'yyyymmdd') >= 20181201group by to_char(created_time,'yyyymmdd')order by min(created_time) asc |
输出的结果如下所示：
2. 按自然周统计
to_char()函数的格式参数值为’iw’时，表示按自然周方式输出日期在全年中的周数排序值，自然周即日历上显示的周排列结果。
如果只输出周数，不便于排查数据，所以按自然周进行聚合时，最好能把该自然周的起始日期或结束日期显示出来，使结果一目了然。
1 2 3 4 5 | select to_char(created_time,'iw') as week, min(created_time), count(mobile_no)from tablewhere to_char(created_time,'yyyymmdd') >= 20181201group by to_char(created_time,'iw')order by min(created_time) asc |
结果如下所示，其中week表示周数。通过MIN(created_time)可以展示出每个自然周的起始日期：
3. 按月统计
to_char()函数的格式参数值为’yyyymm’时可输出格式为yyyymm（年月）的月份统计结果。
1 2 3 4 | select to_char(created_time,'yyyymm') as Month, count(mobile_no)from tablewhere to_char(created_time,'yyyymm') >= 20181201group by to_char(created_time,'yyyymm') |
其结果如下所示：
4. 按季统计
to_char()的格式参数值为’q’，可实现按季度输出统计结果。
1 2 3 4 5 | select to_char(created_time,'q') as q,count(mobile_no)from tablewhere to_char(created_time,'yyyymmdd') >= 20180101group by to_char(created_time,'q')order by min(created_time) asc |
其结果如下所示，Q表示季度。
5. 按年统计
to_char()函数的参数值为’yyyy’时可以实现按年输出统计结果。
1 2 3 4 5 | select to_char(created_time,'yyyy') as y, count(mobile_no)from tablewhere to_char(created_time,'yyyy') >= 2016group by to_char(created_time,'yyyy')order by y asc |
其输出结果如下所示：
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
