根据组内连续两天的情况，将指标列添加到表中

Question

我需要添加一个逻辑，帮助我将连续两天中的第一天标记为1，将第二天标记为0，并按列分组(测试(。如果测试(a(连续三天，则第三天应再次以1等开始。

示例表如下所示，新列是我需要的列。

|---------------------|------------------|---------------------|
|      test           |     test_date    |      new col        |
|---------------------|------------------|---------------------|
|      a              |     1/1/2020     |      1              |
|---------------------|------------------|---------------------|
|      a              |     1/2/2020     |      0              |
|---------------------|------------------|---------------------|
|      a              |     1/3/2020     |      1              |
|---------------------|------------------|---------------------|
|      b              |     1/1/2020     |      1              |
|---------------------|------------------|---------------------|
|      b              |     1/2/2020     |      0              |
|---------------------|------------------|---------------------|
|      b              |     1/15/2020    |      1              |
|---------------------|------------------|---------------------|

因为这似乎是一些缺口和孤岛的问题，我认为一些窗口函数的方法应该能让我达到目的。

我尝试了类似以下的方法来获得连续的部分，但在指标栏上很吃力。

Select 
test, 
test_date,
grp_var = dateadd(day, 
-row_number() over (partition by test order by test_date), test_date)    
from 
my_table

Answer 1

这确实是一个缺口和孤岛问题。我建议使用row_number()和日期之间的差异来生成组，然后使用算术：

select
test,
test_date, 
row_number() over(  
partition by test, dateadd(day, -rn, test_date)
order by test_date
) % 2 new_col
from (
select 
t.*, 
row_number() over(partition by test order by test_date) rn
from mytable t
) t

DB Fiddle上的演示：

test|test_date|new_col：---|：--------|------：a|2020-01-01|1a |2020-01-02|0a|2020-01-03|1b|2020-01-01|1b|2020-01-02|0b|2020-01-15|1