1. 概述
观远BI非直连场景支持 Spark SQL 函数，在正常情况下足够满足用户的模板制作需求，但是在一些特殊领域或场景，Spark函数无法实现或者实现比较麻烦，在这种情况下，观远BI预封装了部分自定义函数（User-Defined Functions，简称UDF）来作为补充，满足更多场景更复杂的数据处理和分析要求。
2. 使用范围
- 仅支持Guan-index抽取数据集、文件数据集等所有非直连数据集的卡片、ETL场景。
3. 常用UDF函数
类型 | 函数 | 用途 | 示例 | 结果 |
日期转换 | weekyear(日期) | 返回ISO 8601周编号年。 相当于spark组合函数：extract(yearofweek from [日期]) | weekyear('2023-01-01') | 2022 |
weekOfYearWithYear (日期) | 把日期转换为文本类型的ISO 8601年周。 相当于spark组合函数：concat(year(date_add(trunc([日期],'week'),3)),'年第',weekofyear([日期]),'周') | weekOfYearWithYear('2023-01-01'） | 2022年第52周 |
日期时间计算 | minutediff(结束时间,开始时间) | 计算分钟差。 相当于 (unix_timestamp(结束时间)-unix_timestamp(结束时间))/60 | MINUTEDIFF('2023-07-02 14:28:53', '2023-07-02 12:00:16') | 148 |
seconddiff(结束时间,开始时间) | 计算秒差。 相当于 unix_timestamp(结束时间)-unix_timestamp(结束时间) | SECONDDIFF('2023-07-02 14:28:53', '2023-07-02 12:00:16') | 8917 |
lasting_days_to_date(日期数组,查询日期) | 返回日期字段内小于等于指定日期的持续天数。 可用于计算库存缺货天数等。 | lasting_days_to_date(collect_list([日期]), TO_DATE('2024-01-01')) | 1 |
数值计算 | raw_xirr(collect_list( map(日期,金额))) | 不一定定期发生的现金流的内部收益率。 聚合度量。 | raw_xirr(collect_list(map([日期],[金额]))) | 0.079 |
array_sum(数值数组) | 对数组里的数值求和 | array_sum(array(1,2,3)) | 6 |
文本处理 | toPinYin(中文文本) | 返回汉字对应的拼音字符（大写）。 | toPinYin('重庆市') | CHONGQINGSHI |
案例：
lasting_days_to_date：如何计算连续发生天数
array_sum：筛选器模糊匹配实现方法
4. 时序UDF函数
观远封装好的时序UDF函数，一般用于制作拉链表，用来解决大数据量存储和查询问题，既可以保留历史数据，又可以节省空间（大幅缩减数据行数）。
类型 | 函数 | 参数 | 输出数据类型 | 说明 |
构建查询表字段 (ETL用) | date_range_build_v2 | (struct_array) | dateRangStruct | 无压缩时间序列值。参数中 struct_array 数组，需要通过 collect_list(struct(date, value)) 等函数来聚合得到。 |
date_text_range_build_v2 | (struct_array) | dateTextRangStruct |
date_range_zipper | (dateRangStruct) | dateRangStruct | 相邻等值压缩的时间序列值。对缺省日期添加一条值为 null 的记录并压缩（若有缺省日期且前一个日期值不为 null，则添加与前一个日期相邻日期且值为 null 的记录，当前日期是否压缩与新添记录做比较；同时对相邻等值记录进行去重，仅保留第一条）。 |
date_text_range_zipper | (dateTextRangStruct) | dateTextRangStruct |
date_range_merge | (dateRangStruct_1, dateRangStruct_2) | dateRangStruct | 合并时间序列，合并后不压缩相邻等值。 |
date_text_range_merge | (dateTextRangStruct_1, dateTextRangStruct_2) | dateTextRangStruct |
date_range_period_to_date | (dateRangStruct, period:string) | dateRangStruct | period: 'week','month','year' 1. 原始日期保留，值按指定周期累加；2. 为缺省周期补齐期初日期，值补零；同时对补齐的相邻等值进行压缩，仅保留第一条。适用于周期内累计值的计算和查找，例如周/月/年累计销售额。 |
查找数据 (卡片用) | date_range_lookup | (dateRangStruct, lookup_date) | Number value | 滚动向上查找，即无对应日期数据就向前查找最近日期的数据，适合库存类数据或者会员状态的查找。 |
date_text_range_lookup | (dateTextRangStruct, lookup_date) | String value |
date_range_get | (dateRangStruct, lookup_date) | Number value | 精确查找，适合销售类数据查找；找不到则返回null。 |
date_text_range_get | (dateTextRangStruct, lookup_date) | String value |
具体用法和案例请参考： 时序UDF函数介绍 用时序UDF制作历史拉链表。
5. 注意事项：
- UDF函数是封装在BI后端代码里，暂时不支持自由调整或者新建；如果使用过程中出现异常报错，建议联系观远技术支持团队，或者用Spark函数代替UDF函数计算。
- 直连数据集场景，如果用户有自定义函数需求，可以在自己的数据库创建自定义函数并维护，然后在观远BI里创建数据账户，那么使用该数据账号的数据集和卡片即可以使用自定义函数。
