如何用DataFocus系统处理Excel表中日期数据?
在对存储的Excel数据进行分析时,经常涉及对日期数据的处理,如计算并提取日期信息、计算相差天数、构建规范日期等等。下面介绍DataFocus系统中常被用到的日期时间公式,以及如何对Excel数据进行处理。
数据表导入与选择
在用DataFocus系统处理Excel表中数据之前,我们先看下如何将数据表导入到系统中并进行选择,以开始对数据的处理。
1.1 数据表的导入
将本地Excel数据表导入到系统中,大致流程如下:
数据表管理模块的“导入表”栏下选择导入本地文件(或资源管理功能模块中点击“创建资源”按钮)。
选定“EXCEL”文件类型,并在选择本地文件后点击“上传“。
上传成功后的 Excel表配置信息界面,可以选择导入的列、重新命名表名及列名、修改列类型等等,配置完成确认无误后点击“确定”完成上传。
1.2 公式的使用
DataFocus公式的使用以及数据分析,是在搜索界面完成的。所以在数据表导入到系统后,需先在搜索界面选择数据源表,再开始用公式处理数据。具体操作如下:
进入搜索页面选择数据表。
点击左下角 “增加公式”按钮会弹出一个公式填写界面。
在空白栏中输入要用的公式或者是利用右边‘公式辅助’按钮选择自己想要的公式,鼠标悬停在公式上,下方会出现对于该公式的解释说明以及样例。
编写好的公式可以自己命名,点击公式名,对公式名字进行编辑(不能与关键词同名)。
若公式计算的最终结果是数值,则在公式输入框下方的“高级设置”中可以修改该公式的聚合方式及列类型。
公式输入正确后点击确定,该公式便会添加在数据表的最下方。
双击该公式即可将公式加入到搜索框内,公式计算结果会显示在报表中。
图 1 搜索界面添加公式
日期数据的处理
2.1 提取日期中的年、月、日
提取日期包括从已有日期中提取年份、提取月份或提取日。
在DataFocus系统中,now公式可用于获取当前系统的日期,以便于进行日期的计算;提取日期中的年份、月份、日、时间,可分别使用公式year、month、day、time。
2.2 构建规范日期
构建规范日期是指将数据转为系统识别的正规格式的日期。在Excel表中的日期列,即使年月日之间使用问号或感叹号间隔,将其导入到DataFocus时也会被系统自动识别出日期格式,即“timestamp”类型。而对于不能识别为日期格式的数据,例如日期数据为8位的数字,可以使用公式substr,concat进行字符串的获取、连接,用公式to_date将连接好的字符串转为日期格式。
2.3 日期计算
DataFocus中有一些日期公式,可以根据需要计算日期并返回特定的值。
A、计算日期差值
diff_days 用于计算日期数据中天数的差值;diff_time用于计算两个日期之间的秒数差。
B、直接为日期列添加值
例如 add_weeks 可为日期列增加周数;add_days可用于计算指定时间加指定天数的值;add_seconds 用于计算指定时间加指定秒的值 等等。
C、计算指定时间
例如:day_number_of_year 计算指定时间是年内的哪一天 ;day_of_week 计算指定时间是周几(Monday~Sunday);is_weedend 用于计算指定日期是否是周末;month_number计算指定日期是当年的第几个月等。
D、获取开始时间
例如:start_of_month 计算指定日期所属月的开始日期;start_of_quarter 计算指定日期所属季度的开始日期;start_of_week 计算指定日期所属周的开始日期 等等。
用DataFocus系统处理Excel表中日期数据时,这些支持的公式均可以使用。除了单纯地使用日期公式,在有需求时也可以与其它公式嵌套使用,例如 文本公式substr、逻辑操作符if..then..else 。