SQL Server - подсчет количества определенных значений в столбце

Question

Я пытаюсь создать набор данных для анализа, который включает ежедневные транзакции, а также дни, когда транзакции не совершались.

Идеальный результат будет таким же, как столбец Count(последующие 0). Я попытался выполнить промежуточные итоги, но не нашел способа подсчитывать только строки с '0' в столбце транзакций, а затем сбрасывать счет, когда столбец транзакций равен <> 0.

Спасибо

Answer 1

Я решил эту проблему в SQL Server 2016 SP1 (13.0.4001.0). Это похоже на проблему "пробелов и островков в последовательностях", и мое решение - модификация одного из примеров, сделанных Dwain Camps.

ПРИМЕЧАНИЕ. В исходном примере указан один номер учетной записи, но решение обслуживает несколько номеров учетных записей.

with TestData as
(
    -- data for Mr Bill Withers (account number 11300)
    select CONVERT(date, '29/06/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 1 as Transactions
    union
    select CONVERT(date, '30/06/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 3 as Transactions
    union
    select CONVERT(date, '01/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 2 as Transactions
    union
    select CONVERT(date, '02/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '03/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '04/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 4 as Transactions
    union
    select CONVERT(date, '05/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 3 as Transactions
    union
    select CONVERT(date, '06/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '07/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '08/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '09/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '10/07/2016', 103) as CalendarDate, 11300 as AccountNumber, 'Mr Bill Withers' as AccountName, 1 as Transactions

    union

    -- data for Mr Bob Builder (account number 11301)
    select CONVERT(date, '29/06/2016', 103) as CalendarDate, 11301 as AccountNumber, 'Mr Bob Builder' as AccountName, 1 as Transactions
    union
    select CONVERT(date, '30/06/2016', 103) as CalendarDate, 11301 as AccountNumber, 'Mr Bob Builder' as AccountName, 0 as Transactions
    union
    select CONVERT(date, '01/07/2016', 103) as CalendarDate, 11301 as AccountNumber, 'Mr Bob Builder' as AccountName, 0 as Transactions

    union

    -- data for Mr Ted Teddy (account number 11302)
    select CONVERT(date, '29/06/2016', 103) as CalendarDate, 11302 as AccountNumber, 'Mr Ted Teddy' as AccountName, 0 as Transactions

    -- add data for additional account numbers...
),

StartingPoints as
(
    select AccountNumber, CalendarDate, Transactions,
           ROW_NUMBER() over(partition by AccountNumber order by CalendarDate) as rownum
    from TestData as A
    where not exists (
        select *
        from TestData as B
        where B.AccountNumber = A.AccountNumber and Transactions = 0 and dateadd(day, 1, B.CalendarDate) = A.CalendarDate
    )
    and Transactions = 0
),

EndingPoints as
(
    select AccountNumber, CalendarDate, Transactions,
           ROW_NUMBER() over(partition by AccountNumber order by CalendarDate) as RowNum
    from TestData as A
    where not exists (
        select *
        from TestData as B
        where B.AccountNumber = A.AccountNumber and Transactions = 0 and dateadd(day, -1, B.CalendarDate) = A.CalendarDate
    )
    and Transactions = 0
),

ZeroTransactionRanges as
(
    SELECT S.AccountNumber, S.CalendarDate AS start_range, E.CalendarDate AS end_range
    FROM StartingPoints AS S
    INNER JOIN EndingPoints AS E ON E.AccountNumber = S.AccountNumber AND E.RowNum = S.RowNum
),

ZeroRunLengths as
(
    select CalendarDate,
           t1.AccountNumber,
           datediff(day, start_range, CalendarDate) + 1 as runLength
    from ZeroTransactionRanges as t1
    INNER JOIN TestData as t2
    on t1.AccountNumber = t2.AccountNumber
    where start_range <= t2.CalendarDate and t2.CalendarDate <= end_range
)

select t1.*,
       case when t2.runLength is null then 0 else t2.runLength end as 'Count(Subsequent 0s)'
from TestData as t1
-- note the LEFT join here!
LEFT JOIN ZeroRunLengths as t2
on t1.AccountNumber = t2.AccountNumber and t1.CalendarDate = t2.CalendarDate
order by AccountNumber, CalendarDate