SQL:第一次出现和最后一次出现之间的行数-略有不同 [英] SQL: Count of rows between first and last occurrence - with a twist
本文介绍了SQL:第一次出现和最后一次出现之间的行数-略有不同的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想找到一个值的第一次出现和最后一次出现之间的行数.但是，当它们之间有五个或更多具有不同值的记录时，请停止计数.
I want to find the count of rows between first and last occurrence of a value. However when there are five or more records of a different value between them, stop counting.
因此，如果最后一次出现是今天，而第一次出现是昨天，则结果将是2(今天加上昨天).
So if last occurrence is today and first occurrence is yesterday, the result would be 2 (today plus yesterday).
如果最后一次出现是今天，第一次出现是8天前，并且两者之间没有发生，则结果将为'1'.但是，如果3天前还会再发生一次，则结果将为4(3 + 2 + 1天前加上今天).
If last occurrence is today and first occurrence is 8 days ago AND there is no occurrence in between the two, the result would be '1'. If however there would be another occurrence 3 days ago, the result would be 4 (3+2+1 days ago plus today).
我希望这是有道理的.
这是我的数据
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重新开始.
What I'm after is a query that returns for a given city and date the weather that day and the number of days since this weather first occurred. However, when there are gaps of five days or more, the count restarts from 1.
就像在11th Aug上查询Ankara一样，它将返回11，因为距Sun首次出现已经11天(包括今天).
Like when queried for Ankara on the 11th Aug it would return 11 since it's been 11 days (including today) since Sun first occurred.
但是对于11th Aug上的Cairo，它将返回1而不是7，因为从8月5日的Clouds到今天的Clouds已有5天以上的时间.
However for Cairo on the 11th Aug it would return 1 and not 7 because it's been 5+ days between Clouds on the 5th Aug and Clouds today.
我已经尝试过使用first_value()，LEAD，LAG和ROW_NUMBER进行很多操作，但是对于所有这些失败都是不幸的，没有什么意义.
I've tried many things with first_value(), LEAD, LAG and ROW_NUMBER but there is nothing that makes sense to how as it all failed miserably.
反正这里...
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 | |
+----+---------------------+--------+---------+----------------+-------------+----------+
在一个以上的问题中，您已经扩展了自己的要求.我可以建议您考虑上述内容，然后决定是否可以使用这两种计算得出所需的最终结果.如果您仍然无法得出结论，请使用文本表格式将预期结果"作为新列添加
Over more than one question you have expanded on your requirements. May I suggest you consider the above and decide if you can use the 2 calculations to arrive at the wanted final result. If you are still unable to come to a conclusion use the text table format to include the "expected result" as a new column
这篇关于SQL:第一次出现和最后一次出现之间的行数-略有不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
