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

231–240 of 302 posts

Re: PRQL – A proposal for a better SQL

#231

Post by rswail »

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…

> Can I take two queries and squash them together in a reliable way? I feel like I end up with a lot of theme and variation in my queries, often involving optional filters.

That's essentially what SQL views do. Each view is a query and then you can treat it like a table and filter/join on it.

Of course, then the problem becomes whether or not the query planner can see through the view to the underlying tables to optimize correctly.

Re: PRQL – A proposal for a better SQL

#232

Post by taeric »

sanderjd wrote: post #213
taeric wrote: post #176

Earlier quoted context omitted.

I challenge this. I accept that there are ambiguities, but I assert that you can go really fast by just telling someone to fetch a few columns by name. I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names…

I think we have just done most of our data work in different environments. When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.

I'm used to emacs, with a global namespace. Such that I'm used to searching all variables globally. Feels that searching all columns would be just as easy, all told.

That said, I want to be clear that I think both methods are valid and work.

Re: PRQL – A proposal for a better SQL

#233

Post by wodenokoto »

There’s a lot to like here. The ordering and the ability to write functions.

I’m not a big fan of the ternary operator. I think the ‘? :’ is hard to read and caters to programmers from system programming languages. A query language should cater to BI and stats people as they typically have a harder time learning syntax than a CS person has learning non-C-like syntax.

I’ve always liked Pythons ‘val if condition else other_val’. It’s easy to read for new-comers, while the ‘? :’ is a devil to google for.

In the second example I don’t understand why there are both let statements and select statements for the same columns. I also don’t understand why select uses square brackets. Maybe I’m too used to sql but why not just not have brackets?

Re: PRQL – A proposal for a better SQL

#234

Post by rixed »

> Unnecessary repetition — the calculations for each measure are repeated, despite deriving from a previous measure. The repetition in the WHERE clause obfuscates the meaning of the expression

In my own in-house SQL-like language I solve this simple issue by allowing previously defined columns to be reused:

  select
    salary + payroll_tax as _gross_salary,
    _gross_salary + benefits_cost as gross_cost,
    ...etc...
the prefixing underscore meaning those columns are just temps excluded from the output.

I do not believe other issues are actually issues, and am quite surprised by the volume of interest for this SQL alternative. Can't be because rust is mentioned, can it?

Re: PRQL – A proposal for a better SQL

#235

Post by rswail »

cerved wrote: post #197
aloisdg wrote: post #163

Earlier quoted context omitted.

Indeed. It looks a lot like dotnet's Linq.

wr to linq to sql: the difference is linq works by making objects to tables and dotnet primitives to sql types, often producing really poor queries as a result

That's the underlying implementation decision, not necessarily caused by the language syntax/semantics.

Re: PRQL – A proposal for a better SQL

#236

Post by sanderjd »

taeric wrote: post #232
sanderjd wrote: post #213

Earlier quoted context omitted.

I think we have just done most of our data work in different environments. When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.

I'm used to emacs, with a global namespace. Such that I'm used to searching all variables globally. Feels that searching all columns would be just as easy, all told. That said, I want to be clear that I think both methods are valid and work.

Yeah, it's just that I don't know what the columns are called. The namespaces (databases and tables) are what guides me to the columns, not vice versa.

I can think back to projects / companies where this may have been different, basically just with fewer different distinct schemas, but it just isn't my recent experience.

(I appreciate your magnanimity in these later comments by the way.)

Re: PRQL – A proposal for a better SQL

#237

Post by maximilianroos »

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.

Thanks for commenting! I hope you can see the influence of dplyr & tidyverse here. Please let me know if you have any other feedback!

Re: PRQL – A proposal for a better SQL

#239

Post by landonxjames »

PartiQL[0] is an open source library that is a superset of SQL that I really like. It supports querying nested structures inside columns, so if a column contains some JSON data you can use the standard dot notation to query nested JSON data directly

[0] https://partiql.org/

Re: PRQL – A proposal for a better SQL

#240

Post by maximilianroos »

hadley wrote: post #222
maximilianroos wrote: post #82

Earlier quoted context omitted.

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

Yes, that's a good example.

Indeed — often SQL doesn't know the column _names_, let alone the types. So for cross-column selection (e.g. `across`), this does become necessary.

Post reply on HN