Вычислять нагрузки и избегать курсоров

Учитывая следующую структуру таблицы, которая представляет собой маршрут автобуса, где пассажиры садятся на автобус с датчиком двери. И есть человек, который сидит на этом автобусе с буфером обмена, держащим подсчет очков.

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 - остановка тока OFFS if SPOT_CHECK имеет значение null, иначе 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

Я могу сделать это с помощью курсора, но есть ли способ сделать это с помощью запроса?

Ответ 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.

Ответ 2

Вы можете использовать рекурсивный запрос

with act_load as
(
  select *, ons load
  from busload
  where stopnumber = 1 and route = 'AAAA'
  union all
  select b.*, case when b.spot_check is null then l.load + b.ons - b.offs
              else b.spot_check
              end load
  from busload b
  join act_load l on b.StopNumber = l.StopNumber + 1 and
                     b.route = l.route
)
select *
from act_load

демо-версия dbfiddle