使用单个 SQL 相关子查询获取两列

Question

我的问题由以下查询表示：

SELECT 
  b.row_id, b.x, b.y, b.something,
  (SELECT a.x FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_x,
  (SELECT a.y FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_y
FROM 
  my_table b

我使用相同的子查询语句两次，以获取 source_x 和 source_y。 这就是为什么我想知道是否可以仅使用一个子查询来完成它？

因为一旦我对真实数据（数百万行）运行此查询，它似乎永远不会完成，并且需要数小时甚至数天（我的连接在结束前挂断）。

我正在使用 PostgreSQL 8.4

Answer 1

我认为你可以使用这种方法：

SELECT b.row_id
     , b.x
     , b.y
     , b.something
     , a.x
     , a.y
  FROM my_table b
  left join my_table a on a.row_id = (b.row_id - 1)
                      and a.something != 42

Answer 2

@DavidEG 发布了查询的最佳语法。

但是，您的问题绝对不仅仅是查询技术。使用 JOIN 代替两个子查询最多可以将速度提高两倍。很可能更少。这并不能解释“小时”。即使有数百万行，设置得当的 Postgres 也应该在几秒钟而不是几小时内完成简单的查询。

• 首先值得注意的是查询中的语法错误：

  ... WHERE a.row_id = (b.row_id - 1), a.something != 42
  

  这里需要

  AND 或 OR，而不是逗号。

• 接下来要检查的是索引。如果 row_id 不是主键，则可能没有索引。为了获得此特定查询的最佳性能，请创建一个多列索引(row_id, some) 上的strong>，如下所示：

  在 my_table （row_id，某事）上创建索引 my_table_row_id_something_idx
  

• 如果过滤器每次都排除 相同的值 <代码>某事！= 42您还可以使用部分索引 而是为了额外加速：

  在 my_table (row_id) 上创建索引 my_table_row_id_something_idx
  哪里有东西！= 42
  

  只有当42是一个通用值或某物是一个比整数更大的列时，这才会产生实质性的差异。 （由于数据对齐，具有两个整数列的索引通常在磁盘上占用的大小与仅具有一列的索引相同。请参阅：

  • 在 PostgreSQL 中计算和节省空间
  • 复合索引也适合第一个字段的查询吗？

• 当性能成为问题时，它始终是一个问题检查您的设置是个好主意。资源在许多发行版都无法处理“数百万行”。

• 根据您的 Postgres 实际版本，升级到当前版本（撰写本文时为 9.1）可能会有很大帮助。

• 最终，硬件始终也是一个因素。调整和优化只能让您到目前为止。

Answer 3

老式语法：

SELECT 
  b.row_id, b.x, b.y, b.something
  , a.x AS source_x
  , a.y AS source
FROM my_table b
    ,my_table a 
WHERE a.row_id = b.row_id - 1
  AND a.something != 42
  ;

Join 语法：

SELECT 
  b.row_id, b.x, b.y, b.something
  , a.x AS source_x
  , a.y AS source
FROM my_table b
JOIN my_table a 
  ON (a.row_id = b.row_id - 1)
WHERE a.something != 42
  ;

Answer 4

SELECT b.row_id, b.x, b.y, b.something, a.x, a.y
  FROM my_table b
  LEFT JOIN (
    SELECT row_id + 1, x, y
      FROM my_table
      WHERE something != 42
  ) AS a ON a.row_id = b.row_id;

Answer 5

Postgres：

    SELECT 
      b.row_id, b.x, b.y, b.something,
      source_x,
      source_y
    FROM 
      my_table b,
LATERAL(SELECT a.x AS source_x, a.y AS source_y FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 )

MsSQL

SELECT 
      b.row_id, b.x, b.y, b.something,
      source_x,
      source_y
    FROM 
      my_table b
OUTER APPLY(SELECT a.x AS source_x, a.y AS source_y FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 )