SQLite的：子查询组由最大数

Question

我用下面的示例行的SQLite数据库与{ID，温度，描述，TIME_STAMP列：

1 45 Clear 2016-01-13 10:24:17 
2 45 Clear 2016-01-13 10:24:41 
3 45 Clear 2016-01-13 10:24:41 
4 45 Rain 2016-01-13 10:24:41 
5 46 Clear 2016-01-13 10:38:29 
6 46 Clear 2016-01-13 10:38:53 
7 46 Clear 2016-01-13 10:39:08 
8 46 Clear 2016-01-13 10:39:08 

我对他们的运行此查询到他们组由时间间隔和天气描述：

SELECT AVG(current_temperatures) AS temp_avg, 
      CASE WHEN strftime('%M', time_stamp) < '30' 
      THEN strftime('%H', time_stamp) 
      ELSE strftime('%H', time_stamp, '+1 hours') END as hour, 
      current_weather_description, 
      count(*) as counter 
      FROM weather_events 
      GROUP BY strftime('%H', time_stamp, '+30 minutes'), current_weather_description 
      order by hour desc 

，结果如下{ROW_NUM，temp_avg，小时，current_weather_description，计数器}：

​​

我的问题是我如何通过最大计数器子查询每个小时和组。所以最终我想得到结果：

"46.0" "11" "Clear" "4" 
"45.0" "10" "Clear" "3" 

我是新来的SQL和SQLite。所有数据来自同一张表。 另外，由于查询的温度是平均值，它如何选择我要求它选择的其余列？例如，如果你选择time_stamp，你会发现它是来自数据库的一个特定的time_stamp。它是否随机选择要选择的列？

Answer 1

你的sql查询得到正确的输出。您已按照half hour time intervals和weather description对行进行分组。 10:00 time interval有两种不同的天气：“清除”和“雨”，以及10:30 time interval的一个天气：“清除”。如果您将从GROUP BY子句中移除weather_description列，则只会获得两行。

与common table expressions的解决方案只显示时间间隔中最常发生的天气描述。

;WITH temp_data AS(
    SELECT AVG(current_temperatures) AS temp_avg, 
      CASE WHEN strftime('%M', time_stamp) < '30' 
      THEN strftime('%H', time_stamp) 
      ELSE strftime('%H', time_stamp, '+1 hours') END as hour, 
      current_weather_description, 
      count(*) as counter 
    FROM weather_events 
    GROUP BY strftime('%H', time_stamp, '+30 minutes'), 
      current_weather_description 
) 

SELECT T1.* FROM temp_data AS T1 
WHERE T1.counter = (SELECT MAX(counter) FROM temp_data AS T2 
        WHERE T2.hour = T1.hour)