每行值之差 - Sum Error [英] The difference between each row value - Sum Error

查看：101
本文介绍了每行值之差 - Sum Error的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的主要查询是在下面的帖子中解决的。

TSQL - 获取每个行值之间的差异



我有一个问题， 

  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的结果。




    • $ b

    考虑到所有这些因素，我们可以通过以下通用表达式来计算单个差异：

     （1000000 + c2.reading  -  ISNULL（c1.reading，c2.reading））％1000000

    其中％是模运算符 Transact-SQL 。



    SUM 以获得相应的汇总值： 

      SUM（（c2。阅读+ 1000000  -  ISNULL（c1.reading，c2.reading））％1000000）AS Count1


    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.

    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

    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 ...

    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

    The result of the above readings is as below

    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

    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

    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

    解决方案

    What you essentially need is to pretend temporarily that 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.

    One way to achieve that logic would be to do something as straightforward as this:

    SUM(
  CASE WHEN c2.reading < c1.reading THEN 1000000 ELSE 0 END
  + c2.reading
  - ISNULL(c1.reading, c2.reading)
) AS Count1

    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:

    d mod 1,000,000 = d

    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,

      (d + 1,000,000 * n) mod 1,000,000 =
= d mod 1,000,000 + (1,000,000 * n) mod 1,000,000

    The first summand, d mod 1,000,000 results in d, the second one, (1,000,000 * n) mod 1,000,000 yields 0, d + 0 = d.

    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:

    (1000000 + c2.reading - ISNULL(c1.reading, c2.reading)) % 1000000

    where % is the modulo operator in Transact-SQL.

    Put the expression into SUM to get the corresponding aggregated values:

    SUM((c2.reading + 1000000 - ISNULL(c1.reading, c2.reading)) % 1000000) AS Count1

    这篇关于每行值之差 - Sum Error的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    其他开发最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