Frank Millman schrieb am 19.03.2021 um 10:16:
Very often "distinct on ()" is faster in Postgres compared to the equivalent 
solution using window functions
The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing 
- at least I can't spot a difference.

If that is correct, you can move them into a common table expression - maybe 
detecting that is SQL Server's secret sauce.

There is a difference.

cl_bal selects WHERE tran_date <= '2018-03-31'.

op_bal selects WHERE tran_date < '2018-03-01'.

The second one could be written as WHERE tran_date <= '2018-02-28', but I don't 
think that would make any difference.

I knew I overlooked something ;)

But as one is a true subset of the other, I think you can merge that into a 
single SELECT statement:

    select '2018-03-01' AS op_date,
           '2018-03-31' AS cl_date,
           a.source_code_id,
           sum(a.tran_tot) AS cl_tot,
           sum(a.tran_tot) filter (where tran_date < '2018-03-01') AS op_tot
    FROM (
       SELECT distinct on (location_row_id, function_row_id, source_code_id) 
source_code_id, tran_tot, tran_date
       FROM prop.ar_totals
       WHERE deleted_id = 0
         AND tran_date <= '2018-03-31'
         AND ledger_row_id = 1
       ORDER BY location_row_id, function_row_id, source_code_id, tran_date DESC
    ) AS a
    GROUP BY a.source_code_id



Reply via email to