用另一个时间戳colum压缩列中的值

Question

我有以下数据显示支持票的状态：

编辑：

更简洁和更通用的例子：

STATUS        SEQ_NO
New           1
Open          2
Open          3
Open          4
Queued        5
Open          6
Open          7
Open          8
Completed     9
Completed     10
Completed     11
Closed        12

我想从中提取记录，

STATUS        SEQ_NO
New           1
Open          2
Queued        5
Open          6
Completed     9
Closed        12

原题：

-- SELECT status, start_time FROM events_tab ORDER BY start_time;
STATUS        START_TIME
New           30/09/2014 3:48:10 PM    -- I want this record,
Open          30/09/2014 3:48:10 PM    -- and this,
Open          1/10/2014 10:41:57 AM
Open          4/03/2015 9:59:04 AM
Queued        18/06/2015 1:31:30 PM    -- and this,
Open          20/06/2015 10:10:47 PM   -- and this,
Open          20/06/2015 11:20:11 PM
Open          27/06/2015 1:18:50 PM
Completed     27/06/2015 1:22:08 PM    -- and this,
Completed     28/09/2015 9:31:55 AM
Completed     5/10/2015 11:57:38 AM
Closed        11/01/2016 9:31:26 AM    -- and this.

这些都是发生在每个州的事件。我想做一个状态变化的时间表。

我想压缩这些记录，以便只显示一个组的第一行。但是，请注意，实际上有两组Open状态。因此，我应该获得两个具有Open状态的记录。

基本上，我希望得到以下结果：

STATUS        START_TIME
New           30/09/2014 3:48:10 PM
Open          30/09/2014 3:48:10 PM
Queued        18/06/2015 1:31:30 PM
Open          20/06/2015 10:10:47 PM
Completed     27/06/2015 1:22:08 PM
Closed        11/01/2016 9:31:26 AM

如何使用SQL语句实现这一点？

我试过了，

SELECT status, MIN(start_time)
FROM events_tab
GROUP BY status;

但这不包括Open状态下的多个记录，正如我上面的意图。

Answer 1

您可以使用巴比妥技术来实现这个目标：

WITH your_table AS (SELECT 'New' status, to_date('30/09/2014 03:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('30/09/2014 03:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('1/10/2014 10:41:57 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('4/03/2015 09:59:04 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Queued' status, to_date('18/06/2015 01:31:30 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('20/06/2015 10:10:47 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('20/06/2015 11:20:11 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('27/06/2015 01:18:50 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('27/06/2015 01:22:08 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('28/09/2015 09:31:55 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('5/10/2015 11:57:38 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Closed' status, to_date('11/01/2016 09:31:26 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual)
SELECT status,
       MIN(start_time) start_time
FROM   (SELECT status,
               start_time,
               row_number() OVER (ORDER BY start_time, status) - row_number() OVER (PARTITION BY status ORDER BY start_time, status) grp
        FROM   your_table)
GROUP BY status, grp
ORDER BY start_time, status;

STATUS    START_TIME
--------- -------------------
New       30/09/2014 15:48:10
Open      30/09/2014 15:48:10
Queued    18/06/2015 13:31:30
Open      20/06/2015 22:10:47
Completed 27/06/2015 13:22:08
Closed    11/01/2016 09:31:26

注:由于有不同状态的行具有相同的start_time，为了获得所追求的结果，我将状态添加到order中。我不知道这是否是一个错误，或者多行是否真的可以有相同的日期。

另外，我假设示例中的数据引用了一个“事物”，但是在真实的表中，您可以拥有多个“东西”，每个“事物”都有自己的状态集等等。

在这种情况下，您需要将区分"things“(例如id或event_name等)的列添加到两个row_number()解析函数中。(例如row_number() over (partition by <thing column(s)> order by start_time, status))

Answer 2

您也可以尝试使用用于模式匹配的SQL

WITH tickets(STATUS, START_TIME) AS (
    SELECT 'New', TO_DATE('30/09/2014 3:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('30/09/2014 3:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('1/10/2014 10:41:57 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('4/03/2015 9:59:04 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Queued', TO_DATE('18/06/2015 1:31:30 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('20/06/2015 10:10:47 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('20/06/2015 11:20:11 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('27/06/2015 1:18:50 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('27/06/2015 1:22:08 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('28/09/2015 9:31:55 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('5/10/2015 11:57:38 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Closed', TO_DATE('11/01/2016 9:31:26 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual)
SELECT STATUS, START_TIME
FROM tickets
    MATCH_RECOGNIZE (
        ORDER BY START_TIME
        MEASURES
            START_TIME AS START_TIME,
            STATUS as STATUS
        PATTERN ( CHNG )
        DEFINE
            CHNG AS CHNG.STATUS <> PREV(CHNG.STATUS) OR PREV(CHNG.STATUS) IS NULL
    )


STATUS     START_TIME
========== ====================
New        30.09.2014 15:48:10
Open       30.09.2014 15:48:10
Queued     18.06.2015 13:31:30
Open       20.06.2015 22:10:47
Completed  27.06.2015 13:22:08
Closed     11.01.2016 09:31:26

CHNG.STATUS <> PREV(CHNG.STATUS)匹配STATUS与前一行不同的每一行。PREV(CHNG.STATUS) IS NULL也用于获取第一行。

Answer 3

使用延迟函数，因为您需要跟踪状态的变化：

11.2&fiddle=38a991b698c858f6f0417c7d4c0dc9d3

with cte1 (st,dt) as 
(
select 'New' as st, '30/09/2014 3:48:10 PM' as dt from dual
union all
select 'Open' as st, '30/09/2014 3:48:10 PM' as dt from dual
union all
select 'Open' as st, '20/09/2014 3:48:10 PM' as dt from dual
union all
select 'Qued' as st, '18/06/2015 1:31:30' as dt from dual

)
select st, min(case when st<>prev_order_date then dt else dt end) as d
from
(
SELECT st, dt,
LAG (st,1) OVER (ORDER BY st) AS prev_order_date
FROM cte1
)a
group by st