-
Notifications
You must be signed in to change notification settings - Fork 250
-
Filter is overloaded here, so to be clear, I am referring to this kind of filter clause: https://www.sqlite.org/windowfunctions.html#the_filter_clause
In the sqlite docs, they have this example:
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER ( ORDER BY a ) AS group_concat FROM t1 ORDER BY a;
This is a bit convoluted because it's also part of a window function. A simpler example is:
SELECT year, group_concat(title, ', ') FILTER (WHERE genre = 'comedy') as comedies, group_concat(title, ', ') FILTER (WHERE genre = 'drama') as dramas FROM movies GROUP BY year;
Is there PRQL that would compile to that SQL?
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 1 comment 3 replies
-
No, unfortunately not at the moment.
TBH this is the first I've heard of it. Is this exclusively a sqlite feature? It seems similar in some ways to QUALIFY...
Beta Was this translation helpful? Give feedback.
All reactions
-
I've used it in Clickhouse and DuckDB before. I see that it's also available in Postgres.
Beta Was this translation helpful? Give feedback.
All reactions
-
Ah great, that is nice. Particularly in a simple aggregate like DuckDB allows.
I think this could be great to include in PRQL in some way. There's a very old issue which seems like it could be reassessed: #82
We'd need to think of the PRQL semantics — we would really want it to be consistent with the existing structure. Possibly the examples in that issue could work; would need to think more.
Beta Was this translation helpful? Give feedback.
All reactions
-
Yes, that's exactly the kind of thing I'm wondering about (I think the case ... when syntax is actually just an alternative for the same thing)!
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1