Блог

Я пытаюсь суммировать окно с фильтром. Я видел что-то похожее на sum(x) filter(condition) over (partition by...) , но, похоже, это не работает в t-sql, SQL Server 2017.

По сути, я хочу суммировать последние 5 строк, у которых есть условие в другом столбце.

Я пробовал sum(case when condition...) over (partition...) и sum(cast(nullif(x))) over (partition...) .

Я попытался объединить таблицу слева с условием where, чтобы отфильтровать условие.

Все вышеперечисленное добавит последние 5 от начальной точки текущей строки с условием.

То, что я хочу, — это из текущей строки. Добавьте последние 5 значений выше, которые соответствуют условию.

 Date| Value | Condition | Result
1-1   10      1          
1-2   11      1 
1-3   12      1
1-4   13      1
1-5   14      0
1-6   15      1
1-7   16      0
1-8   17      0      sum(15 13 12 11 10)
1-9   18      1      sum(18 15 13 12 11)
1-10  19      1      sum(19 18 15 13 12)
  

В приведенном выше примере условием, которое я хотел бы, было бы 1, игнорируя 0, но по-прежнему имея размер «window» равным 5 значениям, отличным от 0.

Этого можно легко достичь с помощью коррелированного подзапроса:

Сначала создайте и заполните пример таблицы (Пожалуйста, сохраните этот шаг в своих будущих вопросах):

 DECLARE @T AS TABLE
(
    [Date] Date, 
    [Value] int, 
    Condition bit
)
INSERT INTO @T ([Date], [Value], Condition) VALUES
('2019-01-01', 10, 1),
('2019-01-02', 11, 1),
('2019-01-03', 12, 1),
('2019-01-04', 13, 1),
('2019-01-05', 14, 0),
('2019-01-06', 15, 1),
('2019-01-07', 16, 0),
('2019-01-08', 17, 0),
('2019-01-09', 18, 1),
('2019-01-10', 19, 1)
  

Запрос:

 SELECT [Date], [Value], Condition,
       (
           SELECT Sum([Value])
           FROM 
           (
               SELECT TOP 5 [Value] 
               FROM @T AS t1
               WHERE Condition = 1
               AND t1.[Date] <= t0.[Date]
-- If you want the sum to appear starting from a specific date, unremark the next row
               --AND t0.[Date] >  '2019-01-07'
               ORDER BY [Date] DESC                 
           ) As t2
           HAVING COUNT(*) = 5 -- there are at least 5 rows meeting the condition
       ) As Result
FROM @T As T0
  

Результаты:

 Date        Value   Condition   Result
2019-01-01  10      1           
2019-01-02  11      1           
2019-01-03  12      1           
2019-01-04  13      1           
2019-01-05  14      0           
2019-01-06  15      1           61
2019-01-07  16      0           61
2019-01-08  17      0           61
2019-01-09  18      1           69
2019-01-10  19      1           77