spark-sql中的concat_ws()

源表

 

 

需求：
输出所有id的miss_time,只取小时和分钟，如：14:25，且时刻要顺序展示

预期输出的结果表如下：

 

① miss_time为datatime类型，要展示成hh:mm格式，需用到DATE_FORMAT(data_time, 'HH:mm')

② 取时刻且要展示在一行，spark-sql中没有group_concat(),所以要考虑别的，可使用concat_ws(',',a,b,c)

 

concat_ws() 函数是一个用于连接字符串的函数，其中的 ws 代表"with separator"（带分隔符）。

这个函数接受两个或更多参数：第一个参数是分隔符，它是一个字符串，用于指定在连接其他参数中的字符串时要插入的分隔符；随后的参数是要连接的字符串，可以是列名、字符串字面量或表达式。

concat_ws() 函数在处理可能包含 NULL 值的字符串时特别有用，因为它会自动忽略 NULL 值。

SELECT concat_ws('-', 'Hello', NULL, 'World') AS result;

这将返回 'Hello-World'，而不是 'Hello-NULL-World'。

思路：

需要按照id分组，将所有的miss_time拿出来，然后取HH:mm，再将这些HH:mm使用concat_ws()连接到一起，由于不知道每个id的miss_time有多少个，所以不能一一列举，可将其放到一个集合或列表，可考虑使用以下函数：

① collect_set(miss_time)  将miss_time字段拿出来然后存到set中，但set是无序的，通过concat_ws(',',collect_list(miss_time))连接后，时刻不能按从小到大的顺序展示，故这种不可取

② collect_list(miss_time)  将miss_time字段拿出来然后存到list中，list是有序的，通过concat_ws(',',collect_list(miss_time))连接后，时刻能按从小到大的顺序展示，可以满足需求，所以只需要将collect_list(字段名)中的字段名按照顺序查出，就能满足需求，即需要在子查询中按照id和miss_time进行排序。

 

最终如下：

select a.id,a.day_time,concat_ws(',',collect_list(a.time1)) times
from
(
select id,day_time,miss_time,DATE_FORMAT(miss_time, 'HH:mm') time1 from data_miss_times where day_time='2024-05-25' order by id,data_time
)a
group by a.id,a.day_time
order by a.id,a.day_time;