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 kommentar(er)
