SQL翻译

有两个组成部分dplyr的SQL翻译系统:

向量表达式的平移X * y + 10
整个动词的翻译如变异()或总结()

要探索它们，您需要加载这两个dbplyr而且dplyr：

库(dbplyr)库(dplyr)

向量

大多数过滤、变异或汇总操作只执行简单的数学运算。这些操作在R和SQL之间非常相似，因此很容易转换。要想知道发生了什么，你可以用translate_sql ()．实现的基础技术translate_sql ()介绍了“先进R”．translate_sql ()构建在R的解析引擎之上，经过精心设计以生成正确的SQL。它还通过正确转义要连接的数据库所需的字符串和变量名，保护您免受SQL注入攻击。

下面的例子介绍了R和SQL之间的一些基本区别。

＂而且＇意味着不同的东西

#在SQLite中，变量名用双引号转义:translate_sql(x) #>  'x' #字符串用单引号转义:translate_sql("x") #>  'x'

许多函数的名称略有不同

translate_sql (x = = 1 & & (y < 2 | | z > 3)) # > < SQL > ' x ' = 1.0 (y < 2.0或' z ' > 3.0) translate_sql (x ^ 2 < 10) # > < SQL >力量(“x”,2.0)< 10.0 translate_sql (x % % 2 = = 10) # > < SQL > ' x ' % 2.0 = 10.0

有些函数有不同的参数顺序:

translate_sql (substr (x 5 10)) # > < SQL > substr(‘x’,5,6)translate_sql(日志(x, 10)) # > < SQL >日志(10.0,“x”)

R和SQL对于整数和实数有不同的默认值。在R中，1是实数，1L是整数。在SQL中，1是整数，1.0是实数
```
translate_sql(1) #>  1.0 translate_sql(1) #> 
```

如果语句被翻译成case语句:

(if (x > 5))#>  CASE WHEN (' x ' > 5.0) THEN ('big') WHEN NOT(' x ' > 5.0) THEN ('small')结束

已知函数

dplyr知道如何将以下R函数转换为SQL:

基本的数学运算符:+，-，＊，/，% %，＾
数学函数:腹肌，这些“可信赖医疗组织”，作用是，印度历的7月，的作用，:，量化，atanh，天花板，因为，cosh，床，双曲余切，经验值，地板上，日志，log10，轮，标志，罪，sinh，√6，棕褐色，双曲正切
逻辑比较:<，< =，! =，> =，>，= =，%, %
布尔操作:＆，& &，|，||，！，xor
基本的聚合:的意思是，总和，最小值，马克斯，sd，var
字符串函数:放低，toupper，trimws，nchar，字符串的子串
强迫类型:as.numeric，as.integer，as.character

完美的翻译是不可能的，因为数据库不具备R所具备的所有功能。的目标dplyr就是提供语义上的而不是字面上的翻译:你的意思是什么而不是做了什么。事实上，即使对于数据库和R中都存在的函数，您也不应该期望结果是相同的;数据库程序员与R核心程序员有不同的优先级。例如，在R中，为了得到更高的数值精度，意思是()循环遍历数据两次。R的意思是()还提供了一个修剪计算裁剪均值的选项;这是数据库所不能提供的。数据库会自动删除null(相当于丢失的值)，而在R中，您必须很好地询问。这意味着简单呼叫的本质意思是(x)会翻译得准确，但更复杂的呼叫呢均值(x, trim = 0.5, na。rm = TRUE)将引发一个错误:

translate_sql(平均(x, na。rm = TRUE)) #>  AVG(`x`) OVER () translate_sql(mean(x, trim = 0.1)) #> Error in mean(x, trim = 0.1): unused argument (trim = 0.1)

translate_sql ()需要一个可选的反对参数。如果没有提供，这将导致dplyr生成(大致)符合SQL-92的SQL。如果提供,dplyr使用sql_translate_env ()查找自定义环境，这使得不同的数据库可以生成略有不同的SQL;看到装饰图案(“新的后台”)为更多的细节。

未知函数

任何函数,dplyr不知道如何转换是保留原样。这意味着数据库中没有覆盖的函数dplyr可以直接通过translate_sql ()．这里有几个例子SQLite：

