Oracle和Vertica中构造日历数据
浏览数：42 /
时间：2015年06月12日
Vertica里面构造日历用法：
SELECT to_number(TO_CHAR(ts::DATE,‘yyyymmdd‘)) as day_id,
year(ts::DATE) as year_of_calendar,
month(ts::DATE) as month_of_year,
dayofweek(ts::DATE) as day_of_week
FROM (
SELECT ‘01-01-2013‘::TIMESTAMP as tm
UNION
SELECT ‘12-31-2500‘::TIMESTAMP as tm
) as t
TIMESERIES ts as ‘1 Day‘ OVER (ORDER BY tm);
Oracle里面构造日历用法：
select to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1) as day_id,
EXTRACT(YEAR FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1))) as year_of_calendar,
EXTRACT(MONTH FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1))) as month_of_year,
--EXTRACT(DAY FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1)) ) as daynum,
to_char(to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1), ‘D‘) as dayofweek
from dual
connect by level <= to_date(‘25001231‘, ‘yyyymmdd‘) -
to_date(‘20130101‘, ‘yyyymmdd‘)
SELECT to_number(TO_CHAR(ts::DATE,‘yyyymmdd‘)) as day_id,
year(ts::DATE) as year_of_calendar,
month(ts::DATE) as month_of_year,
dayofweek(ts::DATE) as day_of_week
FROM (
SELECT ‘01-01-2013‘::TIMESTAMP as tm
UNION
SELECT ‘12-31-2500‘::TIMESTAMP as tm
) as t
TIMESERIES ts as ‘1 Day‘ OVER (ORDER BY tm);
Oracle里面构造日历用法：
select to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1) as day_id,
EXTRACT(YEAR FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1))) as year_of_calendar,
EXTRACT(MONTH FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1))) as month_of_year,
--EXTRACT(DAY FROM (to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1)) ) as daynum,
to_char(to_date(‘20130101‘, ‘yyyymmdd‘) + (level-1), ‘D‘) as dayofweek
from dual
connect by level <= to_date(‘25001231‘, ‘yyyymmdd‘) -
to_date(‘20130101‘, ‘yyyymmdd‘)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
