使用Dbplyr时将日期转换为年四分之一格式 [英] Converting Date to Year-Quarter Format When Using Dbplyr

问题描述

我在数据库中有一个数据框，其日期尺寸是我使用RPostgres/RpostgreSQL和dbplyr拉出的.每个日期的格式都为"YYYY-MM-DD"，我需要添加一个新的日期(或字符)维，该维应以年份-季度格式"YYYY-Q"反映原始日期.(带破折号，而不是句点).

I have a data frame in a database with a date dimension that I pulled using RPostgres/RpostgreSQL and dbplyr. Each date is in the format 'YYYY-MM-DD', and I need to add a new date (or character) dimension that reflects the original date in the year-quarter format "YYYY-Q" (with a dash, not a period).

鉴于我不能将lubridate与dbplyr一起使用，我将如何做到这一点?

Given that I can't use lubridate with dbplyr, how would I accomplish this?

感谢您的帮助！这是到目前为止我所拥有的简化版本，因此您可以看到我正在使用哪些程序包以及如何连接到数据库.

Thanks for your help! Here's a simplified version of what I have so far so you can see what packages I'm using and how I'm connecting to the database.

# Packages
library(RPostgres)
library(RPostgreSQL)
library(dplyr)
library(dbplyr)

# Connect to db 
drv <- dbDriver("Postgres")

# Setup connect to db
conn <- dbConnect(drv,
                  dbname = etc,)

# Define table to use in db
table <- tbl(conn, in_schema("xyz", "abc"))

#Select columns and filter
df <- table %>%
  #Filter for pertinent data
  filter(date > as.Date("2018-01-01") & date <= as.Date("2020-01-01")) 

 

推荐答案

在 dbplyr 中为一堆 lubridate 函数提供了SQL翻译.所以下面的代码对我有用.

A bunch of lubridate functions are given SQL translations in dbplyr. So the code below works for me.

这样做的优点是如果谨慎使用，您可以将数据位置转移到最有效的位置(在服务器上或本地).

An advantage of doing this way is that if used with care, you can shift the location of data to the most efficient location (on the server or locally).

library(dplyr, warn.conflicts = FALSE)
library(DBI)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

pg <- dbConnect(RPostgres::Postgres(), bigint = "integer")
calls <- tbl(pg, sql("SELECT * FROM streetevents.calls"))

calls %>%
    mutate(yq = paste0(year(start_date), "-", quarter(start_date))) %>%
    select(start_date, yq)
#> # Source:   lazy query [?? x 2]
#> # Database: postgres [iangow@/tmp:5432/crsp]
#>    start_date          yq    
#>    <dttm>              <chr> 
#>  1 2013-09-10 19:30:00 2013-3
#>  2 2003-10-22 15:00:00 2003-4
#>  3 2009-10-22 16:00:00 2009-4
#>  4 2017-02-09 06:00:00 2017-1
#>  5 2010-02-22 22:00:00 2010-1
#>  6 2016-08-08 20:30:00 2016-3
#>  7 2016-05-11 13:00:00 2016-2
#>  8 2012-05-15 16:20:00 2012-2
#>  9 2004-08-19 21:00:00 2004-3
#> 10 2017-07-06 13:30:00 2017-3
#> # … with more rows

calls %>%
    collect(n = 10) %>%
    mutate(yq = paste0(year(start_date), "-", quarter(start_date))) %>%
    select(start_date, yq)
#> # A tibble: 10 x 2
#>    start_date          yq    
#>    <dttm>              <chr> 
#>  1 2013-09-10 19:30:00 2013-3
#>  2 2003-10-22 15:00:00 2003-4
#>  3 2009-10-22 16:00:00 2009-4
#>  4 2017-02-09 06:00:00 2017-1
#>  5 2010-02-22 22:00:00 2010-1
#>  6 2016-08-08 20:30:00 2016-3
#>  7 2016-05-11 13:00:00 2016-2
#>  8 2012-05-15 16:20:00 2012-2
#>  9 2004-08-19 21:00:00 2004-3
#> 10 2017-07-06 13:30:00 2017-3

^{由 reprex软件包(v1.0.0)创建于2021-04-03 sup>}

^{Created on 2021-04-03 by the reprex package (v1.0.0)}

这篇关于使用Dbplyr时将日期转换为年四分之一格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！