首页 文章

总和列作为基于时间段的累积

提问于
浏览
1

我有一个表,在不同时期插入记录(每个记录包含一个名为'Amount'的列) .

我想在每5秒后显示累计总量 . 我尝试了以下查询但没有成功:

SELECT Sum(totalamount) AS RealTimeTotalAmount,
       Datepart(second, createstamp) / 5 AS dp
FROM   [order]
WHERE 
       createstamp BETWEEN Dateadd(s, -5, Getdate()) AND Getdate()
GROUP  BY Datepart(second, createstamp) / 5

我面临的问题是,它向我显示了“每秒的累计金额”,我希望看到它像'(累计金额按每秒累计金额直到那一秒)'

以下是源数据的外观:

-----------------------------------------------------------
|OrderID  | CreateStamp             | TotalAmount         |
-----------------------------------------------------------
|1        |2015-03-22 15:26:05.620  | 10                  |
-----------------------------------------------------------
|2        |2015-03-22 15:26:05.653  | 20                  |
-----------------------------------------------------------
|3        |2015-03-22 15:26:05.660  | 10                  |
-----------------------------------------------------------
|4        |2015-03-22 15:26:06.663  | 10                  |
-----------------------------------------------------------
|5        |2015-03-22 15:26:06.670  | 30                  |
-----------------------------------------------------------

基本上,我希望生成的查询返回如下:

----------------------------------------
|Period          | Accumulative Amount | 
----------------------------------------
|0 to 5 seconds  | 30                  |
----------------------------------------
|0 to 10 seconds | 80                  |
----------------------------------------

这基本上是从0时间到5的倍数的累积 . 对于最后5秒基本上我计算一整天的数量直到我执行此查询的时间,例如在此时间之前的整天的数量是50所以结果表应该是这样的

----------------------------------------
|0 to 5 seconds  | 30 + 50 = 80        |
----------------------------------------
|0 to 10 seconds | 80 + 80 = 160       |
----------------------------------------

2 回答

  • 0

    你可以尝试这样的事情 .

    Input Data

    DECLARE @Orders TABLE
    (
        OrderId INT,
        CreateStamp DATETIME,
        TotalAmount NUMERIC(9,2)
    )
    INSERT INTO @Orders
    SELECT 1,'2015-03-22 15:26:05.620',400
    UNION ALL SELECT 2,'2015-03-22 15:26:04.653',500
    UNION ALL SELECT 3,'2015-03-22 15:26:05.660',600
    UNION ALL SELECT 4,'2015-03-22 15:26:06.663',700
    UNION ALL SELECT 5,'2015-03-22 15:26:06.670',900
    UNION ALL SELECT 6,'2015-03-22 15:26:05.660',600
    UNION ALL SELECT 7,'2015-03-22 15:26:09.663',700
    UNION ALL SELECT 8,'2015-03-22 15:26:12.670',900
    

    Query

    ;WITH CTE as 
    (
        SELECT DATEDIFF(minute,0,CreateStamp)totalminutes,Datepart(second, CreateStamp ) / 5 sec,SUM(TotalAmount) TotalAmount
        FROM @Orders
        GROUP BY DATEDIFF(minute,0,CreateStamp),Datepart(second, CreateStamp) / 5
    )
    SELECT  DATEADD(minute,totalminutes,0) dt,sec,(SELECT SUM(TotalAmount) FROM cte WHERE totalminutes <=c2.totalminutes and sec <=c2.sec)
    FROM CTE c2
    ORDER BY sec;
    

    我已经为不同的日期和分钟添加了 GROUP BY DATEDIFF(minute,0,CreateStamp) 来分隔秒数 .

  • 1

    如果我理解正确的话:

    DECLARE @t TABLE
        (
          ID INT ,
          D DATETIME ,
          A MONEY
        )
    DECLARE @mind DATETIME ,
        @maxd DATETIME
    
    
    INSERT  INTO @t
    VALUES  ( 1, '2015-04-07 13:49:15.000', 5 ),
            ( 2, '2015-04-07 13:49:17.000', 15 ),
            ( 3, '2015-04-07 13:49:35.000', 2 ),
            ( 4, '2015-04-07 13:49:45.000', 4 ),
            ( 5, '2015-04-07 13:49:49.000', 20 ),
            ( 6, '2015-04-07 13:50:05.000', 20 ),
            ( 7, '2015-04-07 13:50:09.000', 3 ),
            ( 8, '2015-04-07 13:50:09.000', 3 ),
            ( 9, '2015-04-07 13:50:10.000', 1 ),
            ( 10, '2015-04-07 13:50:15.000', 1 )
    
    
    SELECT  @mind = MIN(d) ,
            @maxd = MAX(d)
    FROM    @t;
    
    WITH    cte
              AS ( SELECT   @mind AS d
                   UNION ALL
                   SELECT   DATEADD(ss, 5, d)
                   FROM     cte
                   WHERE    cte.d <= @maxd
                 )
    
    SELECT cte.d, SUM(A) AS A FROM cte
    JOIN @t t ON t.D < cte.d
    GROUP BY cte.d
    

    输出:

    d                       A
    2015-04-07 13:49:20.000 20.00
    2015-04-07 13:49:25.000 20.00
    2015-04-07 13:49:30.000 20.00
    2015-04-07 13:49:35.000 20.00
    2015-04-07 13:49:40.000 22.00
    2015-04-07 13:49:45.000 22.00
    2015-04-07 13:49:50.000 46.00
    2015-04-07 13:49:55.000 46.00
    2015-04-07 13:50:00.000 46.00
    2015-04-07 13:50:05.000 46.00
    2015-04-07 13:50:10.000 72.00
    2015-04-07 13:50:15.000 73.00
    2015-04-07 13:50:20.000 74.00
    

相关问题