计算TSQL中的非累积值?
问题描述:
给定一个汽车的表格和他们在不同日期(每月的第一个月)读取里程表的数据,我如何编写TSQL(理想情况下,用作SQL Server视图)以返回“增量”值?计算TSQL中的非累积值?
换句话说,我想从Calculate a Running Total in SQL Server的反向操作。
例子:
在此表:
CarId | Date | Mileage --------------------------- 1 1/1/2000 10000 1 2/1/2000 11000 1 3/1/2000 12000 2 1/1/2000 10000 2 2/1/2000 11001 2 3/1/2000 12001 3 1/1/2000 10000 (missing datapoint for (3, 2/1/2000)) 3 3/1/2000 12000
我们会返回类似(细节/边缘情况灵活):
CarId | Date | Delta --------------------------- 1 1/1/2000 10000 1 2/1/2000 1000 1 3/1/2000 1000 2 1/1/2000 10000 2 2/1/2000 1001 2 3/1/2000 1000 3 1/1/2000 10000 3 3/1/2000 2000
答
相同的方法,从@理查德明,但这一个考虑可能的空值包括在原来的问题。
;with cte (rn, id, date, mileage)
as
(
select
row_number() over (partition by id order by id, date)
, id
, date
, mileage
from
cars
where
mileage is not null
)
select
"current".id
, "current".date
, delta = isnull("current".mileage - predecessor.mileage, "current".mileage)
from
cte as "current"
left join cte as predecessor
on "current".id = predecessor.id
and "current".rn - 1 = predecessor.rn
请参阅SQL-Fiddle。
答
这应该为2005年的工作SQL或更高:
WITH cteData As
(
SELECT
CarId,
Date,
Mileage,
ROW_NUMBER() OVER (PARTITION BY CarId ORDER BY Date) As RowNumber
FROM
dbo.Cars
)
SELECT
C.CarId,
C.Date,
CASE
WHEN P.CarId Is Null THEN C.Mileage
ELSE C.Mileage - P.Mileage
END As Delta
FROM
cteData As C
LEFT JOIN cteData As P
ON P.CarId = C.CarId
And P.RowNumber = C.RowNumber - 1
ORDER BY
C.CarId,
C.Date
;
注:这是假定“丢失的数据点为(3,2000年2月1日)”意味着在表3车排不出,2000年2月
答
窗函数大。但是,SQL Server不有你需要到SQL Server 2012中有一个,你有滞后作用:
select t.*,
(Milage - lag(Milage) over (partition by carId order by date)) as Delta
from t
对于早期版本,你可以使用相关子查询:
[顺利上传查询] ,唉。
select t.*, (Mileage - prevMileage) as Delta
from (select t.*,
(select top 1 Mileage from t t2
where t2.carId = t.carId and t2.date < t.date order by desc
) as prevDelta
from t
) t
+0
我很抱歉。每次我尝试更好地格式化查询时,*都会给我一个错误。 – 2013-02-12 20:09:15
答
试图做到这一点没有任何功能2012依存度,光标,while循环,等等
这个工作有些限制范围内 - 即空条目#3车的进入是一个问题因为它:
DECLARE @cars table ([id] int, [date] smalldatetime, [mileage] int)
INSERT INTO @cars ([id], [date], [mileage])
SELECT 1, '1/1/2000', 10000 UNION ALL
SELECT 1, '2/1/2000', 11000 UNION ALL
SELECT 1, '3/1/2000', 12000 UNION ALL
SELECT 2, '1/1/2000', 10000 UNION ALL
SELECT 2, '2/1/2000', 11000 UNION ALL
SELECT 2, '3/1/2000', 12000 UNION ALL
SELECT 3, '1/1/2000', 10000 UNION ALL
SELECT 3, '2/1/2000', NULL UNION ALL
SELECT 3, '3/1/2000', 12000
SELECT t1.id, t1.date, t1.mileage, t2.id, t2.date, t2.mileage, t1.mileage - t2.mileage as miles FROM @cars t1
LEFT JOIN @cars t2
ON t1.id = t2.id
AND t1.date = DATEADD(MONTH,1, t2.date)
我假设“缺少数据点”意味着表中没有一行,不是有一行,但里程是“NULL”。 – 2013-02-13 12:01:50
也许是一个观点。正如您在答案中所评论的那样,我提到了在其他解答之一中看到的基本数据插入。 – Nico 2013-02-13 12:14:49
在我的情况下,特别是,我们确实有空值的记录应该被忽略。 – 2013-02-25 15:52:42