SQL将数组的值分段为伪键，当值发生变化时取值

Question

我有一个SQL数组列，它由24个值组成，即一天中的几个小时(上午12点到晚上11点)。

这些值遵循与以下类似的趋势：[1,1,1,1,1,1,3,3,2,2,2,2,3,2,4,1,1,1,3,4,4,1,1,0]当前订单必须保持不变，因为它显示了当天的时间线。

我正试图创建一个列，将该数组分段为起始索引-结束索引：值突破，而数组的值保持不变。例如{1 - 6: 1, 7 - 8: 3, 9 - 12: 2, 13: 3, 14: 2, 15: 4, 16 - 18: 1, 19: 3, 20 - 21: 4, 22 - 23: 1, 24: 0}

我正在通过Presto/Trino运行这个，但如果这是一个更好的解决方案，也可以使用hive。我尝试了此页面的不同功能https://trino.io/docs/current/functions/array.html与if/case语句结合使用，但效果平平。我在循环数据时进行了研究，但不太熟悉其功能以及它如何与列交互，甚至不太熟悉如何将其与当前代码组合。

Answer 1

此代码适用于我所需要的：

with arr as (
select array [2,4,1,2,3,1,1,1,1,3,1,3,2,2,1,2,1,1,1,4,4,2,1,1] as arr
)
, expl as (
select t.val, t.n from arr
cross join unnest(arr) with ordinality as t (val, n)
)
, expl1 as (
select val, n, lead(val) over (order by n) as next_val
from expl
)
, expl2 as (
select val, n
from expl1
where val <> next_val
)
, expl3 as (
select val, case when lag(n) over (order by n) is null then 1 else lag(n) over (order by n) + 1 end as min_time
, case when lead(n) over (order by n) is null then 24 else n end as max_time
from expl2
)
, expl4 as (
select cast(min_time as varchar(2)) || ' - ' || cast(max_time as varchar(2)) || ': ' || cast(val as varchar(2)) as Breakout
from expl3
)
select array_agg(breakout)
from expl4
;