[dead]
PRQL: Pipelined Relational Query Language
191–200 of 214 posts
Re: PRQL: Pipelined Relational Query Language
#192There seems to be very little support for these SQL challengers despite many people saying SQL sucks and they want a redo. Why is that? Some guesses: - There is some popular non-sql query language that has gained lots of momentum that I just dont know about. - People are more effective with SQL because they know it so no new and especially existing databases will switch. This is definitely true to some extent, but if…
The last one is definitely a big factor, combined with the fact that you still have to compile to SQL, i.e. you are limited by its limitations, and there isn't always a workaround.
The rest are also true to some degree. But I think that once a serious contender comes along, people will eventually move to it.
Re: PRQL: Pipelined Relational Query Language
#193This looks similar in many ways to Kusto Query Language, one of my favourite tropes of Azure: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/... I'm delighted to see an open alternative emerge <3
Note that Kusto Query language is open source see https://github.com/microsoft/Kusto-Query-Language
Re: PRQL: Pipelined Relational Query Language
#194Earlier quoted context omitted.
I've had two real gripes with SQL. The rest of it has been, as you said, pretty good. Complaint 1: Not being able to use selected columns later in the same select. SELECT gnarly_calculation AS some_value, some_value * 2 AS some_value_doubled Instead: SELECT subquery.*, some_value * 2 AS some_value_doubled FROM ( gnarly_calculation AS some_value ) AS subquery Complaint 2: Not being able to specify all columns except .…
Don't use *. It is a common source for all kinds of problems. Many query langs does not support it at all for that reason.
I'm quite aware of your advice regarding using `` against table. However, I'm talking about using `` against subqueries and CTEs where the pieces of the table have already been extracted.
Re: PRQL: Pipelined Relational Query Language
#195How are joins handled? A relational db is largely about the relations, otherwise this is more of a document query language.
I had the same question. From the docs, it looks pretty elegant: from employees join side:left positions (employees.id==positions.employee_id) which translates to SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON employees.id = positions.employee_id
Except if I wrote it by hand, it would be -
SELECT *
FROM employees
LEFT JOIN positions ON employees.id = positions.employee_id
Re: PRQL: Pipelined Relational Query Language
#196If the main complaint people have about SQL is that you can't swap SELECT, FROM and WHERE, then that's pretty good for a language designed in the 70s. This, by contrast, looks like it has a bunch of random line noise for syntax. Why on earth should I like this: `join side:left p=positions (p.id==employees.employee_id)` better than this: `LEFT JOIN positions AS p ON p.id = employees.employee_id` ?
The main issue with SQL is that you are stuck in a very strict way of writing things, which does not clearly match to how I think. The top-down way of writing PRQL where each step is simply a transformation of the previous one makes way more sense to me. SQL is something I'd need a reference manual for, PRQL is simply writing down what I want the query to do. I do agree that PRQL's join syntax is extremely bad, thoug…
Despite it not at all being the right tool for most jobs, this is why I actually enjoy writing spark. It reads top down, and is “sql-ish” for most commands. Enough that you don’t need to go out of your way to learn it if you already know SQL, you just think in terms of serial transformations.
Re: PRQL: Pipelined Relational Query Language
#197Earlier quoted context omitted.
Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I al…
> CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...
[deleted]
Re: PRQL: Pipelined Relational Query Language
#198Earlier quoted context omitted.
Huh. I have been doing this for like 15 years and never have a problem. Text editors like Sublime Text suggest names based on the content of the file. SQL Developer, SQLWorkbench, and DataGrip all seem to handle it just fine.
It will suggest names that aren't in the table you are going to query unless it is psychic.
It is psychic in the sense that it makes educated guesses based on previous entries and the current database and schema. This is the same kind of educated guess it would make with a table name.
Re: PRQL: Pipelined Relational Query Language
#199Re: PRQL: Pipelined Relational Query Language
#200Earlier quoted context omitted.
> the pipelined nature of PRQL really maps much better to how people should think about queries I disagree. Database engines take SQL and transform it into an execution plan that takes into consideration database metadata (size, storage, index analytics, etc.). Queries should be thought of with a _set based_ instead of _procedural_ approach to maximize the benefits of this abstraction - diving into the implementation…
I disagree. I find it extremely hard to reason about large queries as set transformations, whereas it is much easier to break it down to "first this, then that". And this is long before I've even started writing my first line of SQL. So let me write it procedurally and have the optimization engine fix it for me, just like how it fixes my SQL. Even SQL queries are often better understood procedurally. Take this one [1…
queries like these are best suited for window functions, although I am not sure Mysql supports it:
SELECT article, dealer, price FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY article ORDER BY price DESC) as rnk
FROM shop s1
) sub
WHERE sub.rnk=1
ORDER BY article;