Oracle/Pgsql生成自然序列
作者:手心儿有糖
笔记日期:2022年7月1日
一、oracle生成自然序列
- 关键字: CONNECT BY
demo:
SELECT TO_DATE(SUBSTR('${end_date}', 1, 4) - ${v_step} || '0101', 'YYYYMMDD') + LEVEL - 1 AS CALENDAR_DATE,
TO_CHAR(TO_DATE(SUBSTR('${end_date}', 1, 4) - ${v_step} || '0101','YYYYMMDD') + LEVEL - 1,'YYYYMMDD') AS CALENDAR_DT
FROM DUAL
CONNECT BY LEVEL <=
TO_DATE(SUBSTR('${end_date}', 1, 4) + 1 || '1231',
'YYYYMMDD') -
TO_DATE(SUBSTR('${end_date}', 1, 4) - ${v_step} || '0101',
'YYYYMMDD') + 1
解析:设置了2个变量,
e
n
d
d
a
t
e
代表日期,
{end_date}代表日期,
enddate代表日期,{v_step}代表步长。通过输入指定日期和步长,即可得到所需自然日序列。
demo2:
SELECT TO_DATE('20170101','YYYYMMDD') + LEVEL - 1 AS CALENDAR_DATE,
TO_CHAR(TO_DATE('20170101','YYYYMMDD') + LEVEL - 1,
'YYYYMMDD') AS CALENDAR_DT
FROM DUAL
CONNECT BY LEVEL <=TO_DATE('20170202','YYYYMMDD') -
TO_DATE('20170101','YYYYMMDD') + 1
二、Pgsql生成自然序列
- 关键字:generate_series
demo1:
select
generate_series((cast(SUBSTR(${end_date},1,4) as numeric) - ${v_step} || '0101')::date,(cast(SUBSTR(${end_date},1,4) as numeric) + 1 ||'1231')::date , '1 days')::date as NATD_DT_TYPE,
to_char(generate_series((cast(SUBSTR(${end_date},1,4) as numeric) - ${v_step} || '0101')::date,(cast(SUBSTR(${end_date},1,4) as numeric) + 1 ||'1231')::date , '1 days')::date, 'yyyymmdd')as NATD_VAL_TYPE
解析:设置了2个变量, e n d d a t e 代表日期, {end_date}代表日期, enddate代表日期,{v_step}代表步长。通过输入指定日期和步长,即可得到所需自然日序列。
demo2:
select generate_series('20200101'::date,'20200221'::date,'1 days') as tdate
generate_series的使用方法:generate_series(日期1,日期2,时间粒度),时间粒度如,‘1 days’ ,‘1 weeks’,‘1 months’,‘1 years’