跳至正文

8.3 数据准备类函数(上):字符串函数、日期函数

《数据可视化分析2.0》第八章

各种分析工具都提供了大量内置函数(build-in function)从而简化数据准备计算。按照数据类型,可以分为字符串函数、日期函数、类型转化函数等。

8.3.1  字符串函数:截取、查找替换等清理函数

字符串函数是最典型的行级别函数,完成数据清理、截取、拆分、查找、替换等操作。不同工具的字符串函数有类似之处,比如截取函数LEFT、RIGHT、MID,查找函数FIND、替换函数REPLACE等。Tableau常见的字符串函数可以分为如下小类:

1.截取函数——从字符串中返回“子字符串”

截取是最简单的字符串函数,可以从字符串(String)中返回其中一部分即“子字符串”(Substring),也可以称之为“提取函数”。按照截取逻辑,可分为按长度截取、按分隔符截取和按通配符截取多个类型。

1)按照长度和位置截取

典型的截取函数是LEF、RIGHT和MID,分别用于从左侧、右侧、中间截取子字符串,几乎是各种数据处理工具(Excel、SQL、Python等)的标准内置函数。

截取函数的典型应用是从员工身份证ID中提取城市编码、出生日期、性别。

  • 户籍城市编码(身份证前6位):LEFT( [ID] , 6)
  • 出生日期(第七位之后连续8位):MID( [ID] , 7,6)
  • 性别判断(第17位奇数为男,偶数为女):IIF( INT(MID( [ID],17,1))%2)=0, ‘女’, ‘男’)

初学者要特别注意,从字符串中截取结果还是字符串数据类型,因此继续使用算数计算,还要把截取结果从默认字符串数据类型转化为整数(使用了INT转换),而后才能使用算术计算,比如以“余数是否为0”判断奇偶,偶数为女,奇数为男。后续会介绍INT、%余数计算、IIF逻辑判断函数。

(2)按照分隔符和位置截取

8.1节,使用Excel、SQL、Tableau从字段【产品名称】中截取【品牌】字段,如下所示。

  • Excel:使用LEFT和FIND函数结合,= LEFT(N2,FIND(‘ ‘,N2))
  • SQL:使字符串提取函数:SUBSTRING_INDEX(‘产品名称’,‘ ‘,1) 
  • Tableau:使用变换-拆分功能选择完成,本质上是拆分函数:SPLIT([产品名称], ‘ ‘,1)

这里SQL和Tableau的逻辑相近,先指定分隔符,后返回分隔符拆分后的指定部分。Tableau中拆分函数的完整语法是SPLIT([字符串], “分隔符”, 返回第几位),比如:

SPLIT(“a-b-c-d”,”-“,2) = b

Tableau拆分函数被预置在字段右键菜单“变换”中,如图8-16所示。使用这种方法创建的字符串会自动添加TRIM函数——删除前后可能存在的空格。

图8-16  拆分函数和自定义拆分

(3)按照有规律的灵活规则(通配符)截取

如果要从字段【案情介绍】字符串中提取手机号、邮箱,或省市县等有特定规律的字符串,但是不能确定位置,也没有明确的分隔符可以判断,此时就要使用“高大上”的通配符(widecard character)。通配符是可以代表单个或者多个字符的特殊字符,常见的星号(*)代表多个字符,问号(?)代表单一字符。

本书会在8.4节单独介绍。

2.查找、替换函数

截取函数只是从字符串(String)中提取另一个字符串(Substring),可以理解为“查找+提取”两步操作。查找函数则是返回子字符串的位置,而替换则将指定子字符串替换为指定的字符串。查找函数返回结果不同,又可以分为以下分类。

(1)查找函数FIND——返回整数位置(从左侧开始的位置)。

Tableau中包括两个函数:

  • FIND([字符串字段],”被查找子字符串”)  ——返回被查找子字符串首次出现的位置
  • FINDNTH ([字符串字段],”被查找子字符串”,n)——返回被查找子字符串第n次出现的位置

