рекурсивный CTE из обычного CTE

У меня есть предложение 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

Из игры с запросом кажется, что он хочет, чтобы мои рекурсивные члены «из» были из «сегмента», а не из моей таблицы 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"

sqlite common-table-expression recursive-cte
person M. Smith    schedule 20.01.2016    source источник
comment
что определяло порядок галстуков (почему верхний ряд в галстуке является верхним рядом) был ли двигатель свободен в выборе? Действительно ли имеет значение, какой из них возвращается? вы можете использовать функции min/max только для возврата 1.   -  person xQbert    schedule 21.01.2016
comment
@xQbert Я полагаю, что движок свободен в выборе. Я понятия не имею, как он решил заказать галстуки. Нет никакой корреляции с исходными временными метками. Неважно, какой из них выбран, когда есть ничья. Я не думаю, что могу использовать max() для count().   -  person M. Smith    schedule 21.01.2016
comment
Почему все-таки рекурсивный? Просто используйте row_number(), чтобы выбрать первую запись для каждого часа.   -  person cha    schedule 21.01.2016
comment
@cha SQLite не имеет row_number().   -  person CL.    schedule 21.01.2016

Ответы (1)


arrow_upward
1
arrow_downward

Это можно просто сделать с помощью другого 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() можно использовать для выбора целых строк из группы.)

person CL.    schedule 21.01.2016