（SQLite）计算值连续重复3次的次数

Question

我知道这是一个复杂的查询，因此我有一个单独的列，但该列包含几乎99%的null值（这是非常无效的），因此我想知道是否可以通过查询来实现这一点。

我有这样的桌子：

TransactionId | Value | ProductId
      1           3         2
      2           2         2           
      3           1         2
      4           0         4
      5           0         4
      6           0         4
      7           3         7
      8           3         7
      9           1         7
     10           0         3
     11           0         3
     12           0         3
     13           5         1
     14           2         1
     15           3         1
     16           0         4
     17           0         4
     18           0         4

现在的查询是，如果对于3个连续的产品ID，对应的值是0（按照TransactionId ASC的顺序），那么对于ProductId，它将被计为1，即。

ProductId | Count 
    4         2
    3         1

我们如何查询这个？

一个可选的简短问题：p

如果我有很多空值的列，可以吗？

Answer 1

首先使用CTE获取具有3个连续0的行，然后在表中只包括1（因此只计算1）：

with 
cte as (
  select * from (
    select *,
      lag(transactionid) over (order by transactionid) previd,
      lead(transactionid) over (order by transactionid) nextid,
      lag(productid) over (order by transactionid) prevprodid,
      lead(productid) over (order by transactionid) nextprodid,
      lag(value) over (order by transactionid) prevvalue,
      lead(value) over (order by transactionid) nextvalue
    from tablename
  ) t
  where 
    productid = prevprodid and productid = nextprodid and 
    coalesce(value, 0) = 0 and  coalesce(prevvalue, 0) = 0 and coalesce(nextvalue, 0) = 0
),
triples as (select previd id from cte union all select nextid from cte)

select productid, count(*) counter 
from tablename 
where transactionid not in (select id from triples)
group by productid

请看演示
结果：

| ProductId | counter |
| --------- | ------- |
| 1         | 3       |
| 2         | 3       |
| 3         | 1       |
| 4         | 2       |
| 7         | 3       |

Answer 2

在最新版本的SQLite中，您可以使用lag（）/led（）：

select product_id, count(*)
from (select t.*,
             (case when value = 0 and
                        lead(value, 1) over (partition by productid order by transactionid) = 0 and
                        lead(value, 2) over (partition by productid order by transactionid) = 0
                   then 1
              end) as three_zero_flag
      from t
     ) t
where three_zero_flag = 1
group by product_id