sql - 计算日期之间的空值

Question

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

我的表格是这样的:

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

Answer 1

count(expression) 不统计NULL值，算了吧作为聚合函数或窗口函数。 The manual:

number of input rows for which the value of expression is not 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。瞧。

相关:

• Select longest continuous sequence

或者，使用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 子句:

• How can I simplify this game statistics query?

db<> fiddle here