The FILTER clause provides a better way to deal with scoped aggregate functions and it’s a beautiful way to replace the CASE WHEN statements for these cases. The columns used in the HAVING condition must be used in the GROUP BY clause unless an aggregate function is used. The HAVING condition is the same as the WHERE condition that returns TRUE or FALSE. month | total_count | total_amount | mike_count | mike_amount | jon_count | jon_amount In PostgreSQL, the HAVING clause is used after the GROUP BY clause to filter the result of the GROUP BY clause based on the specified condition. The above query is made on the DVD Rental Sample Database and is used to produce a result set for the report that shows a side-by-side comparison of the number and total amounts of payments made in Mike's and Jon's stores broken down by months. Since 9.4 release we can replace the CASE WHEN clauses in these aggregate functions by the new FILTER clause: SELECT EXTRACT ( MONTH FROM payment_date ) AS month, COUNT ( * ) AS total_count, SUM (amount ) AS total_amount, COUNT ( * ) FILTER ( WHERE staff_id = 1 ) AS mike_count, SUM (amount ) FILTER ( WHERE staff_id = 1 ) AS mike_amount, COUNT ( * ) FILTER ( WHERE staff_id = 2 ) AS jon_count, SUM (amount ) FILTER ( WHERE staff_id = 2 ) AS jon_amount ORDER BY title The FILTER clause in Postgres 9.4 HSTORE Type The PostgreSQL HSTORE type as well as hstore literals are supported: HSTORE - HSTORE datatype. you can use a CASE WHEN as shown in the sample below: SELECT title, length, CASE WHEN length > 0 AND length 50 AND length 120 THEN 'Long' END duration The PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators: JSON.
Since CASE is an expression, you can use it in any places where an expression can be used e.g., SELECT, WHERE, GROUP BY, and HAVING clause. It allows you to add if-else logic to the query to form a powerful query. The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages.
Until Postgres 9.4 release, if you wanted to count a few set of records when executing an aggregate function, you had to use a CASE WHEN. Postgres 9.4 was released in December 2014 adding the FILTER clause to aggregate functions and this clause is quite useful when you want to count or sum specific records when executing group by.
0 kommentar(er)
