Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add PRODUCT() aggregate and window function #5939

Closed
lukaseder opened this Issue Mar 3, 2017 · 2 comments

Comments

Projects
None yet
1 participant
@lukaseder
Copy link
Member

lukaseder commented Mar 3, 2017

Most SQL implementations don't have a PRODUCT() aggregate and window function, but it can be emulated easily as such:

-- Aggregate function
exp(sum(log(arg)))

-- Window function
exp(sum(log(arg)) OVER window_specification)

This only works for strictly positive numbers. If zero and negative numbers should be supported, this approach could be used:

WITH v(i) AS (VALUES (-2), (-3), (-4), (-5))
SELECT 
  CASE 
    WHEN SUM (CASE WHEN i < 0 THEN -1 END) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(LN(ABS(i)))) multiplication2
FROM v;

See: https://blog.jooq.org/2018/09/21/how-to-write-a-multiplication-aggregate-function-in-sql

@lukaseder

This comment has been minimized.

Copy link
Member Author

lukaseder commented Sep 20, 2018

First implementation done. Open points:

  • The implementation is not zero-safe (logarithm of zero undefined)
  • The implementation might not get FILTER() clause right
  • Parser support

@lukaseder lukaseder removed the R: Fixed label Sep 20, 2018

@lukaseder lukaseder reopened this Sep 20, 2018

lukaseder added a commit that referenced this issue Sep 20, 2018

lukaseder added a commit that referenced this issue Sep 24, 2018

@lukaseder

This comment has been minimized.

Copy link
Member Author

lukaseder commented Jan 22, 2019

Any open issues will be fixed as bugs

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.