我正在考慮創建一個串列，在那里我可以看到在給定的小時內生成了多少個 ID。因為我想提前顯示從 8 到 21 的小時數，而與日期的小時數無關，所以我使用了 generate series，現在需要找到 Snowfake 的等效項。這是我的查詢：
series as (
SELECT seq4() as Hour
FROM TABLE(GENERATOR(rowcount => 21))
where Hour between 7 and 20
ORDER BY Hour),
ID_table as (
select extract(hour from date) as "Hour",
count(ID) as "Count"
from ID_table
group by 1)
select (Hour.Hour) 1 AS "Hour",
id."Count",
from series as Hour
left join ID_table as id on id."Hour" = Hour.Hour
order by Hour.Hour;
出于某種原因，我只得到了 8 到 16 小時，但是，我希望它顯示 8 到 21 小時，可能是什么問題？
uj5u.com熱心網友回復：
您應該始終考慮 SEQ() 函式不保證間隙，因此為了生成范圍，我建議您使用 ROW_NUMBER() 函式：
https://community.snowflake.com/s/article/Generate-gap-free-sequences-of-numbers-and-dates
無論如何，當我測驗它時，我看到它回傳預期的數字：
SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21));
-- returnns numbers from 0 to 20
SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21)) where Hour between 7 and 20 order by hour;
-- returnns numbers from 7 to 20
with series as (SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21)) where Hour between 7 and 20 ORDER BY Hour)
select (Hour.Hour) 1 AS "Hour"
from series as Hour;
-- returnns numbers from 8 to 21
可能是瀏覽器/用戶界面的問題嗎？
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/398309.html
上一篇：將字典串列分成多個串列
下一篇：將串列的元素映射為另一個字典的值