连续数列的增量列

Question

我要如何获得以下用黄色突出显示的结果？

本质上，我想要一个计算字段，在VeganOption = 1时递增1，在VeganOption = 0时为零。

我尝试使用以下查询，但使用分区后继续在零之后递增。对此我有点困惑。

SELECT [UniqueId]
      ,[Meal]
      ,[VDate]
      ,[VeganOption]
      , row_number() over (partition by [VeganOption] order by [UniqueId])
  FROM [Control]
  order by [UniqueId]

表格数据：

CREATE TABLE Control
    ([UniqueId] int, [Meal] varchar(10), [VDate] datetime, [VeganOption] int);

INSERT INTO Control ([UniqueId], [Meal], [VDate], [VeganOption])
VALUES
('1', 'Breakfast',' 2018-08-01 00:00:00', 1),
('2', 'Lunch',' 2018-08-01 00:00:00', 1),
('3', 'Dinner',' 2018-08-01 00:00:00', 1),
('4', 'Breakfast',' 2018-08-02 00:00:00', 1),
('5', 'Lunch',' 2018-08-02 00:00:00', 0),
('6', 'Dinner',' 2018-08-02 00:00:00', 0),
('7', 'Breakfast',' 2018-08-03 00:00:00', 1),
('8', 'Lunch',' 2018-08-03 00:00:00', 1),
('9', 'Dinner',' 2018-08-03 00:00:00', 1),
('10', 'Breakfast',' 2018-08-04 00:00:00', 0),
('11', 'Lunch',' 2018-08-04 00:00:00', 1),
('12', 'Dinner',' 2018-08-04 00:00:00', 1)
;

这适用于 SQL Server 2016 及以上版本

- user3904868

---

我知道点赞显示了它，但很高兴看到一个表述清晰的问题，包括样本数据、预期结果、尝试以及 CREATE 和 INSERT 语句。谢谢。 :) - Thom A

@Larnu 不是通常的琐碎转换失败从varchar到datetime的错误 :-) - Zohar Peled

这些关于“如何拆分此分隔列表”的问题真的让我很烦恼@ZoharPeled。有这么多....哈哈 - Thom A

Answer 1

您可以使用SUM和ROW_NUMBER创建子组：

WITH cte AS (
  SELECT [UniqueId]
      ,[Meal]
      ,[VDate]
      ,[VeganOption]
      ,sum(CASE WHEN VeganOption = 1 THEN 0 ELSE 1 END) 
         over (order by [UniqueId]) AS grp  --switching 0 <-> 1
  FROM [Control]
)
SELECT *,CASE WHEN VeganOption =0 THEN 0
        ELSE ROW_NUMBER() OVER(PARTITION BY veganOption, grp ORDER BY [UniqueId])
        END AS VeganStreak                  -- main group and calculated subgroup
FROM cte
order by [UniqueId];

Rextester Demo

- Lukasz Szozda

---

我喜欢这种方法，比其他传统的处理间隔和岛屿的方法稍微容易理解一些。 - sgeddes

Answer 2

这是一个变形的间隙和岛屿问题。

我喜欢使用行号的差异来定义连续区间。看起来像这样：

select c.*,
       (case when veganoption = 1
             then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
             else 0
        end) as veganstreak
from (select c.*,
             row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
             row_number() over (order by uniqueid) as seqnum
      from c
     ) c;

这个方法为什么有效有点难以解释。但是，如果您查看子查询的结果，就会看到行号之间的差异如何定义您想要识别的连胜。剩下的工作只是应用row_number()来枚举餐点。

这里有一个Rextester。

- Gordon Linoff

---

它产生了错误的结果：http://rextester.com/YDVD78565。最后两行是（4,5），而不是（1,2）。 - Lukasz Szozda

@LukaszSzozda . . . 谢谢您的付出。我没有注意到日期不是唯一的。uniqueid 是用于逻辑的正确列。 - Gordon Linoff

Answer 3

一种方法是使用CTE定义分组，然后在这些分组上进行进一步的ROW_NUMBER()操作，结果如下：

WITH Grps AS(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
           ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
    FROM Control)
SELECT *,
       CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

- Thom A

---

你应该将 VeganOption 设为 0，代码链接：http://rextester.com/COTWN74238。第 5、6 行应该是 (0,0)，而不是 (1,2)。第 10 行同理。 - Lukasz Szozda