主页 / dba / 问题 / 185344
Accepted
Mio
Asked: 2017-09-08 11:45:16 +0800 CST

使用 CTE 的最小最大值来定义下一个 CTE 的间隔范围

  • 772

我在 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？

redshift

1 个回答

  1. Best Answer

    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所以如果不支持交叉连接，您可以使用连接谓词始终计算为 true 的连接：

        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
    • 4

相关问题