计算载荷并避免游标

小编典典

计算载荷并避免游标

sql

给定以下表格结构，该表格表示公交路线，其中乘客使用车门传感器上下车。而且，有一个人坐在那辆公共汽车上，手里拿着一个装有点数的剪贴板。

CREATE TABLE BusLoad(
ROUTE CHAR(4) NOT NULL,
StopNumber INT NOT NULL,
ONS INT,
OFFS INT,
SPOT_CHECK INT)
go
INSERT BusLoad VALUES('AAAA', 1,   5,   0,    null)
INSERT BusLoad VALUES('AAAA', 2,   0,   0,    null)
INSERT BusLoad VALUES('AAAA', 3,   2,   1,    null)
INSERT BusLoad VALUES('AAAA', 4,   6,   3,    8)
INSERT BusLoad VALUES('AAAA', 5,   1,   0,    null)
INSERT BusLoad VALUES('AAAA', 6,   0,   1,    7)
INSERT BusLoad VALUES('AAAA', 7,   0,   3,    null)

我想在此表中添加一列“ LOAD”，以计算每个停靠点的负载。

负载=先前停止的负载+当前停止的ONS-如果SPOT_CHECK为空，则当前停止的OFFS，否则LOAD = SPOT_CHECK

预期成绩：

ROUTE   StopNumber  ONS OFFS    SPOT_CHECK  LOAD
AAAA    1           5   0       NULL        5
AAAA    2           0   0       NULL        5
AAAA    3           2   1       NULL        6
AAAA    4           6   3       8           8
AAAA    5           1   0       NULL        9
AAAA    6           0   1       7           7
AAAA    7           0   3       NULL        4

我可以使用游标执行此操作，但是有没有办法使用查询来执行此操作？

阅读 168

2021-04-22

共1个答案

小编典典

您可以使用以下查询：

select ROUTE, StopNumber, ONS, OFFS, SPOT_CHECK,
       COALESCE(SPOT_CHECK, ONS - OFFS) AS ld,
       SUM(CASE WHEN SPOT_CHECK IS NULL THEN 0 ELSE 1 END) 
       OVER (PARTITION BY ROUTE ORDER BY StopNumber) AS grp
from BusLoad

要得到：

ROUTE   StopNumber  ONS OFFS    SPOT_CHECK  ld  grp
----------------------------------------------------
AAAA    1           5   0       NULL        5   0
AAAA    2           0   0       NULL        0   0
AAAA    3           2   1       NULL        1   0
AAAA    4           6   3       8           8   1
AAAA    5           1   0       NULL        1   1
AAAA    6           0   1       7           7   2
AAAA    7           0   3       NULL       -3   2

所有你现在想要的是运行总计ld超过ROUTE, grp数据的分区：

;WITH CTE AS (
 ....
 previous query here
)
select ROUTE, StopNumber, ONS, OFFS, SPOT_CHECK, grp,
       sum(ld) over (PARTITION BY ROUTE, grp ORDER BY StopNumber) as load
from cte

演示在这里

注意： 上面的查询适用于从2012年开始的版本。如果要查询2008，则必须以某种方式模拟sum() over (order by ...)。您可以在SO中找到许多相关的帖子。

2021-04-22