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 BY
to get the latest Name
and Value
but I would also like the earliest and latest Timestamp
value:
我正在尝试使用ROW_NUMBER()和PARTITION BY来获取最新的名称和值,但是我也希望获得最早和最新的时间戳值:
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
6 个解决方案
#1
3
This can be done using window functions min
and max
.
这可以使用窗口函数min和max来完成。
select distinct name,
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename
例子
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_value
window 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
#2
1
Use MIN(Timestamp) OVER (PARTITION BY Name)
in addition to the ROW_NUMBER()
column, like so:
除了ROW_NUMBER()列之外,在(按名称划分的分区)上使用MIN(时间戳),如下所示:
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
#3
1
You can use MIN and MAX functions + 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
#4
0
If I'm understanding your question correctly, here's one option using the row_number
function twice. Then to get them on the same row, you can use conditional aggregation
.
如果我理解正确,这里有一个选项两次使用row_number函数。然后要将它们放在同一行上,可以使用条件聚合。
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
#5
0
Think simple.
认为简单。
select
t.Name,
MAX(t.Value),
MIN(t.Timestamp),
MAX(t.Timestamp)
FROM
t
group by
t.Name
#6
0
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
#1
3
This can be done using window functions min
and max
.
这可以使用窗口函数min和max来完成。
select distinct name,
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename
例子
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_value
window 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
#2
1
Use MIN(Timestamp) OVER (PARTITION BY Name)
in addition to the ROW_NUMBER()
column, like so:
除了ROW_NUMBER()列之外,在(按名称划分的分区)上使用MIN(时间戳),如下所示:
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
#3
1
You can use MIN and MAX functions + 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
#4
0
If I'm understanding your question correctly, here's one option using the row_number
function twice. Then to get them on the same row, you can use conditional aggregation
.
如果我理解正确,这里有一个选项两次使用row_number函数。然后要将它们放在同一行上,可以使用条件聚合。
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
#5
0
Think simple.
认为简单。
select
t.Name,
MAX(t.Value),
MIN(t.Timestamp),
MAX(t.Timestamp)
FROM
t
group by
t.Name
#6
0
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