主页 / dba / 问题 / 247275
Accepted
roy naufal
Asked: 2019-09-08 17:27:09 +0800 CST

'where' 中使用的 RANK 返回无效列，但存在于结果集中

  • 772

我基本上有一张桌子，有date, timestamp, DID, coordinates。

我想要一个查询，它将返回带有第 X 天的最后一个坐标、第 X+1 天的第一个坐标和坐标的行。所以它只会返回有 2 个连续日期的结果。

这就是我想出的。一直试图让这个查询工作，它几乎是完美的，但我只需要添加注释掉的where条件，它就会完全符合我的要求。但是当我取消注释时，我收到一个错误“列不存在”：

SELECT  a.timestamp_intersecting_date d1,b.timestamp_intersecting_date d2,   
        a."DID", 
        a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
        RANK () OVER ( 
          PARTITION BY a.timestamp_intersecting_date
          ORDER BY a.timestamp_intersecting_max DESC
       ) timestamp_d1_rank ,
        RANK () OVER ( 
          PARTITION BY b.timestamp_intersecting_date
          ORDER BY b.timestamp_intersecting_max ASC
       ) timestamp_d2_rank,
        a.coords_centroid, b.coords_centroid
FROM
    signals a
INNER JOIN signals b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
--AND timestamp_d1_rank = 1
--AND timestamp_d2_rank = 1
ORDER BY a."DID", t1 desc, t2 asc

如何解决这个问题？

postgresql

3 个回答

  1. 不能在WHERE子句中使用别名，也不能使用 Window 函数。

    为了演示，考虑这个例子（小提琴）。

    CREATE TABLE payment (amount INTEGER, pay_date DATE);

INSERT INTO payment VALUES (54,  '2019-09-01'), (56,  '2019-09-01'), (154, '2019-09-02'), 
(156, '2019-09-02'), (254, '2019-09-03'), (256, '2019-09-03');

    然后运行以下两个查询：

    SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE the_sums > 200; -- ERROR:  column "the_sums" does not exist

    和

    SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE SUM(amount) OVER (PARTITION BY pay_date) > 200;
-- ERROR:  window functions are not allowed in WHERE

    您没有发布错误消息或 PostgreSQL 版本，但您的问题是timestamp_d1_rank并且timestamp_d2_rank是别名。

    你有两个选择：

    • 第一个选项：在子查询中使用别名/窗口函数（也在小提琴中） 


    SELECT * FROM
(
SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
) AS tab
WHERE the_sums < 200;

    结果：

    amount    pay_date  the_sums
    54  2019-09-01       110
    56  2019-09-01       110
    • 第二种选择：您可以使用CTE(aka此处WITH概述的子句或@ypercubeᵀᴹ 上面的答案中的子句，也可以查看小提琴。

    有关这整个区域的一个很好的解释，请参见此处。

    • 5
  2. Best Answer

    您不能在WHERE子句中使用窗口/排名函数或它们的别名，因为它们是在评估SELECT之后在WHERE中评估的。

    WHERE在评估窗口函数之后，您可以使用子查询（派生表或 CTE）来处理第二个子句：

    SELECT d1, d2,
       "DID",
       t1, t2,
       coords_centroid_a,
       coords_centroid_b
FROM
  (
    SELECT  a.timestamp_intersecting_date d1, b.timestamp_intersecting_date d2,   
            a."DID", 
            a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
            RANK () OVER ( 
              PARTITION BY a.timestamp_intersecting_date
              ORDER BY a.timestamp_intersecting_max DESC
           ) timestamp_d1_rank ,
            RANK () OVER ( 
              PARTITION BY b.timestamp_intersecting_date
              ORDER BY b.timestamp_intersecting_max ASC
           ) timestamp_d2_rank,
            a.coords_centroid AS coords_centroid_a, 
            b.coords_centroid AS coords_centroid_b
    FROM
        signals a
    INNER JOIN signals b ON (a."DID" = b."DID")
    WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
    AND a."DID" = b."DID"
  ) AS t
WHERE t.timestamp_d1_rank = 1
  AND t.timestamp_d2_rank = 1
ORDER BY "DID", t1 DESC, t2 ASC ;
    • 4

  3. 现有答案已正确解决了与输入和输出列名称有关的语法规则的混淆。这是一个潜伏的性能问题。

    CROSS JOIN您的查询中有一个瞬态，随着每个("DID", date_col). 示例：如果 a"DID"每天有 100 个坐标，则查询最终会处理 10.000 (!) 个与第二天的组合。

    基于这个假设的表定义：

    CREATE TABLE signals (
   "DID" serial PRIMARY KEY
 , date_col date NOT NULL  -- ?
 , time_col time NOT NULL  -- ?
 , coords_centroid geography  -- ?
);

    假设每个只有几("DID", date_col)行，这个查询应该已经快得多了：

    SELECT a."DID", a.date_col, t1, coords1, t2, coords2
FROM  (  -- first row per ("DID", date_col)
   SELECT DISTINCT ON ("DID", date_col)
          "DID", date_col, time_col AS t2, coords_centroid AS coords2
   FROM   signals
   ORDER  BY "DID", date_col, time_col
   ) b
CROSS JOIN LATERAL (  -- corresponding last row from previous day
   SELECT "DID", date_col, time_col AS t1, coords_centroid AS coords1
   FROM   signals a
   WHERE  a."DID" = b."DID"
   AND    a.date_col = b.date_col - 1
   ORDER  BY time_col DESC
   ) a;

    需要在("DID", date_col, time_col).

    根据您的实际设置和实际数据分布，此处可能会做更多工作。如果您有兴趣并提供缺失的信息，我会详细说明...

    • 3

相关问题