|From:||Eugen Konkov <kes-kes(at)yandex(dot)ru>|
|To:||Krasiyan Andreev <krasiyan(at)gmail(dot)com>, Andreas Karlsson <andreas(at)proxel(dot)se>, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>|
|Subject:||Re: [PATCH] distinct aggregates within a window function WIP|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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
>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,
sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)
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:
>>> 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
|Next Message||Amul Sul||2021-05-13 09:24:04||Re: [Patch] ALTER SYSTEM READ ONLY|
|Previous Message||Dilip Kumar||2021-05-13 09:13:56||Re: RFC: Logging plan of the running query|