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

Question

示例输入

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

期望的输出

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

尝试查询

我正在尝试使用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

Answer 1

这可以使用窗口函数 min 和 max 来完成。

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

Example

编辑:根据评论

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

编辑:另一种方法是使用 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