使用 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

尝试查询

I am trying to use ROW_NUMBER() and PARTITION BY to get the latest Name and Value but I would also like the earliest and latest 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

原文由 Muhammad Rehan Saeed 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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

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

例子

编辑：根据评论

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

原文由 Vamsi Prabhala 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

如果我理解您的问题，请使用 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

原文由 Aldo López 发布，翻译遵循 CC BY-SA 3.0 许可协议