使用纯R处理dbplyr的日期

Question

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

我以前的方法是在dplyr命令中使用SQL语法的片段(例如，请参见答案：这里和这里)。但是，这要求开发人员知道(或找到)相应的SQL命令，当dbplyr的部分要点是将其转换为SQL时。

这让我不禁要问：，只有在连接到远程数据库时，才使用dbplyr转换来操作日期的最佳方法是什么？

理想的解决办法是：

• 只使用dbplyr转换，因此不能使用没有dbplyr转换的函数。
• 使用纯R，不使用SQL片段。
• 在数据库上运行，所以是远程表而不是本地表。

我认为我们至少应该能够：

• 提取年份、月份和日期
• 将年、月和日合并为新的日期。

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

• 增量a日期
• 找出两个日期的区别
• 找到月底的日期

但是，更快/更优雅地进行这些更先进的操作是更好的选择。

Answer 1

答案之一是，这其中的大部分已经成为可能。(请参见答案这里。)

在dbplyr中缺少所需的函数的情况下，一个想法是编写拉请求，将更多的lubridate函数翻译到dbplyr的后端。

翻译似乎不可避免地是特定于后端的。如果您查看PostgreSQL后端这里，您可以看到一些lubridate函数(例如，month或quarter)在那里得到了翻译，而其他函数(例如，ymd)则没有。

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)))

show_query(output)的SQL转换要短得多：

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风格上测试过它的人的评论将是有帮助的。