sqlite - 普通 CTE 的递归 CTE

Question

我有一个 with 子句，按时间间隔和天气描述对一些天气数据进行分组:

With 
temp_table (counter, hour, current_Weather_description) as
(
    SELECT count(*) as counter,
           CASE WHEN  strftime('%M',  time_stamp) < '30' 
                THEN cast(strftime('%H', time_stamp)  as int)
                ELSE cast(strftime('%H', time_stamp, '+1 hours') as int)
           END as hour,
           current_weather_description
    FROM weather_events
    GROUP BY strftime('%H',  time_stamp, '+30 minutes'),
             current_Weather_Description
    order by hour desc 
)
select *
from temp_table

结果{计数器、小时、current_weather_description}:

"1" "10" "Cloudy"

"2" "9" "Clear"
"1" "9" "Meatballs"

"2" "8" "Rain"

"2" "7" "Clear"

"2" "6" "Clear"

"1" "5" "Clear"
"1" "5" "Cloudy"

"1" "4" "Clear"
"1" "4" "Rain"

"1" "3" "Rain"
"1" "3" "Snow"

"1" "2" "Rain"

现在我想编写一个递归查询，每小时选择顶行。顶行将始终包含该时间间隔内出现次数(计数)最高的描述，或者在平局的情况下，它仍会选择顶行。这是我的第一次尝试:

With recursive
temp_table (counter, hour, current_Weather_description) as
(
    SELECT count(*) as counter,
           CASE WHEN  strftime('%M',  time_stamp) < '30' 
                THEN cast(strftime('%H', time_stamp)  as int)
                ELSE cast(strftime('%H', time_stamp, '+1 hours') as int)
           END as hour,
           current_weather_description
    FROM weather_events
    GROUP BY strftime('%H',  time_stamp, '+30 minutes'),
             current_Weather_Description
    order by hour desc 
),
segment (anchor_hour, hour, current_Weather_description) as
(
    select cast(strftime('%H','2016-01-20 10:14:17') as int) as anchor_hour,
           hour,
           current_Weather_Description
    from temp_table
    where hour = anchor_hour
    limit 1
    union all
    select segment.anchor_hour-1,
           hour,
           current_Weather_Description
    from temp_table
    where hour = anchor_hour - 1
    limit 1
)
select *
from segment

通过查询查询，它似乎希望我的递归成员“from”来自“segment”而不是我的 temp_table。我不明白为什么它要我这样做。我正在尝试做类似的事情 example ，但我只想每个递归查询中的 1 行。

这是我想要的结果{计数，小时，描述}:

    "1" "10" "Cloudy"

    "2" "9" "Clear"

    "2" "8" "Rain"

    "2" "7" "Clear"

    "2" "6" "Clear"

    "1" "5" "Clear"

    "1" "4" "Clear"

    "1" "3" "Rain"

    "1" "2" "Rain"

Answer 1

这可以简单地使用另一个 GROUP BY 来完成:

WITH
temp_table(counter, hour, current_Weather_description) AS (
    ...
),
segment(count, hour, description) AS (
    SELECT MAX(counter),
           hour,
           current_Weather_description
    FROM temp_table
    GROUP BY hour
)
SELECT count, hour, description
FROM segment
ORDER BY hour DESC;

(在 SQLite 中，MAX() 可用于从组中选择整行。)