Подсчет нулевых значений между датами

count( expression ) не считает значения NULL, будь то агрегатная функция или оконная функция. Руководство:

количество входных строк, для которых значение выражения не равно нулю

Это ключевой элемент для простого и быстрого запроса.

Предполагая, что transaction_date равно UNIQUE, как предлагает ваш пример, или вам нужно будет определить, как разорвать связи между повторяющимися значениями. (Фактическое определение таблицы могло бы прояснить ситуацию.)

SELECT transaction_date, transaction_sale
     , CASE WHEN transaction_sale IS NOT NULL
            THEN count(*) OVER (PARTITION BY grp) - 1
       END AS count 
FROM  (
   SELECT *
        , count(transaction_sale) OVER (ORDER BY transaction_date DESC) AS grp
   FROM   tbl
   ) sub
ORDER  BY transaction_date;

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

Во внешнем SELECT подсчитайте строки в группе и отобразите где transaction_sale IS NOT NULL. Исправить off-by-1. Вуаля.

Связанный:

• Выбрать самую длинную непрерывную последовательность

В качестве альтернативы, подсчитайте с FILTER (WHERE transaction_sale IS NULL) - полезно для связанных случаев, когда мы не можем просто вычесть 1:

SELECT transaction_date, transaction_sale
     , CASE WHEN transaction_sale IS NOT NULL
            THEN count(*) FILTER (WHERE transaction_sale IS NULL)
                          OVER (PARTITION BY grp)
       END AS count 
FROM  (
   SELECT *
        , count(transaction_sale) OVER (ORDER BY transaction_date DESC) AS grp
   FROM   tbl
   ) sub
ORDER  BY transaction_date;

О предложении FILTER:

• Как упростить этот запрос игровой статистики?

db ‹> скрипт здесь

Это не делает никаких предположений о последовательных датах и ​​т. Д.

with data as (
    select transaction_date, transaction_sale,
        count(transaction_sale)
            over (order by transaction_date desc) as grp
    from T /* replace with your table */
)
select transaction_date, transaction_sale,
    case when transaction_sale is null then null else
        count(case when transaction_sale is null then 1 end)
            over (partition by grp) end as "count"
from data
order by transaction_date;

Смотрите демо здесь. Хотя демонстрация - это SQL Server, она должна одинаково работать на вашей платформе: https://rextester.com/GVR65084

См. Также PostgreSQL: http://sqlfiddle.com/#!15/07c85f/1

Если даты идут подряд, вы можете использовать следующее, чтобы получить предыдущую дату:

select t.*,
       max(transaction_date) filter where (transaction_sale is not null) over (order by transaction_date order by transaction date rows between unbounded preceding and 1 preceding)
from t;

Если разница меньше 12, вы можете использовать age() и extract():

select t.*,
       extract(month from
               age(max(transaction_date) filter where (transaction_sale is notnull)
                       over (order by transaction_date order by transaction date rows between unbounded preceding and 1 preceding
                            ), transaction_date
                   )
               ) as diff

Если дата транзакции - это поле даты, вы можете просто использовать:

select count(*) from Counter where transaction_date > date_lower and transaction_date < date_higher and tx_sale is null;