gpt4 book ai didi

sqlite - 普通 CTE 的递归 CTE

转载 作者:行者123 更新时间:2023-12-01 17:32:45 31 4
gpt4 key购买 nike

我有一个 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"

最佳答案

这可以简单地使用另一个 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() 可用于从组中选择整行。)

关于sqlite - 普通 CTE 的递归 CTE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34911252/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com