观远 BI

常用函数常见问题

创建于 2022-11-01 / 最近更新于 2023-10-11 / 3521
字体: [默认] [大] [更大]

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
7 人点赞过