blocks|key|2788201|text|您可以使用公共表表达式(CTE)来拆分查询处理。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2788202|就像这样：|2788203|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;|code-block|syntax|javascript|2788204|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You could use common table expression (CTE's) to split the query processing.
Something like this:
<pre><code>WITH cte1 AS
(
 SELECT * -- field list is advised...
 FROM DATAFRAME
 WHERE DATE &gt;= 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 &lt;&gt; PREV_NUMBER_OF_PEOPLE
ORDER BY DATE DESC;
</code></pre>

blocks|key|2788266|text|逻辑查询处理是对定义正确结果的查询的概念性解释，与关键字顺序的查询子句不同，它从计算FROM子句开始。理解逻辑查询处理是正确理解T的关键.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2788267|用于检索T中数据的主语句是SELECT语句.以下是按您应该键入的顺序指定的主要查询子句(称为“”)：|2788268|2788269|SELECT|unordered-list-item|2788270|FROM|2788271|WHERE|2788272|GROUP+BY|2788273|HAVING|2788274|ORDER+(|2788275|)|2788276|但如前所述，逻辑查询处理顺序，即概念解释顺序，是不同的。它从FROM子句开始。以下是六个主要查询子句的逻辑查询处理顺序：|2788277|2788278|2788279|2788280|GROUP+BY|2788281|2788282|2788283|ORDER+(|2788284|2788285|您可以使用CTE：|2788286|++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|code-block|syntax|javascript|2788287|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|1H|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|1I|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|1J|8|@]|9|@]|A|$]]|$1|L|3|M|5|G|7|1K|8|@]|9|@]|A|$]]|$1|N|3|O|5|G|7|1L|8|@]|9|@]|A|$]]|$1|P|3|Q|5|G|7|1M|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1N|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1O|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|I|5|G|7|1Q|8|@]|9|@]|A|$]]|$1|X|3|K|5|G|7|1R|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|G|7|1S|8|@]|9|@]|A|$]]|$1|10|3|O|5|G|7|1T|8|@]|9|@]|A|$]]|$1|11|3|F|5|G|7|1U|8|@]|9|@]|A|$]]|$1|12|3|13|5|G|7|1V|8|@]|9|@]|A|$]]|$1|14|3|S|5|6|7|1W|8|@]|9|@]|A|$]]|$1|15|3|16|5|6|7|1X|8|@]|9|@]|A|$]]|$1|17|3|18|5|19|7|1Y|8|@]|9|@]|A|$1A|1B]]|$1|1C|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]]|1D|$]]

Logical query processing is the conceptual interpretation of the query that defines the correct result, and unlike the keyed-in order of the query clauses, it starts by evaluating the FROM clause. Understanding logical query processing is crucial for correct understanding of T-SQL.
The main statement used to retrieve data in T-SQL is the SELECT statement. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):
<ul>
<li>SELECT</li>
<li>FROM</li>
<li>WHERE</li>
<li>GROUP BY</li>
<li>HAVING</li>
<li>ORDER BY</li>
</ul>
But as mentioned, the logical query processing order, which is the conceptual interpretation order, is different. It starts with the FROM clause. Here is the logical query processing order of the six main query clauses:
<ul>
<li>FROM</li>
<li>WHERE</li>
<li>GROUP BY</li>
<li>HAVING</li>
<li>SELECT</li>
<li>ORDER BY</li>
</ul>
You can use a CTE :
<pre><code> WITH CTE1 AS (
SELECT * FROM DATAFRAME
 WHERE DATE&gt;=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 &lt;&gt; PREV_NUMBER_OF_PEOPLE
</code></pre>

blocks|key|2788308|text|只需将lag()移动到派生表中即可。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2788309|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|code-block|syntax|javascript|2788310|entityMap^0|3|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Just move the <code>lag()</code> into the derived table.
<pre><code>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 &gt;= current_date - 90
) AS InnerQuery
WHERE number_of_people &lt;&gt; prev_number_of_people 
ORDER BY date DESC
</code></pre>

blocks|key|1399680|text|您的查询有几个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1399681|1399682|过滤条件应该在外部查询中。|unordered-list-item|1399683|新列定义应该在内部查询中。|1399684|+order+by应该在外部查询中。|offset|length|style|CODE|1399685|1399686|通过这些更改，它应该可以很好地工作：|1399687|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;|code-block|syntax|javascript|1399688|您需要在LAG()之后进行过滤，以便在日期范围内包括最早的一天。如果在内部查询中进行筛选，则LAG()将在这种情况下返回NULL。|1399689|您需要在子查询中定义别名，以便在WHERE中引用它。在SELECT中定义的别名不能在相应的WHERE中使用。这是一个SQL规则，而不是由于您正在使用的数据库。|1399690|entityMap^0|0|0|0|0|1|8|0|0|0|0|4|5|1A|5|1O|4|0|G|5|R|6|19|5|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|13|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|14|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|15|8|@$J|16|K|17|L|M]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|19|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1A|8|@]|9|@]|A|$T|U]]|$1|V|3|W|5|6|7|1B|8|@$J|1C|K|1D|L|M]|$J|1E|K|1F|L|M]|$J|1G|K|1H|L|M]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1I|8|@$J|1J|K|1K|L|M]|$J|1L|K|1M|L|M]|$J|1N|K|1O|L|M]]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|10|$]]

You have several issues with your query:
<ul>
<li>The filtering conditions should be in the outer query.</li>
<li>The new column definition should be in the inner query.</li>
<li>The <code>order by</code> should be in the outer query.</li>
</ul>
With these changes, it should work fine:
<pre><code>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 &lt;&gt; PREV_NUMBER_OF_PEOPLE AND
 DATE &gt;= CURRENT_DATE - 90
ORDER BY DATE DESC;
</code></pre>
You need the filtering after the <code>LAG()</code> so you can include the earliest day in the date range. If you filter in the inner query, the <code>LAG()</code> will return <code>NULL</code> in that case.
You need to define the alias in the subquery so you can refer to it in the <code>WHERE</code>. Aliases defined in a <code>SELECT</code> cannot be used in the corresponding <code>WHERE</code>. This is a SQL rule, not due to the database you are using.

I want to compare a value of my current row with the value of the row before. I came up with this, but it won't work. It can't find <code>PREV_NUMBER_OF_PEOPLE</code> so my <code>WHERE</code> clause is invalid. I'm not allowed to use <code>WITH</code>. Does anyone have an idea?
<pre><code>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&gt;=CURRENT_DATE-90
 ORDER BY DATE DESC
) AS InnerQuery
WHERE NUMBER_OF_PEOPLE &lt;&gt; PREV_NUMBER_OF_PEOPLE 
</code></pre>

Why can't I use my column alias in WHERE clause?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望将当前行的值与前面行的值进行比较。我想出了这个，但行不通。它找不到PREV_NUMBER_OF_PEOPLE，所以我的WHERE子句无效。我不允许使用WITH。有谁有主意吗？SELECT   ID   ,NUMBER_OF_PEOPLE    ,LAG(NUMBER_OF_PEOPLE) OVER (ORDER ...

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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