Oracle最新值按ID分组

ztmd8pv5  于 2023-04-20  发布在  Oracle
关注(0)|答案(3)|浏览(132)

假设我有一个像下面这样的表:

id | start_value | end_value | date
1    null            null      05-APR-23
1    null            5         09-APR-23
1    5               null      15-APR-23
1    null            8         16-APR-23
2    1               null      05-APR-23
2    null            9         09-APR-23
2    9               null      13-APR-23
2    null            -1        16-APR-23.

我想查询最新的非空开始和结束值的id如下?我使用的是Oracle数据库。

id | start_value | end_value | start_date | end_date
1    5               8         15-APR-23    16-APR-23
2    9               -1        13-APR-23    16-APR-23
uttx8gqw

uttx8gqw1#

您可以将LAST_VALUE解析函数与IGNORE NULLS选项一起使用,然后过滤以仅包括最近的行:

SELECT id,
       start_value,
       end_value,
       start_date,
       end_date
FROM   (
  SELECT id,
         LAST_VALUE(start_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_value,
         LAST_VALUE(end_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_value,
         LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_date,
         LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_date,
         ROW_NUMBER()
            OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rn
  FROM   table_name
)
WHERE  rn = 1

其中,对于样本数据:

CREATE TABLE table_name (id, start_value, end_value, "DATE") AS
SELECT 1, null, null, DATE '2023-04-05' FROM DUAL UNION ALL
SELECT 1, null, 5,    DATE '2023-04-09' FROM DUAL UNION ALL
SELECT 1, 5,    null, DATE '2023-04-15' FROM DUAL UNION ALL
SELECT 1, null, 8,    DATE '2023-04-16' FROM DUAL UNION ALL
SELECT 2, 1,    null, DATE '2023-04-05' FROM DUAL UNION ALL
SELECT 2, null, 9,    DATE '2023-04-09' FROM DUAL UNION ALL
SELECT 2, 9,    null, DATE '2023-04-13' FROM DUAL UNION ALL
SELECT 2, null, -1,   DATE '2023-04-16' FROM DUAL;

输出:
| ID|开始值|END_VALUE|开始日期|结束日期|
| --------------|--------------|--------------|--------------|--------------|
| 1|五|八|2019 -04-15 00:00:00|2019 -04-16 00:00:00|
| 二|九|-1|2019 -04-13 00:00:00|2019 -04-16 00:00:00|
fiddle

mzsu5hc0

mzsu5hc02#

有很多可能性,使用last_value(),使用row_number()两次,然后按id和max分组,例如,为每个id取last no empty“start row”,并将其与last no empty“end row”连接:

select id, start_value, end_value, start_date, end_date
from (
    select id, start_value, dt start_date from t 
    where start_value is not null 
    order by row_number() over (partition by id order by dt desc)  
    fetch first 1 rows with ties)
natural join (
    select id, end_value, dt end_date from t 
    where end_value is not null 
    order by row_number() over (partition by id order by dt desc) 
    fetch first 1 rows with ties)

dbfiddle

iszxjhcz

iszxjhcz3#

其中一个选项是使用LEAD()和LAG()分析函数,如下所示:

WITH        --  Sample data
    tbl (ID, START_VALUE, END_VALUE, A_DATE) AS
        (   Select 1, Null, Null, To_Date('05-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, Null, 5,    To_Date('09-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, 5,      Null, To_Date('15-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, Null, 8,    To_Date('16-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, 1,    Null, To_Date('05-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, Null, 9,    To_Date('09-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, 9,    Null, To_Date('13-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, Null, -1,   To_Date('16-APR-23', 'dd-MON-yy') From Dual 
        )
--
--  Main SQL
Select  ID, Max(STARTS) "START_VALUE", Max(ENDS) "END_VALUE", START_DATE, END_DATE
From    ( Select    ID, ROW_NUMBER() OVER(Partition By ID Order By A_DATE DESC) "RN",
                    CASE  WHEN LEAD(ID) OVER(Partition By ID Order By A_DATE DESC) = ID 
                        THEN LEAD(START_VALUE) OVER(Partition By ID Order By A_DATE DESC) 
                    END "STARTS",
                    CASE  WHEN LAG(ID) OVER(Partition By ID Order By A_DATE DESC) = ID
                        THEN LAG(END_VALUE) OVER(Partition By ID Order By A_DATE DESC) 
                    END "ENDS",
                    MAX(CASE WHEN START_VALUE Is Not Null THEN A_DATE END) OVER(Partition By ID) "START_DATE",
                    MAX(CASE WHEN END_VALUE Is Not Null THEN A_DATE END) OVER(Partition By ID) "END_DATE"
          From  tbl
        )
Where RN <= 2
Group By ID, START_DATE, END_DATE
Order By ID
--
--  R e s u l t:
        ID START_VALUE  END_VALUE START_DATE END_DATE 
---------- ----------- ---------- ---------- ---------
         1           5          8  15-APR-23 16-APR-23
         2           9         -1  13-APR-23 16-APR-23

相关问题