透视转换oracle,如何在Oracle SQL 19中进行透视

我在Oracle SQL 19中有下表:

+---------+-----------+---------------------+

| job_num | job_stage | timestamp |

+---------+-----------+---------------------+

| job_1 | waiting | 2020-01-28 11:51:00 |

| job_1 | waiting | 2020-01-28 11:52:00 |

| job_1 | waiting | 2020-01-28 11:53:00 |

| job_1 | running | 2020-01-28 11:54:00 |

| job_1 | running | 2020-01-28 11:55:00 |

| job_1 | running | 2020-01-28 11:56:00 |

| job_1 | running | 2020-01-28 11:57:00 |

| job_1 | finishing | 2020-01-28 11:58:00 |

| job_1 | finishing | 2020-01-28 11:59:00 |

| job_2 | waiting | 2020-01-28 11:52:00 |

| job_2 | waiting | 2020-01-28 11:53:00 |

| job_2 | waiting | 2020-01-28 11:54:00 |

| job_2 | waiting | 2020-01-28 11:55:00 |

| job_2 | waiting | 2020-01-28 11:56:00 |

| job_2 | running | 2020-01-28 11:57:00 |

| job_2 | running | 2020-01-28 11:58:00 |

| job_2 | running | 2020-01-28 11:59:00 |

| job_2 | running | 2020-01-28 12:00:00 |

| job_2 | finishing | 2020-01-28 12:01:00 |

| job_2 | finishing | 2020-01-28 12:02:00 |

| job_2 | finishing | 2020-01-28 12:03:00 |

| job_2 | finishing | 2020-01-28 12:04:00 |

+---------+-----------+---------------------+

我想用以下方式来调整它:

+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

| job_num | min_waiting | max_waiting | min_running | max_running | min_finishing | max_finishing |

+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

| job_1 | 2020-01-28 11:51:00 | 2020-01-28 11:53:00 | 2020-01-28 11:54:00 | 2020-01-28 11:57:00 | 2020-01-28 11:58:00 | 2020-01-28 11:59:00 |

| job_2 | 2020-01-28 11:52:00 | 2020-01-28 11:56:00 | 2020-01-28 11:57:00 | 2020-01-28 12:00:00 | 2020-01-28 12:01:00 | 2020-01-28 12:04:00 |

+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

更具体地说,每一个

job_num

,每个

job_stage

,我想得到最小时间戳和最大时间戳。然后,我想显示每种方法的最小和最大时间戳

工作编号

有人能告诉我如何在oraclesql19中高效地实现这一点吗?任何帮助将不胜感激!

我有以下SQL表供您参考:

未驱动表如下:

with t1 as (

select 'job_1' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:51', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:52', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:53', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:54', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:55', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:56', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:57', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 11:58', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_1' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 11:59', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:52', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:53', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:54', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:55', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'waiting' as job_stage

, to_date('1/28/2020 11:56', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:57', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:58', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'running' as job_stage

, to_date('1/28/2020 11:59', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'running' as job_stage

, to_date('1/28/2020 12:00', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 12:01', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 12:02', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 12:03', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

union

select 'job_2' as job_num

, 'finishing' as job_stage

, to_date('1/28/2020 12:04', 'MM/DD/YYYY HH24:MI') as timestamp

from dual

)

select *

from t1

order by job_num

, timestamp

数据透视表如下:

with t1 as (

select 'job_1' as job_num

, to_date('1/28/2020 11:51', 'MM/DD/YYYY HH24:MI') as min_waiting

, to_date('1/28/2020 11:53', 'MM/DD/YYYY HH24:MI') as max_waiting

, to_date('1/28/2020 11:54', 'MM/DD/YYYY HH24:MI') as min_running

, to_date('1/28/2020 11:57', 'MM/DD/YYYY HH24:MI') as max_running

, to_date('1/28/2020 11:58', 'MM/DD/YYYY HH24:MI') as min_finishing

, to_date('1/28/2020 11:59', 'MM/DD/YYYY HH24:MI') as max_finishing

from dual

union

select 'job_2' as job_num

, to_date('1/28/2020 11:52', 'MM/DD/YYYY HH24:MI') as min_waiting

, to_date('1/28/2020 11:56', 'MM/DD/YYYY HH24:MI') as max_waiting

, to_date('1/28/2020 11:57', 'MM/DD/YYYY HH24:MI') as min_running

, to_date('1/28/2020 12:00', 'MM/DD/YYYY HH24:MI') as max_running

, to_date('1/28/2020 12:01', 'MM/DD/YYYY HH24:MI') as min_finishing

, to_date('1/28/2020 12:04', 'MM/DD/YYYY HH24:MI') as max_finishing

from dual

)

select *

from t1

order by job_num