ORACLE的SQL练习---6. 利用oracle日期函数制作日期维度表

oracle常用的日期函数：

sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期

【示例】select sysdate  from dual;
返回：2020/11/26 10:04:37

add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期

【示例】select sysdate,add_months(sysdate,3)  hz from dual;
返回： 	2020/11/26 10:05:25	2021/2/26 10:05:25

last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。
【参数】：d1，日期型
【返回】：日期

【示例】select sysdate,last_day(sysdate)  hz from dual;
返回： 2020/11/26 10:06:27	2020/11/30 10:06:27

months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型

【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数

【示例】

select sysdate,
months_between(sysdate,to_date('2020-01-01','YYYY-MM-DD')),
months_between(sysdate,to_date('2022-01-01','YYYY-MM-DD')) from dual;

返回： 2020/11/26 10:07:43	10.8200653375149	-13.1799346624851

round(d1[,c1])
【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)
【返回】：日期

【示例】

select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期, 
round(sysdate,'year') 最近年初日期 from dual;

trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【返回】：日期

【示例】

select sysdate 当时日期,
trunc(sysdate) 今天日期,
trunc(sysdate,'day') 本周星期日,
trunc(sysdate,'month') 本月初,
trunc(sysdate,'q') 本季初日期, 
trunc(sysdate,'year') 本年初日期 from dual;

利用ORACLE日期函数构建数仓的日期维度表：

我们在实际的工作中经常要做很多日期的处理，比如做同期、上期、上月、上季度等等的数据对比。每次取数都要加一堆的函数处理很繁琐。把经常用到的这些信息做一张日期维度，就可以取出对应的日期。
例如下表从日、昨日、周、旬、月、季度、半年、年的各种日期粒度来设计：


具体语句如下：

--create table t_dwd_date as 
SELECT to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMMDD')) day_id,---日期ID
       TRUNC(sd + rn) day_date,--日期格式
       to_char(sd + rn, 'YYYY-MM-DD') DAY_STR,--10位字符串格式
       f_GetLunar(sd + rn) as nl,--农历（自定义函数）
       to_char(sd + rn,'day') week_cn,--星期
       to_char(sd + rn,'day','NLS_DATE_LANGUAGE=AMERICAN') week_en ,--星期英文格式
       case when to_char(sd + rn,'day','NLS_DATE_LANGUAGE=AMERICAN') in ('saturday ','sunday') then '1' else '0' end as if_weekend,---周末的判断
       to_number(TO_CHAR(sd + rn, 'DD')) DAY_OF_MONTH,--本月的第几天
       to_number(TO_CHAR(sd + rn, 'DDD')) DAY_OF_YEAR,---本年的第几天
       to_number(TO_CHAR(TRUNC(sd + rn - 1), 'YYYYMMDD')) pre_day_id,---昨日
       to_number(TO_CHAR(add_months(sd + rn, -1), 'YYYYMMDD')) LM_DAY_ID,--上月同期
       to_number(TO_CHAR(add_months(sd + rn, -3), 'YYYYMMDD')) LQ_DAY_ID,--上季度同期
       to_number(TO_CHAR(add_months(sd + rn, -12), 'YYYYMMDD')) LY_DAY_ID,--上年同期
       to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'MM')), -1), 'YYYYMMDD')) LM_START_DAY_ID,--上月第一天
       to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'Q')), -3), 'YYYYMMDD')) LQ_START_DAY_ID,--上季度第一天
       to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'YEAR')), -12), 'YYYYMMDD')) LY_START_DAY_ID,--上年第一天
       to_number(TO_CHAR((TRUNC(sd + rn, 'MM')), 'YYYYMMDD')) CM_START_DAY_ID,--当月第一天
       to_number(TO_CHAR((TRUNC(sd + rn, 'Q')), 'YYYYMMDD')) CQ_START_DAY_ID,--当季度第一天
       to_number(TO_CHAR((TRUNC(sd + rn, 'YEAR')), 'YYYYMMDD')) CY_START_DAY_ID, --本年第一天
       decode(TO_number(TO_CHAR(sd + rn, 'DD')),
              31,
              3,
              trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) TENDAY_ID,--旬
       TRUNC(sd + rn, 'MM') +
       decode(TO_number(TO_CHAR(sd + rn, 'DD')),
              31,
              20,
              trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10) TENDAY_START_DATE,--本旬第一天
       decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
                     31,
                     3,
                     trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
              3,
              last_day(sd + rn),
              TRUNC(sd + rn, 'MM') + 9 +
              decode(TO_number(TO_CHAR(sd + rn, 'DD')),
                     31,
                     20,
                     trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10)) TENDAY_END_DATE,--本旬最后一天
       TO_CHAR(sd + rn, 'YYYY-MM') ||
       decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
                     31,
                     3,
                     trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
              1,
              '上旬',
              2,
              '中旬',
              3,
              '下旬') TENDAY_CN_DESC,---旬  中文描述
       decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
                     31,
                     3,
                     trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
              1,
              '1st',
              2,