跳到主要内容
版本：6.0.0

常用函数常见问题

1. 如何正确处理跨年周显示错误的问题​

场景：在数据集或者ETL里使用函数 concat(year([日期]),'年第',weekofyear([日期]),'周')
取日期的所在周。跨年周，例如2021-01-01实际上是2020年第53周，但返回结果会变成2021年第53周。

图片.png

【解决方案】：利用当周周四的日期，获取跨年周的年份数据。原理：Spark 使用ISO-8601标准对周进行编号：如果包含1月1日的一周(从周一起算)在新一年中天数超过3天，则为新一年第1周；否则，它是上一年的最后一周。所以跨年这一周的周四所在年份，就是这一周所属的年份。函数语法如下：

concat(year(date_add(trunc([日期],'week'),3)),'年第',weekofyear([日期]),'周')

注意：如果是在卡片编辑的场景，建议直接使用系统自带的日期-周。

2. string格式的时间戳如何转换为timestamp格式​

比如：数据库里存的时间格式为string格式的20200905174444，想要转换为2020-09-05 17:44:44这样的timestamp格式。

【解决方案】：先把该字段改成timestamp格式，然后再用DATE_FORMAT改成想要的格式。

DATE_FORMAT(CAST(UNIX_TIMESTAMP([日期], 'yyyyMMddHHmmss') AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')

说明：

1）以上函数为spark语法，适用于非直连数据集，直连数据集需使用对应数据库的语法，比如oracle的语法为：to_date([日期],'yyyyMMddhh24:mi:ss')；

2）格式选择“日期和时间”，选择“日期”的话，时分秒是不显示的；

3）如果格式为数值型，需先将字段转化为string格式。

3. 为什么使用unbase64解码之后不生效？​

【原因】：unbase64之后返回的是二进制，需要再使用cast转换成为string类型。

【解决方案】：换成cast(unbase64([加密的字符串]) as string)

4. power()函数为什么返回结果都为0？​

【原因】：POW(x,y)，这是POW函数本身的限制。当x<0,y<1（即y为分数或者小数时），出来的结果都为0；另外：如果底数 x 为负数并且指数 y 不是整数，将会导致 domain error 错误。用case when函数可以间接解决这个问题：case when x<0 then -POW(ABS(x),y) else POW(x,y) end

5. 为什么使用sequence函数报错？​

比如报错如下：

image.png

【可能原因】

1）可能是开始日期和结束日志的位置写反了，正确应为：sequence([开始日期],[结束日期],interval 1 month)

2）可能是第三个参数的步长为负数，应该为正数。

3）可能是原始数据中存在脏数据，比如开始日期是大于截止日期的。（可以先把这类数据过滤掉）

6. 如何用now函数得到昨天带时分秒的数据？​

from_unixtime(to_unix_timestamp(now()) - 86400, 'yyyy-MM-dd HH:mm:ss')

7. SUBSTRING这个函数，起始位置是负数的时候没法从后面开始截取是什么原因？​

例如：substring([门店名称],-1,4)无法取到后面往前四位的字符

如果是sqlserver的数据源，可以参考如下：substring([门店名称],Len([门店名称])-1,4)。（直连数据库使用内置函数不一定通用，可使用数据源本身的语法。）

8. 获取列时间数据里最晚的那个时间？​

max([时间字段])over(partition by [字段])

如果是全量取最大，可以写成：max([时间字段])over(partition by null)

9. 用round()函数取整后，会带有一位小数点，怎么去掉？​

可以外面再嵌套层int函数进行取整：int(round())

10. 新建字段的数据计算错误可能是什么原因？​

有null值的情况下计算的结果为null值，建议在ETL输出前加一步“null值替换”

11. string格式的日期怎么转换为date，直接用to _date()的结果是null值?​

如果原来的格式为12/25/19（月/日/年），那么要写成to _date([日期],"MM/dd/yy)，即需要在to _date后面指定原来的格式才能正常转换。

12. 为什么用dense_rank函数取前几名的排名，数据行数会出现问题？​

例如：if(dense_rank () over (partition by null order by[金额 ]desc)>8,'其他',[国家]) 会取到多于8个的国家？

【原因】：dense_rank排名返回的是12234455667788这样，所以＞8的可能不止8个。

【解决方案】：可以使用row_number() over( partition by [字段]  order by [字段])

13. 如何实现按照每年1月1日为当年第一周第一天来计算周数（Oracle标准周）？​

【解决方案】：除了国际通用的ISO week周数计算方法，Oracle还支持另一种标准周计数方法：每年的01/01为第一周的开始，date+6天为一周的结尾，函数用法为：

to_char(date,'ww')

      其他数据库，可以借助已有的日期函数计算出指定日期是当年的第几天，除以7后向上取整即可。获取指定日期在一年中的位置函数一般有以下几个（以下函数Spark都可以使用），不同数据库用法可能会有稍微不同。

dayofyear(date) 
date_format(date,'D')
date_part('doy', date)
extract(doy FROM date)

      数值计算中向上取整的函数一般有 ceiling/ceil，嵌套使用如下,或者也可以使用余数来判断周数。

方法一：ceil(dayofyear(date)/7)

方法二：
写法1：if(date_format(date,'D')%7=0,cast((date_format(date,'D')/7) as int),cast((date_format(date,'D')/7) as int)+1);
写法2：case when cast(EXTRACT(DOY FROM date) as int)%7 = 0
then cast((cast(EXTRACT(DOY FROM date) as int)/7) as int)
else cast((cast(EXTRACT(DOY FROM date) as int)/7) as int)+1
end