计算日期之间的空值 [英] Counting null values between dates

问题描述

我正在尝试计算日期之间的空值数量.

I'm trying to calculate the number of null values between dates.

我的桌子看起来像这样:

My table looks like this:

transaction_date    transaction_sale
10/1/2018           NULL
11/1/2018           33
12/1/2018           NULL
1/1/2019            NULL
2/1/2019            NULL
3/1/2019            2
4/1/2019            NULL
5/1/2019            NULL
6/1/2019            10

我希望获得以下输出:

transaction_date    transaction_sale   count
10/1/2018           NULL               NULL
11/1/2018           33                 1
12/1/2018           NULL               NULL
1/1/2019            NULL               NULL
2/1/2019            NULL               NULL
3/1/2019            2                  3
4/1/2019            NULL               NULL
5/1/2019            NULL               NULL
6/1/2019            10                 2

推荐答案

这不对连续日期等做任何假设.

This doesn't make any assumptions about consecutive dates, etc.

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

See a demo here. Although the demo is SQL Server it should work identically on your platform: https://rextester.com/GVR65084

另请参见PostgreSQL: http://sqlfiddle.com/#!15/07c85f/1

Also see for PostgreSQL: http://sqlfiddle.com/#!15/07c85f/1

这篇关于计算日期之间的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！