![]() ![]() I make my living from SQL training, SQL tuning and consulting and my book “SQL Performance Explained”. See also “ Null in Aggregate Functions (count, sum, …)” and “ Conforming Alternatives to filter”. For other invoices, the implied else null returns the null value, which does not change the result of sum. The expression CASE WHEN month = 1 THEN revenue END evaluates to the revenue for invoices from January. , SUM(CASE WHEN month = 12 THEN revenue END) dec_revenue , SUM(CASE WHEN month = 2 THEN revenue END) feb_revenue , SUM(CASE WHEN month = 1 THEN revenue END) jan_revenue Furthermore, else null is the default clause for case expressions without explicit else clause anyway-it is sufficient to skip the else clause altogether. Null is a very good choice for this because it does not change the result of any aggregate function-not even avg. The trick is to map values that do not satisfy the filter criteria to neutral values, which do not change the result of the aggregation. Luckily, this is not a big problem because case can be used for the very same purpose. , SUM(revenue) FILTER (WHERE month = 12) dec_revenueĮven though the filter clause was introduced with SQL:2003, it is hardly supported today. , SUM(revenue) FILTER (WHERE month = 2) feb_revenue , SUM(revenue) FILTER (WHERE month = 1) jan_revenue For this example, it is sufficient to centralize the extract expression within the query-either using the with clause, or as an inline view: SELECT year That could be a generated column or a view so that other queries could reuse this expressions. To make the query more literate, the extract expression can be moved to a central location. The revenues of the other months can be obtained in the same way. In this example, only the invoices from January. The filter clause limits the rows aggregated to those satisfying the condition in parenthesis. This can be easily accomplished with the filter clause: sum(revenue) FILTER (WHERE EXTRACT(MONTH FROM invoice_date) = 1) ![]() To get the revenue for January, for example, the expression sum(revenue) must take only invoices from January into account. The next step is to define twelve columns where each column summarizes the revenues of one month only. Obviously, the result not longer provides a monthly breakdown, nevertheless, this step is required to condense the result to a single row per year. The first step in implementing this requirement is to remove the month from the group by and select clauses to get one row per year: SELECT EXTRACT(YEAR FROM invoice_date) year In other words, the rows of a year should be turned into columns. Quite often, the data is required in another form: for example, just one row per year and a separate column for each month. The query returns the result in a purely vertical form-one value per row. The following explanation is therefore based on a query that reports monthly revenues: SELECT EXTRACT(YEAR FROM invoice_date) year This requirement is particularity common in a reporting context. At its heart, the requirement is to transpose data from multiple rows into columns of a single row. Pivoting data is a rather common problem that comes in many different flavors.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |