我有一个制造操作的数据集。在流程的某些部分，可能有一些步骤可以并行完成，这意味着它们可以按任何顺序完成，甚至可能重叠。例如，在下面的示例中，订单1001的步骤2、3和4可以按任何顺序进行。Type=C表示并行操作。
因为历史数据可以显示以任何顺序完成的并行步骤，所以我想将C步骤的每个块视为一行，并使用该组中的最小开始时间和最大结束时间，如所需表格所示。
如何在SQL中实现这一点？特别是HANASQL，但任何相关的示例都会有所帮助。
当前：
+-----------+------+------+---------------------+---------------------+
| order_nbr | step | type | start | end |
+-----------+------+------+---------------------+---------------------+
| 1001 | 1 | P | 2021-01-01 00:00:00 | 2021-01-01 09:00:00 |
| 1001 | 2 | C | 2021-01-04 03:00:00 | 2021-01-04 06:00:00 |
| 1001 | 3 | C | 2021-01-03 07:00:00 | 2021-01-03 08:00:00 |
| 1001 | 4 | C | 2021-01-05 10:00:00 | 2021-01-05 15:00:00 |
| 1001 | 5 | Z | 2021-01-06 00:00:00 | 2021-01-06 06:00:00 |
| 1001 | 6 | Z | 2021-01-06 16:00:00 | 2021-01-06 20:00:00 |
| 1001 | 7 | C | 2021-01-07 08:00:00 | 2021-01-07 09:00:00 |
| 1001 | 8 | C | 2021-01-07 10:00:00 | 2021-01-07 12:00:00 |
| 1002 | 1 | P | 2021-01-04 08:00:00 | 2021-01-04 16:00:00 |
+-----------+------+------+---------------------+---------------------+
所需：
+-----------+---------+------+---------------------+---------------------+
| order_nbr | step | type | start | end |
+-----------+---------+------+---------------------+---------------------+
| 1001 | 1 | P | 2021-01-01 00:00:00 | 2021-01-01 09:00:00 |
| 1001 | 2, 3, 4 | C | 2021-01-03 07:00:00 | 2021-01-05 15:00:00 |
| 1001 | 5 | Z | 2021-01-06 00:00:00 | 2021-01-06 06:00:00 |
| 1001 | 6 | Z | 2021-01-06 16:00:00 | 2021-01-06 20:00:00 |
| 1001 | 7, 8 | C | 2021-01-07 08:00:00 | 2021-01-07 12:00:00 |
| 1002 | 1 | P | 2021-01-04 08:00:00 | 2021-01-04 16:00:00 |
+-----------+---------+------+---------------------+---------------------+
正如前面所评论的，这是一个缺口和孤岛问题，因此您可以查看链接的文章来深入了解该问题。但是，在找到孤岛后，您需要有条件地对数据进行分组(在孤岛中，您只需要折叠type = 'C'项。
这是代码：
with s as (
select '1001' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-01 00:00:00' as start_ts, timestamp '2021-01-01 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '2' as step, 'C' as ex_type, timestamp '2021-01-04 03:00:00' as start_ts, timestamp '2021-01-04 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '3' as step, 'C' as ex_type, timestamp '2021-01-03 07:00:00' as start_ts, timestamp '2021-01-03 08:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '4' as step, 'C' as ex_type, timestamp '2021-01-05 10:00:00' as start_ts, timestamp '2021-01-05 15:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '5' as step, 'Z' as ex_type, timestamp '2021-01-06 00:00:00' as start_ts, timestamp '2021-01-06 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '6' as step, 'Z' as ex_type, timestamp '2021-01-06 16:00:00' as start_ts, timestamp '2021-01-06 20:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '7' as step, 'C' as ex_type, timestamp '2021-01-07 08:00:00' as start_ts, timestamp '2021-01-07 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '8' as step, 'C' as ex_type, timestamp '2021-01-07 10:00:00' as start_ts, timestamp '2021-01-07 12:00:00' as end_ts from dummy union all
select '1002' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-04 08:00:00' as start_ts, timestamp '2021-01-04 16:00:00' as end_ts from dummy
)
, num as (
select
s.*
/*Find consecutive rows on ex_type field*/
, row_number() over(partition by order_nbr order by start_ts asc) as r1
, row_number() over(partition by order_nbr, ex_type order by start_ts asc) as r2
from s
)
select
order_nbr
, ex_type
, min(start_ts) as start_ts
, max(end_ts) as end_ts
, string_agg(step, ',' order by start_ts asc) as steps
from num
group by
order_nbr
, ex_type
, case
/*For C use group number, for others - use original row number not to collapse them*/
when ex_type = 'C'
then r1 - r2
else r1
end
order by
order_nbr
, start_ts asc
这是db<gt；将PostgreSQL作为HANA语法相同的平台，用于相关函数。
在使用由astentx提供的答案之前，以下是我的方法，该方法为非C行和C类型行组创建一个id。
with s as (
select '1001' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-01 00:00:00' as start_ts, timestamp '2021-01-01 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '2' as step, 'C' as ex_type, timestamp '2021-01-04 03:00:00' as start_ts, timestamp '2021-01-04 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '3' as step, 'C' as ex_type, timestamp '2021-01-03 07:00:00' as start_ts, timestamp '2021-01-03 08:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '4' as step, 'C' as ex_type, timestamp '2021-01-05 10:00:00' as start_ts, timestamp '2021-01-05 15:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '5' as step, 'Z' as ex_type, timestamp '2021-01-06 00:00:00' as start_ts, timestamp '2021-01-06 06:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '6' as step, 'Z' as ex_type, timestamp '2021-01-06 16:00:00' as start_ts, timestamp '2021-01-06 20:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '7' as step, 'C' as ex_type, timestamp '2021-01-07 08:00:00' as start_ts, timestamp '2021-01-07 09:00:00' as end_ts from dummy union all
select '1001' as order_nbr, '8' as step, 'C' as ex_type, timestamp '2021-01-07 10:00:00' as start_ts, timestamp '2021-01-07 12:00:00' as end_ts from dummy union all
select '1002' as order_nbr, '1' as step, 'P' as ex_type, timestamp '2021-01-04 08:00:00' as start_ts, timestamp '2021-01-04 16:00:00' as end_ts from dummy
)
select
b.order_nbr,
b.ex_type,
min(b.start_ts) as start_ts,
max(b.end_ts) as end_ts,
string_agg(b.step, ',') as steps
from
(select
a.order_nbr,
a.step,
a.ex_type,
a.start_ts,
a.end_ts,
sum(a.inc) over (order by a.order_nbr asc, a.start_ts asc) as id
from
(select
s.order_nbr,
s.step,
s.ex_type,
s.start_ts,
s.end_ts,
case
when s.ex_type = 'C' and s.ex_type = lag(s.ex_type) over (partition by s.order_nbr order by s.start_ts)
then 0
else 1
end as inc
from
s
order by
s.order_nbr asc,
s.start_ts asc
) as a
) as b
group by
b.order_nbr,
b.ex_type,
b.id
order by
b.order_nbr asc,
min(b.start_ts) asc