[PATCH] distinct aggregates within a window function WIP

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?

Andreas

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

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

/messages/by-id/CAN1PwonqojSAP_N91zO5Hm7Ta4Mdib-2YuUaEd0NP6Fn6XutzQ@mail.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