使用 CTE 中的最小最大值來定義下一個 CTE 的間隔範圍

我在 Redshift 上有一個表，在查詢中我試圖從生成的序列中生成 id 列表和 id 列表。

WITH 

missing_rows AS (
 SELECT id, created_at
 FROM logs
 WHERE created_at > GETDATE() - INTERVAL '2 hours'
),

range AS (
 SELECT min(id) AS min_id, max(id) AS max_id
 FROM missing_rows
),

series AS (
 SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id, 
 FROM logs
 FULL OUTER JOIN range ON ???? -- here is my issue
 WHERE active_connections.id 
BETWEEN range.min_id -- how to use min_id from range
   AND range.max_id -- how to use max_id from range 
)

SELECT *
FROM series

如何在下一個查詢中使用範圍值series？

Range 正好返回 1 行，因此您可以使用笛卡爾積（交叉連接）：

, series AS (
   SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id, 
   FROM logs
   CROSS JOIN range
   WHERE active_connections.id BETWEEN range.min_id
                                   AND range.max_id
)

我不熟悉，redshift所以如果不支持交叉連接，您可以使用連接謂詞始終評估為真的連接：

   FROM logs
   JOIN range ON 1=1
   WHERE active_connections.id BETWEEN range.min_id
                                   AND range.max_id

或舊式“，”加入；

   FROM logs, range
   WHERE active_connections.id BETWEEN range.min_id
                                   AND range.max_id

您還可以使用普通連接並在 on 子句中使用 where 謂詞：

   FROM logs
   JOIN range 
       ON active_connections.id BETWEEN range.min_id
                                    AND range.max_id

引用自：https://dba.stackexchange.com/questions/185344