translate_sql(水珠(x, y) # > < SQL >水珠(x, y) translate_sql (x %和% ab %) # > < SQL >“x”像“ab %”

窗口函数

对于窗口函数，事情变得有点棘手，因为SQL的窗口函数比以R或为基数提供的特定变体表达能力强得多dplyr．他们有这样的形式[expression] OVER ([partition子句][order子句][frame_clause])：

的表达式是变量名和窗口函数的组合。卡塔尔世界杯欧洲预选赛赛程表对窗口函数的支持因数据库而异，但大多数支持排序函数，引领，滞后，n，第一个，最后的，数，最小值，马克斯，总和，avg而且stddev．
的分区条款指定窗口函数如何在组上分解。它起着类似的作用集团对于聚合函数，和group_by ()在dplyr．可以将不同的窗口函数划分到不同的组中，但并不是所有数据库都支持它，也不是所有数据库都支持它卡塔尔世界杯欧洲预选赛赛程表dplyr．
的订单条款控制顺序(当产生差异时)。这对于排序函数很重要，因为它指定了根据哪些变量进行排序，但对于累积函数和领先函数也需要这样做。无论何时考虑SQL中的before和after，都必须告诉它哪个变量定义了顺序。如果在需要的时候缺少order子句，一些数据库将失败并返回错误消息，而另一些则返回不确定的结果。
的框架条款定义哪些行，或者框架，它们被传递给窗口函数，描述应该包含哪些行(相对于当前行)。frame子句提供了两个偏移量，它们决定了帧的开始和结束。有三个特殊值:负意味着包含前面所有的行(在SQL中为“无界前面”)，0表示当前行(“当前行”)和正表示所有后面的行(“无界后面的行”)。完整的选项集是全面的，但相当令人困惑，并在下面可视化总结。
```
Knitr::include_graphics("windows.png"， dpi = 200)
```
在许多可能的规范中，通常使用的只有三种。它们在聚合变量之间进行选择:
- 回收:在无约束的前和无约束的后之间
- 累积:未绑定的前一行和当前行之间
- 滚动:在前面两个和后面两个之间
dplyr根据使用的是再生聚合还是累积聚合生成框架子句。

要了解如何将各个窗口函数转换为SQL，我们可以再次使用translate_sql ()：

警告:SQL中缺失的值总是会被删除。使用' AVG(x, na。rm = TRUE)` to silence this warning #> This warning is displayed only once per session. #>  AVG(`G`) OVER () translate_sql(rank(G)) #>  RANK() OVER (ORDER BY `G`) translate_sql(ntile(G, 2)) #>  NTILE(2) OVER (ORDER BY `G`) translate_sql(lag(G)) #>  LAG(`G`, 1, NULL) OVER ()

如果tbl之前已经在管道中分组或安排，那么dplyr将使用该信息设置“分区by”和“顺序by”子句。对于交互式探索，您可以通过设置vars_group而且vars_order参数translate_sql ()

translate_sql(cummean(G)， vars_order = "year") #>  AVG(' G ') OVER (ORDER BY ' year ' ROWS UNBOUNDED above) translate_sql(rank()， vars_group = "ID") #>  rank() OVER (PARTITION BY ' ID ')

在R和SQL之间转换窗口函数时存在一些挑战，因为dplyr尽量使窗口函数与现有的R类似函数和SQL函数相似。这意味着根据你使用的窗口函数，有三种方法来控制order子句:

对于排序函数，排序变量是第一个参数:排名(x)，ntile (y, 2)．如果省略或零，将使用与TBL相关联的默认顺序(由安排())．
累积聚合只需要一个参数(要聚合的向量)。要控制顺序，请使用order_by ()．
骨料中实现dplyr（引领，滞后，nth_value，first_value，last_value)有一个order_by论点。提供它以覆盖默认顺序。

下面的代码片段说明了这三个选项:

mutate(players, min_rank(yearID)， order_by(yearID, cumsum(G))， lead(G, order_by = yearID))

目前没有办法按多个变量排序，除非设置默认的顺序安排()．这将在未来的版本中添加。

整个表

所有dplyr生成一个动词选择声明。为了演示，我们将创建一个包含几个表的临时数据库:

con <- DBI::dbConnect(RSQLite::SQLite()， ":memory:") flights <- copy_to(con, nycflights13::flights) airports <- copy_to(con, nycflights13::airports)

单表动词

select ()而且变异()修改选择条款:

flights %>% select(contains("delay")) %>% show_query() #>  #> select ' dep_delay '， ' arr_delay ' #> FROM ' nycflights13::flights ' flights %>% select(distance, air_time) %>% mutate(speed = distance / (air_time / 60)) %>% show_query() #>  #> select ' distance '， ' air_time '， ' distance ' / (' air_time ' / 60.0) AS ' speed ' #> FROM ' nycflights13::flights '

(正如您在这里看到的，生成的SQL并不总是像手工生成的那样最小。)

filter ()生成一个在哪里条款:

flights %>% filter(month == 1, day == 1) %>% show_query() #>  #> SELECT * #> FROM ' nycflights13::flights ' #> WHERE ((' month ' = 1.0) AND (' day ' = 1.0))

安排()生成一个命令条款:

flights %>% arrange(carrier, desc(arr_delay)) %>% show_query() #>  #> SELECT * #> FROM ' nycflights13::flights ' #> ORDER BY ' carrier '， ' arr_delay ' desc

总结()而且group_by ()共同努力，共同创造集团条款:

flights %>% group_by(月，日)%>% summarise(delay = mean(dep_delay)) %>% show_query() #>  #>警告:在SQL中总是删除缺失的值。使用' mean(x, na。rm = TRUE)` to silence this warning #> This warning is displayed only once per session. #> SELECT `month`, `day`, AVG(`dep_delay`) AS `delay` #> FROM `nycflights13::flights` #> GROUP BY `month`, `day`

双表动词

x而且y不必是同一个数据库中的表。如果您指定复制= TRUE，dplyr将复制y表放入相同的位置x变量。如果您已经下载了一个摘要数据集，并确定了一个感兴趣的子集，现在需要完整的数据，那么这是很有用的。您可以使用semi_join(x, y, copy = TRUE)将感兴趣的索引上传到同一数据库中的临时表x，然后在数据库中执行高效的半连接。

如果您正在处理大型数据，设置auto_index = TRUE．这将自动向临时表添加连接变量的索引。

在幕后

谓词级别的SQL翻译是在tbl_lazy，它基本上跟踪您在管道中执行的操作(参见lazy-ops。R)．将其转换为SQL查询需要三个步骤:

sql_build ()递归遍历惰性op数据结构，以构建查询对象(select_query ()，join_query ()，set_op_query ()的不同子类型选择我们可能生成的查询。
sql_optimise ()遍历这些SQL对象，寻找潜在的优化。目前，这只涉及到在可能的情况下删除子查询。
sql_render ()调用SQL生成函数(sql_select ()，sql_join ()，sql_subquery ()，sql_semijoin ()等等)来生成实际的SQL。这些函数中的每一个都是泛型函数，将连接作为参数，因此可以为不同的数据库定制详细信息。