特别注意,FIND函数返回整数数字,没有则为空(null);在视图中,度量默认会聚合,相当于行级别函数被聚合了,新用户特别容易在这里“翻车”,不过也是深刻领会行级别计算与聚合关系的契机,举例如下。

比如,从字段【订单ID】查找子字符串“20”,然后返回所在位置。如图8-17所示,在“列”中双击创建即席计算,输入 FIND( [订单ID],”20″),每个订单ID就会返回一个整数数字。图中左侧是在数据表明细中的计算结果,而在右侧视图中,以【订单ID】为依据分组聚合了,订单“CN-2016-1070056”明细中有11行交易,所以聚合为44。

图8-16  FIND字符查找函数

Tableau的视图犹如Excel的透视表;在视图中,数字度量默认聚合。为了查看每个订单ID的情况,如图8-16右侧所示,在FIND函数字段上右击,在弹出的下拉菜单中将其从度量改为维度,连续并转化为离散,拖曳到订单ID之后显示,就构成了交叉表的样式。

可见,行级别计算既可以作为被聚合成为度量,也可以随时切换为维度。维度、度量是相对于问题而言的,因此是主观的。

FIND函数通常与LEFT、RIGHT等截取函数结合,比如“从左侧截取到第一个××字符”,这也是Excel中截取字符串字段的逻辑,表达式如下。

  • LEFT([字符串字段],FIND([字符串],”子字符串”)-1 ) [1]

笔者在实施项目时,由于SAP HANA直连不支持拆分函数SPLIT,因此就使用FIND函数从字段【物料编码】间接提取每个部分,字段的格式为“一级物料—二级物料—三级物料”,截取“一级物料”如下。

LEFT([物料编码], FIND([物料编码],”-“)-1 )  //截取左侧到第一个短横分隔符

(2)包含函数CONTAINS——返回布尔值。

CONTAINS([查找字段],"子字符串")

CONTAINS函数用于验证字段中是否包含被查找的子字符串,只是它返回是否而非位置,如果包含,则返回TRUE,否则返回FALSE。这个判断通常与逻辑判断结合,比如“当字符串中包含“××”字符串时,定义为‘危险’,否则‘正常’”:

IIF( CONTAINS([被查找字段],"××"), "危险" , "正常" )

在实践中,能用CONTAINS判断有无的,就不要用FIND加判断完成。

(3)指定位置判断是否包含——返回布尔值。

  • STARTSWITH ([字符串字段],”子字符串”)
  • ENDSWITH([字符串字段],”子字符串”)

CONTAINS函数用于验证字符串中是否包含某个字符(完全不在乎位置),有时候需要缩小查找范围,仅仅查找是否以指定字符开头,或者以此结尾,这时就用STARTSWITH和ENDSWITH函数。比如字符串是否以“AA”开头,如果是,则返回TRUE。

STARTSWITH ([字符串字段],”AA”)       //字符串是否以“AA”字符开头

(4)替换函数REPLACE——返回字符串。

REPLACE ([字符串字段],”子字符串”,”用于替换的字符串”)

这个函数的用法简单清晰,把指定子字符串更换为一个新字符串。

笔者在公安局警情分析中遇到了这样的例子,“借助字符判断案件是否为涉财案件”。涉财案件的基本标准是,警情记录中一定会包含“涉嫌金额××元”“被盗财物××万元”等字样,因此可以按照以下的字段判断“涉财案件”。

CONTAINS([案件详情],"元")

不过,案件详情记录报警人的家庭住址、事发地点等,存在大量的“×号楼×单元”字样,这里的“单元”会影响上面的判断。比较简单的方案是先把案件详情中的“单元”临时修改,如下:

REPLACE ([案件详情],"单元","单圆") 

而后再判断是否包含代表货币的“元”,如下所示。

CONTAINS( REPLACE ([案件详情],"单元","单圆") ,"元")

更进一步,使用逻辑函数IIF,把包含“元”的数据,标记为“涉财案件”,如图8-18所示。

