【R】SQLのクエリをいい感じに作ってくれるRの関数

DBにクエリを投げてテストする際、RStudioからRPostgreSQLなどのライブラリを利用すると非常に便利です。

なぜかというと、DBからの戻り値をRのデータフレーム型の変数に格納することができ、 その値をR言語の豊富な関数によって集計することができるからです。

overlap.hatenablog.jp

ただ、私の場合sqlのクエリの文字列を手入力しなければならず、毎回面倒な上自由度が低く、ぐんにょりしていました。

sql <- "
SELECT 
  date, 
  sum(sales) AS amount 
FROM 
  table 
WHERE 
  amount > 100
ORDER BY 
  date
"
db.con <- RPostgreSQL::dbConnect(
  drv = dbDriver("PostgreSQL"),
  host = "192.********",
  user = "takeshi",
  password = "******",
  port = ****,
  dbname = "unko"
)
db.res <- dbSendQuery(db.con, sql)
dataset <- fetch(db.res)
head(dataset)

というわけで、SQLを簡単に生成する関数を作ってみました。

私は気遣いのできるプログラマーなので、引数にカラムをc('col1', 'col2')みたいなリスト形式で与えると、コンマ区切りで出力してくれるようにしました（SELECT col1, col2 ...）。

必要最低限のSELECT文は出せるようになったので、JOINやUNION ALLなどはSELECT文同士を文字列結合すれば定義できると思います。

make_query <- function(
  select = "*", # string or list
  from, # string
  where = NULL, #string
  groupby = NULL, # string or list
  orderby = NULL # string or list
  ){
  # 関数内のローカルスコープで文字列結合の演算子を定義
  # 毎回paste関数を使うのも面倒なので
  "&" <- function(e1, e2) {
    if (is.character(c(e1, e2))) {
      paste(e1, e2)
    } 
  }
  return (
    "SELECT" & paste0(select, collapse = ", ") &
      "FROM" & from &
      # ifelse関数は三項演算子みたいなもの
      # WHERE句にAND条件しか指定できないのが萌えポイント
      ifelse(
        is.character(where),
        "WHERE" & paste0(where, collapse = ' AND '),
        ''
      ) &
      ifelse(
        is.character(groupby), 
        "GROUP BY" & paste0(groupby, collapse = ", "),
        ''
      ) &
      ifelse(
        is.character(orderby),
        "ORDER BY" & paste0(orderby, collapse = ", "),
        ''
      )
    )
}

# 使い方
make_query(
  select = c('date', 'sum(sales) AS amount'),
  from = 'table',
  where = 'amount > 100',
  orderby = 'date'
  )
# => "SELECT date, sum(sales) AS amount FROM table WHERE amount > 100  ORDER BY date"
# 引数が指定されてない箇所（NULLのままの箇所）は文字列結合の際に空白が2つ入ってしまう