连接到Oracle DB时如何通过dbplyr使用EXTRACT [英] How to use EXTRACT through dbplyr when connecting to an Oracle DB

问题描述

进行以下查询:

SELECT EXTRACT(month FROM order_date) "Month"
  FROM orders

(来自官方Oracle文档 )

您如何将上述EXTRACT操作集成到dbplyr链中?

How would you go at integrating such EXTRACT operations above in a dbplyr chain ?

我愿意接受任何其他解决方法(甚至是丑陋/昂贵)来提取服务器端的月份.

I'm open to any other workaround (even ugly/costly) to extract the month on server side.

推荐答案

更优雅:

tbl(con, "orders") %>% mutate(Month = extract(NULL %month from% order_date))

这将导致以下SQL(ANSI SQL):

This results in the following SQL (ANSI SQL):

EXTRACT( MONTH FROM "order_date")

之所以有用，是因为运算符的名称(在百分号之间)实际上是转换为SQL的. NULL消失(与NA不同).

This trick works because the names of operators (what's between the percent signs) are literally translated to SQL. NULL disappears (unlike NA).

这篇关于连接到Oracle DB时如何通过dbplyr使用EXTRACT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！