图8-18  使用CONTAINS和REPLACE的实例

在没有其他异常字符的情况下,这个方案才可行;如果追求更高的可靠性,可以使用后续的“通配符函数”。规则越复杂,函数也随着越高级。

3.其他特殊字符串函数

除了上述3类字符串函数,还有一些特殊的字符串函数可供使用,如下所示。

(1)空格函数SPACE(N)。

生成N个空格字符串,比如SPACE(2)生成两个空格,通常与其他函数结合使用。

SPACE(2)+"单"+SPACE(2)+"元"+SPACE(2)  = "  单  元  "

(2)移除空格函数。

前面变换函数中自动添加了TRIM函数,它会移除字符串前后的空格,如下所示。

TRIM("  单  元  ")  = "单  元"

另外还有个比较延伸的函数,分别删除前导空格(L为LEFT)、删除尾随空格(R为RIGHT)。

LTRIM("  单  元  ")  = "单  元  "
RTRIM("  单  元  ")  = "  单  元"

(3)大小写切换函数:UPPER、LOWER。

分别把字符串改为大写或者小写(当然是英文字符),特别适合批量修改身份证尾号x为X。

(4)把每个单词都改为首字母大写:PROPER函数。

这个是2022.4版本的新函数,相当于UPPER、LOWER函数的组合使用,可以把数据值中的单词全部改为首字母大写、其他小写。特别适合于英文名称处理。比如:

PROPER("xi le jun")  = "Xi Le Jun "

(4)字符串长度函数:LEN函数。

返回字符串的长度。

8.3.2  日期函数:日期独特性与转化、计算

日期是非常特殊的维度字段,它不仅具有连续性,而且自带层次特征,可以从年到季度、月要天、小时到分钟、分秒到毫秒一直切分下去。日期的层次性,是使用日期字段和创建日期函数的基础。

不同的数据库通常使用不同的日期格式,常见的如“2020-2-10 10:20:30”,任意的日期或时间都由多个日期部分构成,每个部分都有一个名称(datepart)对应。可以参考图8-19理解日期的层次结构和格式构成。

图8-19  日期的层次和离散/连续属性

日期自带层次结构,因此分析中可以调整日期的层次快速调整视图的分组聚合。常见的日期层次有年/季度/月/天(离散),还有年/年季度/年月/年周/年月日(连续),鼠标右击日期字段,可以在弹出的下拉菜单中快速调整。高级用户可以借助参数,实现年/季度/月等切换。

日期默认都是连续的,日期坐标轴会有一个明显特征:坐标轴分别向前、向后延伸一段距离,代表前后无限延伸。日期也可以改为离散显示,离散显示是为了更好地展示相互差异而非总体趋势。

Tableau的日期函数可以按照功能,分为以下几类。

  • 日期创建函数:创建日期函数MAKEDATE、创建时间函数MAKETIME、创建日期时间函数MAKEDATETIME、TODAY函数、NOW函数。
  • 日期转化函数:DATE函数、DATETIME函数、转化函数DATEPARSE、截取函数DATETRUNC。
  • 日期提取函数:DATEPART函数取日期的构成部分之数字、DATENAME函数取日期构成部分的名称,以及各种简化形式函数YEAR、QUARTER、MONTH、WEEK、DAY。
  • 日期计算函数:差异计算函数DATEDIFF、增减函数DATEADD。

1.日期创建类函数(数字到日期)

一个完整的日期是由年、月、日组成的,而时间是由小时、分钟、秒组成的。既可以把多个散落在不同字段中的日期部分组成一个完成日期,也可以提取一个完整日期中的某个部分。

Tableau提供了多个函数把多个字段合并为一个完整日期、时间或者日期时间。其中,MAKEDATE(year,month,day)用于创建日期, MAKETIME(hour,minute,second)用于创建时间,这两个函数中的构成部分,必须是数字(整数),如图8-20所示。如果数据中没有代表“day”的数字,在“day”对应的函数位置输入1就可以代表各月月初。

