来自普通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“来自”段“，而不是我的temp_table。我不明白它为什么要我这么做。我试图做一些类似于这个示例的事情，但是我只想从每个递归查询中得到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

这可以通过以下方式与另一组简单地完成：

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()可用于从组中选择整行。)