CODEWITHSUNDEEP
sqlitecommon-table-expressionrecursive-cte
M. Smith
M. Smith

Reputation: 389

recursive CTE from Ordinary CTE

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"

Upvotes: 1

Views: 143

Answers (1)

CL.
CL.

Reputation: 180080

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.)

Upvotes: 1

Related Questions