图8-20  构建日期的函数MAKEDATE

在金融、仓库分析中,MAKEDATE常用于快速创建期初、期末日期,如下:

MAKEDATE(2022,X,1) 创建各月月初(X为1~12),和DATRTRUNC截取到月逻辑类似。

MAKEDATE(2022,X,0) 创建各月期初,即上月月末,对应DATRTRUNC截取到月减一天。
除了MAKEDATE和MAKETIME函数,还有一个函数MAKEDATETIME(date,time),它可以把独立的日期和时间两个字段合二为一,通常与上面两个函数结合使用。

另外两个特别重要的函数是TODAY()和NOW()。注意,这两个函数没有参数,分别返回当天的日期(比如2022-9-12)和当下的日期时间(比如2022-9-12 08:37:20)。

比如希望只查看当天的数据,可以创建如下的判断函数之后加入筛选器:

  • 字段名称:筛选今天的数据
  • 表达式内容:[订单日期]=TODAY()

TODAY()和NOW()两个函数可以根据系统的日期自动变化,是动态筛选的好办法。业务分析还会使用TODAY()-1代表昨日——日期加减数字,代表天的加减计算。

另外,如果需要个日期常量,比如2022年9月12日,不必创建参数并为之设置初始值,Tableau中可以使用#2022-09-12#代表日期常量(注意月份必须两位)。这在仪表板开发测试时很常用。

TODAY() - #2022-09-12#

2.日期转化和解析函数(字符串到日期)

日期连续、时序分析的前提是时间类的数据类型。在字符串上点击切换数据类型时,背后实际上两个函数,日期类型对应DATE函数,日期时间类型对应DATEIME函数,如图8-21上方所示。

图8-21  两类日期转化函数情景,两类转化函数

点击字段更改类型适用于相对规范的字符串转换,复杂格式经常会转换失败,Tableau提供了专业转化函数DATEPARSE,将字符串映射为标准日期格式,英文PARSE是“解析、句法分析”之意。

使用DATEPARSE函数的关键是使用标准的字符组合来表示当前字符串的构成。国际上有一种标准化的日期解析标准,使用字母及位数代表字符串到日期的映射关系,比如M代表月,一个M代表1月,两个MM代表02月(包含前导零),三个MMM代表Feb,四个MMMM代表英文全称February。

日期解析函数DATEPARSE的常用符号对照表如表8-3所示。

表8-3  日期解析函数DATEPARSE的常用符号对照表[2]

日期部分符号示例字符串示例格式
时区ZPDTPacific Daylight Timez, zz, or zzzzzzz
公历的年y22、2022yy、yyyy
ISO-8601的年Y22、2022YY、YYYY
年中的季度(1~4)Q2,02,季2,第二季度(中文)2,02,Q2,2nd quarter (英文)Q,QQ,QQQQ
年中的月(1~12)M9、09、九月、九月9、09、Sep、September、SM、MM、MMM、MMMMM、MM、MMM、MMMM、MMMMM
年中的周 (1~52)w8、27w、ww
月中的天(1~31)d1、09d、dd
日期部分符号示例字符串示例格式
周中的天(1~7)EMonEEE
年中的天(1~365)D23、143D、DD、DDD
期间(am/pm 标记)aAM、am、PMa
小时 (12进制),小时 (24进制)h、H1、0314h、hh、HH
分钟m8、59m、mm
一分钟中的秒s2、05s、ss
小数秒S2,23,235,2350S、SS、SSS、SSSS
天中的毫秒A23450AAAAA

如表8-4所示,这里举例如下,帮助读者进一步理解。

表8-4  常见的日期字符串及其解析规范

字符串样式字符串格式解析备    注
2022.07.10 15:08:56 PDTyyyy.MM.dd  HH:mm:ss zzz解析中应该包含分隔符,z代表时区
Wed, July 10, 2022EEE, MMM d, yyyyy不同位数的M,代表不同的字符串长度
12:08 PMh:mm aAM、PM的期间,用a代表
01996.July.10 12:08 PMyyyyy.MMMM.dd hh:mm aaa 

