Oracle和Vertica中构造日历数据
来源:互联网 发布:古剑奇谭ol激活码淘宝 编辑:程序博客网 时间:2024/04/30 23:57
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')
0 0
- Oracle和Vertica中构造日历数据
- Oracle和Vertica中构造日历数据
- Vertica的这些事<七>—— Vertica中实现Oracle中的ws_concat功能
- ORACLE中创建日历
- Vertica的这些事<十>—— vertica中group by 和join 语句的优化
- SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
- 使用COPY命令在Vertica中加载数据
- 利用kattle将SQLserver中的数据导入到vertica中
- vertica-清除删除数据
- Vertica的这些事<六>—— SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
- vertica
- Vertica的这些事<八>—— vertica加密数据
- Oracle、Vertica和Hive三种数据库查询表空间的方法
- Vertica创建User和Schema
- Oracle中构造数组
- oracle导入csv文本文件到vertica
- Oracle 日历
- oracle 日历
- Oracle和Vertica中构造日历数据
- 你所不知道的Java序列化
- 在驱动模块初始化函数中实现设备节点的自动创建
- UML中的用例图和类图
- 面向对象——(4)静态方法
- Oracle和Vertica中构造日历数据
- poj 2263
- 分享一个自定义的中间凸起的tabBar
- error LNK2005: _DllMain@12 已经在 XXXX.obj 中定义
- DMU
- linuxcast笔记:linux网络基本配置
- ReactiveCocoa与Functional Reactive Programming
- 深入理解SetUID
- Android 如何修改Sim卡语言自适应