样例输入
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
last_value(value) over (partition by name order by timestamp)
。 - Chitharanjan Das