计算 2 个日期之间的唯一天数，其中每一天在 MySQL 中都有多行 [英] Count unique number of days between 2 dates where each day will have multiple rows in MySQL
问题描述
我有一个 mysql 数据库，其中包含自 2004 年以来大约 450 万行天气数据.我试图找出温度等于或低于 0 的时间段内有多少天.下面是一个数据示例(比温度更多的列) 每分钟收集一次数据.我意识到我的日期和时间列应该在一个单独的日期时间列中，我现在确实在使用它，但它只有最近几个月的数据.
I have a mysql database with about 4.5 million rows of weather data since 2004 in it. I am trying to find out how many days in a time period that the temperature was at or below 0. Below is an example of the data (many more columns than just temperature) Data is collected every minute. I realize that my date and time columns should be in a single datetime column and I do use that now but it only has data for the last few months in it.
date time temperature
2014-02-01 23:58:00 -20.3
2014-02-01 23:59:00 -20.4
--- --- ---
2014-02-02 00:00:00 -20.5
2014-02-02 00:01:00 -20.5
2014-02-02 00:02:00 -20.6
--- --- ---
2014-02-17 08:30:00 17.2
2014-02-17 08:31:00 17.2
我可以单独显示一个月的天数:
I can show the days for a month individually:
mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2013' and month(date) = '12' and temperature <='0' group by天(日期)按天(日期)升序排列；
mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2013' and month(date) = '12' and temperature <= '0' group by day(date) order by day(date) asc;
+------------+----------+-------+
| Ddate | Ttime | Temp |
+------------+----------+-------+
| 2013-12-05 | 23:59:00 | -3.6 |
| 2013-12-06 | 23:59:00 | -22.7 |
| 2013-12-07 | 23:59:00 | -25.2 |
| 2013-12-08 | 23:59:00 | -4 |
---------------------------------
+------------+----------+-------+
25 rows in set (6.95 sec)
以下内容不起作用，因为它只显示 1 月的数据，而没有显示 2 月的数据(截至今天，即 2 月 17 日).
The following does not work though because it only shows the data for January and nothing for February (through today which is February 17).
mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2014' and temperature <= '0' group by day(date) order by day(date)) 升序；
mysql> select distinct(date) as Ddate, time as Ttime, temperature as Temp from wx_data where Year(date) = '2014' and temperature <= '0' group by day(date) order by day(date) asc;
+------------+----------+-------+
| Ddate | Ttime | Temp |
+------------+----------+-------+
| 2014-01-01 | 00:00:00 | -20.7 |
| 2014-01-02 | 00:00:00 | -28.8 |
| 2014-01-03 | 00:00:00 | -12.5 |
| 2014-01-04 | 08:39:00 | 0 |
| 2014-01-05 | 00:00:00 | -19.8 |
---------------------------------
| 2014-01-31 | 00:00:00 | -21.5 |
+------------+----------+-------+
28 rows in set (6.86 sec)
为了得到二月，我需要使用当前月份再做一次选择.因此，虽然我可以手动添加总行数(12 月和 1 月为 53)，但我仍然需要为 2 月做另一个选择，并在那些天数中添加总共 68 天.我只想计算时间跨度中的天数，而不是总行数.
In order to get February, I need to do another select using the current month. So, while I can add the total rows manually (53 for December and January), I would still have to do another select for February and add in those days for a total of 68 days. I just want to get a count of the days in the timespan, not the total number of rows.
大致如下:
从 wx_data 中选择 count_number_of_days，其中温度 <= 0;
select count_number_of_days from wx_data where temperature <= 0;
我希望这是有道理的.
推荐答案
如果您只是在寻找指定时间范围内低于零的天数，那么您可以使用以下内容 (SQL Fiddle):
If you are just looking for a count of the days below zero for a specified time frame then you could use the following (SQL Fiddle):
SELECT COUNT(*)
FROM
(
SELECT DISTINCT m.date
FROM MyTable AS m
WHERE m.temperature < 0
AND Year(m.date) = 2014
) AS mm
在示例 SQL Fiddle 中，有一天中具有多个温度读数的独特日子.如果这些读数中的任何一个低于零，则将其计算在内.如您所见，有四个独特的日子，其中只有三个低于零.
In the example SQL Fiddle there are unique days with multiple temperature readings during the day. If any of those readings fall below zero then it is counted. As you can see there are four unique days of which only three of those fall below zero.
这篇关于计算 2 个日期之间的唯一天数，其中每一天在 MySQL 中都有多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
