每行值之差 - Sum Error [英] The difference between each row value - Sum Error
问题描述
我的主要查询是在下面的帖子中解决的。
我有一个问题,
id device_id时间阅读shift_id
150323 3 2013-02-25 15:22:01.273 999948.00 43
150324 1 2013-02-25 15:22:01.423 999962.00 43
150325 3 2013-02-25 15:22:01.463 999966.00 43
150326 1 2013-02-25 15:22: 01.610 999979.00 43
150327 3 2013-02-25 15:22:01.650 999983.00 43
150328 1 2013-02-25 15:22:01.810 999997.00 43
对于上述情况,我得到的结果是,但当以下情况下,根据提供给我的解决方案,读数正确,但我想增加差额...
id device_id时间读数shift_id
150322 1 2013-02-25 15:22:01.233 999945.00 43
150323 3 2013-02-25 15:22:01.273 999948.00 43
150324 1 2013-02-25 15: 22:01.423 999962.00 43
150325 3 2013-02-25 15:22:01.463 999966.00 43
150326 1 2013-02-25 15:22:01.610 999979.00 43
150327 3 2013-02 -25 15:22:01.650 999983.00 43
150328 1 2013-02-25 15:22:01.810 999997.00 43
150329 3 2013-02-25 15:22:01.853 1.00 43
150330 1 2013-02-25 15:22:02.000 15.00 43
150331 3 2013-02-25 15:22:02.040 18.00 43
150332 1 2013-02-25 15:22:02.187 32.00 43
以上数据的结果如下
日班时间设备ID计数1
2013-02-25第2次11 1 39145.00
2013-02-25第2次11 3 39148.00
2013-02 -25第二次12 1 248022.00
2013-02-25第二次12 3 248022.00
2013-02-25第二次13 1 389195.00
2013-02-25第二次13 3 389197.00
2013 -02- 25 2nd 14 1 201855.00
2013-02-25 2nd 14 3 201854.00
2013-02-25 2nd 15 1 -877108.00
2013-02-25 2nd 15 3 -877112.00
这两个负值应该是正值,价值应该是
122892 - 122889。 / p>
注意:读数的最大值将始终为999999.00,之后我将再次从0开始到999999.00再次从0开始。这基本上是计数设备,它计数。下面是查询被建议在堆栈溢出,我已根据需要修改了一下。
declare @todate datetime;
将@total声明为十进制(18,2);
SET @fromdate ='2/23/2013 10:51:17 AM';
SET @todate ='2/25/2013 12:10:56 PM';
WITH cte AS
(
SELECT *,
ROW_NUMBER()OVER(PARTITION BY device_id ORDER BY [time])as NId
FROM [dbo]。[readings (r.time作为日期)> = CAST(@fromdate作为日期)和
cast(r.time作为日期)< = CAST(@todate作为日期)和
r.device_id<> 5
)
SELECT CAST(c1。[time] as DATE)as [Day],
(
从变换中选择s.name s其中s.id = c1.shift_id
)作为转换,
DATEPART(小时,c1.time)作为[小时],
c1.device_id作为[设备ID],$ b $ (c2.reading - ISNULL(c1.reading,c2.reading))< 0 then
sum(c1.reading - ISNULL(c2.reading,c1.reading) )
else
sum(c2.reading - ISNULL(c1.reading,c2.reading))
end
)AS Count1
FROM cte c1 left JOIN cte c2 ON c1.device_id = c2.device_id AND
c1.NId + 1 = c2.NId
cast by cast(c1 .time作为DATE),c1.shift_id,DATEPART(小时,c1.time),c1.device_id
通过cast命令(c1.time作为DATE),c1.shift_id,DATEPART(小时,c1.time), c1.device_id
你基本需要的是暂时假装 c2.reading
在达到1,000,000后没有换行,只有当 c2.reading< c1.reading
。也就是说,在这一点上,您需要将 c2.reading
增加1,000,000,然后减去 c1.reading
。当 c2.reading> = c1.reading
时,查询应计算正常差异,即减去 c1.reading
原始(非增加) c2.reading
值。
实现该逻辑的一种方法可以直接做这样的事情:
SUM(
CASE WHEN c2.reading< c1.reading THEN 1000000 ELSE 0 END
+ c2.reading
- ISNULL(c1.reading,c2.reading)
)AS Count1
然而,也有不同的方法。
您的阅读值,其中两个也不能超过100万。因此,您可以自由应用 modulo 1,000,000以获得积极的区别,并且这会给你相同的差异:
pre $ code> d mod 1,000,000 = d
此外,将1,000,000的倍数加上正数差不会影响模1,000,000的结果,因为根据模运算的分布性, p>
(d + 1,000,000 * n)mod 1,000,000 =
= d mod 1,000,000 +(1,000,000 * n)mod 1,000,000
第一个加数 d mod 1,000,000
code> d ,第二个,(1,000,000 * n)mod 1,000,000
得到0, d + 0 = d
。
另一方面,加上1,000,000到负差会给我们一个正确的正面差异。 所以,总结一下, 加上1,000,000到负的差值给我们一个(正确的)正差异, 一个正模差额100,000产生相同的正面差异,并且加上1,000,000到正差异不会影响模1,000,000的结果。 考虑到所有这些因素,我们可以通过以下通用表达式来计算单个差异: 其中 My main query was solved at the following post TSQL - Get the difference between each row value I have one problem of summing the reading values between each row. for the above scenario i am getting the results but when following is the case the readings are coming correct according to the solution provided to me, but i want to increment the difference ... The result of the above readings is as below Those two values in negative should be in positive and the value should be something like
122892 - 122889. Note : The max value for reading will always be 999999.00 and after that i will again start from 0 upto 999999.00 and again start from 0. This is basically the counting device which counts. Below is the query which was suggested to at stack overflow and i have modified a bit according to my need
What you essentially need is to pretend temporarily that One way to achieve that logic would be to do something as straightforward as this: However, there's also a different approach. Your reading values, and, as a consequence, differences between any two of them as well, can never exceed 1,000,000. Therefore, you can freely apply modulo 1,000,000 to a positive difference and that'll give you the same difference back: Moreover, adding multiples of 1,000,000 to a positive difference won't affect the result of modulo 1,000,000 because, according to the distributiveness of the modulo operation, The first summand, On the other hand, adding 1,000,000 to a negative difference would give us a correct positive difference. So, to sum up, adding 1,000,000 to a negative difference gives us a (correct) positive difference, a positive difference modulo 1,000,000 yields the same positive difference, and adding 1,000,000 to a positive difference doesn't affect the result of modulo 1,000,000. Taking all that into account, we can end up with the following universal expression to calculate a single difference: where Put the expression into
这篇关于每行值之差 - Sum Error的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
$ b
(1000000 + c2.reading - ISNULL(c1.reading,c2.reading))%1000000
%
是模运算符 Transact-SQL 。
SUM
以获得相应的汇总值:
SUM((c2。阅读+ 1000000 - ISNULL(c1.reading,c2.reading))%1000000)AS Count1
id device_id time reading shift_id
150323 3 2013-02-25 15:22:01.273 999948.00 43
150324 1 2013-02-25 15:22:01.423 999962.00 43
150325 3 2013-02-25 15:22:01.463 999966.00 43
150326 1 2013-02-25 15:22:01.610 999979.00 43
150327 3 2013-02-25 15:22:01.650 999983.00 43
150328 1 2013-02-25 15:22:01.810 999997.00 43
id device_id time reading shift_id
150322 1 2013-02-25 15:22:01.233 999945.00 43
150323 3 2013-02-25 15:22:01.273 999948.00 43
150324 1 2013-02-25 15:22:01.423 999962.00 43
150325 3 2013-02-25 15:22:01.463 999966.00 43
150326 1 2013-02-25 15:22:01.610 999979.00 43
150327 3 2013-02-25 15:22:01.650 999983.00 43
150328 1 2013-02-25 15:22:01.810 999997.00 43
150329 3 2013-02-25 15:22:01.853 1.00 43
150330 1 2013-02-25 15:22:02.000 15.00 43
150331 3 2013-02-25 15:22:02.040 18.00 43
150332 1 2013-02-25 15:22:02.187 32.00 43
Day Shifts Hour Device ID Count1
2013-02-25 2nd 11 1 39145.00
2013-02-25 2nd 11 3 39148.00
2013-02-25 2nd 12 1 248022.00
2013-02-25 2nd 12 3 248022.00
2013-02-25 2nd 13 1 389195.00
2013-02-25 2nd 13 3 389197.00
2013-02-25 2nd 14 1 201855.00
2013-02-25 2nd 14 3 201854.00
2013-02-25 2nd 15 1 -877108.00
2013-02-25 2nd 15 3 -877112.00
declare @fromdate datetime;
declare @todate datetime;
declare @total as decimal(18,2);
SET @fromdate = '2/23/2013 10:51:17 AM';
SET @todate ='2/25/2013 12:10:56 PM';
WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY device_id ORDER BY [time]) AS NId
FROM [dbo].[readings] r
where cast(r.time as date)>= CAST(@fromdate as date) and
cast(r.time as date) <= CAST(@todate as date) and
r.device_id<>5
)
SELECT CAST (c1.[time] as DATE) as [Day],
(
select s.name from shifts s where s.id = c1.shift_id
) as Shifts,
DATEPART(hour,c1.time) as [Hour],
c1.device_id as [Device ID],
(select
case when sum(c2.reading - ISNULL(c1.reading, c2.reading)) < 0 then
sum(c1.reading - ISNULL(c2.reading, c1.reading))
else
sum(c2.reading - ISNULL(c1.reading, c2.reading))
end
) AS Count1
FROM cte c1 left JOIN cte c2 ON c1.device_id = c2.device_id AND
c1.NId + 1 = c2.NId
group by cast(c1.time as DATE), c1.shift_id , DATEPART(hour,c1.time), c1.device_id
order by cast(c1.time as DATE), c1.shift_id,DATEPART(hour,c1.time), c1.device_id
c2.reading
didn't wrap around after reaching 1,000,000, and that only when c2.reading < c1.reading
. That is, at that point you'd need to increase c2.reading
by 1,000,000, then subtract c1.reading
. And when c2.reading >= c1.reading
, the query should calculate the "normal" difference, i.e. subtract c1.reading
from the original (non-increased) c2.reading
value.SUM(
CASE WHEN c2.reading < c1.reading THEN 1000000 ELSE 0 END
+ c2.reading
- ISNULL(c1.reading, c2.reading)
) AS Count1
d mod 1,000,000 = d
(d + 1,000,000 * n) mod 1,000,000 =
= d mod 1,000,000 + (1,000,000 * n) mod 1,000,000
d mod 1,000,000
results in d
, the second one, (1,000,000 * n) mod 1,000,000
yields 0, d + 0 = d
.
(1000000 + c2.reading - ISNULL(c1.reading, c2.reading)) % 1000000
%
is the modulo operator in Transact-SQL.SUM
to get the corresponding aggregated values:SUM((c2.reading + 1000000 - ISNULL(c1.reading, c2.reading)) % 1000000) AS Count1