ghost
commented
6 years ago @AjarKeen commented on Apr 4, 2018, 7:54 PM UTC:
I think the problem isn't related to the tidyeval issue. It seems to just be that translation of the failing code into SQL results in a reference to the newly created column within the same statement, which doesn't work.
The code that fails is this:
flights_sdf %>%
group_by(carrier) %>%
summarize(count_num = n(),
mean_dep_delay = mean(dep_delay),
ratio = mean_dep_delay / count_num) %>%
collect()
Which is translated to:
SELECT `carrier`,
count(*) AS `count_num`,
AVG(`dep_delay`) AS `mean_dep_delay`,
`mean_dep_delay` / `count_num` AS `ratio`
FROM `flights`
GROUP BY `carrier`
We can test that query out on a toy table:
CREATE TABLE flights (
carrier VARCHAR(50),
dep_delay FLOAT
);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('UA', 0);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('AA', 5);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('DL', 10);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('UA', 5);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('AA', 10);
INSERT INTO `flights` (`carrier`, `dep_delay`) VALUES ('DL', 15);
SELECT `carrier`,
count(*) AS `count_num`,
AVG(`dep_delay`) AS `mean_dep_delay`,
`mean_dep_delay` / `count_num` AS `ratio`
FROM `flights`
GROUP BY `carrier`
Which fails: Unknown column 'mean_dep_delay'.
Instead, we need the R code to be translated into something like this:
SELECT `carrier`,
`mean_dep_delay` / `count_num` AS `ratio`
FROM (
SELECT `carrier`,
count(*) AS `count_num`,
AVG(`dep_delay`) AS `mean_dep_delay`
FROM `flights`
GROUP BY `carrier`
) AS flights_grouped
Which works on the toy example:
carrier | ratio
AA | 3.75
DL | 6.25
UA | 1.25
I'm not sure how to make such a change in dbplyr, but it would definitely be a useful feature.
hadley
doorisajar
@AjarKeen commented on Jan 8, 2018, 9:44 PM UTC:
cc @javierluraschi since I've only tested this with
sparklyr, not with otherdbplyrbackends.This issue was moved by krlmlr from tidyverse/dplyr/issues/3295.