使用 lag() 解析函数，用于获取前一个数字并与当前数字进行比较，以检查连续的数字。
演示：

with your_table as (--replace this subquery with your table
select stack(11, --the number of tuples
'fk',4,'2019-01-01 10:10:10.123',
'fk',4,'2019-01-01 10:10:10.124',
'fk',2,'2019-01-01 10:10:10.125',
'4f',1,'2019-01-01 10:10:10.126',
'4f',8,'2019-01-01 10:10:10.127',
'4f',8,'2019-01-01 10:10:10.128',
'h9',7,'2019-01-01 10:10:10.129',
'h9',4,'2019-01-01 10:10:10.130',
'h9',7,'2019-01-01 10:10:10.131',
'h9',7,'2019-01-01 10:10:10.132',
'h9',7,'2019-01-01 10:10:10.133'
) as (id, number, order_ts)
) --replace this subquery with your table

select id, collect_list(case when number = lag_number then null else number end) as aggregate
  from 
      (select id, number, order_ts,
              lag(number) over (partition by id order by order_ts) lag_number
         from your_table 
       distribute by id sort by order_ts
      )s         
  group by id;

结果：

id  aggregate   
4f  [1,8]   
fk  [4,2]   
h9  [7,4,7]