窗口函数的SQL条件

本文介绍了窗口函数的SQL条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想对我的数据库（PostgreSQL v9.4.5）发出特殊请求，但是我没有做到。

I want to do a special request on my database (PostgreSQL v9.4.5), but I don't manage to do it.

为了简单起见，假设我有下表 AvgTemperatures ，代表不同城市的平均气温不同，并根据不同的时间长度（以月为单位）进行计算：

In order to simply, let's say I have the following table AvgTemperatures, representing different averages of temperature taken in different cities, and calculated on different length of time (counted in months) :

 id |   city    |  avg | months 
----+-----------+------+--------
  1 |  New-York |   20 |     3   <--- average temperate over the last 3 months
  2 |  New-York |   19 |     6   <--- average temperate over the last 6 months
  3 |  New-York |   15 |    12   <--- etc
  4 |  New-York |   15 |    24
  5 |    Boston |   13 |     3
  6 |    Boston |   18 |     8
  7 |    Boston |   17 |    12
  8 |    Boston |   16 |    15
  9 |   Chicago |   12 |     2
 10 |   Chicago |   14 |    12
 11 |     Miami |   28 |     1
 12 |     Miami |   25 |     4
 13 |     Miami |   21 |    12
 14 |     Miami |   22 |    15
 15 |     Miami |   20 |    24

现在，想象一下，我想选择与至少在一个城市中的度量有关的所有行一个平均值超过19度。在这种情况下，我想要：

Now, imagine that I want to select all the rows concerning the measures in a city where at least one average has been over 19 degrees. In this case I want :

 id |   city    |  avg | months 
----+-----------+------+--------
  1 |  New-York |   20 |     3  
  2 |  New-York |   19 |     6  
  3 |  New-York |   15 |    12  
  4 |  New-York |   15 |    24  
 11 |     Miami |   28 |     1  
 12 |     Miami |   25 |     4  
 13 |     Miami |   21 |    12  
 14 |     Miami |   22 |    15  
 15 |     Miami |   20 |    24

我可以做以下事情：

 SELECT *
 FROM AvgTemperatures
 WHERE MIN(avg) OVER (PARTITION BY city) > 16

但是：

********** Erreur **********

ERROR: window functions not allowed in WHERE clause

此外，我不能在 GROUP BY 中使用：

What's more, I cannot use GROUP BY as in :

 SELECT *
 FROM AvtTemperatures
 GROUP BY city
 HAVING MIN(avg) > 16

因为由于聚合，我将丢失信息（由于以下原因，该查询无效）

because I will lose information due to the aggregation (by the way this query is not valid because of the "SELECT *").

我很确定我可以使用 OVER PARTITION BY 解决该问题，但我不知道如何。有人有想法吗？

I'm pretty sure I can use the OVER PARTITION BY to solve that, but I don't know how. Does someone have an idea ?

推荐答案

首先，它叫做

对窗口功能的影响：

假设您拥有：

CREATE TABLE Test ( Id INT) ;

INSERT  INTO Test VALUES  ( 1001 ), ( 1002 ) ;

SELECT Id
FROM Test
WHERE Id = 1002
  AND ROW_NUMBER() OVER(ORDER BY Id) = 1;

案例1：

如果首先是（Id = 1002），则如果（ROW_NUMBER（）OVER（ORDER BY Id）= 1）

结果：1002

案例2：

如果（ROW_NUMBER（）OVER（ORDER BY Id）= 1），则检查（Id = 1002）

结果：空

此示例说明了为什么我们不能在WHERE子句中使用窗口函数。
您可以对此进行更多思考，并找到为什么仅在 SELECT 和 ORDER BY 子句中允许使用窗口功能
的原因！

This example shows why we cannot use Window Functions in WHERE clause. You can think more about this and find why Window Functions are allowed to be used just in SELECT and ORDER BY clauses!

要获取所需内容，可以使用 CTE / subquery 包裹窗口函数，如：

To get what you want you can wrap windowed function with CTE/subquery as in Gordon answer:

;WITH cte AS
(
  SELECT t.*, MAX(AVG) OVER (PARTITION BY city) AS average
  FROM avgTemperatures t
)
SELECT *
FROM cte
where average > 19
ORDER BY id;

输出：

╔═════╦══════════╦═════╦═════════╗
║ id  ║   city   ║ avg ║ months  ║
╠═════╬══════════╬═════╬═════════╣
║   1 ║ New-York ║  20 ║     3   ║
║   2 ║ New-York ║  19 ║     6   ║
║   3 ║ New-York ║  15 ║    12   ║
║   4 ║ New-York ║  15 ║    24   ║
║  11 ║ Miami    ║  28 ║     1   ║
║  12 ║ Miami    ║  25 ║     4   ║
║  13 ║ Miami    ║  21 ║    12   ║
║  14 ║ Miami    ║  22 ║    15   ║
║  15 ║ Miami    ║  20 ║    24   ║
╚═════╩══════════╩═════╩═════════╝

这篇关于窗口函数的SQL条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！