how do i translate postgresql queries into mysql? Here is the actual request

with vw as ( select area_id, (case when to_char(period, 'yyyy')::INTEGER = %(year)s then to_char(period, 'mm')::INTEGER when to_char(period, 'yyyy')::INTEGER < %(year)s then 0 end) month_num, round(sum((case when type=1 then sum else 0 end))/1000000,1) consumption, round(sum((case when type=2 then sum else 0 end))/1000000,1) payment from clearing_main where (0 = %(abonent_type)s or abonent_type = %(abonent_type)s) and region_id = %(region_id)s and (0 = %(area_id)s or area_id = %(area_id)s) group by area_id, month_num ) select gs_month_num as id, (select sum(consumption)-sum(payment) from vw where vw.month_num < gs_month_num) begin_debt, (select sum(consumption) from vw where vw.month_num = gs_month_num) consumption, (select sum(payment) from vw where vw.month_num = gs_month_num) payment, (select sum(consumption)-sum(payment) from vw where vw.month_num = gs_month_num) debt_plus, (select sum(consumption)-sum(payment) from vw where vw.month_num <= gs_month_num) end_debt, (select (case when sum(consumption) > 0 then round(sum(payment)*100/sum(consumption), 1) else 0 end) from vw where vw.month_num = gs_month_num) percent from generate_series(1, extract('month' from now())::integer) gs_month_num order by gs_month_num;

it also generates months from 1 to the desired month. I did something similar, but it only shows me one month.

 SELECT g_month as id, (SELECT SUM(consumption)-SUM(payment) FROM y WHERE y.month < g_month) AS begin_debt, (SELECT SUM(payment) FROM y WHERE y.month = g_month) AS payment, (SELECT SUM(consumption)-SUM(payment) FROM y WHERE y.month <= g_month) AS end_debt, (SELECT (CASE WHEN SUM(consumption) > 0 THEN ROUND(SUM(payment)*100/SUM(consumption), 1) ELSE 0 END) FROM y WHERE y.month = g_month) AS percent FROM (SELECT MONTH(NOW()) AS g_month) g_month, (SELECT area_id, (CASE WHEN YEAR(period) = 2016 THEN MONTH(period) WHEN YEAR(period) < 2016 THEN 0 END) AS month, ROUND(SUM((CASE WHEN type = 1 THEN sum ELSE 0 END))/1000000, 1) AS consumption, ROUND(SUM((CASE WHEN type = 2 THEN sum ELSE 0 END))/1000000, 1) AS payment FROM main WHERE abonent_type = 1 AND region_id = 79 AND area_id = 7922 GROUP BY area_id, month) AS y ORDER BY g_month`

Also in this (SELECT SUM(consumption)-SUM(payment) FROM y WHERE y.month < g_month) AS begin_debt is a call to the table y and it gives an error that the table was not found. I will be grateful.

  • You generate_series are not simulated as "in the forehead". You can like this here. Stackoverflow.com/questions/536013/… - Mike
  • And you need to rewrite the request for good, and at postgesql, in the form that it is impossible to work with it now and for the database the load is too large with such a number of subqueries that could be reduced to almost one. And without an example of a database with data in it, it is unrealistic to help you, requests like this are simply not replicated, it is necessary to check and verify the issue - Mike
  • The fact of the matter is that the request needs to be processed, but already in mysql postgresql the code is not mine, so it’s impossible to make an adequate request to the tables ... - iAxel
  • An example of tables with data on some sqlfiddle would be done, then there would be something to talk about. In my current view, all subqueries of the form (SELECT SUM(consumption)-SUM(payment) FROM y WHERE y.month < g_month) as begin_debt expanded into something in the form of SUM(IF(y.month < g_month, consumption-payment,0)) . Because it seems that this is data from previous lines, it might be easier to get them back to variables in previous lines. And the subquery that calls from the sample list to table Y is basically impossible - Mike
  • I agree with you when referring to the Y table, displays an error that there is no such table, but even so I have no idea how to process the request, and reduce everything to a minimum, do you have options? it's very important to me - iAxel

2 answers 2

The query will require the seqnum table, with a single column X , which will have 13 lines with ordinal numbers from 0 to 12 - this is a replacement for generate_series. The wild number of subqueries in the original postgresql query was not needed at all, all this could be done with analytic window functions. Unfortunately, there are no such in MySQL, so I @ed variable ( @ed ) for the cumulative accumulation of the debit of the account and transferring it to the next line as the initial one. The final group by and having had to be applied because to get a production rate at the beginning of the year, you need a line with zero month, which was absent in the final sample of postgresql (generate did not generate line 0).

 select month_num, round(@ed,1) as begin_debt, consumption, payment, consumption-payment as debt_plus, @ed:=round(@ed+coalesce(consumption,0)-coalesce(payment,0),1) as end_debt, (case when consumption>0 then round(payment*100/consumption,1) else 0 end) as percent from ( select SX as month_num, sum(consumption) as consumption, sum(payment) as payment from seqnum S left join ( select (case when year(period) = 2016 then month(period) when year(period) < 2016 then 0 end) as month_num, round(sum((type=1)*sum),1) consumption, round(sum((type=2)*sum),1) payment from main M where region_id = 79 and period<=now() group by area_id, month_num ) B on SX=B.month_num where SX<=month(now()) group by SX ) A, (select @ed:=0) B group by month_num having month_num>0 order by month_num

So that the query could produce results on the sample data you provided made the following changes:

  1. Removed the abonent_type check
  2. removed the division of sums by million
  3. added to the where clause of the internal query and period<=now() , because previously the records with the year> 2016 were selected, but they belonged to the month NULL, which excluded them from the final sample.

I left the grouping by area_id in the internal query so that it would introduce rounding errors, as it did in the old query from postgresql, perhaps such results were expected ...

  • Thank you very much, you helped me a lot), but a new nuance appeared, the calculation for 8 months did not turn out to be correct, for about 8 months more than 10 million were paid, but on request even 1 million does not show - iAxel
  • I will update the data now and recheck again, but thank you very much) I hope you will help again - iAxel
  • @iAxel The results of the internal subquery, look it short, it will be easier to understand. If it gives errors, remove the group by; see the raw data that it takes, whether everything gets into them - Mike
  • I did not remove anything) updated the data in the database and everything fell into place, thanks again! - iAxel

cte is only in MySQL 8.0 / MariaDB 10.2, but they are not yet ga

recursive can be emulated using the procedure

in your case, you can simply transfer to the from-subquery

regarding what is not possible from the subquery in the select part, it will turn to the subquery in the from part - duplicate y (i.e. from-subquery) in the subquery in the select part, for example:

instead

select 1, (select 2 from y) x from (select 3) y;

need to

select 1, (select 2 from (select 3) y) x from (select 3) y;

readability will be terrible, but there's nothing to be done

To generate a sequence, use the reference table or MariaDB emnip 10.1