Does PRQL support SQL FILTER clauses?

[jcuenod]

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?

[max-sixty]

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...

[jcuenod]

I've used it in Clickhouse and DuckDB before. I see that it's also available in Postgres.

See https://modern-sql.com/feature/filter

[max-sixty]

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.

[jcuenod]

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)!