recursive CTE from Ordinary CTE

Question

I have a with clause that groups some weather data by time intervals and weather descriptions:

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

Result {counter, hour, 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"

Now I would like to write a recursive query that goes hour by hour selecting the top row. The top row will always include the description with the highest occurrence (count) for that time interval or in case of a tie, it will still chose the top row. Here's my first attempt:

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 playing around with the query it seems it wants my recursive members "from" to be from "segment" instead of my temp_table. I don't understand why it wants me to do that. I'm trying to do something similar to this example, but I would like only 1 row from each recursive query.

This is the result I desire {count, hour, description}:

    "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"

what determined the order of the ties (why is the top the the top row in a tie) was the engine free to choose? Does it really matter which one is returned? you could use min/max functions just to return 1. — xQbert, Jan 20 '16 at 22:11
@xQbert I suppose the engine is free to choose. I have no clue how it chose to order the ties. There's no correlation with the original time_stamps. It doesn't matter which one is chosen when there's a tie. I don't think I can use max() on a count(). — M. Smith, Jan 20 '16 at 22:22
Why recursive anyway? Just use row_number() to select the first entry for each hour — cha, Jan 20 '16 at 22:40

score 1 · Accepted Answer · answered Jan 21 '16 at 08:06

This can simply be done with another 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;

(In SQLite, MAX() can be used to select entire rows from a group.)

recursive CTE from Ordinary CTE

1 Answers1