Postgres Pro
Downloads
Home   >   mailing lists

Re: [PATCH] distinct aggregates within a window function WIP - Mailing list pgsql-hackers

From Eugen Konkov
Subject Re: [PATCH] distinct aggregates within a window function WIP
Date
Msg-id 15210250762.20210513121841@yandex.ru
Whole thread Raw
In response to Re: [PATCH] distinct aggregates within a window function WIP  (Eugen Konkov <kes-kes@yandex.ru>)
List pgsql-hackers
Tree view
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:
  1. Assign row_number at each order: row_number() over (partition by agreement_id, order_id ) as nrow
  2. 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 

pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: RFC: Logging plan of the running query
Next
From: Amul Sul
Date:
Subject: Re: [Patch] ALTER SYSTEM READ ONLY