3.日期提取和截取函数

(1)日期部分Datepart和Datename

在数据明细中,经常见到多个日期字段,比如【订单日期】【订单年度】【订单月份】,其中的年度、月份是从【订单日期】中提取的一部分。在数据明细中存储大量的日期部分会占用数据库空间,因此可以在分析时随用随查,这就是日期提取函数DATEPART。对应的语法如下:

DATEPART( ‘date_part’,  [订单日期] )

在函数中指定date_part部分,比如year、quarter、month、day、week,就从【订单日期】中获得了对应的日期部分了。

Tableau之美,在于它把很多计算交互化了:双击日期默认按“年”分组,点击视图中的日期字段,可以随时转化为其他日期部分,甚至可以切换连续和离散显示,如图8-22所示。因此,把日期部分保存下来的必要性就比较低了。

图8-22  DATEPART函数:取日期的指定部分

为了进一步简化计算和嵌套,Tableau提供了多种简化函数,如下:

  • YEAR( [订单日期])=DATEPART(“year”,[订单日期]) //结果为年
  • QUARTER([订单日期])=DATEPART(“quarter”,[订单日期])           //结果为季度1~4
  • MONTH([订单日期])=DATEPART(“month”,[订单日期])    //结果为月1~12
  • WEEK([订单日期])=DATEPART(“week”,[订单日期]) //结果为周1~53
  • DAY([订单日期])=DATEPART(“day”,[订单日期])     //结果为天1~31

在一些特殊场景中,还可以使用dayofyear、weekday返回“全年第几数”和星期几。

Tableau在默认公历历法(格里高利历)之外,还支持ISO-8601规范,后者以周一为每周第一天,并且在每年第一周上有特别的处理方法,可以在视图的日期字段上点击切换。对应的日期部分函数是 ISOYEAR、ISOQUARTER、ISOMONTH、ISOWEEK。

DATEPART函数提取日期部分为数值(整数),另一个函数DATENAME(date_part,[标准日期字段])则可以提取为字符串。比如2016年 3月31日提取月度,返回的字符串结果就是“3月”,或者英文的“March”,如图8-23所示。

图8-23  DATENAME函数提取日期某个部分的名称

要注意二者区别,DATEPART函数返回结果为整数,可以根据需要设置多种显示格式;DATENAME的结果是字符串,因此不能设置格式。

(2)日期截取DATETRUNC

如果要从订单日期中统计“年月”的销售额,此时就需要提取两个日期部分,或者说从年截取到月的部分。此时就有了连续截取函数DATETRUNC。

TRUNC是英文TRUNCATE(截断、裁断)的缩写,DATETRUNC将日期截取到相应的连续部分,比如2019年1月5日和2019年1月10日截取到“月”,都是2019年1月1日。裁断后的日期依然是完全连续的,区别于DATEPART和DATENAME。

和DATEPART类似,连续的日期截取也可以在视图中点击完成,如图8-24所示,以中间的线为分割,上面的是DATEPART日期部分(默认离散),下面是DATETRUNC日期截取(默认连续)。

图8-24  在视图中点击转换为日期截取函数DATETRUNC

这里要特别说明的是,Tableau实际上把DATEPART和DATETRUNC函数都内置到了“创建→自定义日期”之中了。如图8-25所示,右击“发货日期”字段,在弹出的下拉菜单中选择“创建→自定义日期”命令,可以截取日期的指定部分。这里的“日期部分”对应DATEPART日期部分函数,而“日期值”对应DATETRUNC连续截取函数

不过,笔者强烈不推荐使用“自定义日期”中的“年/月”生成日期部分。日期部分是DATEPART,“年/月”的日期部分是两个DATEPART算术计算而来,且预设离散、字符串显示。如上图8-25右侧所示,这容易给初学者带来误导,不利于理解数据类型和字段分类。读者要理解下面两种计算的区别。

  • “年/月”部分:DATEPART(‘year’, [发货日期])*100 + DATEPART(‘month’, [发货日期]))
  • “截取函数”获得年月部分:DATE( DATETRUNC( ‘month’ ,  [订单日期] ) )

