SQL 使用 ROW_NUMBER 和 PARTITION BY 获取第一行和最后一行

Getting the First and Last Row Using ROW_NUMBER and PARTITION BY

提问by Muhammad Rehan Saeed

Sample Input

样本输入

Name | Value | Timestamp
-----|-------|-----------------
One  | 1     | 2016-01-01 02:00
Two  | 3     | 2016-01-01 03:00
One  | 2     | 2016-01-02 02:00
Two  | 4     | 2016-01-03 04:00

Desired Output

期望输出

Name | Value | EarliestTimestamp | LatestTimestamp
-----|-------|-------------------|-----------------
One  | 2     | 2016-01-01 02:00  | 2016-01-02 02:00
Two  | 4     | 2016-01-01 03:00  | 2016-01-03 04:00

Attempted Query

尝试查询

I am trying to use ROW_NUMBER()and PARTITION BYto get the latest Nameand Valuebut I would also like the earliest and latest Timestampvalue:

我正在尝试使用ROW_NUMBER()并PARTITION BY获取最新的Name，Value但我也想要最早和最新的Timestamp值：

SELECT
    t.Name,
    t.Value,
    t.????????? AS EarliestTimestamp,
    t.Timestamp AS LatestTimestamp
FROM 
    (SELECT
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP DESC) AS RowNumber,
        Name,
        Value
        Timestamp) t
WHERE t.RowNumber = 1

采纳答案by Vamsi Prabhala

This can be done using window functions minand max.

这可以使用窗口函数min和max.

select distinct name, 
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename

Example

例子

Edit: Based on the comments

编辑：根据评论

select t.name,t.value,t1.earliest,t1.latest
from t 
join (select distinct name, 
      min(tm) over(partition by name) earliest, max(tm) over(partition by name) latest
      from t) t1 on t1.name = t.name and t1.latest = t.tm

Edit: Another approach is using the first_valuewindow function, which would eliminate the need for a sub-query and join.

编辑：另一种方法是使用first_value窗口函数，这将消除对子查询和连接的需要。

select distinct
name, 
first_value(value) over(partition by name order by timestamp desc) as latest_value,
min(tm) over(partition by name) earliest,
-- or first_value can be used 
-- first_value(timestamp) over(partition by name order by timestamp)
max(tm) over(partition by name) latest
-- or first_value can be used
-- first_value(timestamp) over(partition by name order by timestamp desc)
from t

回答by gofr1

You can use MINand MAXfunctions + OUTER APPLY:

您可以使用MIN和MAX函数 + OUTER APPLY：

SELECT  t.Name, 
        p.[Value],
        MIN(t.[Timestamp]) as EarliestTimestamp ,
        MAX(t.[Timestamp]) as LatestTimestamp
FROM Table1 t
OUTER APPLY (SELECT TOP 1 * FROM Table1 WHERE t.Name = Name ORDER BY [Timestamp] DESC) p
GROUP BY t.Name, p.[Value]

Output:

输出：

Name    Value   EarliestTimestamp   LatestTimestamp
One     2       2016-01-01 02:00    2016-01-02 02:00
Two     4       2016-01-01 03:00    2016-01-03 04:00

回答by Chitharanjan Das

Use MIN(Timestamp) OVER (PARTITION BY Name)in addition to the ROW_NUMBER()column, like so:

MIN(Timestamp) OVER (PARTITION BY Name)除了ROW_NUMBER()列之外使用，如下所示：

SELECT
    t.Name,
    t.Value,
    t.EarliestTimestamp AS EarliestTimestamp,
    t.Timestamp AS LatestTimestamp
FROM 
    (SELECT
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP DESC) AS RowNumber,
        MIN(Timestamp) OVER (PARTITION BY Name) AS EarliestTimestamp,
        ^^
        Name,
        Value
        Timestamp) t
WHERE t.RowNumber = 1

回答by NEER

Think simple.

想简单。

select 
    t.Name, 
    MAX(t.Value), 
    MIN(t.Timestamp), 
    MAX(t.Timestamp) 
FROM 
    t
group by 
    t.Name

回答by Aldo López

If I understood your question, use the row_number()function as follows:

如果我理解您的问题，请row_number()按如下方式使用该功能：

SELECT  
    t.Name,  
    t.Value,  
    min(t.Timestamp) Over (Partition by name) As EarliestTimestamp,  
    t.Timestamp AS LatestTimestamp  
FROM   
    (SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP DESC) AS     RowNumber,  
        Name,  
        Value,  
        Timestamp) t  
WHERE t.RowNumber = 1  
Group By t.Name, t.Value, t.TimeStamp

回答by sgeddes

If I'm understanding your question correctly, here's one option using the row_numberfunction twice. Then to get them on the same row, you can use conditional aggregation.

如果我正确理解您的问题，这里有一个选项使用该row_number函数两次。然后要将它们放在同一行上，您可以使用conditional aggregation.

This should be close:

这应该很接近：

SELECT
    t.Name,
    t.Value,
    max(case when t.minrn = 1 then t.timestamp end) AS EarliestTimestamp,
    max(case when t.maxrn = 1 then t.timestamp end) AS LatestTimestamp
FROM 
    (SELECT
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP) as minrn,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TIMESTAMP DESC) as maxrn,
        Name,
        Value
        Timestamp
     FROM YourTable) t
WHERE t.minrn = 1 or t.maxrn = 1
GROUP BY t.Name, t.Value