Pattern match from sql in R


#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


#2

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)