SQL-如何从每个ID的上一个日期中获得最新和最新的信息

Question

我有一个表：

id, date, value
a,  5/22/2019 11:00, 2211
b,  5/22/2019 11:00, 2211
c,  5/22/2019 11:00, 2211
a,  5/22/2019 10:00, 2210
b,  5/22/2019 10:00, 2210
c,  5/22/2019 10:00, 2210
a,  5/21/2019 17:00, 2117
b,  5/21/2019 17:00, 2117
c,  5/21/2019 17:00, 2117
a,  5/21/2019 15:00, 2115
b,  5/21/2019 15:00, 2115
c,  5/21/2019 15:00, 2115

希望从上一个日期获得当前(最新(和最新值的ID和值：

id, latest, previous_date_close
a,   2211,    2117
b,   2211,    2117
c,   2211,    2117

到目前为止，我有一些零件(当前和上一行(，但需要以前的日期和最新日期：

：

with cte as
(
select 
    id, value,
    lag(value, 1, 0) over (partition by id order by date ) as "date - 1",
    row_number() over(partition by id order by date desc) rn
from 
    table1
) select * from cte where rn=1

谢谢。

Answer 1

hmmm。。。我认为两个级别的窗口功能可以完成您想要的工作：

with cte as (
      select id, value,
             lag(value, 1, 0) over (partition by id order by date ) as "date - 1",
             row_number() over (partition by id order by date desc) as rn
      from (select t1.*,
                   row_number() over (partition by id, to_date(date) order by date desc) as seqnum_id_day
            from table1 t1
           ) t1
     where seqnum_id_day = 1
    )
select *
from cte
where rn = 1;

Answer 2

您可以使用两个CTE进行。

WITH myCte AS (
SELECT 
    id, 
    value,
    row_number() over(PARTITION BY id ORDER BY date DESC) rn
FROM 
    table1
),
myOtherCte AS (
SELECT
    a.id,
    a.svalue,
    row_number() over(PARTITION BY a.id ORDER BY a.date DESC) rn
FROM
    table1 a
WHERE
    CAST(a.date AS DATE) < (SELECT MAX(CAST(b.date AS DATE))
                                                   FROM table1 b
                                                   WHERE a.id = b.id)
)   
SELECT
  t1.id,
  t1.value,
  t2.value
FROM myCte t1
JOIN myOtherCte t2 ON t1.id = t2.id
WHERE t1.rn = 1
AND t2.rn = 1

这是对此中的演示：SQL小提琴