为什么我不能在 WHERE 子句中使用我的列别名？

Question

我想比较当前行的值与前一行的值。我想出了这个办法，但行不通。它找不到PREV_NUMBER_OF_PEOPLE，所以我的WHERE条款无效。我不允许使用WITH。有人知道吗?

SELECT
ID
,NUMBER_OF_PEOPLE 
,LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
,DATE 
FROM (
SELECT * FROM DATAFRAME
WHERE DATE>=CURRENT_DATE-90
ORDER BY DATE DESC
) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE 

Answer 1

您的查询有几个问题:

• 过滤条件应在外部查询中。
• 新的列定义应该在内部查询中。
• order by应该在外查询中。

通过这些更改，它应该可以正常工作:

SELECT ID, NUMBER_OF_PEOPLE, PREV_NUMBER_OF_PEOPLE, DATE
FROM (SELECT D.*,
LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
FROM DATAFRAME D
) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE AND
DATE >= CURRENT_DATE - 90
ORDER BY DATE DESC;

您需要在LAG()之后过滤，以便您可以在日期范围中包含最早的一天。如果在内部查询中进行过滤，在这种情况下，LAG()将返回NULL。

您需要在子查询中定义别名，以便您可以在WHERE中引用它。SELECT中定义的别名不能在对应的WHERE中使用。这是一个SQL规则，与您正在使用的数据库无关。

Answer 2

您可以使用公共表表达式(CTE)来分割查询处理。

像这样:

WITH cte1 AS
(
SELECT * -- field list is advised...
FROM DATAFRAME
WHERE DATE >= CURRENT_DATE-90
),
cte2 AS
(
SELECT ID
,NUMBER_OF_PEOPLE 
,LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
,DATE 
FROM cte1
)
SELECT ID
,NUMBER_OF_PEOPLE 
,PREV_NUMBER_OF_PEOPLE
,DATE 
FROM cte2
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE
ORDER BY DATE DESC;

Answer 3

逻辑查询处理是定义正确结果的查询的概念解释，与查询子句的键入顺序不同，它从计算FROM子句开始。理解逻辑查询处理对于正确理解T-SQL至关重要。

在T-SQL中用于检索数据的主要语句是SELECT语句。以下是按照您应该键入它们的顺序指定的主要查询子句(称为"键入顺序"):

• 选择
从
• ,
GROUP BY
• 在
• ,

但如前所述，逻辑查询处理顺序，即概念解释顺序是不同的。它从FROM子句开始。以下是六个主要查询子句的逻辑查询处理顺序:

从

>

,

GROUP BY

在

选择

,

您可以使用CTE:

WITH CTE1 AS (
SELECT * FROM DATAFRAME
WHERE DATE>=CURRENT_DATE-90

),
CTE2 AS (
SELECT
ID
,NUMBER_OF_PEOPLE 
,LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
,DATE 
FROM CT2
)
SELECT * FROM CT2
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE

Answer 4

只需将lag()移动到派生表中。

SELECT *
FROM (
SELECT id, 
number_of_people, 
lag(number_of_people) over (order by date) as prev_number_of_people, 
date
FROM dataframe
WHERE date >= current_date - 90
) AS InnerQuery
WHERE number_of_people <> prev_number_of_people 
ORDER BY date DESC