9/10/2023 0 Comments Postgresql cross joinTo calculate the last day of the current month in Postgres you have to do following: date_trunc('month', the_date) + interval '1 month - 1 day'ĭate_trunc('month'. TODO: Adding the 0.5 part for the second group. So, with that the first part of your fix_costs value can be calculated.ģc) COALESCE(., 0) Because of filtering the NL records and the DIV BY ZERO error handling there are some NULL results in the fix_costs column, which is fixed with this function. Since you always added the last day of a month, it did not need to be calculated explicitly. So the join condition is: Where the months of both tables fit (with date_trunc() the compared values all are YYYY-MM-01)ģa) date_part('day'. ) normalizes a date to the first of the current month. This result is added as a new column to the result.ġb) Adding the FILTER clause removes the country = 'NL' records from this calculationġc) NULLIF(., 0) is to avoid DIVISION BY ZERO when calculating the percentage between the current sales_volumn and the previously calculated total (which would happen in the second sales_date group).ġd) CASE clause removes NL values from first part of divisionġe) Calculate the percentage for the second part of your fix_costs value (as I understood it)Ģa) Natural join which conditions: One table without None countries and the second only the None country records.Ģb) date_trunc('month'. ) window function returns the total of the sales_volumn values from the sales_date. JOIN sales s2 ON s1.country 'None' AND s2.country = 'None'ĪND date_trunc('month', s1.sales_date) = date_trunc('month', s2.sales_date)ġa) SUM(.) OVER (PARTITION BY. NULLIF(SUM(s1.sales_volume) FILTER (WHERE s1.country != 'NL') OVER (PARTITION BY s1.sales_date), 0) S2.fix_costs / date_part('day', s2.sales_date) *ĬASE WHEN s1.country != 'NL' THEN s1.sales_volume END However, this query can demonstrate the way to the solution: It is not clear, how in the second sales_date group the last fix_costs part comes to 0.5 (Percentage would yield DIVISION BY ZERO error.) This is descibed in step 2 which I highlighted. However, I guess, the most important part for your question is how to do the join. I am letting it there as documentation, nonetheless. So, in fact, I guess there was no need for the huge explanation, after all. CROSS JOINs don't have a join condition.Īfter re-reading your code I find that my construction is surprisingly similar to yours.There are no LAST_DAY() and DAY() functions in Postgres AFAIK.How do I need to modify the query to also make it work in Postgres 9.5? LINE 22: ON f.month_ld = LAST_DAY(s.sales_date) However, now I switched to Postgres 9.5 and I am getting an error on the CROSS JOIN: ERROR: syntax error at or near "ON" This query worked without any issue in MariaDB. Sales_volume / NULLIF(SUM(CASE WHEN country 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date), 0)ĮLSE (f.fix_costs / DAY(LAST_DAY(sales_date))) * 1 / SUM(country 'NL') OVER (PARTITION by sales_date) THEN ((f.fix_costs/ DAY(LAST_DAY(sales_date))) * WHEN SUM(CASE WHEN country 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date) > 0 With reference to the solution in this question I tried to apply the following query to get the expected result: SELECT I use the CTE in the example because it looks cleaner for the sake of the example.(country, sales_date, sales_volume, fix_costsĮxpected Result: sales_date | country | sales_volume | fix_costs This way the planner has a better understanding about the rows affected and the possibilities for using indexes. In this case one should do the same in the FROM clause, as a parenthesized sub-join instead of the current reference to data_points. (It has to materialize all the rows, regardless of there is data for a given day or not). Note: when either the table(s) or the generated series are big, doing the CROSS JOIN inside a CTE might be a bad idea. So, the whole query is: WITH data_points AS (ĬROSS JOIN generate_series(''::date, '', '1 day') t(i) Then, as the finishing step, I just (outer) join the existing measurements, based on the pest ID and date - optionally giving a default for the missing values via COALESCE(). This is easily achieved by a CROSS JOIN, see the WITH query below. I usually solve such problems by setting up a table for all the possible data points (here the pests and dates).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |