Thread: [PATCH] distinct aggregates within a window function WIP
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 unboundfollowing ) 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) -- Best regards, Eugen Konkov
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 unboundfollowing ) > > 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 unboundfollowing ) >> >> 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
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:
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
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