使用纯R语言处理dbplyr中的日期

Question

dbplyr将dplyr和基本R命令转换为SQL，使开发人员可以编写R代码并在数据库中执行 (tidyverse参考文献)。在R中处理日期时，通常使用lubridate包。然而，目前dbplyr对lubridate函数的翻译不存在。因此，使用dbplyr的开发人员需要找到其他处理日期的方法。

我以前的方法是在我的dplyr命令中使用SQL语法片段（例如参见答案：这里和这里）。但是，这要求开发人员知道（或找到）相应的SQL命令，而dbplyr的一部分意义就是它会为您翻译成SQL。

这让我想问：什么是使用dbplyr翻译连接到远程数据库时处理日期的最佳方法？

理想的解决方案应该：

• 只使用dbplyr翻译，因此无法使用没有dbplyr翻译的函数。
• 纯R，没有SQL片段。
• 在数据库上运行，因此是远程表而不是本地表。

我认为至少应该能够：

• 提取年、月和日
• 将年、月和日组合成新日期

从这些操作中，您可以手动进行其他操作，例如：

• 增加日期
• 查找两个日期之间的差异
• 查找月底的日期

但更快/更优雅的方式来执行这些更高级的操作会更好。

- Simon.S.A.

---

在PostgreSQL上，至少使用RPostgres，您可以很容易地使用lubridate函数，如此处所示。我认为可以通过这种方式完成您要求的所有事情。如果其他SQL实现（例如MySQL）无法正常工作，则可能需要对相关软件包（例如RMySQL）进行调整。 - Ian Gow

Answer 1

其中一个答案是很多已经可以实现。（参见答案这里。）

如果在dbplyr中缺少所需的功能，一个想法是编写拉取请求，将更多lubridate函数的翻译添加到dbplyr后端中。

看起来翻译不可避免地与后端有关。如果您查看PostgreSQL后端这里，您可以看到一些lubridate函数（例如month或quarter）在此处给出了翻译，但其他函数（例如ymd）则没有。

- Ian Gow

---

谢谢。看起来这有些取决于使用的具体SQL版本。可能通过特定的包（例如：RPostgres，当与PostgreSQL数据库一起使用时，如第一个链接所述），也可能是因为dbplyr可以翻译某些SQL版本，但不包括其他版本（如第二个链接所述）。因此，开发人员应首先尝试使用lubridate方法，并仅在失败时才采用解决方案。 - Simon.S.A.

@Simon.S.A. 我认为 "datediff" 函数是 dbplyr 可以做更多的领域（鉴于 SQL 方言之间的差异，dbplyr 在这里真的可以大放异彩）。 - Ian Gow

Answer 2

首先想到的方法是将日期转换为文本，因为已经有了不同形式文本操作的 dbplyr 翻译。这种方法依赖于 as.character 将日期转换为字符，以及 substr 提取年、月或日作为文本。然后可以将其转换为数字并进一步处理。

(1) 设置模拟数据库连接以测试翻译（选择您喜欢的 SQL 方言）：

library(dplyr)
library(dbplyr)

df = data.frame(start_dates = c('2020-01-31', '2020-02-28', '2020-03-31'))

# simulate a connection to test translation (pick your preferred flavor)
df = tbl_lazy(df, con = simulate_mssql())
# df = tbl_lazy(df, con = simulate_hive())
# df = tbl_lazy(df, con = simulate_impala())
# df = tbl_lazy(df, con = simulate_oracle())
# df = tbl_lazy(df, con = simulate_postgres())
# df = tbl_lazy(df, con = simulate_mysql())
# df = tbl_lazy(df, con = simulate_sqlite())

(2) 示例 - 提取日期组件，增加年份，然后重新组合：

output = df %>%
  mutate(text_date = as.character(start_dates)) %>%
  mutate(text_year = substr(text_date, 1, 4),
         text_month = substr(text_date, 6, 7),
         text_day = substr(text_date, 9, 10)) %>%
  mutate(num_year = as.numeric(text_year),
         num_month = as.numeric(text_month),
         num_day = as.numeric(text_day)) %>%
  select(start_dates, num_year, num_month, num_day) %>%
  mutate(next_year = num_year + 1) %>%
  mutate(next_year_text_date = paste0(next_year, '-', num_month, '-', num_day)) %>%
  mutate(next_year_date = as.Date(next_year_text_date)) %>%
  select(start_dates, next_year_date)

调用show_query(output)，然后会得到以下翻译，但格式不太好。我知道嵌套查询不被认为是良好的SQL实践，但这就是dbplyr翻译的工作方式。

SELECT `start_dates`
    , TRY_CAST(`next_year_text_date` AS DATE) AS `next_year_date`
FROM (
    SELECT `start_dates`
        , `num_year`
        , `num_month`
        , `num_day`
        , `next_year`
        , `next_year` + '-' + `num_month` + '-' + `num_day` AS `next_year_text_date`
    FROM (
        SELECT `start_dates`
            , `num_year`
            , `num_month`
            , `num_day`
            , `num_year` + 1.0 AS `next_year`
        FROM (
            SELECT `start_dates`
                , TRY_CAST(`text_year` AS FLOAT) AS `num_year`
                , TRY_CAST(`text_month` AS FLOAT) AS `num_month`
                , TRY_CAST(`text_day` AS FLOAT) AS `num_day`
            FROM (
                SELECT `start_dates`
                    , `text_date`
                    , SUBSTRING(`text_date`, 1, 4) AS `text_year`
                    , SUBSTRING(`text_date`, 6, 2) AS `text_month`
                    , SUBSTRING(`text_date`, 9, 2) AS `text_day`
                FROM (
                    SELECT `start_dates`
                        , TRY_CAST(`start_dates` AS VARCHAR(MAX)) AS `text_date`
                    FROM `df`
                ) `q01`
            ) `q02`
        ) `q03`
    ) `q04`
) `q05`

（3）提取组件，压缩：

output = df %>%
  mutate(num_year = as.numeric(substr(as.character(start_dates), 1, 4)),
         num_month = as.numeric(substr(as.character(start_dates), 6, 7)),
         num_day = as.numeric(substr(as.character(start_dates), 9, 10)))

使用SQL翻译，show_query(output)更加简短：

SELECT `start_dates`
    , TRY_CAST(SUBSTRING(TRY_CAST(`start_dates` AS VARCHAR(MAX)), 1, 4) AS FLOAT) AS `num_year`
    , TRY_CAST(SUBSTRING(TRY_CAST(`start_dates` AS VARCHAR(MAX)), 6, 2) AS FLOAT) AS `num_month`
    , TRY_CAST(SUBSTRING(TRY_CAST(`start_dates` AS VARCHAR(MAX)), 9, 2) AS FLOAT) AS `num_day`
FROM `df`

希望这个可以在dbplyr可以翻译的所有SQL版本上运行。由于我无法访问每个SQL版本进行测试，因此来自已经在特定SQL版本上测试过的人的评论将会很有帮助。

- Simon.S.A.