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

161–170 of 302 posts

Re: PRQL – A proposal for a better SQL

#161

Post by Supermancho »

bachmeier wrote: post #40
omarhaneef wrote: post #12

Earlier quoted context omitted.

There was this professor of language who would say "Do you think the question ('are carpets furniture?') tells you something about the ambiguity of the word carpet, or do you think it tells you something about the ambiguity in the world?" Similarly, I think joins are "tough" not because of the way SQL expresses them but because the logical possibilities of merging data from multiple tables are varied.

Then make some cases easier and fall back to the SQL we already have for the rest?

SQL has effectively failed, as a standard, despite it's ubiquity. It's literally being aged out, which makes for opportunities for PRQL, etc to fill pragmatic gaps.

eg the lack of default column aliasing from joins

    SELECT 
        A.id AS A__id, 
        A.name AS A__name, 
        B.id AS B__id, 
        B.name AS B__name 
    FROM A 
    LEFT JOIN B 
        ON A.other_id = B.other_id
When you could have:

    SELECT 
        A.*, 
        B.* 
    FORMAT (TABLE__) 
    FROM A 
    LEFT JOIN B 
        ON A.other_id = B.other_id

Re: PRQL – A proposal for a better SQL

#162

Post by glogla »

This is cool! I love it.

Few notes:

1) SQL also allows you to define windowing reusably, like this

    select
        sum(blah) over window_abc,
        avg(blah) over window_abc
    from table_xyz
    window window_abc as (partition by x order by y)
so that second example could be written somewhat less repetitively but it wouldn't change the whole point.

2) Sadly my main pain point with SQL for ETL is not possible to solve with a transpiler - SQL has exactly one target so doing things like "I want these records to go to a table A and those records go to table B" is not possible with one query.

3) It would be cool to see how this does typically annoying and repetitive cases from analytics / data warehousing world. I'm thinking like SCD1/2 implementation. But I don't even know if mutation is there yet.

4) I would recommend investing in one canonical formatter, like Go has. So that there isn't infinite number of ways the same query could be formatted for people to argue over preference.

EDIT:

5) Since this seems to be focused on analytics (by the choice of queries and Snowflake in examples), I want to highlight that someone suggested to use TPC-H (or TPC-DS) queries as a benchmark. It does sound like a good idea.

Re: PRQL – A proposal for a better SQL

#165

Post by Rovanion »

gmfawcett wrote: post #132
ako wrote: post #86

Earlier quoted context omitted.

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…

Not all database systems can optimize queries well over CTE boundaries. I believe this is still true for PostgreSQL (no longer true, see below -- it was true a few years ago). So there's a potential performance hit for (the otherwise excellent advice of) writing with CTE's.

IRC tells me this has been fixed now.

Re: PRQL – A proposal for a better SQL

#166

Post by aloisdg »

tester756 wrote: post #143
spullara wrote: post #135

SQL could get a lot better by just adopting the ordering of operations like they did with LINQ: https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...

Funnily enough LINQ Query syntax is really uncommon and everybody uses method syntax var list = new List {1,2,3} var extracted = list ....................Where(x => x > 1) ....................Select(x => $"my number: {x}) ....................ToList();

Because the original felt odd in C#.

Re: PRQL – A proposal for a better SQL

#167

Post by dvasdekis »

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…

Column aliases would have saved me hundreds of hours over the course of my career. Sorely missing from standard SQL, and would make the need for PRQL less acute.

Re: PRQL – A proposal for a better SQL

#168

Post by gmfawcett »

Rovanion wrote: post #165
gmfawcett wrote: post #132

Earlier quoted context omitted.

Not all database systems can optimize queries well over CTE boundaries. I believe this is still true for PostgreSQL (no longer true, see below -- it was true a few years ago). So there's a potential performance hit for (the otherwise excellent advice of) writing with CTE's.

IRC tells me this has been fixed now.

Awesome news! thank you for sharing this. I found this post which confirms IRC and suggests it was an improvement in PG 12:

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

Today is a great day to have been wrong on the Internet. :)

Post reply on HN