241–250 of 302 posts
Earlier quoted context omitted.
> My only gripe is the 'auto-generated' column names for aggregates For what it's worth, a similar problem already exists with SQL. Something simple like select count(*) from my_table; automatically aliases the column to `count`, even if `my_table` has a column called `count`. In practice, I don't think this is a major problem.
Which database does that? MSSQL doesn't assign any name to the column in this case.
Earlier quoted context omitted.
Maybe you'd like to check FunSQL.jl, my library for compositional construction of SQL queries. It also follows algebraic approach and covers many analytical features of SQL including aggregates/window functions, recursive queries and correlated subqueries/lateral joins. One thing where it differs from dlpyr and similar packages is how it separates aggregation from grouping (by modeling GROUP BY with a universal aggre…
This is awesome! I'll add a link to it on PRQL. I guess the biggest difference between FunSQL (and similarly dbplyr) and PRQL is that the former needs a Julia (or R) runtime to run. I really respect the library and keen to see how it develops.
I have re-implemented parts of FunSQL in Python and OCaml (the one I have ended up using) and have added a concrete syntax similar to what you have in PRQL.
from employees
define
salary + payroll_tax as gross_salary,
gross_salary + benefits_cost as gross_cost
where gross_cost > 0 and country = 'usa'
group by title, country
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 count
order by sum_gross_cost
where count > 200
limit 20
from users as u
join (from comments group by user_id) as c on c.user_id = u.id
select
u.username,
c.count() as comment_count,
c.max(created_date) as comment_last_created_date
I think this approach is very powerful as it allows you to build reusable query fragments in isolation but then combine them into a single query which fully specifies what's being selected.
I'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…
SELECT id, name, author Quick, what is this query about? What's ironic is that I think you have it backwards: the columns are the implementation detail, not the table. The table is the context: you can't change that without having to change everything else. But columns are the last step, the selection after the filters, joins, etc. They can be changed at any time without affecting the logic.
> The table is the context: you can't change that without having to change everything else.
Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately.
For example:
from employees
left_join positions [id=employee_id]
...is equivalent to...
SELECT * FROM employees LEFT JOIN positions ON id = employee_id
I'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).
For example:
select @@versionEarlier quoted context omitted.
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).
You've never authored a SQL query that does things like check special functions that don't exist in a table? For example: select @@version
from @@special
select versionEarlier quoted context omitted.
I've added the `let` keyword given a few people commented on this.
Awesome that you're responding to feedback like this! Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change w…
Couldn’t we just not allow modifying an existing column? Ie. we would not allow
count = count + 1
new_count = count + 1
BTW window definitions are reusable using the WINDOW clause, there's no need to define it over and over in SELECT.
SELECT
date,
CASE WHEN is_valid_price
THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return
ELSE NULL
END AS return_total,
CASE WHEN is_valid_price
THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return
ELSE NULL
END AS return_usd,
CASE WHEN is_valid_price
THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return - interest_rate / 252
ELSE NULL
END AS return_excess,
CASE WHEN is_valid_price
THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return - interest_rate / 252
ELSE NULL
END AS return_usd_excess
FROM
prices
WINDOW
w AS (PARTITION BY sec_id ORDER BY date)
;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…
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
employees,
lateral ( select
(salary + payroll_tax) as gross_salary,
(salary + payroll_tax + healthcare_cost) as gross_cost
) employee_ext
where
country = 'usa'
and gross_cost > 0
group by title, country
having count(*) > 200
order by sum_gross_cost
limit 3;Earlier quoted context omitted.
Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?
In my experience, a lot of very semantically reasonable and readable code end up with very penalizing SQL at the end, and it's a real challenge to then rewrite the whole into decent queries. There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something importan…
I work on a lot of smaller IT projects for SME's, internal tools and platforms that are made on a small budget and, thus, end up having a tight deadline in order to not go over budget.
The vast majority of these projects are versions of CRUD apps for this and that, we tend to value readable code over performant hard-to-read code as it makes code review faster.
Earlier quoted context omitted.
FWIW the separate `group_by()` is one of my greatest design regrets with dplyr — I wish I had made `by` a parameter of `summarise()`, `mutate()`, `filter()` etc.
Is it too deeply entrenched to change? The number of times I have had a data.frame grouped when it wasn't supposed to be, I can count on my fingers. But the hours that I spent trying to figure it out must amount to a paycheck or two.
IMO for such a small adjustment the benefits don't outweight the costs.