sql-server - SQL Server：借记卡条件交易
问题描述
我在一家为客户提供多种套餐的电信公司工作。其中一些套餐包括他们每个月可以使用的一定数量的免费长途通话时间。
目前，免费分钟套餐存在一个缺陷，即允许客户获得比套餐实际允许的更多的免费分钟。同时，当前的系统有时会阻止客户以正确的顺序用完他们所有的空闲时间。
我已经想出如何修改流程以确保将包裹正确分配给客户的通话记录。现在，我正在尝试改变我们处理这些记录的方式，以防止他们使用超过免费分钟包中的内容。
我正在尝试使用 CTE、窗口函数和更新语句的组合来解决这个问题。这个想法是，拥有免费通话时间套餐的客户可以像使用借记卡一样使用该套餐，这样如果通话时间少于他们剩余的分钟数，它会覆盖通话并从金额中减去剩余的分钟数可用的免费分钟数；但如果持续时间超过可用的免费分钟数，则不包括通话。我已经弄清楚了几乎所有内容，但是我被困住了，因为我无法弄清楚如何查找通过窗口函数在前一行中计算的值。
这是我使用测试表获得的结果示例（注意：持续时间以秒为单位，因此它转换为分钟）：
CustID Duration FrMinsAvailable NewAvMins MinutesBilled PrevAvMins
---------------------------------------------------------------------------
14000 250000 4250.2 83.5 4166.7 NULL
14000 9000 4250.2 -66.5 150 83.5
14000 4800 4250.2 -146.5 80 -66.5
14000 450 4250.2 -154 7.5 -146.5
14000 335 4250.2 -159.6 5.6 -154
14000 200 4250.2 -162.9 3.3 -159.6
14000 65 4250.2 -164 1.1 -162.9
14000 45 4250.2 -164.7 0.8 -164
14000 32 4250.2 -165.3 0.5 -164.7
14000 25 4250.2 -165.7 0.4 -165.3
14000 21 4250.2 -166 0.4 -165.7
14000 5 4250.2 -166.1 0.1 -166
这是我想要获得的结果：
CustID Duration FrMinsAvailable NewAvMins MinutesBilled PrevAvMins
-------------------------------------------------------------------------------
14000 250000 4250.2 83.5 4166.7 NULL
14000 9000 4250.2 83.5 150 83.5
14000 4800 4250.2 3.5 80 83.5
14000 450 4250.2 3.5 7.5 3.5
14000 335 4250.2 3.5 5.6 3.5
14000 200 4250.2 0.2 3.3 3.5
14000 65 4250.2 0.2 1.1 0.2
14000 45 4250.2 0.2 0.8 0.2
14000 32 4250.2 0.2 0.5 0.2
14000 25 4250.2 0.2 0.4 0.2
14000 21 4250.2 0.2 0.4 0.2
14000 5 4250.2 0.1 0.1 0.2
最后，这是我使用的测试代码：
DECLARE @testDuration TABLE (CustID INT, Duration INT)
INSERT INTO @testDuration(CustID, Duration)
VALUES (14005, 65), (14005, 200), (14005, 4800), (14005, 25),
(14005, 5), (14005, 450), (14005, 21), (14005, 32),
(14005, 335), (14005, 45), (14005, 9000), (14005, 250000);
WITH my_cte AS
(
SELECT
d.CustID,
d.Duration,
fm.FrMinsAvailable,
ROUND((fm.FrMinsAvailable-SUM(CAST(d.Duration AS FLOAT) / 60)
OVER (PARTITION BY d.CustID ORDER BY d.Duration DESC)), 1) NewAvMins,
ROUND((CAST(d.Duration AS FLOAT) / 60), 1) BillMins
FROM
(SELECT '14000' CustID, '4250.2' FrMinsAvailable) fm
INNER JOIN
@testDuration ON fm.CustID = d.SerialNoID
GROUP BY
d.CustID, d.Duration, fm.FrMinsAvailable
)
SELECT
my_cte.*,
(LAG(my_cte.NewAvMins) OVER (PARTITION BY my_cte.CustID ORDER BY my_cte.Duration DESC)) PrevAvMins
FROM
my_cte
我最终打算做的是使用这些结果来设置一个值，允许客户在 MinutesBilled <= PrevAvMins 的情况下获得免费分钟数。如果您问我为什么不只是创建一堆表来完成此任务，那实际上是我试图通过最小化读取和写入来减少运行此进程的服务器上的负载，因为处理所有数据的整个过程每天收到的记录已经涉及很多记录，需要几个小时才能完成，并且服务器还有其他程序要运行。
该解决方案不必涉及窗口函数和 CTE，但这是我想出的最佳解决方案。我真的很感激一些好的反馈！:)
谢谢！
PS 我正在使用 SQL Server 2014。
解决方案
您需要使用SUM OVER()来获得累积总数，请参阅下面的代码工作：
DECLARE @testDuration TABLE (CustID INT, Duration decimal (18,0))
INSERT INTO @testDuration(CustID, Duration)
VALUES
(14005, 65)
, (14005, 200)
, (14005, 4800)
, (14005, 25)
, (14005, 5)
, (14005, 450)
, (14005, 21)
, (14005, 32)
, (14005, 335)
, (14005, 45)
, (14005, 9000)
, (14005, 250000);
if object_id('tempdb..#callRecords') is not null
drop table #callRecords;
select td.CustID, Duration,4250.2 as FrAvailableMinutes
into #callRecords
from @testDuration as td;
with cte as (
select cr.CustID
, cr.Duration
, cr.FrAvailableMinutes
, row_number() over (partition by cr.CustID order by duration asc) as CallDateKey
from #callRecords as cr
)
select cte.CustID
, cte.Duration
, cte.FrAvailableMinutes
, cte.CallDateKey
, round(sum(Duration) over (order by CallDateKey rows between unbounded preceding and current row)/60,2) as CumulativeDUration
, cte.FrAvailableMinutes - round(sum(Duration) over (order by CallDateKey rows between unbounded preceding and current row)/60,2) as MinutesLeft
from cte
order by CallDateKey desc
编辑：根据您在下面的评论，我还为您的业务需求提供了一个循环解决方案：
declare @testDuration table
(
CustID int
, Duration decimal(18, 0)
, CallDateKey int
);
insert into @testDuration
(
CustID
, Duration
, CallDateKey
)
values
(14005, 65, 1)
, (14005, 200, 2)
, (14005, 4800, 3)
, (14005, 25, 4)
, (14005, 5, 5)
, (14005, 450, 6)
, (14005, 21, 7)
, (14005, 32, 8)
, (14005, 335, 9)
, (14005, 45, 10)
, (14005, 9000, 11)
, (14005, 250000, 12)
, (14005, 500, 13);
if object_id('tempdb..#Billing') is not null
drop table #Billing;
create table #Billing
(
CustId int
, Duration decimal(18, 0)
, FrAvailableSeconds decimal(18, 2)
, CallDateKey int
, CumulativeDuration decimal(18, 2)
, SecondsLeft decimal(18, 2)
);
with CallRecords
(CustID, DUration, CallDateKey, FrAvailableSeconds)
as (
select td.CustID
, Duration
, td.CallDateKey
, 4250.20 * 60
from @testDuration as td
)
insert into #Billing
(
CustId
, Duration
, FrAvailableSeconds
, CallDateKey
, CumulativeDuration
, SecondsLeft
)
select cr.CustID
, cr.DUration
, cr.FrAvailableSeconds
, cr.CallDateKey
, round( sum(DUration) over (order by
CallDateKey
rows between unbounded preceding and current row
) / 60
, 2
)
, cr.FrAvailableSeconds
from CallRecords as cr;
declare @a int = (
select min(CustId) from #Billing as b
);
declare @b int = (
select max(CustId) from #Billing as b
);
declare @x int;
declare @y int;
while @a <= @b
begin
set @x = (
select min(b.CallDateKey) from #Billing as b where b.CustId = @a
);
set @y = (
select max(b.CallDateKey) from #Billing as b where b.CustId = @a
);
while @x <= @y
begin
update b
set b.SecondsLeft = case
when isnull(ps.SecondsLeft, b.FrAvailableSeconds) - b.Duration < 0 then
isnull(ps.SecondsLeft, b.FrAvailableSeconds)
else
isnull(ps.SecondsLeft, b.FrAvailableSeconds) - b.Duration
end
from #Billing as b
left join (
select b.CustId
, SecondsLeft
from #Billing as b
where
b.CallDateKey = @x - 1
and b.CustId = @a
) as ps
on b.CustId = ps.CustId
where
b.CustId = @a
and b.CallDateKey = @x;
set @x += 1;
end;
set @a += 1;
end;
select b.CustId
, b.Duration
, b.FrAvailableSeconds
, b.FrAvailableSeconds / 60 as FrAvailableMinutes
, b.CallDateKey
, b.CumulativeDuration
, b.SecondsLeft
, b.SecondsLeft / 60 as MinutesLeft
from #Billing as b
order by
b.CallDateKey desc;
推荐阅读
- mysql - 为什么 '=' 在这个 SQL 查询中不起作用？
- c# - EF Core 3 x.Contains() 在 x 是 ICollection 的表达式中
- azure - 如何发布我的 Azure DevOps 版本？
- stripe-payments - 创建订单时如何使用 Stripe Orders API 收到“卡片错误”？
- c++ - 从参数中剪切特定位置的向量字符串
- python - 安装 Jupyter 扩展 - 从 setup.py 自动安装和启用
- sql - SSIS 开发人员表达式
- c++ - 使用 bazel 构建的二进制文件在 pprof 中查看源代码
- c++ - 声明具有可变维度的二维数组
- c++ - 如果你有两个全局函数，一个用于 `Widget`，另一个用于 `double`，而 `Widget` 类定义了 cast-to-double，会发生什么？