图8-25  使用预置“自定义日期”创建多个日期字段(不推荐)

DATETRUNC计算确保了结果的“日期”数据类型,因此可以和其他日期直接比较,在视图中可以根据需要切换连续、离散显示,保留了它的灵活性。

这个写法常被用来计算日期对应的“期初日期”,比如仓储进销存、财务期间、金融期初等。本月的期初值是上月月末,结合简单计算即可获得。

  • 本月月初:DATE( DATETRUNC( ‘month’ ,  [订单日期] ) )
  • 上月月末:DATE( DATETRUNC( ‘month’ ,  [订单日期] ) ) – 1

除了“天”的差异可以直接加减,其他的部分就要使用日期计算函数了。

4.日期计算函数

日期计算包含两种情景:两个日期计算间隔时间、日期和数字计算。对应两个日期函数:DATEDIFF和DATEADD。

DATEDIFF( ’day’ , [订单日期], [发货日期])  // 订单到发货的间隔天数

DATEADD( ’day’ , 6, [订单日期])  // 订单日期增加6天

DATEDIFF函数的关键是指定间隔单位,支持多个参数,如图8-26所示。

图8-26  日期计算函数DATEDIFF

DATEADD函数与之类似,相当于在指定日期上增加一个特定的时长。

举例,有不少单位将每月26日(含)之后的业绩算到次月统计,与其修改数据采集系统,不如在Tableau中创建一个辅助字段“统计月份”,为符合条件的“订单日期”增加若干天到下个月,逻辑过程和语法如图8-27所示。

图8-27  DATEADD函数的示例

考虑到不同月份的天数不同,不足31天的会出现25日也到下个月的情况,因此可以结合DATEPART或DAY函数、逻辑判断进一步完善上述过程,并用DATE转换数据类型,如下所示。

IF DAY([订单日期]) >= 26 
THEN DATE( DATEADD( 'day' , 6, [订单日期])  )
ELSE  [订单日期]  END

这样,使用新字段的“年月”部分作为统计月份了。在日期计算中,以“天”为间隔或单位的计算,可以直接使用加减法,另外建议仅保留计算中有效的部分,因此还可以进一步完善,如下所示。

IF DAY([订单日期]) >= 26  
THEN   DATE( DATETRUNC( 'month',  [订单日期])+ 6 )    
ELSE   [订单日期]  END

5.案例:使用日期函数筛选日期范围

DATEPART函数是最常用的日期函数,在日期筛选时也应用广泛。这里分享两个案例:

(1)筛选历史年度“年初至今”同期数据[3]

比如,要计算今年“年初至今”的销售额与去年同期的差异,需要对销售明细做“同期筛选”。如图8-28所示,这里有两种筛选方法:(1)历史年度的数据,保留到“同月同日”,(2)历史年度的数据,保留到相同的天数(比如第169天)。统计上看,后者可以保证经营天数完全相同。

假设当下日期是6月18日。第一种方法的筛选标准是就是:之前月份(1~5月)全部保留,当月(6月)保留到6月18日。借助于MONTH和DAY函数,可以使用如下判断完成:

IF MONTH( [订单日期]) < MONTH( TODAY() )  //历史月份 
OR (MONTH( [ 订单日期]) =MONTH( TODAY() ) AND DAY([ 订单日期]) <= DAY(TODAY())  )  // 当月
THEN "同期比较日期"
ELSE "不可同期比较日期"
END

图8-28  保留历史年度的YTD同期日期

这里使用MONTH和DAY简化了DATEPART函数。满足条件的明细行标记为“同期比较日期”,否则标记为“不可同期比较日期”。

而第二种方法,则可以保证历史月份的运营天数是完全相同的,这里使用不太常用的dayofyear日期部分完成,如下所示:

