1. はじめに

dplyr は PostgreSQL のウインドウ関数に一部対応していますが、まだ対応されていないものもあります。

参考：Window functions and grouped mutate/filter

今回、first_value() を使いたくなったのですが、対応されていないようです。
下記の記事で行ったように、dplyr で作成されたクエリの SQL 文字列を変更することによって任意の SQL を投げることができます。

R6 をハックして dplyr + PostgreSQL で移動平均を計算する

この記事では、dplyr でデータベースアクセスする際に first_value() を使う方法を示します。

2. first_value() の実行

まずは、下記のようなテーブルを用意します。

R

library(dplyr)
library(stringr)

glimpse(my_tbl)

結果

Observations: 1959
Variables:
$ id      (int) 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 6, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, ...
$ payment (int) 6400, 3000, 19200, 15600, 10000, 10000, 2000, 3000, NA, 15600, 32400, 2000, 12800, 8000...
$ date    (time) 2014-02-06, 2014-02-06, 2014-12-25, 2014-03-05, 2014-01-30, 2014-01-30, 2014-02-06, 20...

まずは似たような SQL を生成する rank() 関数を使ってクエリを生成します。

R

q <- my_tbl %>% 
  group_by(id) %>%
  mutate(first=rank(date))
q$query

結果

<Query> SELECT "id", "payment", "date", "first"
FROM (SELECT "id", "payment", "date", rank() OVER (PARTITION BY "id" ORDER BY "date") AS "first"
FROM "ljavnfvqqf") AS "_W4"
<PostgreSQLConnection:(1792,0)>

このクエリの rank() の部分を first_value() に変更します。

R

q$query$sql <- q$query$sql %>% str_replace("rank\\(\\)", 'first_value("date")')
q$query

結果

<Query> SELECT "id", "payment", "date", "first"
FROM (SELECT "id", "payment", "date", first_value("date") OVER (PARTITION BY "id" ORDER BY "date") AS "first"
FROM "ljavnfvqqf") AS "_W4"
<PostgreSQLConnection:(1792,0)>

クエリが書き換わりましたので、実行します。

R

q %>% collect

結果

   id payment       date      first
1   1    6400 2014-02-06 2014-02-06
2   2    3000 2014-02-06 2014-02-06
3   2   15600 2014-03-05 2014-02-06
4   2   19200 2014-12-25 2014-02-06
5   3   10000 2014-01-30 2014-01-30
6   3   10000 2014-01-30 2014-01-30
7   3    2000 2014-02-06 2014-01-30
8   4    2000 2014-01-15 2014-01-15
9   4    3000 2014-01-28 2014-01-15
10  4   15600 2014-02-28 2014-01-15
.. ..     ...        ...        ...

目的の結果が得られました。

3. 取得変数の変更

first_value() は、並び替えるカラムと取得するカラムを別々に指定することができます。
上記では、どちらも date を使いました。
rank() に入れる引数が並び替えるカラム、first_value() に入れる引数が取得するカラムに対応します。

例えば、一番早い時間(date)の支払(payment)を取得するためには、date が並び替えるカラム、payment が取得するカラムとなります。
それぞれ、rank(date), first_value(payment) とすることによって目的のクエリが生成できます。

R

q <- my_tbl %>% 
  group_by(id) %>%
  mutate(first=rank(date))
q$query$sql <- q$query$sql %>% str_replace("rank\\(\\)", 'first_value("payment")')
q$query

結果

<Query> SELECT "id", "payment", "date", "first"
FROM (SELECT "id", "payment", "date", first_value("payment") OVER (PARTITION BY "id" ORDER BY "date") AS "first"
FROM "ljavnfvqqf") AS "_W5"
<PostgreSQLConnection:(1792,0)>

目的のクエリが生成できたので、実行します。

R

q %>% collect

結果

   id payment       date first
1   1    6400 2014-02-06  6400
2   2    3000 2014-02-06  3000
3   2   15600 2014-03-05  3000
4   2   19200 2014-12-25  3000
5   3   10000 2014-01-30 10000
6   3   10000 2014-01-30 10000
7   3    2000 2014-02-06 10000
8   4    2000 2014-01-15  2000
9   4    3000 2014-01-28  2000
10  4   15600 2014-02-28  2000
.. ..     ...        ...   ...

実行結果も意図したとおりに取れています。

4. 降順に並び替える

rank() の並び替えはデフォルトでは昇順です。
降順に並び替えるには arrange() を使って次のようにします。

R

q <- my_tbl %>% 
  group_by(id) %>%
  arrange(desc(date)) %>%
  mutate(first=rank())
q$query

結果

<Query> SELECT "id", "payment", "date", "first"
FROM (SELECT "id", "payment", "date", rank() OVER (PARTITION BY "id" ORDER BY "date" DESC) AS "first"
FROM "ljavnfvqqf"
ORDER BY "id", "date" DESC) AS "_W13"
<PostgreSQLConnection:(1792,0)>

クエリが rank() OVER (PARTITION BY "id" ORDER BY "date" DESC) と降順になったことが確認できます。
これを first_value() で書き換えます。

R

q$query$sql <- q$query$sql %>% str_replace("rank\\(\\)", 'first_value("date")')
q$query

結果

<Query> SELECT "id", "payment", "date", "first"
FROM (SELECT "id", "payment", "date", first_value("date") OVER (PARTITION BY "id" ORDER BY "date" DESC) AS "first"
FROM "ljavnfvqqf"
ORDER BY "id", "date" DESC) AS "_W15"
<PostgreSQLConnection:(1792,0)>

目的のクエリが生成されました。

R

q %>% collect

結果

   id payment       date      first
1   1    6400 2014-02-06 2014-02-06
2   2   19200 2014-12-25 2014-12-25
3   2   15600 2014-03-05 2014-12-25
4   2    3000 2014-02-06 2014-12-25
5   3    2000 2014-02-06 2014-02-06
6   3   10000 2014-01-30 2014-02-06
7   3   10000 2014-01-30 2014-02-06
8   4   32400 2015-01-19 2015-01-19
9   4      NA 2014-12-25 2015-01-19
10  4   15600 2014-02-28 2015-01-19
.. ..     ...        ...        ...

実行結果も意図どおりです。

以上です。

関連

dplyr でデータベースを扱うときの情報をそろそろまとめておく