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

Some aggregation functions need ORDER BY clause #4383

Open
aljazerzen opened this issue Apr 11, 2024 · 2 comments
Open

Some aggregation functions need ORDER BY clause #4383

aljazerzen opened this issue Apr 11, 2024 · 2 comments
Labels

Comments

@aljazerzen
Copy link
Member

aljazerzen commented Apr 11, 2024

let json_agg = func arr -> s"json_agg({arr})"

from invoices
sort total
aggregate (json_agg f"{month}: {total}")

(intention: I want a single row that is a json array, which contains "month: total" entries, sorted by total)

I think this PRQL should produce the following SQL:

SELECT
  json_agg(month || ': ' || total ORDER BY total)
FROM invoices

The important bit is that json_agg has its column ORDERed BY total.

This is because from invoices | sort total produces a relation, ordered by total, so this ordering should be accounted by the aggregation function.

@max-sixty
Copy link
Member

Yes agree.

What's the class of functions that require the ORDER BY? There's WINDOW, though possibly that's a separate issue entirely.

How would we encode this? We have a field on each function that specifies "include an ORDER BY clause within the function"?

@aljazerzen
Copy link
Member Author

In PRQL, columns are arrays, not sets as they are in SQL. So columns do have an order they are in, which means that by default all aggregation function should take ordering into account.

So I think we could encode that in std.sql.prql with an annotation:

@{aggregation_unordered = true}
let sum = func col -> s"..."

@{aggregation_unordered = false}
let json_agg = func col -> s"..."

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants