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

221–230 of 302 posts

Re: PRQL – A proposal for a better SQL

#221

Post by makeitdouble »

brigandish wrote: post #212
makeitdouble wrote: post #193

Earlier quoted context omitted.

On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…

Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?

In my experience, a lot of very semantically reasonable and readable code end up with very penalizing SQL at the end, and it's a real challenge to then rewrite the whole into decent queries.

There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something important enough to thoroughly optimize, but also complex enough that the existent tests only cover a tiny portion of the important use cases (if you're reusing a bunch of query bits, you're probably working with a wide combination of input/outputs). I've seen literally weeks spent on trying to optimize ORM chained subqueries.

Re: PRQL – A proposal for a better SQL

#222

Post by hadley »

maximilianroos wrote: post #82
ridaj wrote: post #77

Very cool Design goal question - is the goal to have a language that blindly compiles to SQL, or will compilation require data schema knowledge? Suggestion: where possible stick to the well established SQL keywords (prefer "group by" to "aggregate by")

Initially the transpiling would be context-free of the schema. It would really nice to have context when _writing_ the code, so we could do things like autocomplete (IIUC this is what Malloy does already). Are there features you can think of that would be helpful if we had the schema context during transpiling?

From dbplyr experience, folks want to be able to do stuff like `across(which(is.numeric), mean)`, which you can't do currently because dplyr doesn't know the column types (although it does maintain a list of the column names).

Re: PRQL – A proposal for a better SQL

#223

Post by ziml77 »

tester756 wrote: post #137
_jal wrote: post #128

Earlier quoted context omitted.

They are not fully-aligned goals, and autocomplete should not be given equal consideration on par with human clarity. If you want nice autocomplete too, that's fine, but if there is a tradeoff, human understanding is the primary concern.

I don't understand why do you think about it this way C#'s LINQ (really powerful tool similar to SQL) works the same way look: var list = new List {1,2,3} var extracted = list .............................Where(x => x > 1) .............................Select(x => $"my number: {x}) .............................ToList(); or var extarcted = ........................from x in list ........................where x > 1 .....…

Technically to be equivalent you need to wrap the second one in parentheses so you can use ToList() on it. Unfortunately a bit ugly. I'm not sure why they didn't add one more keyword to handle pipelining into other functions. Something like "feed", "into", or "pipe". Or just pluck the |> operator from F#.

Re: PRQL – A proposal for a better SQL

#224

Post by ziml77 »

yen223 wrote: post #211
blintz wrote: post #109

This is a nice idea, especially given all the work people have done recently to make in-language querying nicer (Spark comes to mind). My only gripe is the 'auto-generated' column names for aggregates. This seems like a recipe for disaster - what if there is already (as there almost certainly will be) named "sum_gross_cost"? The behavior also just seems rather unexpected and implicit. My suggestion would be simple sy…

> My only gripe is the 'auto-generated' column names for aggregates For what it's worth, a similar problem already exists with SQL. Something simple like select count(*) from my_table; automatically aliases the column to `count`, even if `my_table` has a column called `count`. In practice, I don't think this is a major problem.

Which database does that? MSSQL doesn't assign any name to the column in this case.

Re: PRQL – A proposal for a better SQL

#227

Post by sin7 »

hadley wrote: post #219
maximilianroos wrote: post #63

Earlier quoted context omitted.

I've made this change [1]. Thank you! [1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...

FWIW the separate `group_by()` is one of my greatest design regrets with dplyr — I wish I had made `by` a parameter of `summarise()`, `mutate()`, `filter()` etc.

Is it too deeply entrenched to change? The number of times I have had a data.frame grouped when it wasn't supposed to be, I can count on my fingers. But the hours that I spent trying to figure it out must amount to a paycheck or two.

Re: PRQL – A proposal for a better SQL

#229

Post by datan3rd »

Awkward syntax — when developing the query, commenting out the final line of the SELECT list causes a syntax error because of how commas are handled, and we need to repeat the columns in the GROUP BY clause in the SELECT list.

There are some SQL varieties that actually allow a hanging comma! Also, the provided examples seem comma-dependent, no?

As someone who writes a ton of analytical SQL, i think this would get super messy for long, complex queries with casting, case statements, windows functions, etc.

Most people just need to learn to write better SQL!

Re: PRQL – A proposal for a better SQL

#230

Post by SNosTrAnDbLe »

I would also take a look at MDX which comes from Microsoft for classical analytical processing workloads. I feel that it provides a nice way to think of data in terms of dimensions/attributes and hierarchies and measures as it gives a good way to think of what to aggregate vs what fields to use to do that aggregation. SQL is feels a little more generic and it would be nice for it to have a little more schema aware specifically for analytical workloads.
Post reply on HN