我想找到值第一次和最后一次出现之间的行数。但是，当它们之间有五条或五条以上不同值的记录时，请停止计数。
所以，如果最后一次出现在今天，而第一次出现在昨天，结果将是2(今天加昨天(。
如果最后一次发生在今天，而第一次发生在8天前，并且两者之间没有发生，则结果为"1"。然而，如果3天前再次发生，则结果为4(3+2+1天前加上今天(。
我希望这是有道理的。
这是我的数据
Date City Weather
==============================
2018-08-11 Ankara Sun
2018-08-10 Ankara Sun
2018-08-09 Ankara Sun
2018-08-08 Ankara Sun
2018-08-07 Ankara Sun
2018-08-06 Ankara Sun
2018-08-05 Ankara Rain
2018-08-04 Ankara Clouds
2018-08-03 Ankara Rain
2018-08-02 Ankara Sun
2018-08-01 Ankara Sun
2018-08-11 Cairo Clouds
2018-08-10 Cairo Sun
2018-08-09 Cairo Sun
2018-08-08 Cairo Sun
2018-08-07 Cairo Sun
2018-08-06 Cairo Sun
2018-08-05 Cairo Clouds
2018-08-04 Cairo Sun
2018-08-03 Cairo Sun
2018-08-02 Cairo Sun
2018-08-01 Cairo Sun
我想要的是一个查询，返回给定城市的日期、当天的天气以及自该天气首次出现以来的天数。但是，当间隔五天或更长时间时，计数将从1重新开始。
就像在11th Aug上查询Ankara一样，它会返回11，因为Sun首次出现已经11天了(包括今天(。
然而，对于11th Aug上的Cairo，它将返回1而不是7，因为从8月5日的Clouds到今天的Clouds已经过去了5天以上。
我已经尝试了很多关于first_value((、LEAD、LAG和ROW_NUMBER的东西，但没有任何意义，因为它们都失败了。
不管怎样，它就在这里。。。
select
city, val,datediff(day, min(datadate), '2018-10-30') + 1 as DaysPresent
from d
where val = last_val
group by city,val;
或者。。。
select
date, city, weather, datediff(day,ca.prior,d.date)+1 as daysPresent
from d
cross apply (
select min(prev.date) as prior
from d as prev
where prev.city = d.city
and prev.date between dateadd(day,-4,d.date) and dateadd(day,0,d.date)
and prev.weather = d.weather
) ca
order by city,date
预期结果
+----+---------------------+--------+---------+----------------+-------------+----------+----------------------------
| | date | city | weather | prior_the_same | prior_types |expected | why?
+----+---------------------+--------+---------+----------------+-------------+----------+----------------------------
| 1 | 11.08.2018 00:00:00 | Ankara | Sun | 5 | 2 | 11 | 11t day since 1st time Sun
| 2 | 10.08.2018 00:00:00 | Ankara | Sun | 4 | 3 | 10 | 10t day since 1st time Sun
| 3 | 09.08.2018 00:00:00 | Ankara | Sun | 7 | 3 | 9 | 9th day since 1st time Sun
| 4 | 08.08.2018 00:00:00 | Ankara | Sun | 7 | 3 | 8 | 8th day since 1st time Sun
| 5 | 07.08.2018 00:00:00 | Ankara | Sun | 6 | 3 | 7 | 7th day since 1st time Sun
| 6 | 06.08.2018 00:00:00 | Ankara | Sun | 5 | 3 | 6 | 6th day since 1st time Sun ( <5 days gap since last Sun keeps counting )
| 7 | 05.08.2018 00:00:00 | Ankara | Rain | 2 | 3 | 3 | 3rd day since 1st time Rain
| 8 | 04.08.2018 00:00:00 | Ankara | Clouds | 0 | 3 | 1 | 1st day Clouds
| 9 | 03.08.2018 00:00:00 | Ankara | Rain | 0 | 2 | 1 | 1st day Rain
| 10 | 02.08.2018 00:00:00 | Ankara | Sun | 1 | 0 | 2 | 2nd day since 1st time Sun
| 11 | 01.08.2018 00:00:00 | Ankara | Sun | 0 | 0 | 1 | 1st day Sun
| 12 | 11.08.2018 00:00:00 | Cairo | Clouds | 6 | 6 | 1 | 1st time Clouds ( >5 days gap since last Clouds resets the count )
| 13 | 10.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | 10 | 10t day since 1st time Sun
| 14 | 09.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | 9 | 9th day since 1st time Sun
| 15 | 08.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | 8 | 8th day since 1st time Sun
| 16 | 07.08.2018 00:00:00 | Cairo | Sun | 6 | 1 | 7 | 7th day since 1st time Sun
| 17 | 06.08.2018 00:00:00 | Cairo | Sun | 5 | 1 | 6 | 6th day since 1st time Sun ( <5 days gap since last Sun keeps counting )
| 18 | 05.08.2018 00:00:00 | Cairo | Clouds | 0 | 4 | 1 | 1st time Clouds
| 19 | 04.08.2018 00:00:00 | Cairo | Sun | 3 | 0 | 4 | 4th day since 1st time Sun
| 20 | 03.08.2018 00:00:00 | Cairo | Sun | 2 | 0 | 3 | 3rd day since 1st time Sun
| 21 | 02.08.2018 00:00:00 | Cairo | Sun | 1 | 0 | 2 | 2nd day since 1st time Sun
| 22 | 01.08.2018 00:00:00 | Cairo | Sun | 0 | 0 | 1 | 1st day Sun
+----+---------------------+--------+---------+----------------+-------------+----------+----------------------------
最新
declare @day_range integer = 5;
select
t.date, t.city, t.weather
, datediff(day,ca1.prior_dt,t.date)+1 as prior_the_same
, twist.prior_types
, twist.prior_mx_dt
from mytable t
cross apply (
select count(prev.weather) as prior_types, max(prev.date) as prior_mx_dt
from mytable as prev
where prev.city = t.city
and prev.date between dateadd(day,-@day_range,t.date) and t.date
and prev.weather <> t.weather
) twist
cross apply (
select min(prev.date) as prior_dt
from mytable as prev
where prev.city = t.city
and (twist.prior_types < @day_range or prev.date >= twist.prior_mx_dt)
and prev.weather = t.weather
) ca1
order by t.city, t.date DESC
结果：
+----+---------------------+--------+---------+----------------+-------------+---------------------+
| | date | city | weather | prior_the_same | prior_types | prior_mx_dt |
+----+---------------------+--------+---------+----------------+-------------+---------------------+
| 1 | 11.08.2018 00:00:00 | Ankara | Sun | 11 | 0 | NULL |
| 2 | 10.08.2018 00:00:00 | Ankara | Sun | 10 | 1 | 05.08.2018 00:00:00 |
| 3 | 09.08.2018 00:00:00 | Ankara | Sun | 9 | 2 | 05.08.2018 00:00:00 |
| 4 | 08.08.2018 00:00:00 | Ankara | Sun | 8 | 3 | 05.08.2018 00:00:00 |
| 5 | 07.08.2018 00:00:00 | Ankara | Sun | 7 | 3 | 05.08.2018 00:00:00 |
| 6 | 06.08.2018 00:00:00 | Ankara | Sun | 6 | 3 | 05.08.2018 00:00:00 |
| 7 | 05.08.2018 00:00:00 | Ankara | Rain | 3 | 3 | 04.08.2018 00:00:00 |
| 8 | 04.08.2018 00:00:00 | Ankara | Clouds | 1 | 3 | 03.08.2018 00:00:00 |
| 9 | 03.08.2018 00:00:00 | Ankara | Rain | 1 | 2 | 02.08.2018 00:00:00 |
| 10 | 02.08.2018 00:00:00 | Ankara | Sun | 2 | 0 | NULL |
| 11 | 01.08.2018 00:00:00 | Ankara | Sun | 1 | 0 | NULL |
| 12 | 11.08.2018 00:00:00 | Cairo | Clouds | 1 | 5 | 10.08.2018 00:00:00 |
| 13 | 10.08.2018 00:00:00 | Cairo | Sun | 10 | 1 | 05.08.2018 00:00:00 |
| 14 | 09.08.2018 00:00:00 | Cairo | Sun | 9 | 1 | 05.08.2018 00:00:00 |
| 15 | 08.08.2018 00:00:00 | Cairo | Sun | 8 | 1 | 05.08.2018 00:00:00 |
| 16 | 07.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | 05.08.2018 00:00:00 |
| 17 | 06.08.2018 00:00:00 | Cairo | Sun | 6 | 1 | 05.08.2018 00:00:00 |
| 18 | 05.08.2018 00:00:00 | Cairo | Clouds | 1 | 4 | 04.08.2018 00:00:00 |
| 19 | 04.08.2018 00:00:00 | Cairo | Sun | 4 | 0 | NULL |
| 20 | 03.08.2018 00:00:00 | Cairo | Sun | 3 | 0 | NULL |
| 21 | 02.08.2018 00:00:00 | Cairo | Sun | 2 | 0 | NULL |
| 22 | 01.08.2018 00:00:00 | Cairo | Sun | 1 | 0 | NULL |
在线查看：https://rextester.com/ZSHT63407
原始
样本数据为：
CREATE TABLE mytable(
Date DATE NOT NULL
,City VARCHAR(6) NOT NULL
,Weather VARCHAR(6) NOT NULL
);
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-11','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-10','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-09','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-08','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-07','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-06','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-05','Ankara','Rain');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-04','Ankara','Clouds');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-03','Ankara','Rain');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-02','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-01','Ankara','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-11','Cairo','Clouds');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-10','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-09','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-08','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-07','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-06','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-05','Cairo','Clouds');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-04','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-03','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-02','Cairo','Sun');
INSERT INTO mytable(Date,City,Weather) VALUES ('2018-08-01','Cairo','Sun');
使用此查询：
declare @day_range integer = 7;
declare @ignore_range integer = 5;
select
t.date, t.city, t.weather
, datediff(day,ca1.prior_dt,t.date) as prior_the_same
, ca2.prior_types
from mytable t
cross apply (
select min(prev.date) as prior_dt
from mytable as prev
where prev.city = t.city
and prev.date between dateadd(day,-@day_range,t.date) and t.date
and prev.weather = t.weather
) ca1
cross apply (
select count(prev.weather) as prior_types
from mytable as prev
where prev.city = t.city
and prev.date between dateadd(day,-@day_range,t.date) and t.date
and prev.weather <> t.weather
) ca2
order by t.city, t.date DESC
结果如下：
+----+---------------------+--------+---------+----------------+-------------+----------+
| | date | city | weather | prior_the_same | prior_types |expected? |
+----+---------------------+--------+---------+----------------+-------------+----------+
| 1 | 11.08.2018 00:00:00 | Ankara | Sun | 5 | 2 | |
| 2 | 10.08.2018 00:00:00 | Ankara | Sun | 4 | 3 | |
| 3 | 09.08.2018 00:00:00 | Ankara | Sun | 7 | 3 | |
| 4 | 08.08.2018 00:00:00 | Ankara | Sun | 7 | 3 | |
| 5 | 07.08.2018 00:00:00 | Ankara | Sun | 6 | 3 | |
| 6 | 06.08.2018 00:00:00 | Ankara | Sun | 5 | 3 | |
| 7 | 05.08.2018 00:00:00 | Ankara | Rain | 2 | 3 | |
| 8 | 04.08.2018 00:00:00 | Ankara | Clouds | 0 | 3 | |
| 9 | 03.08.2018 00:00:00 | Ankara | Rain | 0 | 2 | |
| 10 | 02.08.2018 00:00:00 | Ankara | Sun | 1 | 0 | |
| 11 | 01.08.2018 00:00:00 | Ankara | Sun | 0 | 0 | |
| 12 | 11.08.2018 00:00:00 | Cairo | Clouds | 6 | 6 | |
| 13 | 10.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | |
| 14 | 09.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | |
| 15 | 08.08.2018 00:00:00 | Cairo | Sun | 7 | 1 | |
| 16 | 07.08.2018 00:00:00 | Cairo | Sun | 6 | 1 | |
| 17 | 06.08.2018 00:00:00 | Cairo | Sun | 5 | 1 | |
| 18 | 05.08.2018 00:00:00 | Cairo | Clouds | 0 | 4 | |
| 19 | 04.08.2018 00:00:00 | Cairo | Sun | 3 | 0 | |
| 20 | 03.08.2018 00:00:00 | Cairo | Sun | 2 | 0 | |
| 21 | 02.08.2018 00:00:00 | Cairo | Sun | 1 | 0 | |
| 22 | 01.08.2018 00:00:00 | Cairo | Sun | 0 | 0 | |
+----+---------------------+--------+---------+----------------+-------------+----------+
在不止一个问题上，您对自己的需求进行了扩展。我可以建议你考虑以上内容，并决定是否可以使用这两个计算来得出所需的最终结果吗。如果您仍然无法得出结论，请使用文本表格式将"预期结果"作为新列