Milan
June 4, 2018, 9:30am
1
Hi,
I have one sql file from which I have to select table name and its corresponding column name.
For e.g.
Select T1.Name , T1.Age, T2.Dept_Name from employee T1 , department T2 where T1.Dept_No= T2.Dept_No
I want the result like
Table_Name Column_Name
employee Name
employee Age
department Dept_Name
Can this is possible using R.
Thanks & Regards,
Milan
Milan:
Hi,
I have one sql file from which I have to select table name and its corresponding column name.
For e.g.
Select T1.Name , T1.Age, T2.Dept_Name from employee T1 , department T2 where T1.Dept_No= T2.Dept_No
I want the result like
Table_Name Column_Name
employee Name
employee Age
department Dept_Name
Can this is possible using R.
Thanks & Regards,
Milan
Hi, Milan,
It works in dplyr
and data.table
package.
In dplyr
package,
Select T1.Name , T1.Age, T2.Dept_Name
from employee T1 , department T2
where T1.Dept_No= T2.Dept_No
from employee T1 , department T2
means inner join.
where T1.Dept_No= T2.Dept_No
means joining by Dept_No
Select T1.Name , T1.Age, T2.Dept_Name
means selecting Name
, Age
, Dept_Name
.
employee %>%
inner_join(department, by = 'Dept_No') %>%
select(Name, Age, Dept_Name)
By the way, dbplyr:: translate_sql
helps translate dplyr
statements into sql
statement, here is an example.
translate_sql(if (x > 5) "big" else "small")
#> <SQL> CASE WHEN ("x" > 5.0) THEN ('big') WHEN NOT("x" > 5.0) THEN ('small') END
Also,
dbplyr::show_query
convert the dplyr
statements into sql
statement. Here is an example in the intro of dplyr
vignettes.
tailnum_delay_db <- flights_db %>%
group_by(tailnum) %>%
summarise(
delay = mean(arr_delay),
n = n()
) %>%
arrange(desc(delay)) %>%
filter(n > 100)
tailnum_delay_db %>% show_query()
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> <SQL>
#> SELECT *
#> FROM (SELECT *
#> FROM (SELECT `tailnum`, AVG(`arr_delay`) AS `delay`, COUNT() AS `n`
#> FROM `flights`
#> GROUP BY `tailnum`)
#> ORDER BY `delay` DESC)
#> WHERE (`n` > 100.0)
2 Likes