SQL查询-计算值高于X的连续行数

Question

我被困在一个问题上，希望得到一些指导。我们希望生成一个报告，计算产品的连续日期（从用户提供的日期向后看）。数据集如下：

Product, InventoryDate, InventoryQuantity
A, 3/1/2021, 123
B, 3/1/2021, 98
C, 3/1/2021, 101
A, 2/22/2021, 115
B, 2/22/2021, 99
C, 2/22/2021, 105
A, 2/15/2021, 150
B, 2/15/2021, 105
C, 2/15/2021, 50

因此，我们想得出一份报告，该报告在2021年3月2日提供的日期如下所示：

Product, CurrentInventory, PreviousWeeksCountAbove100
A,123,2
B,98,0
C,101,1

尽管我正在努力编写查询，但我甚至不知道该怎么称呼它，因为我需要一个特定阈值上的连续值计数。我可以计算阈值以上的值，但不确定如何添加“连续数周”逻辑。

Answer 1

您可以使用窗口函数。根据行数分配一个组，该行数的值从末尾算起低于X。然后过滤和聚合：

select product,
       sum(case when grp = 0 and val > 100 then 1 else 0 end) as consecutive_weeks
from (select t.*,
             sum(case when val <= 100 then 1 else 0 end) over (partition by product order by inventorydate desc) as grp
      from t
      where inventorydate <= ?
     ) t
group by product;