Rで解析：SQLの問い合わせにVISUALIZE句を足してプロットする「ggsql」パッケージ

「ggsql」パッケージは、いつものSQLの問い合わせにVISUALIZE句とDRAW句を書き足すだけで、集計した結果をそのままプロットにできるパッケージです。Rのデータフレームを表として登録すれば、絞り込みや集計から図の指定までを1本の問い合わせで書けます。中ではDuckDBが動くので、別にデータベースを用意する必要はありません。仕上がった図はSVGやPDF、PNGで保存でき、Shinyアプリにもそのまま埋め込めます。SQLに慣れていて、集計した結果をすぐ図で確かめたい人に向いていると考えます。

パッケージバージョンは0.5.2。Windows 11 x64 (build 26200)のR version 4.6.1で確認しています。

＜おすすめのRに関する書籍です＞

データサイエンティストのための最新知識と実践 Rではじめよう! [モダン]なデータ分析

モダンな書き方でデータ分析を進めるための一冊。今どきのRの流儀を押さえたい人に。

www.amazon.co.jp

パッケージのインストール

下記コマンドを実行してください。

# パッケージのインストール
install.packages("ggsql")

# パッケージの読み込み
library("ggsql")

コマンド例

詳細はコメント、パッケージのヘルプを確認してください。

ggsqlでは、データを読む窓口を「リーダー」と呼びます。リーダーにデータフレームを表として登録し、SQLの問い合わせを投げます。プロットにするときは、SELECT文の後ろに「VISUALIZE 列名 AS x, 列名 AS y」で軸の対応を、「DRAW point」「DRAW line」「DRAW bar」で図の種類を書きます。ggsql_executeコマンドが返すのは図の仕様（Spec）で、これを書き出し役の「ライター」に渡すとSVGなどの形になります。

以下の例では、共通のデータとして、京都府内の4地区（左京区、伏見区、亀岡市、京丹後市）における、京野菜の月別出荷量と単価を模したデータを作成します。

# 乱数を固定
set.seed(1234)

# 4地区×12か月の出荷量と単価の架空データを作成
shukka <- data.frame(
  chiku = rep(c("左京区", "伏見区", "亀岡市", "京丹後市"), each = 12),
  tsuki = rep(1:12, times = 4),
  ryou = round(rnorm(48, mean = 120, sd = 30)),
  tanka = round(runif(48, min = 250, max = 600))
)

DuckDBのリーダーを作る：duckdb_readerコマンド

問い合わせ先になるDuckDBのリーダーを作ります。databaseを指定しなければメモリ上に作られ、Rを閉じると消えます。

# メモリ上にDuckDBのリーダーを作成
reader <- duckdb_reader()

データフレームを表として登録する：ggsql_registerコマンド

Rのデータフレームを、リーダーの中の表として登録します。登録した名前でSQLから参照できます。登録済みの表の一覧はggsql_table_namesコマンド、登録の解除はggsql_unregisterコマンドで行います。

# 出荷データを "shukka" という表として登録
ggsql_register(reader, shukka, "shukka")

# 登録されている表の名前を確認
ggsql_table_names(reader)
[1] "shukka"

SQLの結果をデータフレームで受け取る：ggsql_execute_sqlコマンド

VISUALIZE句を含まない、ふつうのSQLを実行して結果をデータフレームで受け取ります。プロットにする前に集計の中身を確かめるときに使えます。

# 地区ごとの年間出荷量と平均単価を集計し、出荷量の多い順に並べる
ggsql_execute_sql(
  reader,
  "SELECT chiku, SUM(ryou) AS goukei, ROUND(AVG(tanka), 1) AS heikin_tanka
   FROM shukka GROUP BY chiku ORDER BY goukei DESC"
)
     chiku goukei heikin_tanka
1   伏見区   1440        414.6
2   左京区   1282        343.4
3   亀岡市   1233        469.7
4 京丹後市   1159        460.9

＜おすすめのRに関する書籍です＞

Rによるスクレイピング入門 | 石田基広, 市川太祐, 瓜生真也, 湯谷啓明

Web上の情報をRで収集する方法を、HTMLやAPIの基礎から解説。例題を動かしながら覚えられる。

問い合わせからプロットを作る：ggsql_executeコマンド

