SQL:从首次出现某些值以来的行数 [英] SQL: Count of rows since certain value first occurred

问题描述

在SQL Server中，我试图计算自过去5天以来首次观察到与今天相同的天气(假设今天是2018年8月6日)的天数.每个城镇.

In SQL Server, I'm trying to calculate the count of days since the same weather as today (let's assume today is 6th August 2018) was observed first in the past 5 days. Per town.

以下是数据:

+---------+---------+--------+--------+--------+
| Date    | Toronto | Cairo  | Zagreb | Ankara |
+---------+---------+--------+--------+--------+
| 1.08.18 | Rain    | Sun    | Clouds | Sun    |
| 2.08.18 | Sun     | Sun    | Clouds | Sun    |
| 3.08.18 | Rain    | Sun    | Clouds | Rain   |
| 4.08.18 | Clouds  | Sun    | Clouds | Clouds |
| 5.08.18 | Rain    | Clouds | Rain   | Rain   |
| 6.08.18 | Rain    | Sun    | Sun    | Sun    |
+---------+---------+--------+--------+--------+

这需要表现良好，但到目前为止，我只想针对每个镇进行单个查询(并且将会有数十个镇，而不仅仅是四个镇).这行得通，但不会扩展规模.

This needs to perform well but all I came up with so far is single queries for each town (and there are going to be dozens of towns, not just the four). This works but is not going to scale.

这里是多伦多的那个...

Here's the one for Toronto...

SELECT 
    DATEDIFF(DAY, MIN([Date]), GETDATE()) + 1 
FROM
    (SELECT TOP 5 * 
     FROM Weather 
     WHERE [Date] <= GETDATE()
     ORDER BY [Date] DESC) a
WHERE 
    Toronto = (SELECT TOP 1 Toronto 
               FROM Weather
               WHERE DataDate = GETDATE())

...由于今天有雨，而过去5天内的第一次降雨是8月3日，因此正确返回4.

...which correctly returns 4 since today there is rain and the first occurrence of rain within the past 5 days was 3rd August.

但是我要返回的是一个像这样的表:

But what I want returned is a table like this:

+---------+-------+--------+--------+
| Toronto | Cairo | Zagreb | Ankara |
+---------+-------+--------+--------+
| 4       | 5     | 1      | 5      |
+---------+-------+--------+--------+

这怎么可能?

推荐答案

您真的不想尝试对数据透视表执行此操作，并且当您声明数据不是以这种方式存储时，您还没有t向我们展示了如何以专栏的方式到达城市的枢纽-真可惜.

You really don't want to be trying to do this on pivoted data, and while you state that the data isn't stored that way, you haven't shown us how you got to the pivot of cities as columns - which is a shame.

因此，我已在一个公用表表达式中取消透视"该样本，然后使用apply operator来计算前5天相同天气的先前发生次数.看来您知道如何旋转，我留给您去旋转最终结果.

So, I have "unpivoted" the sample in a common table expression, then used an apply operator to count the prior occurrences of the same weather in 5 previous days. As it seems you know how to pivot I leave it to you to then pivot the final result.

with cte as (
        select
              date, city, weather
        FROM (
              SELECT * from mytable
             ) AS cp
        UNPIVOT (
                  Weather FOR City IN (Toronto, Cairo, Zagreb, Ankara)
            ) AS up
    )

select 
        date, city, weather, ca.prior
from cte
cross apply (
    select count(*) as prior
    from cte as prev 
    where prev.city = cte.city
    and prev.date between dateadd(day,-6,cte.date) and dateadd(day,-1,cte.date)
    and prev.weather = cte.weather
    ) ca

使用此示例数据:

CREATE TABLE mytable(
   Date    date  NOT NULL
  ,Toronto VARCHAR(9) NOT NULL
  ,Cairo   VARCHAR(9) NOT NULL
  ,Zagreb  VARCHAR(9) NOT NULL
  ,Ankara  VARCHAR(9) NOT NULL
);
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180801','Rain','Sun','Clouds','Sun');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180802','Sun','Sun','Clouds','Sun');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180803','Rain','Sun','Clouds','Rain');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180804','Clouds','Sun','Clouds','Clouds');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180805','Rain','Clouds','Rain','Rain');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180806','Rain','Sun','Sun','Sun');

上面的查询产生了以下结果:

The query above produced this result:

+----+---------------------+---------+---------+-------+
|    |        date         |  city   | weather | prior |
+----+---------------------+---------+---------+-------+
|  1 | 01.08.2018 00:00:00 | Ankara  | Sun     |     0 |
|  2 | 02.08.2018 00:00:00 | Ankara  | Sun     |     1 |
|  3 | 03.08.2018 00:00:00 | Ankara  | Rain    |     0 |
|  4 | 04.08.2018 00:00:00 | Ankara  | Clouds  |     0 |
|  5 | 05.08.2018 00:00:00 | Ankara  | Rain    |     1 |
|  6 | 06.08.2018 00:00:00 | Ankara  | Sun     |     2 |
|  7 | 01.08.2018 00:00:00 | Cairo   | Sun     |     0 |
|  8 | 02.08.2018 00:00:00 | Cairo   | Sun     |     1 |
|  9 | 03.08.2018 00:00:00 | Cairo   | Sun     |     2 |
| 10 | 04.08.2018 00:00:00 | Cairo   | Sun     |     3 |
| 11 | 05.08.2018 00:00:00 | Cairo   | Clouds  |     0 |
| 12 | 06.08.2018 00:00:00 | Cairo   | Sun     |     4 |
| 13 | 01.08.2018 00:00:00 | Toronto | Rain    |     0 |
| 14 | 02.08.2018 00:00:00 | Toronto | Sun     |     0 |
| 15 | 03.08.2018 00:00:00 | Toronto | Rain    |     1 |
| 16 | 04.08.2018 00:00:00 | Toronto | Clouds  |     0 |
| 17 | 05.08.2018 00:00:00 | Toronto | Rain    |     2 |
| 18 | 06.08.2018 00:00:00 | Toronto | Rain    |     3 |
| 19 | 01.08.2018 00:00:00 | Zagreb  | Clouds  |     0 |
| 20 | 02.08.2018 00:00:00 | Zagreb  | Clouds  |     1 |
| 21 | 03.08.2018 00:00:00 | Zagreb  | Clouds  |     2 |
| 22 | 04.08.2018 00:00:00 | Zagreb  | Clouds  |     3 |
| 23 | 05.08.2018 00:00:00 | Zagreb  | Rain    |     0 |
| 24 | 06.08.2018 00:00:00 | Zagreb  | Sun     |     0 |
+----+---------------------+---------+---------+-------+

自第一次发生以来(过去5天内)的天数

For count of days since the first occurrence (within the past 5 days)

select 
        date, city, weather, datediff(day,ca.prior,cte.date) as prior
from cte
cross apply (
    select min(prev.date) as prior
    from cte as prev 
    where prev.city = cte.city
    and prev.date between dateadd(day,-6,cte.date) and dateadd(day,-1,cte.date)
    and prev.weather = cte.weather
    ) ca

这篇关于SQL:从首次出现某些值以来的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！