Show HN: PRQL 0.2 – a better SQL

Post by maximilianroos » Mon, Jun 27, 2022, 6:40 PM UTC

maximilianroos wrote: post #16

cogman10 wrote: post #11

Earlier quoted context omitted.

In particular, I think this is looking pretty good and I'd want to see even more complicated examples. For example, What do window functions end up looking like? [1] What about crazy operations like calculating percentile_cont? [2] Or just in general, how would "implementation specific" queries end up looking? [1] https://www.postgresql.org/docs/current/tutorial-window.html [2] https://docs.microsoft.com/en-us/sql/t-…

Great questions! Window functions are here [1]. (We should add these to the homepage too) Implementation specific queries can be handled by the Dialect parameter [2], though there's still lots of work to do to build that out. [1]: https://prql-lang.org/book/transforms/window.html [2]: https://prql-lang.org/book/queries/dialect_and_version.html

Post by coremoff » Mon, Jun 27, 2022, 6:51 PM UTC

aerzen wrote: post #14

ComputerGuru wrote: post #7

From a mathematical point-of-view are there any transforms/operations (note: not end results, but actual operations) that this can do that SQL can't or vice-versa?

As said, currently PRQL transpiles to SQL, so all expressions in PRQL are can be expressed in SQL. But not all SQL expression can be translated back into PRQL - some intentionally and some are just not yet implemented (UNION - i.e. vertical concat). But we also have plans for doing things that some SQL databases may not support, such as pivot (rows to columns).

Post by rackjack » Mon, Jun 27, 2022, 6:57 PM UTC

Post by jitl » Mon, Jun 27, 2022, 7:00 PM UTC

    from employees
    join side:left positions [id==employee_id]

    SELECT
      employees.*,
      positions.*
    FROM
      employees
      LEFT JOIN positions ON id = employee_id

Post by beaugunderson » Mon, Jun 27, 2022, 7:00 PM UTC

aerzen wrote: post #27

davidw wrote: post #13

A good example might be a groupwise maximum. Those always tend to be a bit of a PITA in SQL if you're not writing them regularly. Be interesting to see what it transpiles to, as well.

If you only want maximum of one column, the PRQL is quite simple: from my_table group column_a ( aggregate (max column_b) ) If you want the row with the maximum value it gets interesting: from my_table group column_a ( sort [-column_b] take 1 ) You can read more about group here: https://prql-lang.org/book/transforms/group.html