I resolve my problem https://stackoverflow.com/a/67167595/4632019:
Could it be possible PG will use `filter` trick when DISTINCT is used: `sum (distinct suma)`? This will benefit to not write second SELECT https://www.postgresql.org/message-id/CAN1PwonqojSAP_N91zO5Hm7Ta4Mdib-2YuUaEd0NP6Fn6XutzQ%40mail.gmail.com >About yours additional note, I think that it is not possible to get easy the same result with appropriate use of window framing options, Can you try next approach? My approach is https://dbfiddle.uk/?rdbms=postgres_13&fiddle=01c699f3f47ca9fca8215f8cbf556218: Assign row_number at each order: row_number() over (partition by agreement_id, order_id ) as nrow Take only first suma: filter nrow = 1 with data as ( select * from (values ( 1, 1, 1, 1.0049 ), (2, 1,1,1.0049), ( 3, 1,1,1.0049 ) , ( 4, 1, 2, 1.0049 ), (5, 1,2,1.0057), ( 6, 2, 1, 1.53 ), ( 7,2,1,2.18), ( 8,2,2,3.48 ) ) t (id, agreement_id, order_id, suma) ), intermediate as (select *, row_number() over (partition by agreement_id, order_id ) as nrow, (sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma, from data) select *, sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id) from intermediate Wednesday, April 22, 2020, 10:05:19 AM, you wrote: > Hello Andreas, > Tuesday, April 21, 2020, 6:17:00 PM, you wrote: >> On 4/21/20 5:06 PM, Eugen Konkov wrote: >>> Hi. I read the thread. >>> Probably this fiddle will be helpful for testing: >>> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=abe845142a5099d921d3729043fb8491 >>> I recently encountered a problem: >>> Why Window-specific functions do not allow DISTINCT to be used within the >>> function argument list? >>> sum( DISTINCT order_cost ) OVER ( PARTITION BY invoice_id ORDER BY >>> invoice_id, group_id RANGE unbound preceeding and unbound following ) >>> behavior is quite deterministic: >>> ORDER BY will create peers in partition >>> DISTINCT will get only one peer >>> I resolve my problem via two subqueries, but it seems this logic may >>> be applied to window functions (did not check this for other functions >>> thought) >> Sorry, I do not follow. What problem did you encounter? > Lack of DISTINCT for window function SUM -- Best regards, Eugen Konkov
