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

61–70 of 302 posts

Re: PRQL – A proposal for a better SQL

#61

Post by tomtheelder »

maximilianroos wrote: post #55
munk-a wrote: post #48

I 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?

Not the original commenter, but just using `by` makes total sense to me.

Re: PRQL – A proposal for a better SQL

#62

Post by magicalhippo »

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(gross_cost) AS sum_gross_cost,
        COUNT(*) as count
    FROM (
        SELECT
            title,
            country,
            salary,
            (salary + payroll_tax) AS gross_salary,
            (salary + payroll_tax + healthcare_cost) AS gross_cost
        FROM employees
        WHERE country = 'USA'
    ) emp
    WHERE gross_cost > 0
    GROUP BY title, country
    ORDER BY sum_gross_cost
    HAVING count > 200
This cuts down the repetition a lot, and can also help the optimizer in certain cases. Could do another nesting to get rid of the HAVING if needed.

Still, think the PRQL looks very nice, especially with a "let" keyword as mentioned in another thread here.

[1]: https://news.ycombinator.com/item?id=30053860

Re: PRQL – A proposal for a better SQL

#63

Post by maximilianroos »

tomtheelder wrote: post #61
maximilianroos wrote: post #55

Earlier quoted context omitted.

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?

Not the original commenter, but just using `by` makes total sense to me.

I've made this change [1]. Thank you!

[1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...

Re: PRQL – A proposal for a better SQL

#67

Post by kovek »

Could there be a tool that would translate PRQL to SQL? One could then write in ~/my_scripts/closest_points.prql and then run a command to get the sql equivalent and use that in exiting SQL tools that do not currently access PRQL (like Postgres).

Re: PRQL – A proposal for a better SQL

#68

Post by ako »

kovek wrote: post #67

Could there be a tool that would translate PRQL to SQL? One could then write in ~/my_scripts/closest_points.prql and then run a command to get the sql equivalent and use that in exiting SQL tools that do not currently access PRQL (like Postgres).

From the PRQL page: "PRQL transpiles to SQL, so it can be used with any database that uses SQL."

Re: PRQL – A proposal for a better SQL

#69

Post by tomtheelder »

BeefWellington wrote: post #58

This is another in a series of these kinds of proposals that look excellent on first glance for perhaps the 75% case but start getting syntactically messy when I want to customize the resultset returned. On the surface, they're always neat but when you start to dig into how you'd implement something in an RDBMS, it begins to fall apart. Let's look at the example syntax: from employees filter country = "USA" # Each li…

> Where in here is it clearly stated which fields are returned? In the original SQL it's right up front but here it's buried into the "aggregate" function, and I'm not clear that this isn't an oversight.

It's in the aggregate portion, like you said. Other example queries have a select portion. Why does it matter that it's not in the leading position like SQL?

> Does this mean that the database must scan all records of the employee table in order to return the result before moving to the next step in the query? Must I index all fields? If not, how does a query planner prepare for this scenario?

No, they are just describing how the statement is supposed to be interpreted by a human. I think you can basically just shuffle all the filter statements to the end and keep it logically equivalent.

This is a proposal for a "transpiles to SQL" language. So long as that transpliation is predictable, you cannot run into the sort of issues you are describing.

Re: PRQL – A proposal for a better SQL

#70

Post by maxwelljoslyn »

beagle3 wrote: post #53

shakti / K / kdb+ implements "real SQL", which is concise but readable, and could give you a few ideas. Here's a copy-paste from https://shakti.sh/ under document/sql.d (cannot deep link, unfortunately). The most most magical aspects are automatic joins - both left joins and "foreign key chase" joins. The fk-chase joins, in particular, should be part of every query language, and can possibly be added in a backward co…

Thanks for the tip. That automatic "foreign key chasing" looks phenomenal. Byebye, much of that big tedious chunk in the middle of your 2nd example... Wish I had that for more of the SQL I write.
Post reply on HN