计算日期之间的空值 [英] 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屋!
查看全文