Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PRQL – A proposal for a better SQL

github.com

Post reply on HN
Print view View original

91–100 of 302 posts

Re: PRQL – A proposal for a better SQL

#91

Post by mmsimanga »

ako wrote: post #86
magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

Sybase IQ allows you to use the column alias anywhere else in the query.

Re: PRQL – A proposal for a better SQL

#92

Post by cogman10 »

ianbicking wrote: post #78

I like it, it's readable, unlike some SQL alternatives I've seen it doesn't make me feel like I'm dumb and don't understand what a query even is. I can't decide if it would be better or worse if it stuck more closely to SQL keywords. You use "from" and "select", but not "where", "order by", "group by". There's some danger of it being in an uncanny valley of SQLish, but I'm pretty sure I'd prefer just using those term…

Yup, I like a lot of things about the way this looks. In particular, I like how friendly this looks to be for things like auto complete (pretty annoying to need to practically type the entire sql query only to go back and fix up the columns in order to get autocomplete to work).

Specific things I'd like to see.

How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_id == id. But what happens when you have 2 columns with the same name that you are joining on? (like employee_id to employee_id in some mapping table).

Subqueries are pretty important in what I do, so what do those look like (perhaps covered by the "thinking about CTEs section").

How about opportunities for optimization hints? In T-SQL you can hint at which index the optimizer should prefer to a specific query.

Common SQL patterns would also be interesting. Like, how would you do keyset pagination?

Edit: Also, I'd like a discussion about null. SQL null handling rules are terrible. I understand them, I work with them, but at the same time, they are so different from other languages concept of "null" that they are easy to trip over.

Re: PRQL – A proposal for a better SQL

#93

Post by 999900000999 »

>Compatible — PRQL transpiles to SQL, so it can be used with any database that uses SQL. Where possible PRQL can unify syntax across databases. PRQL should allow for a gradual onramp — it should be practical to mix SQL into a PRQL query where PRQL doesn't yet have an implementation.

Awesome.

I hate SQL so much, I know for personal projects this is gold. I imagine actually using it at work might draw some questions though

Re: PRQL – A proposal for a better SQL

#94

Post by correct-me-plz »

ako wrote: post #86
magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

Snowflake lets you refer to column aliases, and it's great!

There's the slight issue of shadowing of table column names, which they resolve by preferring columns to aliases if both are named the same. So sometimes my aliases end up prefixed with underscores, but that's not a big deal.

Re: PRQL – A proposal for a better SQL

#96

Post by mrweasel »

jacktheturtle wrote: post #23

Do people still write SQL?

You kinda have to. Assuming that you're using an ORM, you still need to understand how it translate to SQL, and help it do the translation correctly.

Personally I've seen developer use the Django ORM, and create application with terrible performance. Tweaking the queries, you can help guide the ORM to generate better SQL, which in turn will affect your performance greatly.

We're currently facing a problem with a custom who have an application with terrible performance/scaling issues. The entire thing is very database heavy, but interaction is done solely via Hibernate. I have nothing against Hibernate, it's a fine ORM, but you need to understand it well enough that you can guide it towards better queries (Which sometimes involve actually writing SQL). At some point you need to decide if your time isn't better spend learning SQL directly, as that via always provide you with better access to the functionality provided by the database.

Re: PRQL – A proposal for a better SQL

#97

Post by akdor1154 »

Looks really nice, i've been scribbling away in a little notebook all the things i would do in "akdor's dream sql", and what you have here hits pretty much exactly.

Wondering about generic use of `let` - you have let for col defns, but `func` for functions and a TODO for tables/CTEs - could/should `let` do the lot? (Like another commenter posted, this is how MS's M language, used in PowerQuery in PowerBI and Excel works). Could enable an escape from point-free for entire queries if taken to extreme generality, not sure if that's a good thing, maybe it could be?

Bikeshedding: even with some OCaml/F# experience, i find `f x y` harder to read than `f(x, y)`.

Re: PRQL – A proposal for a better SQL

#98

Post by inglor »

galkk wrote: post #88

I'm quite opposed to the idea "from should be first". I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed). Literally first example from page - I have no idea what is being returned: from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / varia…

The big advantage of "from first" like we have in Kusto KQL (a database we use at Microsoft) is that it provides much better autocomplete (if I write the `from` it can easily autocomplete the projection).

If you want an interesting example of how a query language built for developer experience and autocompletions looks definitely check it out!.

Re: PRQL – A proposal for a better SQL

#99

Post by clarkevans »

maximilianroos wrote: post #30
xi wrote: post #4

Earlier quoted context omitted.

Maybe you'd like to check FunSQL.jl, my library for compositional construction of SQL queries. It also follows algebraic approach and covers many analytical features of SQL including aggregates/window functions, recursive queries and correlated subqueries/lateral joins. One thing where it differs from dlpyr and similar packages is how it separates aggregation from grouping (by modeling GROUP BY with a universal aggre…

This is awesome! I'll add a link to it on PRQL. I guess the biggest difference between FunSQL (and similarly dbplyr) and PRQL is that the former needs a Julia (or R) runtime to run. I really respect the library and keen to see how it develops.

Writing an alternative syntax is straight forward. Perhaps prototype PRQL using xi's excellent FunSQL backend? This way it's working out of the gate. Once syntax+semantics are pinned, writing another backend in the language of your choice would then be easier. Getting the backend correct is non-trivial work, and xi has done this already. Besides, we need a sandbox syntax anyway, so it might be fun to collaborate.

Re: PRQL – A proposal for a better SQL

#100

Post by cogman10 »

bachmeier wrote: post #5

My thought is that joins are the tough part of the SQL learning curve, but I don't see much in here that reduces the complexity of joins.

IMO, this appears not to be something that solves the SQL learning curve but rather the usability of a query language with tooling.

I don't think there is much that could be done to address left, right, inner, outer join semantics. It's just something you have to learn if you want to do a lot of SQL (though, you are likely only ever going to use left and inner joins).

Post reply on HN