sql - 计算两次手机充值之间的平均值

Question

我有一个手机充值表，如下。

在 UserId,Date 级别，我有特定用户的 Balance 金额，他在特定日期为 voice 通信消耗的金额以及 data(互联网)使用量。

每当用户为其帐户充值时，Balance 都会增加。

所以我想要一个查询，它可以帮助我找到每个用户两次充值之间的平均 Voice 和 Data 金额/余额。

充值表

+--------+-----------+---------+-------+------+
| Userid |   Date    | Balance | Voice | Data |
+--------+-----------+---------+-------+------+
|      1 | 4/5/2018  |     100 |    10 |   15 |  //Recharge of 100
|      1 | 4/6/2018  |      75 |     5 |   10 |
|      1 | 4/7/2018  |      60 |    10 |   10 |
|      1 | 4/8/2018  |      90 |    10 |   20 | //Recharge of 50
|      1 | 4/9/2018  |      60 |    10 |   20 |
|      1 | 4/10/2018 |      50 |    20 |   30 |// Recharge of 20
|      2 | 4/1/2018  |     200 |    50 |   40 |// Recharge of 200
|      2 | 4/2/2018  |     110 |    20 |   20 |
|      2 | 4/3/2018  |      70 |    20 |   10 |
|      2 | 4/4/2018  |      55 |    10 |   40 |// Recharge of 15
|      2 | 4/5/2018  |      5  |    2  |   2  |
+--------+-----------+---------+-------+------+

在上表中

给定一天的余额 = 前一天的(余额 - SUM(语音 + 数据))

如您所见，UserId 1 100 减少到 75 (100 - (10 + 15))。

但是在第三行(Date = 4/8/2018)他充值了 50，因此他的余额变成了 90 而不是 40。

所以我想为 UserId = 1

找到 100 到 90 之间的平均 Voice 和 Data 列

下面是我想要的输出

+--------+-------------+-----------+----------+
| UserID | RechageDate | Avg_Voice | Avg_Data |
+--------+-------------+-----------+----------+
|      1 | 4/8/2018    |      8.33 |    11.66 |
|      1 | 4/10/2018   |        10 |       20 |
|      2 | 4/4/2018    |      27.5 |       25 |
+--------+-------------+-----------+----------+

我知道这个问题很难理解，但我已经尽力解释了。

如有任何歧义，请随时询问。

Answer 1

我增加了两个新列来确定充值信息:我认为您在另一个表中有充值信息，如果您可以像我一样放置该信息，则此查询将有效。

DECLARE @tbl table(
    Userid int,
    Date    datetime,
    Balance int,
    Voice int,
    Data int,
    Recharge int,
    RechargeSN int
)

INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/5/2018'  ,     100 ,    10 ,   15,100,3)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/6/2018'  ,      75 ,     5 ,   10,0,3)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/7/2018'  ,      60 ,    10 ,   10,0,3)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/8/2018'  ,      90 ,    10 ,   20,50,2)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/9/2018'  ,      60 ,    10 ,   20,0,2)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(1,'4/10/2018' ,      50 ,    20 ,   30,20,1)

INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(2,'4/1/2018'  ,     200 ,    50 ,   40,200,2)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(2,'4/2/2018'  ,     110 ,    20 ,   20,0,2)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(2,'4/3/2018'  ,      70 ,    20 ,   10,0,2)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(2,'4/4/2018'  ,      55 ,    10 ,   40,15,1)
INSERT INTO @tbl(    Userid,    [Date],    Balance,    Voice,    Data,Recharge,RechargeSN) VALUES(2,'4/5/2018'  ,      5  ,    2  ,   2,0,1)

--SELECT * FROM @tbl t

SELECT userid, RechageDate  = max(date), Avg_Voice = (cast(SUM(voice) AS numeric) / Count(voice))  , Avg_Data = cast(SUM(Data) AS numeric) / Count(Data) *1.00 
--, t.RechargeSN 
FROM @tbl t
GROUP BY t.Userid, t.RechargeSN
ORDER BY t.Userid