5 ms·
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
by magicalhippo 5y ago
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 https://news.ycombinator.com/item?id=30053860
- ako 5y agoWith 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_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gross_cost) AS sum_gross_cost, COUNT(*) as emp_count from usa_employees group by title, country having count(*) > 200 order by sum_gross_cost limit 3 Readability is pretty similar to prql. It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression.
- magicalhippo 5y ago> With a CTE The DB we use supports those, I just learned about them too late so keep forgetting they exist :( > It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression. The DB we use supports that, so in your CTE you could write AND gross_cost > 0 We do that all the time, which will be a pain now that we're migrating to a different DB server which doesn't.
- mmsimanga 5y agoSybase IQ allows you to use the column alias anywhere else in the query.
- correct-me-plz 5y agoSnowflake lets you refer to column aliases, and it's great! There's the slight issue of shadowing of table column names, which they resolve by preferring columns to aliases if both are named the same. So sometimes my aliases end up prefixed with underscores, but that's not a big deal.
- twoodfin 5y agoThe trade-off is that a schema change (adding a column) unrelated to your query can modify its behavior. Favoring aliases over columns instead has the potential to introduce irresolvable ambiguities as you can’t “qualify” a column alias with a SELECT list or subquery ID the way you can qualify a column by its table/view alias.
- jsyolo 5y agowhat expressions are being repeated here?
- oblio 5y ago> (salary + payroll_tax) AS gross_salary, > (salary + payroll_tax + healthcare_cost) AS gross_cost > AND (salary + payroll_tax + healthcare_cost) > 0 And his is a simple example.
- dagss 5y agoIn Microsoft SQL cross apply can be used for this in even more situations and with less repetition: select top(20) title, country, ... avg(gross_salary) as average_gross_salary, ... from employees cross apply ( select gross_salary = employees.salary + employees.payroll_tax, -- or "as .." gross_cost = ... ) v -- some name required but don't need to use it if column names are unique where ...