VISUALIZE句とDRAW句を含む問い合わせを実行し、図の仕様（Spec）を返します。表示するとプロットになります。GROUP BYで集計した結果をそのまま棒グラフにしたり、「AS stroke」で地区ごとに線の色を分けたりできます。

# 地区ごとの年間出荷量を集計し、棒グラフにする
spec_bar <- ggsql_execute(
  reader,
  "SELECT chiku, SUM(ryou) AS goukei FROM shukka GROUP BY chiku
   VISUALIZE chiku AS x, goukei AS y
   DRAW bar
   LABEL title => '地区別の年間出荷量'"
)

# プロットを表示
spec_bar

# 6月以降の出荷量の推移を、地区ごとに色を分けた折れ線にする
spec_line <- ggsql_execute(
  reader,
  "SELECT * FROM shukka WHERE tsuki >= 6
   VISUALIZE tsuki AS x, ryou AS y, chiku AS stroke
   DRAW line"
)

# プロットを表示
spec_line

問い合わせを実行前に確かめる：ggsql_validateコマンド

SQLを実行せずに、問い合わせの書き方に誤りがないかを確かめます。結果が正しいかどうかはggsql_is_validコマンドでTRUE/FALSEとして取り出せます。

# 折れ線の問い合わせの書き方を確かめる
ggsql_validate(
  "SELECT * FROM shukka WHERE tsuki >= 6
   VISUALIZE tsuki AS x, ryou AS y, chiku AS stroke
   DRAW line"
)
<ggsql_validated> [valid]
• Has VISUALISE clause

＜おすすめのRに関する書籍です＞

Rによるやさしい統計学 | 山田 剛史, 杉澤 武俊, 村井 潤一郎

心理統計の入門としてロングセラーの一冊。統計の考え方とRの操作を同時に身につけられる。

プロットをファイルに保存する：ggsql_saveコマンド

図の仕様をファイルに書き出します。形式はファイルの拡張子で決まり、.svg、.pdf、.hep、.png、.jsonが使えます。.pngの書き出しにはrsvgパッケージが要ります。

# 地区別の棒グラフを幅800、高さ500のSVGとして保存
ggsql_save(spec_bar, "shukka_chiku.svg", width = 800, height = 500)

地区別の棒グラフを幅800、高さ500のSVGとして保存

ライターで図の形式に変える：ggsql_renderコマンド

図の仕様をライターに渡して、SVGの文字列などに変えます。ライターにはsvg_writer、pdf_writer、hep_writerがあります。ファイルに保存せず、文字列のままほかの処理に渡したいときに使います。

# 折れ線の仕様を幅800、高さ500のSVGの文字列に変える
svg_text <- ggsql_render(svg_writer(width = 800, height = 500), spec_line)

# SVGの文字列をファイルに書き出す
writeLines(svg_text, "shukka_suii.svg")

SVGの文字列をファイルに書き出す

Shinyアプリにプロットを置く：ggsqlOutputコマンド

Shinyアプリの画面に、ggsqlのプロットを表示する枠を置きます。サーバー側ではrenderGgsqlコマンドに問い合わせの文字列を渡します。例では、選んだ地区の月別出荷量を棒グラフにします。

# Shinyを読み込み
library("shiny")

# 地区を選ぶ欄と、プロットの表示枠を置いた画面を作成
ui <- fluidPage(
  selectInput("chiku", "地区", choices = unique(shukka$chiku)),
  ggsqlOutput("chart", height = "450px")
)

# 選んだ地区の月別出荷量を棒グラフにする処理を作成
server <- function(input, output, session) {
  output$chart <- renderGgsql(
    {
      sprintf(
        "SELECT * FROM shukka WHERE chiku = '%s'
       VISUALIZE tsuki AS x, ryou AS y
       DRAW bar",
        input$chiku
      )
    },
    reader = reader
  )
}

# アプリを起動
shinyApp(ui, server)

＜おすすめのRに関する書籍です＞

データに触れながら学ぶ統計学 R言語のUIツール「Exploratory」で身につくデータサイエンスの手法 | 西田勘一郎

RベースのUIツール「Exploratory」を使い、コードを書かずに統計とデータサイエンスの手法を体験できる。

この記事が誰かの役に立ちますように。