Oracle BIEE 时间函数 (ago,todate)
oracle 中时间用法
oracle 中时间用法在Oracle数据库中，时间的处理和使用是非常重要的。
Oracle提供了许多用于处理时间的函数和数据类型，以下是一些常见的时间用法：1. 数据类型：DATE，Oracle中用于存储日期和时间的数据类型。
它包含年、月、日、时、分和秒的信息。
TIMESTAMP，比DATE类型精确度更高的日期和时间数据类型，可以精确到纳秒级别。
2. 获取当前时间：使用SYSDATE函数可以获取当前的日期和时间。
使用CURRENT_TIMESTAMP函数可以获取当前的时间戳。
3. 日期和时间函数：TO_DATE，将字符串转换为日期类型。
TO_CHAR，将日期类型格式化为字符串。
EXTRACT，从日期中提取特定的部分，比如年、月、日、小时等。
ADD_MONTHS，在日期上加上指定的月数。
MONTHS_BETWEEN，计算两个日期之间相差的月数。
4. 日期和时间运算：可以使用+和-运算符在日期上进行加减操作。
可以对日期进行比较，判断日期的先后顺序。
5. 时区转换：使用AT TIME ZONE可以将日期和时间从一个时区转换到另一个时区。
使用FROM_TZ和CAST函数可以进行时区转换。
6. 时间戳：可以使用TO_TIMESTAMP将字符串转换为时间戳类型。
可以使用FROM_TZ函数将带有时区信息的日期转换为时间戳类型。
7. 时区信息：Oracle数据库中存储了时区信息，可以通过查询相关的数据字典视图来获取时区信息。
总的来说，Oracle提供了丰富的日期和时间处理功能，可以满足各种复杂的时间需求。
通过合理使用日期和时间函数，可以方便地进行日期和时间的计算、转换和格式化。
同时，时区的处理也是Oracle日期和时间处理中需要特别注意的部分，确保在多时区环境下能够正确处理日期和时间数据。
Oracle 日期时间函数的用法
Oracle 日期时间函数的用法在oracle中处理日期大全 TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year: yy two digits 98 yyyy four digits 1998在oracle中处理日期大全TO_DATE格式Day:dd number 12dy abbreviated friday spelled out fridayddspth spelled out, ordinal twelfthMonth:mm number 03mon abbreviated marmonth spelled out marchYear:yy two digits 98yyyy four digits 199824小时格式下时间范围为： 0:00:00 - 23:59:59....12小时格式下时间范围为： 1:00:00 - 12:59:59 ....1.日期和字符转换函数用法（to_date,to_char）2.select to_char( to_date(222,'J'),'Jsp') from dual显示Two Hundred Twenty-Two3.求某天是星期几select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 星期一selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;monday设置日期语言ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';也可以这样TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')4.两个日期间的天数select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;5. 时间为null的用法select id, active_date from table1UNIONselect 1, TO_DATE(null) from dual;注意要用TO_DATE(null)6.a_date between to_date('20011201','yyyymmdd') andto_date('20011231','yyyymmdd')那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
oralce函数
oralce函数Oracle是一种关系数据库管理系统，它使用了一种名为Oracle数据库的数据库管理系统。
Oracle是一种强大的工具，提供了许多内置函数，可以用于在数据库中进行各种操作。
以下是一些常用的Oracle函数。
1.聚合函数-AVG：计算指定列的平均值。
-COUNT：计算指定列中非空数据的数量。
-SUM：计算指定列的总和。
-MAX：找到指定列的最大值。
-MIN：找到指定列的最小值。
2.字符串函数-CONCAT：将两个字符串连接成一个字符串。
-LOWER：将字符串转换为小写。
-UPPER：将字符串转换为大写。
-LENGTH：计算字符串的长度。
-SUBSTR：返回一个字符串的子字符串。
3.数值函数-ROUND：将一个数值四舍五入到指定的小数位数。
-CEIL：向上取整，返回不小于指定数值的最小整数。
-FLOOR：向下取整，返回不大于指定数值的最大整数。
-ABS：返回指定数值的绝对值。
-MOD：返回两个数值的余数。
4.日期和时间函数-SYSDATE：返回当前日期和时间。
-ADD_MONTHS：在指定日期上增加指定的月份。
-TRUNC：截断日期或时间到指定的精度。
-MONTHS_BETWEEN：计算两个日期之间的月数差。
-TO_CHAR：将日期转换为指定格式的字符串。
5.条件函数-DECODE：根据条件返回不同的值。
-CASE：根据条件执行不同的操作。
-NVL：如果给定的表达式为NULL，则将其替换为指定的值。
-NULLIF：如果两个表达式的值相等，则返回NULL。
6.分析函数-ROW_NUMBER：为每一行分配一个唯一的数字。
-RANK：为每一行分配一个排名，如果有并列的值，则排名相同。
-DENSE_RANK：为每一行分配一个排名，如果有并列的值，则排名可以重复。
-LEAD：返回指定行后的值。
-LAG：返回指定行前的值。
上述函数只是Oracle提供的一小部分功能，Oracle还提供了许多其他有用的函数。
Oracle日期函数大全
ORACLE日期时间函数大全TO_DATE格式(以时间:2007-11-02 13:45:25为例)Year:yy two digits 两位年显示值:07yyy three digits 三位年显示值:007yyyy four digits 四位年显示值:2007Month:mm number 两位月显示值:11mon abbreviated 字符集表示显示值:11月,若是英文版,显示novmonth spelled out 字符集表示显示值:11月,若是英文版,显示novemberDay:dd number 当月第几天显示值:02ddd number 当年第几天显示值:02dy abbreviated 当周第几天简写显示值:星期五,若是英文版,显示friday spelled out 当周第几天全写显示值:星期五,若是英文版,显示fridayddspth spelled out, ordinal twelfthHour:hh two digits 12小时进制显示值:01hh24 two digits 24小时进制显示值:13Minute:mi two digits 60进制显示值:45Second:ss two digits 60进制显示值:25其它Q digit 季度显示值:4WW digit 当年第几周显示值:44W digit 当月第几周显示值:124小时格式下时间范围为：0:00:00 - 23:59:59....12小时格式下时间范围为：1:00:00 - 12:59:59 ....1. 日期和字符转换函数用法（to_date,to_char）select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//2.select to_char( to_date(222,'J'),'Jsp') from dual显示Two Hundred Twenty-Two3.求某天是星期几select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;星期一select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;monday设置日期语言ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';也可以这样TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')4. 两个日期间的天数ceil(n) 取大于等于数值n的最小整数floor(n)取小于等于数值n的最大整数SELECT floor(TO_DATE('2008-05-02 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -TO_DATE('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) 相差天数FROM DUAL;相差天数1SELECT ceil(TO_DATE('2008-05-02 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -TO_DATE('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) 相差天数FROM DUAL;相差天数---------25. 时间为null的用法select id, active_date from table1UNIONselect 1, TO_DATE(null) from dual;注意要用TO_DATE(null)6.月份差a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
oracle的todate用法
Oracle的todate用法1. 什么是Oracle的todate函数？在Oracle数据库中，todate是一个非常重要的函数，它用于将日期字符串转换为日期类型。
todate函数接受两个参数：第一个参数是日期字符串，第二个参数是日期格式模式。
2. todate函数的语法todate(date_string, format)•date_string: 一个表示日期的字符串。
•format: 可选参数，指定了日期字符串的格式模式。
3. todate函数支持的格式模式以下是一些常用的日期格式模式：•YYYY-MM-DD: 年-月-日•YYYY/MM/DD: 年/月/日•DD-MM-YYYY: 日-月-年•DD/MM/YYYY: 日/月/年•MM-DD-YYYY: 月-日-年•MM/DD/YYYY: 月/日/年此外，还有其他一些可以使用的格式模式，如：•YY-MM-DD HH24:MI:SS•YY/MM/DD HH24:MI:SS•YY/MM/DD HH12:MI:SS AM4. todate函数示例下面给出一些示例来演示todate函数的使用。
示例1：将字符串转换为日期类型假设我们有一个表格名为employees，其中有一个列名为hire_date。
该列存储了员工入职日期的信息。
现在我们想要将一个日期字符串'2022-01-01'插入到该列中。
INSERT INTO employees (hire_date) VALUES (todate('2022-01-01', 'YYYY-MM-DD'));示例2：将日期字符串转换为特定格式假设我们有一个表格名为orders，其中有一个列名为order_date。
该列存储了订单的下单日期信息。
现在我们想要查询出所有订单的下单日期，并以特定的格式显示。
SELECT to_char(order_date, 'YYYY年MM月DD日') AS formatted_order_dateFROM orders;示例3：将字符串转换为时间戳类型假设我们有一个表格名为logs，其中有一个列名为log_time。
oracleto_date用法
oracleto_date用法ORACLE TO_DATE函数是用来将一个字符串转换为日期类型的函数。
它的一般用法为 TO_DATE(string, format)，其中string是代表日期的字符串，format是指定将字符串转换为日期的格式。
1.日期格式化字符串：-YYYY：四位年份-YY：两位年份-MM：两位月份-MON：月份的英文缩写-MONTH：月份的完整英文名-DD：两位天数-HH：两位小时数（24小时制）-MI：两位分钟数-SS：两位秒数-AM/PM：上午/下午标记-HH24：两位小时数（24小时制）2.示例：-TO_DATE('2024-12-31','YYYY-MM-DD')：将字符串'2024-12-31'转换为日期类型-TO_DATE('22-12-31','YY-MM-DD')：将字符串'22-12-31'转换为日期类型-TO_DATE('31-DEC-2024','DD-MON-YYYY')：将字符串'31-DEC-2024'转换为日期类型-TO_DATE('2024-12-3123:59:59','YYYY-MM-DDHH24:MI:SS')：将字符串'2024-12-3123:59:59'转换为日期类型3.源字符串和日期格式不匹配的问题：4.对于单个数字的月份或天数：如果源字符串只有单个数字的月份或天数，可以在格式字符串中使用FM修饰符来取消默认的空格或零填充。
5.使用NLS设置：TO_DATE函数的转换行为可以受到NLS（National Language Support）设置的影响。
可以使用NLS参数来指定使用特定的NLS设置进行日期转换。
例如：TO_DATE('2024-十二-31','YYYY-MON-DD','NLS_DATE_LANGUAGE=CHINESE')：将字符串'2024-十二-31'按照中文日期格式转换为日期类型。
Oracle中的TODATE和TOCHAR函数
1. 日期和字符转换函数用法（to_dat e,to_cha r）select to_cha r(sysdat e,'yyyy-mm-dd hh24:mi:ss') as nowTim e from dual; //日期转化为字符串select to_cha r(sysdat e,'yyyy') as nowYea r from dual; //获取时间的年select to_cha r(sysdat e,'mm') as nowMon th from dual; //获取时间的月select to_cha r(sysdat e,'dd') as nowDay from dual; //获取时间的日select to_cha r(sysdat e,'hh24') as nowHou r from dual; //获取时间的时select to_cha r(sysdat e,'mi') as nowMin ute from dual; //获取时间的分select to_cha r(sysdat e,'ss') as nowSec ond from dual; //获取时间的秒select to_dat e('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//2. select to_cha r( to_dat e(222,'J'),'Jsp') from dual显示TwoHundre d Twenty-Two3. 求某天是星期几select to_cha r(to_dat e('2002-08-26','yyyy-mm-dd'),'day') from dual;星期一selectto_cha r(to_dat e('2002-08-26','yyyy-mm-dd'),'day','NLS_DA TE_LA NGUAG E = Americ an') from dual;monday设置日期语言ALTERSESSIO N SET NLS_DA TE_LA NGUAG E='AMERIC AN';也可以这样TO_DAT E ('2002-08-26', 'YYYY-mm-dd', 'NLS_DA TE_LA NGUAG E =Americ an')4. 两个日期间的天数select floor(sysdat e - to_dat e('20020405','yyyymm dd')) from dual;5. 时间为nul l的用法select id, active_date from table1UNIONselect 1, TO_DAT E(null) from dual;注意要用TO_DATE(null)6.月份差a_date betwee n to_dat e('20011201','yyyymm dd') andto_dat e('20011231','yyyymm dd')那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
Oracle中TO_DATE函数
Oracle中TO_DATE函数Oracle中TO_DATE格式TO_DATE格式(以时间:2007-11-02 13:45:25为例)Year:yy two digits 两位年显示值:07yyy three digits 三位年显示值:007yyyy four digits 四位年显示值:2007Month:mm number 两位月显示值:11mon abbreviated 字符集表示显示值:11月,若是英文版,显示novmonth spelled out 字符集表示显示值:11月,若是英文版,显示novemberDay:dd number 当月第几天显示值:02ddd number 当年第几天显示值:02dy abbreviated 当周第几天简写显示值:星期五,若是英文版,显示friday spelled out 当周第几天全写显示值:星期五,若是英文版,显示fridayddspth spelled out, ordinal twelfthHour:hh two digits 12小时进制显示值:01hh24 two digits 24小时进制显示值:13Minute:mi two digits 60进制显示值:45Second:ss two digits 60进制显示值:25其它Q digit 季度显示值:4WW digit 当年第几周显示值:44W digit 当月第几周显示值:124小时格式下时间范围为：0:00:00 - 23:59:59....12小时格式下时间范围为：1:00:00 - 12:59:59 ....1. 日期和字符转换函数用法（to_date,to_char）select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//2.select to_char( to_date(222,'J'),'Jsp') from dual显示Two Hundred Twenty-Two3.求某天是星期几select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;星期一selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_ LANGUAGE = American') from dual;monday设置日期语言ALTER SESSION SETNLS_DATE_LANGUAGE='AMERICAN';也可以这样TO_DATE ('2002-08-26', 'YYYY-mm-dd','NLS_DATE_LANGUAGE = American')4. 两个日期间的天数select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;5. 时间为null的用法select id, active_date from table1UNIONselect 1, TO_DATE(null) from dual;注意要用TO_DATE(null)6.月份差a_date between to_date('20011201','yyyymmdd') andto_date('20011231','yyyymmdd')那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle BIEE 中的时间维与时间轴函数
累计、同比、环比是BI 项目中常用的分析方法。
本文讲述如何利用Oracle BIEE 的时间维（Time Dimension）及时间轴函数（Time Series Conversion Function）实现累计、同比、环比计算。
本文使用的示例数据可以通过此链接下载：/。
如果你不了解如何创建Oracle BIEE 资料库，请参考之前的文章Hello BIEE。
目录
∙时间轴函数概述
∙创建时间维的准备工作
∙创建时间维
∙使用时间轴函数定义度量值
∙查询
∙参考
时间轴函数概述
累计、同比、环比是BI 项目中常用的分析方法。
如果采用关系型数据库，直接通过SQL 语句进行这样的计算是比较麻烦的。
Oracle BIEE 提供的解决方法是时间轴函数。
时间轴函数是位于逻辑模型层内的函数，使用时间轴函数能够定义出“去年同期值”或“本年累计值”之类的与时间相关的计算字段。
Oracle BIEE 现在支持两个时间轴函数：
∙Ago：从当前时间起回溯用户指定的n 个时间段，返回当时的度量值。
∙ToDate：从用户指定时间段的起点开始，到当前时间为止，计算度量值的累计值。
使用时间轴函数的前提条件是在逻辑模型内定义了时间维。
上面两个函数说明中的“时间段”实际上指的是时间维的层级（Level）。
例如使用Ago 函数定义“去年同期值”，指定的时间段为“年”这一层级，指定的回溯周期为1，表示得到去年的值。
再比如使用ToDate 函数定义“本年累计值”，指定的时间段也是“年”这一层级，表示从年的起点（年初）开始到当前时间为止，求度量值的累计值。
创建时间维的准备工作
时间维的创建过程与创建普通维度基本相同，只是维度对象的某些设置不同。
因此创建物理模型、逻辑模型、及展现模型的过程在此不细说了，如果你不了解请参考之前的文章Hello BIEE。
在物理模型层，导入时间维表V_COMMON_DATE；将V_COMMON_DATE.DAY_ID 定义为V_COMMON_DATE 的主键；将V_COMMON_DATE.DAY_ID 定义为
V_FINANCE_EXPENSE.CONSUME_DATE 的外键。
在逻辑模型层，将物理表“V_COMMON_DATE”拖放到逻辑模型“Finance”内；使用“Rename Wizard”重命名；将逻辑字段“Day Id”定义为逻辑表“Date Dim”的逻辑主键；在逻辑表“Date
Dim”和“Expense Fact”之间创建逻辑连接。
在展现模型层，将逻辑表“Date Dim”拖放到展现模型“消费分析”内；只保留年、月、日的名称字段，并重命名。
需要注意的是，Oracle BIEE 对与时间维相关的物理表及逻辑表有以下要求[6]：
∙如果某个物理表的字段被映射到了时间逻辑表上，那么这个物理表中的字段不能再映射到其他逻辑表中。
∙作为时间逻辑表的数据源的物理表，只允许与物理事实表关联。
且关联只能基于外键（foreign key），而不能基于复杂连接（complex join）。
创建时间维
与创建普通维度对象一样，以逻辑表“Date Dim”为基础创建维度对象“Date”；依次定义层级：“Total Level”、“Year Level”、“Month Level”、“Day Level”，并设定层级的元素数；将逻辑表“Date Dim”的各列拖放当相应的层级；定义各层级的层键，并设定主层键，以及用于下钻的层键。
时间维与普通维度有两处不同。
首先，双击维度“Date”节点，在“Dimension”对话框中需要选中“Time Dimension”选项，以表明此维度对象为时间维度。
其次，时间维度中需要定义时间序列键（Chronological Key）。
时间序列键的作用是表明时间维中具有一组单调增加的时间序列值[1]。
定义时间序列时存在以下原则：
∙时间维中，至少一个层级上应定义有时间序列键[2]。
∙物理存储的最细时间粒度上必须定义时间序列键[3]。
在例子中，物理表“V_FINANCE_EXPENSEE”的日期字段“CONSUME_DATE”为日期，即物理存储的最细时间粒度为日期，因此时间维“Date”的“Day Level”上应该定义时间序列键。
∙除了必要的时间序列键，时间维的各个层级上均可建立额外的时间序列键，这主要是为提高查询性能服务[4]。
∙时间维的同一层级上可以建立多个时间序列键，但只有第一个时间序列键有效。
[5]在这个简单的例子中，我只把层级“Day Level”的逻辑层键“Day Id”定义为时间序列键。
使用时间轴函数定义度量值
定义好时间维后，就可以在逻辑事实表中定义使用时间轴函数的逻辑列了。
在逻辑表“Expense Fact”上单击右键，选择“New Object\Logical Column”创建逻辑列。
在“Logical Column”对话框中选择“Use existing logical columns as source”再按右侧的按钮，可以打开“Expression Builder”编辑逻辑列的公式。
最后不要忘记将逻辑列添加到展现模型中。
例如，使用Ago 函数定义“去年同期值”。
AGO(Finance."ExpenseFact".Amount, Finance."Date"."Year Level", 1)
第一个参数是逻辑字段Finance."ExpenseFact".Amount，是用于时间轴运算的度量值；第二个参数是逻辑模型时间维的层级Finance."Date"."Year Level"，表明回溯的时间单位为年；第三个参数表示回溯的时间周期，1 则为回溯一年，即去年。
再例如，使用ToDate函数定义“本年累计值”。
TODATE(Finance."ExpenseFact".Amount, Finance."Date"."Year Level")
第一个参数是逻辑字段Finance."ExpenseFact".Amount，是用于时间轴运算的度量值；第二个参数是逻辑模型时间维的层级Finance."Date"."Year Level"，表明累计计算开始自年时间的起点，终止到当前时间。
在进行时间查询时应注意几个重要的粒度[7]，以查询select "Date Dim"."Month Name", "Expense Fact"."Amount Year Ago" 为例：
∙"Date Dim"."Month Name" 为查询粒度。
∙"Expense Fact"."Amount Year Ago" 逻辑列对应的时间维层级为时间序列粒度。
∙在包含时间序列函数的查询中，时间序列粒度必须大于等于查询粒度。
∙"Expense Fact"."Amount Year Ago" 逻辑列对应的物理表的时间粒度被称为存储粒度。
∙与存储粒度对应的时间维层级上必须定义时间序列键。
Ago 与ToDate函数可以嵌套调用，但需要遵守一定规则[8]。
在使用时间序列函数时也有一定限制，具体请参考BIEE 文档[9]。
查询
定义如下查询，其中“消费金额(同期)”与“消费金额(年累计)”分别为使用Ago 和ToDate 函数创建的逻辑列：
结果如下图所示：。