Just wanna say, I absolutely love this.
PRQL – A proposal for a better SQL
101–110 of 302 posts
Re: PRQL – A proposal for a better SQL
#102I'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!.
Designing languages around autocomplete is like designing toilets for better toilet paper dispensers.
The language should be right for human understanding, not automated mad-lib generation.
Re: PRQL – A proposal for a better SQL
#103I'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!.
That's interesting because it also explains why I was going to say I do like having from first. When trying to reason about a query, I mentally go through the following:
1. What tables are being pulled from? This speaks to the potential domain of the query. 2. What data is being selected (I can now know what is or isn't being pulled from the aforementioned tables...) 3. What operations, aggregations, groupings, etc. are being performed to work on the pulle data
Of course from vs select ordering is completely arguable, but my thinking process seems to follow that of the auto complete--in other words that my cognitive load of looking at the select statement is lessened when I know from what the columns are being selected.
It also follows (at least to me) the mental process of writing the query. First look at the tables, then decide what columns, then decide what functions to apply.
Re: PRQL – A proposal for a better SQL
#104I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order. My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve re…
This is great feedback, and I agree with you re de-prioritizing terseness. And I agree with you on both the assignments and `split` being a bit awkward. Kusto just uses `by`, WDYT?
By actually sounds great to me to, yea. In this case it's short but it's extremely communicative!
Re: PRQL – A proposal for a better SQL
#105Just wanna say, I absolutely love this.
One piece of feedback:
"sort sum_gross_cost # Uses the auto-generated column name." ... seems like a huge landmine. Languages really should not have any implicit way of constructing identifiers (among other reasons it is not easily greppable).
You might consider using a syntax like `sum:gross_cost` which can function as a sort parameter and an aggregation, but is actually recognizable as an object instead of having an implicit transformation going on in the background. Like this:
...
filter gross_cost > 0
aggregate by:[title, country] [
sum:gross_cost,
]
sort sum:gross_cost
...Re: PRQL – A proposal for a better SQL
#106You don't need additional SQL repetitive cruft.
from population
select country, rollup(city), count(*)
sort
select country, city, count(*)
from population
group by country, rollup(city)
order by country, city
Re: PRQL – A proposal for a better SQL
#107Earlier quoted context omitted.
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!.
Designing languages around autocomplete is like designing toilets for better toilet paper dispensers. The language should be right for human understanding, not automated mad-lib generation.
I'd agree if there was any way whatsoever of fixing this issue, but there simply isn't. The editor can't even begin to guess what you might want until you write your FROM.
Re: PRQL – A proposal for a better SQL
#108Earlier quoted context omitted.
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!.
Designing languages around autocomplete is like designing toilets for better toilet paper dispensers. The language should be right for human understanding, not automated mad-lib generation.
And one of use cases is writing queries which it helps immensely.
Best of both worlds would allow both orders.
Just automatically transform the query to the usual form after it's execution.
Re: PRQL – A proposal for a better SQL
#109This 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 syntax that lets you optionally give a name to a particular aggregate column:
...
filter gross_cost > 0
aggregate by:[title, country] [
average salary,
sum gross_salary,
average gross_cost,
let sum_gc = sum gross_cost,
count,
]
sort sum_gc
Re: PRQL – A proposal for a better SQL
#110I'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…
I agree that the columns of the results should be more obvious. But I am a proponent of "from should be first". I have never written a SQL query without thinking about the contents of a table or its relations. If it was my way, I would describe where the data I'm pulling from, then describe any filters/joins, then describe the columns that I'm interested in (last).