代码之家 › 专栏 › 技术社区 › sampathsris Rihards Fridrihsons

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

oracle sql

sampathsris Rihards Fridrihsons · 技术社区 · 6 年前

编辑:

更简洁和通用的示例:

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 状态。所以我应该拿两张 正常开放

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

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;

但这并不包括多个记录 正常开放

4 回复 | 直到 6 年前

Boneist 6 年前

你可以用 Tabibitosan

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

注意:由于您有不同状态的行具有相同的开始时间,因此我将状态添加到order by中,以便得到您要查找的结果。我不知道这是不是打字错误,也不知道多行是否真的可以有相同的日期。

另外,我假设示例中的数据引用一个“thing”,但在实际表中,可以有多个“thing”,每个“thing”都有自己的状态集等。

在这种情况下,您需要将区分“事物”的列(如id或event\u name等)添加到这两个列中 row_number() 解析函数(例如 row_number() over (partition by <thing column(s)> order by start_time, status) )

Wernfried Domscheit 6 年前

SQL for Pattern Matching

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

Zaynul Abadin Tuhin 6 年前

使用 row_number 窗口函数

    select STATUS ,START_TIME from 
    (
    select STATUS,START_TIME,
    row_number() over (partition by STATUS,EXTRACT(YEAR FROM START_TIME) order by START_TIME) rn
   from events_tab
    ) t where rn=1

Fahmi 6 年前

在需要跟踪状态变化时使用滞后函数:

https://dbfiddle.uk/?rdbms=oracle_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