sql-server - 如何在 MS SQL 中将 last_value 与 group by 结合使用？

Question

我有这样的表：

name    |   timeStamp   |   previousValue   |   newValue    
Mark    |   13.12.2020  |       123         |   155     
Mark    |   12.12.2020  |       123         |   12      
Tom     |   14.12.2020  |       123         |   534     
Mark    |   12.12.2020  |       123         |   31      
Tom     |   11.12.2020  |       123         |   84      
Mark    |   19.12.2020  |       123         |   33      
Mark    |   17.12.2020  |       123         |   96      
John    |   22.12.2020  |       123         |   69      
John    |   19.12.2020  |       123         |   33      

我想混合 last_value、count (*) 和 group 来得到这个结果：

name    |   count   |   lastValue   
Mark    |       5   |       33
Tom     |       2   |       534
John    |       2   |       69

这部分：

select name, count(*) from table group by name  

返回表：

name    |   count   
Mark    |       5   
Tom     |       2   
John    |       2   

..但我必须为每个名称添加最后一个值。

怎么做？最好的祝福！

标签： sql-servertsqlgroup-by

Answer 1

LAST_VALUE是一个窗口函数，因此您需要先获取该值，然后聚合：

WITH CTE AS(
    SELECT [name],
           [timeStamp], --This is a poor choice for a column's name. timestamp is a (deprecated) synonym of rowversion, and a rowversion is not a date and time value
           previousValue,
           newValue,
           LAST_VALUE(newValue) OVER (PARTITION BY [name] ORDER BY [timeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastValue
    FROM dbo.YourTable)
SELECT [Name],
       COUNT(*) AS [count],
       lastValue
FROM CTE
GROUP BY [Name],
         lastValue;