IF  DATEPART('dayofyear', [订单日期]) <= DATEPART('dayofyear', TODAY() )  //保留相同的天数
THEN  "同期比较日期"
ELSE  "不可同期比较日期"
END

不考虑闰年的情况下,两种逻辑是相同的,否则就会有一天的小差异。

(2)使用计算完成日期筛选

筛选背后都是计算,不管是相对日期筛选还是绝对日期筛选,都可以使用日期函数保存下来,从而提高分析的效率;甚至可以发布到Tableau Server从而简化所有分析师创建计算的时间。

结合实践应用,这里列举常见的日期范围及其对应的计算逻辑(保留True的部分)。

  • 当前月份:DATEDIFF( ‘month’ , [订单日期],TODAY()) = 0  
  • 当月截至今日:DATEDIFF( ‘month’ , [订单日期],TODAY()) = 0 AND [订单日期] < TODAY()
  • 过去三个月:DATEDIFF( ‘month’ , [订单日期],TODAY()) <=3
           AND DATEDIFF( ‘month’ , [订单日期],TODAY()) >=0
  • 筛选最后月份(年月): DATEDIFF( ‘month’ , [订单日期], { MAX([订单日期]) }) = 0
    { MAX([订单日期]) }代表数据中的最大日期,它是第10章会讲解的FIXED LOD计算。

当然,还有很多方法都能实现类似的功能,笔者特别喜欢DATEDIFF的方法。

8.3.3  数据类型转换函数

分析的前提是确保每个字段的数据类型正确,因此类型转换是数据清理的重要职能之一。Tableau常用的数据类型及其对应的转化函数如下。

  • 数字(小数) Decimal——FLOAT()函数
  • 数字(整数) Integer——INT()函数
  • 日期 Date ——DATE()函数
  • 日期时间Datetime——DATETIME()函数
  • 字符串String——STR()函数

按照“数据清理优先处理”的基本原则,数据分析应该先确保数据类型准确性,因此类型转换通常优先于其他计算。点击字段切换数据类型,等价于如下的手动转换过程。

DATE([订单日期]) 
DATE(#2022-02-12 18:40:45#)  = 2022/2/12 
DATETIME(#2022-02-12#)     = 2022/2/12 00:00:00

在本书中,数据类型转换视为数据准备的内容,主要发生在数据表明细行之中,因此作为行级别函数介绍。虽然可以对聚合后的数据值做类型转换,通常没有实质意义,而且容易引起差异,比如INT(SUM([销售额]))SUM(INT([销售额])),前者在聚合基础上设置了聚合值的显示格式(四舍五入为整数),而后者则是先把行级别转化为整数再聚合,二者会在计算结果上有一定差异。

过去,Excel把数据格式和自定义格式混杂在一起,一定程度上影响了分析师对数据明细的数据类型的认知;SQL中没有可视化界面,通常无须考虑显示问题。而在Tableau中,“字段的数据类型设置”和“视图显示格式设置”是独立的,一个日期字段,可以在不同工作表中显示为不同样式。

初学者要区分数据类型与数据的显示格式二者的差异。本章的数据类型转换定位为数据清理计算,而非显示设置。


[1]  注意,函数引用字符的引号必须是半角的单引号或者双引号,全角(中文)下会报错。

[2]  更多可以参考国际通用标准icu-project.org中的附录,中英文的结果略有差异,以月和周最为明显。国际时区有多种写法,因此对应z、o、v、V、x、X等多种简化形式。

[3]  本实例可在Tableau官网知识库中查询,这里简化了官方逻辑,并提供了附加的dayofyear方法


了解 喜乐君 的更多信息

订阅后即可通过电子邮件收到最新文章。

《8.3 数据准备类函数(上):字符串函数、日期函数》有1个想法

  1. Pingback: 8.4数据准备类函数(下):“正则表达式” – 喜乐君

评论已关闭。

了解 喜乐君 的更多信息

立即订阅以继续阅读并访问完整档案。

Continue reading