需求如下:
将连续在一起的类别,把ranks中的值聚合在一起,并且按照ranks值大小顺序聚合在一起(ranks是我根据时间戳生成的)
生成如下:
代码如下:
SELECT
bodylabel ,regexp_replace(concat_ws(’>’,sort_array(collect_list(concat_ws(’:’,CAST(msgtimestamp AS STRING),ranks)))),’\d+:’,’’) AS new
FROM
(
SELECT
bodylabel
,msgtimestamp
,ranks
,ranks - ROW_NUMBER() OVER(PARTITION BY bodylabel ORDER BY ranks) AS P
FROM tablename
) t0 GROUP BY bodylabel, P