66802

SQL Condition on Window function

Question:

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

In order to simply, let's say I have the following table <strong>AvgTemperatures</strong>, 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

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

I could do something like :

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

But :

********** Erreur ********** ERROR: window functions not allowed in WHERE clause

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 *").

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 ?

Answer1:

First of all it something called <strong><a href="http://social.technet.microsoft.com/wiki/contents/articles/20724.all-at-once-operations-in-t-sql.aspx#All-at-Once" rel="nofollow">all-at-once operation</a></strong>

<blockquote>

"All-at-Once Operations" means that all expressions in the same logical query process phase are evaluated logically at the same time.

</blockquote>

And great chapter <strong>Impact on Window Functions</strong>:

Suppose you have:

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; <blockquote>

<strong>All-at-Once operations tell us these two conditions evaluated logically at the same point of time.</strong> Therefore, SQL Server can evaluate conditions in WHERE clause in arbitrary order, based on estimated execution plan. So the main question here is which condition evaluates first.

</blockquote>

<strong>Case 1:</strong>

If ( Id = 1002 ) is first, then if ( ROW_NUMBER() OVER(ORDER BY Id) = 1 )

Result: 1002

<strong>Case 2:</strong>

If ( ROW_NUMBER() OVER(ORDER BY Id) = 1 ), then check if ( Id = 1002 )

Result: empty

<blockquote>

<strong>So we have a paradox.</strong>

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 <strong>SELECT</strong> and <strong>ORDER BY</strong> clauses!

</blockquote>

To get what you want you can wrap windowed function with CTE/subquery as in <a href="https://stackoverflow.com/a/33629600/5070879" rel="nofollow">Gordon answer</a>:

;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;

<kbd><strong><a href="https://data.stackexchange.com/stackoverflow/query/387988" rel="nofollow">LiveDemo</a></strong></kbd>

Output:

╔═════╦══════════╦═════╦═════════╗ ║ 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 ║ ╚═════╩══════════╩═════╩═════════╝

Answer2:

The simplest solution is to use the <a href="http://www.postgresql.org/docs/current/static/functions-aggregate.html" rel="nofollow">bool_or aggregate function</a>

select id, city, avg, months from avttemperatures where city in ( select city from avttemperatures group by 1 having bool_or(avg > 19) ) order by 2, 4 ; id | city | avg | months ----+----------+-----+-------- 11 | Miami | 28 | 1 12 | Miami | 25 | 4 13 | Miami | 21 | 12 14 | Miami | 22 | 15 15 | Miami | 20 | 24 1 | New-York | 20 | 3 2 | New-York | 19 | 6 3 | New-York | 15 | 12 4 | New-York | 15 | 24

The test table:

create table avttemperatures ( id int, city text, avg int, months int ); insert into avttemperatures (id, city, avg, months) values ( 1,'New-York',20,3), ( 2,'New-York',19,6), ( 3,'New-York',15,12), ( 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);

Answer3:

Use a subquery to get the maximum and then a where:

select t.* from (select t.*, max(avg) over (partition by city) as maxavg from avgTemperatures t ) t where maxavg > 19;

An alternative is to do this in the where clause:

select t.* from avgTemperatures t where t.city in (select t2.city from avgTemperatures t2 where t2.avg > 19);

Answer4:

No need to aggregate if you only want to know if <em>at least one</em> exists:

SELECT id, city, avg, months FROM avgtemperatures t WHERE EXISTS ( SELECT 42 FROM avgtemperatures x WHERE x.city = t.city AND x.avg > 19 ) ORDER BY city,months DESC ;

Note: avg is a bad name for a column.

Answer5:

You need to wrap this in a derived table to be able to use in the where clause:

select * from ( SELECT t.*, MIN(avg) OVER (PARTITION BY city) as city_avg FROM AvgTemperatures t ) x WHERE city_avg > 16

Recommend

  • Group By and add columns
  • How can I get a specific version of a dataset row?
  • How to get list of users who's birthday is today in MongoDB
  • FragmentActivity with a Fragment Containing a MapView
  • Always require certain dependencies in RequireJS
  • How to concat Pandas dataframe columns
  • Create function that can pass a parameter without making a new component
  • Using same constraints in multiple classes
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • How can we prepend rows to a react native list-view?
  • Python pandas melting data to multiple columns and coulmn names in another column
  • C# - Most efficient way to iterate through multiple arrays/list
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • netsh acl setting (need alternative method - registry settings?)
  • Not able to aggregate on nested fields in elasticsearch
  • Unable to decode certificate at client new X509Certificate2()
  • Remove final comma from string in vb.net
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Read text file and split every line in MSBuild
  • C# - Serializing and deserializing static member
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • Java applet as stand-alone Windows application?
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Display Images one by one with next and previous functionality
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • Web-crawler for facebook in python
  • Why winpcap requires both .lib and .dll to run?
  • Is there a mandatory requirement to switch app.yaml?
  • SetUp method failed while running tests from teamcity
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • SQL merge duplicate rows and join values that are different
  • python regex in pyparsing
  • Error creating VM instance in Google Compute Engine
  • How to set the response of a form post action to a iframe source?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • unknown Exception android