WINDOW 関数を一通り試してみよう

この記事は個人ブログと同じ内容です

www.ritolab.com

MySQL も 8 から WINDOW 関数が使えるようになり更に利便性が向上していますが、SQL の WINDOW 関数にはどんな関数があるのか？ということで、WINDOW 関数を一通り試してみます。

WINDOW 関数

WINDOW 関数は、結果行の集約を行うことなく集計・分析のための計算を行う事のできる関数。

ウィンドウ関数は、一連のクエリー行に対して集計のような操作を実行します。ただし、集計操作ではクエリー行が単一の結果行にグループ化されますが、ウィンドウ関数ではクエリー行ごとに結果が生成されます。

引用元：MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.2 Window 関数の概念と構文

例えば集計関数を用いる際に GROUP BY すると結果行がまとめられますが、WINDOW 関数を用いる（もしくは集約関数を WINDOW 関数として処理する）場合は結果行はまとめられず、各行に結果が付与されるようになります。

GROUP BY で集計

SELECT
    user_id,
    AVG(score) AS average
FROM scores
GROUP BY user_id;

+---------+---------+
| user_id | average |
+---------+---------+
|       1 | 76.7204 |  <-- 行が user_id でまとめられる
|       2 | 73.7097 |
|       3 | 74.1505 |
|       4 | 74.5591 |
|       5 | 75.2688 |
+---------+---------+

WINDOW 関数で集計

SELECT
    user_id,
    score,
    AVG(score) OVER (PARTITION BY user_id) AS average
FROM scores;

+---------+-------+---------+
| user_id | score | average |
+---------+-------+---------+
|       1 |    85 | 76.7204 |  <-- それぞれのレコードに average が付与される
|       1 |    83 | 76.7204 |
|       1 |    88 | 76.7204 |
|       1 |    90 | 76.7204 |
|       1 |    58 | 76.7204 |
+---------+-------+---------+

（AVG() は集約関数ですが上記のように集約関数を WINDOW 関数として処理する事も可能）

基本形

分析関数 OVER句([PARTITION BY 句] [ORDER BY 句] [frame 句])

FUNCTION_NAME(expr) OVER ([PARTITION BY expr, expr,..] [ORDER BY expr, expr,..] [ROWS|RANGE ...])

  • PARTITION BY 句：どのカラムをグループとするか
  • ORDER BY 句：ソート順の指定
  • frame 句：対象行範囲の指定

これらを必要に応じて指定しつつ、振りたい値を行に付与していく。

名前付き Window

ウィンドウを定義する事で同じものをまとめられてクエリを簡単にできる。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.21.4 名前付きウィンドウ

SELECT
    subject,
    user_id,
    average,
    MIN(average) OVER w1 as min_avg,
    MAX(average) OVER w1 as max_avg
FROM user_avg_scores_by_subject
WINDOW w1 AS (PARTITION BY subject) -- <- ウィンドウを定義
;

サンプルテーブル

サンプルで score テーブルを作成し、ここに対してクエリを投げていきます。

create table scores
(
    id                  bigint unsigned auto_increment primary key,
    user_id             int unsigned not null,
    subject             varchar(255) not null,
    score               int unsigned not null,
    implementation_date date         not null
)

5 名のユーザーが 1 日に 3 科目のテストを 1 ヶ月間受けた結果を格納したテーブルになっています。

+-----+---------+---------+-------+---------------------+
| id  | user_id | subject | score | implementation_date |
+-----+---------+---------+-------+---------------------+
|   1 |       1 | sub01   |    85 | 2021-12-01          |
|   2 |       1 | sub02   |    83 | 2021-12-01          |
|   3 |       1 | sub03   |    88 | 2021-12-01          |
|   4 |       1 | sub01   |    90 | 2021-12-02          |
|   5 |       1 | sub02   |    58 | 2021-12-02          |
|   6 |       1 | sub03   |    84 | 2021-12-02          |
|   7 |       1 | sub01   |    76 | 2021-12-03          |
|   8 |       1 | sub02   |    72 | 2021-12-03          |
（略）

次項からクエリを書いていきますが、COUNT や SUM などの集約関数も WINDOW 関数として処理できるのでそちらも含めて試していきます。

COUNT

レコード数をカウントします。

SELECT
    implementation_date,
    subject,
    user_id,
    score,
    COUNT(user_id) OVER (PARTITION BY user_id) as total_tests
FROM scores
ORDER BY implementation_date, subject, user_id;

PARTITION BY 句に user_id を指定して、ユーザーごとの総レコード数（＝総テスト実施数）を付与します。

+---------------------+---------+---------+-------+-------------+
| implementation_date | subject | user_id | score | total_tests |
+---------------------+---------+---------+-------+-------------+
| 2021-12-01          | sub01   |       1 |    85 |          93 |
| 2021-12-01          | sub01   |       2 |    85 |          93 |
| 2021-12-01          | sub01   |       3 |    78 |          93 |
| 2021-12-01          | sub01   |       4 |    93 |          93 |
| 2021-12-01          | sub01   |       5 |    78 |          93 |
（略）
| 2021-12-31          | sub03   |       5 |    61 |          93 |
+---------------------+---------+---------+-------+-------------+
465 rows in set (0.00 sec)

1 日 3 テスト × 1 ヶ月（31 日）のため 1 人あたりの総テスト実施数は 93 回ですが、それらが各レコードに追加されていることがわかります。（このテーブルの総レコード数は 93 × 5 人分 = 465 レコード）

SUM

合計値を算出します。

SELECT
    user_id,
    subject,
    score,
    implementation_date,
    SUM(score) OVER (PARTITION BY user_id, subject) as total_by_subject
FROM scores
ORDER BY user_id, implementation_date, subject;

user_id と subject でグルーピングして、各ユーザーの科目ごとの合計得点を付与します。

+---------+---------+-------+---------------------+------------------+
| user_id | subject | score | implementation_date | total_by_subject |
+---------+---------+-------+---------------------+------------------+
|       1 | sub01   |    85 | 2021-12-01          |             2417 |
|       1 | sub02   |    83 | 2021-12-01          |             2405 |
|       1 | sub03   |    88 | 2021-12-01          |             2313 |
（略）
|       5 | sub03   |    61 | 2021-12-31          |             2438 |
+---------+---------+-------+---------------------+------------------+
465 rows in set (0.00 sec)

AVG

平均値を算出します。

SELECT
    user_id,
    implementation_date,
    subject,
    score,
    AVG(score) OVER (PARTITION BY user_id, subject) AS average
FROM scores
ORDER BY user_id, implementation_date, subject;

ユーザごと各科目の平均点を付与します。

+---------+---------------------+---------+-------+---------+
| user_id | implementation_date | subject | score | average |
+---------+---------------------+---------+-------+---------+
|       1 | 2021-12-01          | sub01   |    85 | 77.9677 |
|       1 | 2021-12-01          | sub02   |    83 | 77.5806 |
|       1 | 2021-12-01          | sub03   |    88 | 74.6129 |
（略）
|       5 | 2021-12-31          | sub03   |    61 | 78.6452 |
+---------+---------------------+---------+-------+---------+
465 rows in set (0.01 sec)

また、 frame 句で範囲を指定すればその区間での平均値も算出できるので、移動平均を求めたい時にも使えて便利です。

SELECT
    user_id,
    implementation_date,
    subject,
    score,
    AVG(score) OVER (PARTITION BY user_id, subject) AS average,
    AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average_3,
    AVG(score) OVER (PARTITION BY user_id, subject ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_average_5
FROM scores
ORDER BY user_id, implementation_date, subject;

3 区間と 5 区間での平均値を付与します。

+---------+---------------------+---------+-------+---------+------------------+------------------+
| user_id | implementation_date | subject | score | average | moving_average_3 | moving_average_5 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
|       1 | 2021-12-01          | sub01   |    85 | 77.9677 |          87.5000 |          83.6667 |
|       1 | 2021-12-01          | sub02   |    83 | 77.5806 |          70.5000 |          71.0000 |
|       1 | 2021-12-01          | sub03   |    88 | 74.6129 |          86.0000 |          80.3333 |
|       1 | 2021-12-02          | sub01   |    90 | 77.9677 |          83.6667 |          79.5000 |
|       1 | 2021-12-02          | sub02   |    58 | 77.5806 |          71.0000 |          71.2500 |
|       1 | 2021-12-02          | sub03   |    84 | 74.6129 |          80.3333 |          75.7500 |
|       1 | 2021-12-03          | sub01   |    76 | 77.9677 |          77.6667 |          81.8000 |
|       1 | 2021-12-03          | sub02   |    72 | 77.5806 |          67.3333 |          72.6000 |
|       1 | 2021-12-03          | sub03   |    69 | 74.6129 |          71.6667 |          72.8000 |
（略）
|       5 | 2021-12-31          | sub03   |    61 | 78.6452 |          63.5000 |          60.3333 |
+---------+---------------------+---------+-------+---------+------------------+------------------+
465 rows in set (0.00 sec)

LAG / LEAD

指定行分、前の値（LAG）・後ろの値（LEAD）を付与します。

SELECT
    user_id,
    implementation_date,
    subject,
    score,
    LAG(score, 1) over (PARTITION BY user_id, subject) as last_score,
    LEAD(score, 1) over (PARTITION BY user_id, subject) as next_score
FROM scores
ORDER BY user_id, subject, implementation_date;

user_id と subject でグルーピングした上で、各行に前後行の score を付与します。

+---------+---------------------+---------+-------+------------+------------+
| user_id | implementation_date | subject | score | last_score | next_score |
+---------+---------------------+---------+-------+------------+------------+
|       1 | 2021-12-01          | sub01   |    85 |       NULL |         90 |
|       1 | 2021-12-02          | sub01   |    90 |         85 |         76 |
|       1 | 2021-12-03          | sub01   |    76 |         90 |         67 |
|       1 | 2021-12-04          | sub01   |    67 |         76 |         91 |
|       1 | 2021-12-05          | sub01   |    91 |         67 |         98 |
|       1 | 2021-12-06          | sub01   |    98 |         91 |         58 |
|       1 | 2021-12-07          | sub01   |    58 |         98 |         83 |
（略）
|       1 | 2021-12-30          | sub01   |    93 |         56 |         76 |
|       1 | 2021-12-31          | sub01   |    76 |         93 |       NULL |
|       1 | 2021-12-01          | sub02   |    83 |       NULL |         58 |
|       1 | 2021-12-02          | sub02   |    58 |         83 |         72 |
|       1 | 2021-12-03          | sub02   |    72 |         58 |         72 |
（略）
|       5 | 2021-12-31          | sub03   |    61 |         66 |       NULL |
+---------+---------------------+---------+-------+------------+------------+
465 rows in set (0.00 sec)

グルーピングされた行の先頭と後尾についてはそれぞれ last と next が存在しないので null になっています。

ROW_NUMBER

行グループ内で序数を振る。

SELECT
    implementation_date,
    user_id,
    ROW_NUMBER() over (PARTITION BY user_id, subject) as times,
    subject,
    score
FROM scores;

ユーザーと科目でグルーピングし、科目毎の実施回（times）を振ってみます。

+---------------------+---------+-------+---------+-------+
| implementation_date | user_id | times | subject | score |
+---------------------+---------+-------+---------+-------+
| 2021-12-01          |       1 |     1 | sub01   |    85 |
| 2021-12-02          |       1 |     2 | sub01   |    90 |
| 2021-12-03          |       1 |     3 | sub01   |    76 |
| 2021-12-04          |       1 |     4 | sub01   |    67 |
| 2021-12-05          |       1 |     5 | sub01   |    91 |
| 2021-12-06          |       1 |     6 | sub01   |    98 |
（略）
| 2021-12-29          |       1 |    29 | sub01   |    56 |
| 2021-12-30          |       1 |    30 | sub01   |    93 |
| 2021-12-31          |       1 |    31 | sub01   |    76 |
| 2021-12-01          |       1 |     1 | sub02   |    83 |
| 2021-12-02          |       1 |     2 | sub02   |    58 |
| 2021-12-03          |       1 |     3 | sub02   |    72 |
（略）
| 2021-12-29          |       5 |    29 | sub03   |    54 |
| 2021-12-30          |       5 |    30 | sub03   |    66 |
| 2021-12-31          |       5 |    31 | sub03   |    61 |
+---------------------+---------+-------+---------+-------+
465 rows in set (0.01 sec)

RANK

順位付けを行う。

SELECT
    implementation_date,
    subject,
    user_id,
    score,
    RANK() OVER (PARTITION BY subject, implementation_date ORDER BY score DESC) as ranking
FROM scores
ORDER BY implementation_date, subject, ranking;

subject と implementation_date でグルーピングを行い、score 降順でランク付けする事で、テスト日ごと各科目別の順位を付与します。

+---------------------+---------+---------+-------+---------+
| implementation_date | subject | user_id | score | ranking |
+---------------------+---------+---------+-------+---------+
| 2021-12-01          | sub01   |       4 |    93 |       1 |
| 2021-12-01          | sub01   |       1 |    85 |       2 |
| 2021-12-01          | sub01   |       2 |    85 |       2 |
| 2021-12-01          | sub01   |       3 |    78 |       4 |
| 2021-12-01          | sub01   |       5 |    78 |       4 |
| 2021-12-01          | sub02   |       1 |    83 |       1 |
| 2021-12-01          | sub02   |       2 |    81 |       2 |
| 2021-12-01          | sub02   |       5 |    76 |       3 |
| 2021-12-01          | sub02   |       3 |    70 |       4 |
| 2021-12-01          | sub02   |       4 |    50 |       5 |
| 2021-12-01          | sub03   |       4 |    97 |       1 |
（略）
| 2021-12-31          | sub03   |       5 |    61 |       5 |
+---------------------+---------+---------+-------+---------+
465 rows in set (0.01 sec)

NTILE

指定したグループ数で分類してランク付けを行う。分類は近しい値（＝できるだけ同じサイズ）でグルーピングされる。

SELECT
    user_id,
    total,
    NTILE(3) OVER (ORDER BY total desc) as g_rank
FROM user_total_scores;

5 人の合計得点を 3 つのグループに分類してランク付けを行います。

+---------+-------+--------+
| user_id | total | g_rank |
+---------+-------+--------+
|       1 |  7135 |      1 |
|       5 |  7000 |      1 |
|       4 |  6934 |      2 |
|       3 |  6896 |      2 |
|       2 |  6855 |      3 |
+---------+-------+--------+
5 rows in set (0.00 sec)

3 つのグループに分類されてランク付けされているのが確認できます。

DENSE_RANK

グループのランクを振る。

同率順位があっても順位が繰り下がらないところが rank() との違い。

SELECT
    subject,
    user_id,
    average,
    DENSE_RANK() OVER (PARTITION BY subject ORDER BY average desc) AS d_rnk,
    RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk
FROM user_avg_scores_by_subject;

各科目別でのユーザーの平均点に対してランク付けを行います。

+---------+---------+---------+-------+-----+
| subject | user_id | average | d_rnk | rnk |
+---------+---------+---------+-------+-----+
| sub01   |       1 |      78 |     1 |   1 |
| sub01   |       4 |      76 |     2 |   2 |
| sub01   |       3 |      75 |     3 |   3 |
| sub01   |       5 |      74 |     4 |   4 |
| sub01   |       2 |      73 |     5 |   5 |
| sub02   |       1 |      78 |     1 |   1 |
| sub02   |       4 |      76 |     2 |   2 |
| sub02   |       2 |      73 |     3 |   3 |
| sub02   |       3 |      73 |     3 |   3 |
| sub02   |       5 |      73 |     3 |   3 |
| sub03   |       5 |      79 |     1 |   1 |
| sub03   |       1 |      75 |     2 |   2 |
| sub03   |       2 |      75 |     2 |   2 |
| sub03   |       3 |      74 |     3 |   4 |
| sub03   |       4 |      71 |     4 |   5 |
+---------+---------+---------+-------+-----+

sub03 の順位を見てみると、同率順位があった際にその後の順位が繰り下がっていないことがわかります。

PERCENT_RANK

パーセントランクを算出する。

  • ランク最上位を 0 としてランクをパーセントで振る。
  • ランクの範囲は 0 〜 1
  • パーセントランクの計算式は（rank - 1）/（ウィンドウまたはパーティションの行数 - 1）
SELECT
    subject,
    user_id,
    average,
    RANK() OVER (PARTITION BY subject ORDER BY average desc) as rnk,
    PERCENT_RANK() OVER (PARTITION BY subject ORDER BY average desc) as p_rnk
FROM user_avg_scores_by_subject;

各位ユーザー科目毎の平均点に対してそれぞれパーセントランクを振ってみます。

+---------+---------+---------+-----+-------+
| subject | user_id | average | rnk | p_rnk |
+---------+---------+---------+-----+-------+
| sub01   |       1 |      78 |   1 |     0 |
| sub01   |       4 |      76 |   2 |  0.25 |
| sub01   |       3 |      75 |   3 |   0.5 |
| sub01   |       5 |      74 |   4 |  0.75 |
| sub01   |       2 |      73 |   5 |     1 |
| sub02   |       1 |      78 |   1 |     0 |
| sub02   |       4 |      76 |   2 |  0.25 |
| sub02   |       2 |      73 |   3 |   0.5 |
| sub02   |       3 |      73 |   3 |   0.5 |
| sub02   |       5 |      73 |   3 |   0.5 |
| sub03   |       5 |      79 |   1 |     0 |
| sub03   |       1 |      75 |   2 |  0.25 |
| sub03   |       2 |      75 |   2 |  0.25 |
| sub03   |       3 |      74 |   4 |  0.75 |
| sub03   |       4 |      71 |   5 |     1 |
+---------+---------+---------+-----+-------+

CUME_DIST

行グループ内で累積分布を振る。

指定したグループの（orderによる）最終行を 1 として、そこに向けて 0 から積み上がっていくイメージ。相対的な位置がわかる。

SELECT
    user_id,
    subject,
    average,
    CUME_DIST() OVER (PARTITION BY subject ORDER BY average) as cume_dist_by_avg
FROM user_avg_scores_by_subject
ORDER BY user_id, subject;

ユーザーの科目別平均から、科目をグループとして累積分布を振ってみます。

+---------+---------+---------+------------------+
| user_id | subject | average | cume_dist_by_avg |
+---------+---------+---------+------------------+
|       1 | sub01   |      78 |                1 |
|       1 | sub02   |      78 |                1 |
|       1 | sub03   |      75 |              0.8 |
|       2 | sub01   |      73 |              0.2 |
|       2 | sub02   |      73 |              0.6 |
|       2 | sub03   |      75 |              0.8 |
|       3 | sub01   |      75 |              0.6 |
|       3 | sub02   |      73 |              0.6 |
|       3 | sub03   |      74 |              0.4 |
|       4 | sub01   |      76 |              0.8 |
|       4 | sub02   |      76 |              0.8 |
|       4 | sub03   |      71 |              0.2 |
|       5 | sub01   |      74 |              0.4 |
|       5 | sub02   |      73 |              0.6 |
|       5 | sub03   |      79 |                1 |
+---------+---------+---------+------------------+

結果を見ると、ユーザー 1 は科目 sub03 の成績に関しては 80% の位置にいる事がわかります。

FIRST_VALUE / LAST_VALUE / NTH_VALUE

グループにおいてそれぞれ最初・最後・指定行の値を振る。

SELECT
    subject,
    user_id,
    average,
    FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
    LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
    NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;

科目別ユーザー平均点から、点数順においてそれぞれ最初・最後・3 番目行の値を振ってみます。

+---------+---------+---------+-------+------+-------------+
| subject | user_id | average | first | last | third_value |
+---------+---------+---------+-------+------+-------------+
| sub01   |       1 |      78 |    73 |   78 |          75 |
| sub01   |       4 |      76 |    73 |   78 |          75 |
| sub01   |       3 |      75 |    73 |   78 |          75 |
| sub01   |       5 |      74 |    73 |   78 |          75 |
| sub01   |       2 |      73 |    73 |   78 |          75 |
| sub02   |       1 |      78 |    73 |   78 |          73 |
| sub02   |       4 |      76 |    73 |   78 |          73 |
| sub02   |       2 |      73 |    73 |   78 |          73 |
| sub02   |       3 |      73 |    73 |   78 |          73 |
| sub02   |       5 |      73 |    73 |   78 |          73 |
| sub03   |       5 |      79 |    71 |   79 |          75 |
| sub03   |       1 |      75 |    71 |   79 |          75 |
| sub03   |       2 |      75 |    71 |   79 |          75 |
| sub03   |       3 |      74 |    71 |   79 |          75 |
| sub03   |       4 |      71 |    71 |   79 |          75 |
+---------+---------+---------+-------+------+-------------+

LAST_VALUE() で全体の last を取る場合はデフォルトは自分行までしか読まないため上記クエリのように frame 句の指定が必要です。

その場合は FIRST_VALUE() を使って last も取得するとシンプルだし事故らなくて良いと思いました。

SELECT
    subject,
    user_id,
    average,
    FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average) as first,
    LAST_VALUE(average) OVER (PARTITION BY subject ORDER BY average ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last,
    FIRST_VALUE(average) OVER (PARTITION BY subject ORDER BY average DESC) as last_by_first_value, -- <- average の降順の最初行をとる
  NTH_VALUE(average ,3) OVER (PARTITION BY subject ORDER BY average DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_value
FROM user_avg_scores_by_subject;

結果は LAST_VALUE() と同じになります。

+---------+---------+---------+-------+------+---------------------+-------------+
| subject | user_id | average | first | last | last_by_first_value | third_value |
+---------+---------+---------+-------+------+---------------------+-------------+
| sub01   |       1 |      78 |    73 |   78 |                  78 |          75 |
| sub01   |       4 |      76 |    73 |   78 |                  78 |          75 |
| sub01   |       3 |      75 |    73 |   78 |                  78 |          75 |
| sub01   |       5 |      74 |    73 |   78 |                  78 |          75 |
| sub01   |       2 |      73 |    73 |   78 |                  78 |          75 |
| sub02   |       1 |      78 |    73 |   78 |                  78 |          73 |
| sub02   |       4 |      76 |    73 |   78 |                  78 |          73 |
| sub02   |       2 |      73 |    73 |   78 |                  78 |          73 |
| sub02   |       3 |      73 |    73 |   78 |                  78 |          73 |
| sub02   |       5 |      73 |    73 |   78 |                  78 |          73 |
| sub03   |       5 |      79 |    71 |   79 |                  79 |          75 |
| sub03   |       1 |      75 |    71 |   79 |                  79 |          75 |
| sub03   |       2 |      75 |    71 |   79 |                  79 |          75 |
| sub03   |       3 |      74 |    71 |   79 |                  79 |          75 |
| sub03   |       4 |      71 |    71 |   79 |                  79 |          75 |
+---------+---------+---------+-------+------+---------------------+-------------+

JSON_ARRAYAGG

グループの値を JSON にまとめる。

SELECT
    user_id,
    subject,
    implementation_date,
    score,
    JSON_ARRAYAGG(score) OVER (PARTITION BY user_id, subject ORDER BY user_id, subject) as all_scores_for_subject
FROM scores
ORDER BY  user_id, subject;

ユーザーの科目別の得点をまとめてみます。

+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
| user_id | subject | implementation_date | score | all_scores_for_subject                                                                                                         |
+---------+---------+---------------------+-------+--------------------------------------------------------------------------------------------------------------------------------+
|       1 | sub01   | 2021-12-01          |    85 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76]   |
|       1 | sub01   | 2021-12-02          |    90 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76]   |
|       1 | sub01   | 2021-12-03          |    76 | [85, 90, 76, 67, 91, 98, 58, 83, 69, 87, 98, 91, 70, 91, 60, 55, 95, 92, 93, 78, 66, 94, 50, 84, 63, 56, 69, 83, 56, 93, 76]   |
（略）

そのユーザーの科目別の得点がまとめられていることが確認できました。

JSON_OBJECTAGG

グループの値を JSON にまとめる。

こちらは key:value の形式で出力できる。

SELECT
    implementation_date,
    subject,
    user_id,
    score,
    JSON_OBJECTAGG(user_id, score) OVER (PARTITION BY implementation_date, subject ORDER BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as scores_for_everyone
FROM scores
ORDER BY  implementation_date, subject, user_id;

結果行に、その回・科目の全員の得点を付与してみます。

+---------------------+---------+---------+-------+------------------------------------------------+
| implementation_date | subject | user_id | score | scores_for_everyone                            |
+---------------------+---------+---------+-------+------------------------------------------------+
| 2021-12-01          | sub01   |       1 |    85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78}  |
| 2021-12-01          | sub01   |       2 |    85 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78}  |
| 2021-12-01          | sub01   |       3 |    78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78}  |
| 2021-12-01          | sub01   |       4 |    93 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78}  |
| 2021-12-01          | sub01   |       5 |    78 | {"1": 85, "2": 85, "3": 78, "4": 93, "5": 78}  |
| 2021-12-01          | sub02   |       1 |    83 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76}  |
| 2021-12-01          | sub02   |       2 |    81 | {"1": 83, "2": 81, "3": 70, "4": 50, "5": 76}  |
（略）
| 2021-12-31          | sub03   |       5 |    61 | {"1": 79, "2": 96, "3": 67, "4": 100, "5": 61} |
+---------------------+---------+---------+-------+------------------------------------------------+

user_id : score でまとめられているのが確認できました。

まとめ

WINDOW 関数は「分析関数」と呼ばれているだけあって分析を行う上で知っておいて損はない便利な関数が多く、一度試しておくと選択肢の幅が広がるのでオススメです。

WINDOW 関数自体も、知ってると知らないとでは記述するクエリ量が地味に違ってくる（＝計算量も変わってくる）ので、集計関数と併せて上手く使いこなしていきたいところです。

Laravel APISpec Generatorの使い方

API OAS Laravel

Laravelのカレンダー | Advent Calendar 2021 - Qiita の3日目の記事。

前回Laravel APISpec Generatorを作った記事を公開した。

https://zenn.dev/kotamat/articles/2a63e9958e0905

ただ、この内容だけだと具体的にどういう効果がありそうかが見えないので、具体的な使用方法を元に紹介してみる。

ちなみにNuxt3とのつなぎ込みの紹介はこちらのプレゼン資料を見てもらえると良いかもしれない

https://slides.com/kotamat/nuxt3-laravel-apispec-generator

使用方法1: サーバーで使ってるマスターデータをフロントでも使う

例えば下記の様に、configの中にmasterデータを保管しているとする。

config/master
├── aa.php
└── bb.php

aa.php

<?php
return [
    'hoge' => 'hogehoge',
    'fuga' => 'fugafuga'
];

bb.php

<?php
return [
    'cat' => '猫',
    'dog' => '犬'
];

そして、MasterControllerを下記のように設定しておき

<?php

namespace App\Http\Controllers;

class MasterController extends Controller
{
    public function __invoke()
    {
        return config('master');
    }
}

下記のテストケースを書いておく。

<?php

namespace Tests\Feature\Http\Controllers;

use Tests\TestCase;

class MasterControllerTest extends TestCase
{

    public function testInvoke()
    {
        $res = $this->getJson(route('master'));
        $res->assertOk();
    }
}

実際にOASを吐き出してみる

テストを実行すると下記のようなjsonが吐き出され

jsonを開く

{
    "openapi": "3.0.0",
    "info": {
        "title": "auto generated spec",
        "version": "0.0.0"
    },
    "paths": {
        "\/api\/master": {
            "get": {
                "summary": "\/api\/master",
                "description": "\/api\/master",
                "operationId": "\/api\/master:GET",
                "security": [],
                "responses": {
                    "200": {
                        "description": "",
                        "content": {
                            "application\/json": {
                                "schema": {
                                    "type": "object",
                                    "properties": {
                                        "aa": {
                                            "type": "object",
                                            "properties": {
                                                "hoge": {
                                                    "type": "string",
                                                    "example": "hogehoge"
                                                },
                                                "fuga": {
                                                    "type": "string",
                                                    "example": "fugafuga"
                                                }
                                            },
                                            "required": [
                                                "hoge",
                                                "fuga"
                                            ]
                                        },
                                        "bb": {
                                            "type": "object",
                                            "properties": {
                                                "cat": {
                                                    "type": "string",
                                                    "example": "\u732b"
                                                },
                                                "dog": {
                                                    "type": "string",
                                                    "example": "\u72ac"
                                                }
                                            },
                                            "required": [
                                                "cat",
                                                "dog"
                                            ]
                                        }
                                    },
                                    "required": [
                                        "aa",
                                        "bb"
                                    ],
                                    "title": "\/api\/master_GET_response_200"
                                }
                            }
                        }
                    }
                },
                "parameters": [
                    {
                        "in": "header",
                        "name": "Content-Type",
                        "schema": {
                            "type": "string"
                        },
                        "description": "application\/json"
                    },
                    {
                        "in": "header",
                        "name": "Accept",
                        "schema": {
                            "type": "string"
                        },
                        "description": "application\/json"
                    }
                ]
            }
        }
    }
}

例えばtypescript-axiosで吐き出したコードを使うと

import { DefaultApi } from "~/spec";

const api = new DefaultApi()
const { data } = await api.apiMasterGET()
data.aa.fuga // :string

というような形で参照することができる。

masterデータはプロダクトが大きくなるにつれて追加されていくものであり、個別で型を使いまわしたいケースもあると思うが、下記のようにすれば小さい単位で型を取り回すことができる。

import { ApiMasterGETResponse200 } from "~/spec";
type aa = ApiMasterGETResponse200['aa']
// こんなかんじで具体型も吐き出されているのでこれを使っても良い
import { ApiMasterGETResponse200Aa } from "~/spec";
type aa2 = ApiMasterGETResponse200Aa

レスポンスステータスごとに型を使い分ける

例えば200が返るパターンと、422（バリデーション）が返ってくるパターンでは当然返却されるdataの型は変わってくる。

例えば下記の様なAPIをかんがえてみる

Controller

<?php

class JobController extends Controller
{
    public function update(UpdateRequest $request, Job $job)
    {
        $job->fill($request->safe()->all());
        return $job;
    }
}

UpdateRequest

<?php

class UpdateRequest extends FormRequest
{
    public function authorize()
    {
        return $this->user() instanceof User;
    }

    public function rules()
    {
        return [
            'name' => "required|string",
            'user_id' => "exists:" . User::class . ",id"
        ];
    }
}

今回は下記のような200, 403, 422がそれぞれ返ってくるようなテストケースをかんがえてみる

<?php

class JobControllerTest extends TestCase
{
    /**
     * @dataProvider provideUpdateParams
     */
    public function testUpdate(bool $auth, bool $hasName, int $statusCode)
    {
        /** @var Job $job */
        $job = Job::factory()->create();
        if ($auth) {
            $this->actingAs($job->user);
        }
        $params = Job::factory()->make()->toArray();
        if (!$hasName) {
            unset($params['name']);
        }
        $res = $this->putJson(route('job.update', ['job' => $job->id]), $params);
        $res->assertStatus($statusCode);
    }

    public function provideUpdateParams(): array
    {
        return [
            [
                "auth" => true,
                "name" => true,
                "expect" => 200
            ],
            [
                "auth" => false,
                "name" => true,
                "expect" => 403
            ],
            [
                "auth" => true,
                "name" => false,
                "expect" => 422
            ],
        ];

    }
}

実際にOASを吐き出してみる

今回はステータスコードごとにjsonファイルが吐き出される

storage/app/api/
└── job
    └── {job}
        ├── PUT.200.json
        ├── PUT.403.json
        └── PUT.422.json

全ファイルを php artisan apispec:aggregate マージしたものが下記

jsonを開く

{
    "openapi": "3.0.0",
    "info": {
        "title": "auto generated spec",
        "version": "0.0.0"
    },
    "paths": {
        "\/api\/job\/{job}": {
            "put": {
                "summary": "\/api\/job\/{job}",
                "description": "\/api\/job\/{job}",
                "operationId": "\/api\/job\/{job}:PUT",
                "security": [
                    {
                        "bearerAuth": []
                    }
                ],
                "responses": {
                    "200": {
                        "description": "",
                        "content": {
                            "application\/json": {
                                "schema": {
                                    "type": "object",
                                    "properties": {
                                        "id": {
                                            "type": "integer",
                                            "example": 22
                                        },
                                        "name": {
                                            "type": "string",
                                            "example": "Ms. Ana Rosenbaum"
                                        },
                                        "created_at": {
                                            "type": "string",
                                            "example": "2021-11-29T11:19:09.000000Z"
                                        },
                                        "updated_at": {
                                            "type": "string",
                                            "example": "2021-11-29T11:19:09.000000Z"
                                        },
                                        "user_id": {
                                            "type": "integer",
                                            "example": 47
                                        }
                                    },
                                    "required": [
                                        "id",
                                        "name",
                                        "created_at",
                                        "updated_at",
                                        "user_id"
                                    ],
                                    "title": "\/api\/job\/{job}_PUT_response_200"
                                }
                            }
                        }
                    },
                    "403": {
                        "description": "",
                        "content": {
                            "application\/json": {
                                "schema": {
                                    "type": "object",
                                    "properties": {
                                        "message": {
                                            "type": "string",
                                            "example": "This action is unauthorized."
                                        },
                                        "exception": {
                                            "type": "string",
                                            "example": "Symfony\\Component\\HttpKernel\\Exception\\AccessDeniedHttpException"
                                        },
                                        "file": {
                                            "type": "string",
                                            "example": "\/var\/www\/html\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Exceptions\/Handler.php"
                                        },
                                        "line": {
                                            "type": "integer",
                                            "example": 387
                                        },
                                        "trace": {
                                            "type": "array",
                                            "items": {
                                                "type": "object",
                                                "properties": {
                                                    "file": {
                                                        "type": "string",
                                                        "example": "\/var\/www\/html\/vendor\/laravel\/framework\/src\/Illuminate\/Foundation\/Exceptions\/Handler.php"
                                                    },
                                                    "line": {
                                                        "type": "integer",
                                                        "example": 332
                                                    },
                                                    "function": {
                                                        "type": "string",
                                                        "example": "prepareException"
                                                    },
                                                    "class": {
                                                        "type": "string",
                                                        "example": "Illuminate\\Foundation\\Exceptions\\Handler"
                                                    },
                                                    "type": {
                                                        "type": "string",
                                                        "example": "->"
                                                    }
                                                },
                                                "required": [
                                                    "file",
                                                    "line",
                                                    "function",
                                                    "class",
                                                    "type"
                                                ]
                                            }
                                        }
                                    },
                                    "required": [
                                        "message",
                                        "exception",
                                        "file",
                                        "line",
                                        "trace"
                                    ],
                                    "title": "\/api\/job\/{job}_PUT_response_403"
                                }
                            }
                        }
                    },
                    "422": {
                        "description": "",
                        "content": {
                            "application\/json": {
                                "schema": {
                                    "type": "object",
                                    "properties": {
                                        "message": {
                                            "type": "string",
                                            "example": "The given data was invalid."
                                        },
                                        "errors": {
                                            "type": "object",
                                            "properties": {
                                                "name": {
                                                    "type": "array",
                                                    "items": {
                                                        "type": "string",
                                                        "example": "The name field is required."
                                                    }
                                                }
                                            },
                                            "required": [
                                                "name"
                                            ]
                                        }
                                    },
                                    "required": [
                                        "message",
                                        "errors"
                                    ],
                                    "title": "\/api\/job\/{job}_PUT_response_422"
                                }
                            }
                        }
                    }
                },
                "parameters": [
                    {
                        "in": "header",
                        "name": "Content-Type",
                        "schema": {
                            "type": "string"
                        },
                        "description": "application\/json"
                    },
                    {
                        "in": "header",
                        "name": "Accept",
                        "schema": {
                            "type": "string"
                        },
                        "description": "application\/json"
                    },
                    {
                        "in": "path",
                        "name": "job",
                        "required": true,
                        "schema": {
                            "type": "integer"
                        },
                        "description": "22"
                    }
                ],
                "requestBody": {
                    "content": {
                        "application\/json": {
                            "schema": {
                                "type": "object",
                                "properties": {
                                    "name": {
                                        "type": "string",
                                        "example": "Ms. Ana Rosenbaum"
                                    },
                                    "user_id": {
                                        "type": "integer",
                                        "example": 47
                                    }
                                },
                                "required": [
                                    "name",
                                    "user_id"
                                ],
                                "title": "\/api\/job\/{job}_PUT_request"
                            }
                        }
                    }
                }
            }
        }
    },
    "components": {
        "securitySchemes": {
            "bearerAuth": {
                "type": "http",
                "scheme": "bearer",
                "bearerFormat": "JWT"
            }
        }
    }
}

で、これをどう使うのかというと

import { ApiJobJobPUTRequest, ApiJobJobPUTResponse403, ApiJobJobPUTResponse422, DefaultApi } from "~~/spec"

export default async () => {
    const api = new DefaultApi
    const param: ApiJobJobPUTRequest = {
        name: "user name",
        user_id: 1
    }
    const { data, status } = await api.apiJobJobPUT({
        job: 1,
        apiJobJobPUTRequest: param
    })
    switch (status) {
        case 403:
            return { data: data as any as ApiJobJobPUTResponse403, status }
        case 422:
            return { data: data as any as ApiJobJobPUTResponse422, status }
        default:
            return { data: data, status: status as 200 }
    }
}

上記のように、リクエストパラメータに ApiJobJobPUTRequest 型を付けて送るのは通常パターンではあるが、その返却されたステータスコードを元に switch 文で型を詰め直して返却している。 この関数の返り値は下記のようになり、ステータスコードと中のデータが合わさったunion型のPromiseが返る

() => Promise<{
    data: ApiJobJobPUTResponse403;
    status: 403;
} | {
    data: ApiJobJobPUTResponse422;
    status: 422;
} | {
    data: ApiJobJobPUTResponse200;
    status: 200;
}>

この返り値は下記のようにif文で分岐させることによってほしいデータの型を得ることができる

const res = await fn()

if (res.status === 200) {
    // ApiJobJobPUTResponse200
    res.data.name
}
if (res.status === 422) {
    // ApiJobJobPUTResponse422
    res.data.message
}
if (res.status === 403) {
    // ApiJobJobPUTResponse403
    res.data.trace
}

ちなみにVue3 script setupでは下記のように呼び出すと v-if での分岐で参照データの切り分けができるようになる。(useFetchはNuxt3の関数)

<template>
    <div>
        <div v-if="data.status === 200">{{ data.data.name }}</div>
        <div v-if="data.status === 422">{{ data.data.message }}</div>
        <div v-if="data.status === 403">{{ data.data.trace }}</div>
    </div>
</template>
<script setup lang="ts">
const { data } = await useFetch("/api/job/update")

</script>

こうすることでフロントエンドでステータスコードごとに型安全な表示の切り替えを行うことができるようになる

まとめ

今回は利用ケースとして有り得そうな2つのケースをベースに紹介させてもらった。 もしかしたらこういう使い方もできるかも？というのがあればぜひコメントとかいただけると嬉しいです。

backcheck を TypeScript に移行するまでの流れ

はじめに

皆さん、こんにちは。株式会社ROXX、backcheck開発チームの山口と申します。 backcheckフロントエンドのTypeScript移行がある程度軌道にのってきたので、ここまでの過程を文書化することにしました。

この記事ではTSの導入までの過程についてかいつまんでお話ししようと思います。

やっていること

Nuxt2系(JavaScript)で書かれたbackcheckのフロントエンドをTypeScriptへマイグレーションしています（2021/11時点で現在進行中）。 IEのサポートの終了+Nuxt3がStableになるタイミングで、Nuxt3+Composition APIへの乗り換えを予定していることから、vue-class-componentやvue-property-decoratorは導入せず、Options APIを使用したまま、TypeScriptのみを導入することとしました。

TypeScript 移行で目指すゴール

「完全 TypeScript 化ではなく、最速で8割 TypeScript 化を目指す」

これをスローガンとして、まずは、普段の開発作業で触る箇所に対して TypeScript でかける状態をなるべく早く用意することをゴールとして考えました。

なぜ TypeScript に書き換えるのか

主には以下を目的としてTypeScriptを導入することにしました。

  • 静的解析により、型安全に開発できる
    • 型がドキュメントがわりになる
    • 早い段階でエラーに気づける

TS移行完了までに相応のコストがかかりますが、移行が完了していなくても部分的に恩恵を受けられることや、長期的にみてコードの品質や開発速度が向上することが移行への後押しになりました。

メンバーのTSキャッチアップ

弊チームでは、実務でTSを書いたことがあるメンバーが12人中７人でした。 このままだと残った5人がフロントのコードが書けなくなる + 実務で使ったことのあるメンバーでも理解度がまちまちであったため、慣れるまでは移行作業を4人参加のモブプロで進めることでキャッチアップすることとしました。

また、モブプロ以外の施策として、TS移行のキックオフ前に、TS未経験者に向けたTypeScriptワークショップを行いました。これにより、モブプロ開始のタイミングで最低限の基本知識は全員が聞いたことがある状態とすることができました。 （TypeScript説明会ではTypeScript Deep Diveをベースに、概要や基本的な機能などについて解説を行いました）

TS環境構築

TypeScript、eslintの設定

キックオフの時点から"strict": trueの状態としています。 vueファイルでmixinsを呼び出している箇所など、型の適用が難しい箇所については、将来的にリファクタリングすることとして、マイグレーションのタイミングではts-ignoreすることでエラーを回避するようにしました。

また、TSにマイグレーションする上で、リファクタリングしたくなるコードは、影響範囲が大きくなってしまうので別でタスクを用意し、このプロジェクトではなるべくリファクタリングをしないように決めました。

eslintの設定は、TSマイグレーション未対応のjsやvueファイルでもエラーが出てしまうような項目については、overrideして設定をOFFにしています。

サンプル実装の作成

移行作業の着手前に、1ファイルだけTS化したファイルを用意することで、他ファイルを移行する際の判例としました。実装の例を用意したことで、実装イメージがチームの共通認識としてもてたので、よかったと思います。

TSに移行したファイルのリグレッションテスト

該当画面の挙動にデグレがないかを確認するための、ブラックボックスなテストをQAとして行うこととしました。 その他に、utilsの関数や共通コンポーネントなど、全体影響があるものに関しては、正常系フローの動作確認を行うテストを別途実施することで、デグレが起きていないことを確認することにしました。

移行計画の作成

内容としては開発フローへの乗せ方、作業の進め方の2つを事前に決めました。

開発フローへの乗せ方

backcheck のフロントエンドは JS, vue ファイルあわせて約550ファイル・5万行のコードがあります。 これを80%TSに移行するための超概算で以下の数字がでました。

たとえば... 1スプリントあたりのベロシティの20%を移行作業にわりあてたとすると → 33週かかる 1年は約52週 つまり... 完了までに7ヶ月くらいかかります。

なかなかかかりますね...

TSの移行作業の割合を、1スプリントあたりのベロシティの20%以下に落としてしまうと完了までに年単位でかかってしまうため、プランニングする量としては1スプリントあたりのベロシティの20%を固定枠で設けて、メインのストーリータスクと並行して進めていくというやり方にしました。

移行作業の進め方

当初の計画では、こちらのポッドキャストで説明していた進め方を参考に、影響範囲の少ないところから着手していく計画でした。

api→utils→middleware→vueファイルの順に、細かくマイグレーションしていくことで、依存ファイルの多いvueファイルに着手するタイミングには、依存ファイルが全てTSに置き換わっているイメージです。

しかし、普段の開発の中で触る箇所はある程度絞られています。そのため、TypeScriptマイグレーション専任の担当者を設けずに進めている弊チームでは、影響範囲の少ない箇所からマイグレーションをしても日頃の開発フローの中で恩恵を受けられるまでに相応の時間がかかることに気がつきました。

そのため、普段の開発で触る頻度の高い箇所で、よりはやく恩恵をうけられるように、画面（pagesディレクトリ）単位でチケットを立て、画面に依存しているファイルは全てそのチケットの中でマイグレーションする方針に変更しました。 また、constantsで定義していた定数に関しては事前に一括でTS化を行ってしまいました。

おわりに

実際に移行作業をスタートすると、キックオフ時点に考慮が漏れていて後から決定した内容などもあったりしました。導入準備だけでも3ヶ月ほどかかったので、TS移行の計画を立てる場合はある程度長い目で見ながら進めるのがいいかと思います。

その他、ここはどう進めたの？この設定はどうした？こうした方がよさそう。などご意見、ご質問がありましたらぜひお声がけください。

また、現時点でチーム全員がTSでの実装イメージが持てている状態までTSのキャッチアップが進んだので、今後はモブプロをやめて移行作業の速度アップを考えています。 その辺についてもお時間のある時に記事にしようと思うのでお楽しみに。

MinIO を使ってローカルでの開発環境の外部ストレージを Amazon S3 からローカルのコンテナへ置き換える

この記事は個人ブログと同じ内容です

www.ritolab.com

ローカルでのアプリケーション開発は、できるだけ外部のサービスに依存させたくないものです。

API が提供されている外部サービスであればモックすれば良いですし、ストレージに関しても、開発時はローカルにファイルを設置するようにして、確認環境以降は外部ストレージに置くようにする事は可能です。

一方で、AWS SDK を使ってストレージ操作をしてるとそのソースコードがきちんと動くものなのか、動作確認がローカルで出来ない状態にはなります。（ローカルでのファイル操作には AWS SDK を用いないため）

今回はそれらを払拭するために、MinIO を使って、ローカルでの開発環境の外部ストレージを S3 から MinIO に置き換えてみます。

MinIO

MinIO は、Amazon S3 互換のオブジェクトストレージサーバーです。

min.io

MinIO は S3 と互換性を持つので、AWS SDK を通じてアクセス・操作が可能です。

そして MinIO には、Docker Image が公式から提供されています。

hub.docker.com

つまり、ローカル環境の外部ストレージを「S3（インターネット上のサービス）」から「MinIO のコンテナ」に置き換えることができれば、インターネットへの通信がなくせて（外部サービスに依存せず）、閉じた開発が可能になります。

開発環境

MinIO を導入する環境として、docker compose で作成されたローカルでのコンテナ環境を想定します。

docker-compose.yml

MinIO のコンテナを定義します。

minio:
    image: 'minio/minio'
    container_name: minio
    environment:
        MINIO_ROOT_USER: minioadminuser
        MINIO_ROOT_PASSWORD: minioadminpassword
    entrypoint: bash
    command: -c "/opt/bin/minio server /export --address :9999 --console-address :9001"
    volumes:
        - ./docker/minio/data:/export
    ports:
        - '9000:9999'
        - '9001:9001'

command で MinIO の起動を行なっていますが、ポイントが 2 点あります。

  • --address :9999 で、MinIO の API のポートを指定しています。API のデフォルトポートは 9000 ですが、ポート番号を変更する必要がなければここを記載する必要はありません。
  • --console-address :9001 でコンソールの ポートを指定しています。コンソール（GUI）の IP に関しては起動時に自動的に決定されます。docker compose で動かす場合はコンソールの IP をマッピングしてあげる必要があるため IP を指定して固定にするようにしています。

volumes では MinIo ストレージの永続化を行なっているのですが、この場合だと data ディレクトリ配下に切ったディレクトリがそのままバケットや階層になるので永続化しておくとかなり使いやすかったです。

イメージはこんな感じ。（ホスト側に docker/minio/data というコンテナ用のディレクトリを作ってその配下をつなげた場合）

docker
└─ minio
    └─ data
        ├─ my-bucket-1
        │   └─ sub-1
        │       └─ sample.png
        ├─ my-bucket-2
        │   └─ sample.png
        └── my-bucket-3

my-bucket-(n) はディレクトリですが、MinIO のコンソールを開くとそのままバケットとして認識されます。（起動後に追加しても認識されるのでバケットや階層の追加はかなり簡単）

ports では API と コンソール（GUI）のポートをマッピングしています。

これらの設定で起動させると、MinIO のストレージが動作します。

コンソールへは、localhost:9001 でアクセスします。

f:id:ro9rito:20211110083724p:plainf:id:ro9rito:20211110083721p:plainf:id:ro9rito:20211110083719p:plain

Laravel Flysystem での設定

環境は用意できましたが、Laravel の Flysystem を用いてストレージ操作を行なっている場合の設定のポイントも残します。

確認すべきは 3 点です。設定ファイルに url と endpoint そして use_path_style_endpoint の設定項目があるかを確認して、なければ追加します。

config/filesystems.php

's3' => [
    'driver' => 's3',
    'key' => env('AWS_ACCESS_KEY_ID'),
    'secret' => env('AWS_SECRET_ACCESS_KEY'),
    'region' => env('AWS_DEFAULT_REGION'),
    'bucket' => env('AWS_BUCKET'),
    'url' => env('AWS_URL'), // <- これがあるか確認
    'endpoint' => env('AWS_ENDPOINT'), // <- これがあるか確認
    'use_path_style_endpoint' => env('AWS_USE_PATH_STYLE_ENDPOINT', false), // <- これがあるか確認
],

最近のバージョンならありますが、バージョンアップしながら使い続けている場合だとこの辺のプロパティは手動で追加しない限りは付いていない場合もあるので、もしなければつけます。

あとは、ローカルの .env の項目です。

.env

AWS_ACCESS_KEY_ID=minioadminuser
AWS_SECRET_ACCESS_KEY=minioadminpassword
AWS_DEFAULT_REGION=ap-northeast-1
AWS_BUCKET=YOUR_BUCKET_NAME　
AWS_USE_PATH_STYLE_ENDPOINT=true
AWS_ENDPOINT=http://minio:9999
AWS_URL=http://localhost:9000
  • AWS_USE_PATH_STYLE_ENDPOINT は ture で設定します。
  • AWS_ENDPOINT は、同一ネットワーク内からの MinIO コンテナへのアクセス URL を指定します。（例えばバックエンドからの取得はこっち）
  • AWS_URL は、外部からリソースとして参照する（例えばブラウザからの参照）場合の URL を指定します。

これらを設定すると、MinIO ストレージでの操作ができるようになります。

署名付き URL を取得する際のつまづき

実際に操作してみて 1 点躓いたポイントがありました。

temporaryUrl() メソッドを用いて署名付き URL を取得すると、出力される URL は http://minio:9000/〜 となるのですが、この URL は同一ネットワークからのアクセス用の URL なので、例えば HTML で img タグに指定する場合は http://localhost:9000/〜 の URL を使いたい。となります。

そこで Filesystem のソースを掘っていくと、「temporary_url っていう設定値を持っていたらベースの URL を置き換えるよ」という記述があり、これは助かると思って設定してみたのですが、確かにベースの URL は変更されましたが、その URL では SignatureDoesNotMatch となってしまい使えませんでした。

ソースを見てみると、署名を付加されたものに対して単純にベースの URL を置換している（今回でいえば minio:9000 を localhost:9000 に置換）だけなので、署名を再生成しているわけでもなくただの URL 改ざん状態になっているため機能しない状態でした。

最終的には署名付き URL 取得用の disk 設定を config 側に 1 つ追加して事なきを得ましたが、これはこれで冗長な気がしないでもないので、docker compose だからこそ出てくる差分ではあるもののもっと上手く設定で吸収できればやりたいところでした。

まとめ

ローカルにベタで保存でも問題ないとは思いつつ、それでも環境によって切り替える手間が省ける、AWS SDK を用いたファイル操作の動作確認が行えるという点ではローカル環境のストレージを S3 から MinIO に置き換えるのは一定アリかなとは思いました。

取得や保存、削除といった操作に関しては問題なく、既存の実装をほぼ変更する事なく S3 から MinIO への切り替えは行えました。（署名付き URL の取得は確認が必要）

何よりローカルの開発環境がインターネット上にあるサービスに依存しなくなるのはうれしいですね。

Laravelに1行足すだけでOpenAPIを吐き出せるものを作った

API Laravel OAS

この記事は個人ブログと同じ内容です。

kotamat.com

SPA + API構成でLaravelを使っているところも多いかと思いますが、双方の通信のインターフェースを担保するのに皆さんはどうしているでしょうか。

REST APIのスキーマ定義としてOpen APIが現在主流かとは思いますが、スキーマ定義言語そのものが最初に作ったら終わりというものではなく、サービスが続く限りメンテナンスをし続けなければならないものであるため、特にサービスロンチ前のプロダクトでは得られる恩恵よりもコストが上回ってしまうために嫌煙されてしまうこともあるかと思います。

一方でOpenAPIを書いておくと、インターフェースから型定義を生成できるため、通常開発以上のコストを払わなくて済むのであれば、ぜひとも導入しておきたいものかと思います。

今回は過去に作った kotamat/laravel-apispec-generator を改修し、1行書くだけでOASのjsonファイルを吐き出せるものを作ってみたので紹介します。

https://github.com/kotamat/laravel-apispec-generator

ざっくりとした仕様

LaravelにはPHPUnitを拡張したテストがあり、特に$this->json()ないしはそれの拡張メソッドを使うことによってエンドポイントに対するリクエスト、レスポンスのテストを容易に書くことができます。

今回作ったライブラリは、このテストを通している全エンドポイントに対して、OASを吐き出せるものとなっています。

使い方

個々のテストケースごとにOASを吐き出す

当該パッケージをインストールし

composer require --dev kotamat/laravel-apispec-generator

対象としたいクラスにて、下記のようにuseを差し込むだけです。

class SomeTestCase extends TestCase
{
+    use ApiSpec\ApiSpecOutput\ApiSpecOutput;
    //...
}

その後、テストを実行すると storage/app 配下に各エンドポイントのOAS定義ファイルがjson形式で出力されます。 (例えば http://example.com/api/hoge/fuga へのGETリクエストで200が帰ってくるテストであれば、storage/app/api/hoge/fuga/GET.200.json が書き出されます )

全エンドポイントに集約したOASを吐き出す

吐き出されるものはあくまでそのテストで使用したエンドポイントのOASのみです。OASを使いたいケースは全エンドポイントに対してのOASがほしい事が多いと思うので、今回はartisanコマンドにて、一つのjsonファイルに集約する処理も追加しました。

集約する場合は

php artisan apispec:aggregate

を実行すると、上記で生成したjsonファイルを読み込み storage/app/all.json にOASを吐き出します。

集約する際は、リクエストURI、HTTPメソッド、レスポンスのステータスコードごとにユニークなものを集計します。もし同じリクエストURI、HTTPメソッド、レスポンスのステータスコードのテストがある場合は、あとから実行されたもので上書きされます。

(今後は anyOf とかを使って複数のリクエストボディ、レスポンスボディで使えるようにしたいとは思っているが、codegen側で不具合があり、適切な型定義が吐き出せなかった。)

生成されたOASはどうする？

jsonの中身をSwaggerEditorにコピペしてみるのもいいし、下記コマンドをLaravelのルートディレクトリで実行してみてTypeScriptの型定義をだしてみるのもいいかなと思います。

target=storage/app/all.json

docker run --rm -v "${PWD}:/local" openapitools/openapi-generator-cli generate -i /local/${target} -g typescript-fetch -o /local/dist

まとめとか

今回はLaravelのフィーチャーテストからOASを吐き出すパッケージに関して紹介させてもらいました。 このパッケージを使えば追加メンテ工数ほぼゼロでOASの運用ができるようになるかと思います。興味あればぜひ使ってみてください。

正直8時間くらいで作ったものというのもあり、現状まだ全仕様を網羅しているわけではないため、もしかしたらエラー出ちゃうところもあるかもしれないです。修正したほうがいいところあれば気軽にPR出してもらえると嬉しいです。

エンジニア３年目の僕がスクラムマスターはじめました。

この記事は個人ブログと同じ内容です

エンジニア３年目の僕がスクラムマスターはじめました。

こんにちは YamaguchiSota です。

まず対象読者と、この記事で書くことをご紹介します。

対象読者：

  • これからスクラムマスターになる人・なりたい人
  • スクラムマスターってどんなことやるの？な人

この記事で書くこと：

自己紹介

山口 壮太(YamaguchiSota) 

1993年生まれ。 茨城県 水戸市 在住。 高卒→工場勤務→SES（Coder）→受託開発ベンチャー→株式会社 ROXX（現在 在籍中）

2021年4月から株式会社 ROXX で backcheck というリファレンスチェックサービスのエンジニアとして従事していますが、この度10月から開発エンジニアおよびスクラムマスターを兼任することになりました。

なぜスクラムマスターになったのか

なぜ、僕がスクラムマスターになったのか。

チームの背景として、元々スクラムマスターをやっていた方が PM に転向する上で、スクラムマスターと PM の兼任は難しいということで新スクラムマスターを探すことになりました。

とはいえ、前任のスクラムマスター達はポジションは変えどチームには在籍しているということ。チームとしてアジャイル関連の書籍の輪読会を行ったり学習の機会を持つことで、ある程度メンバーの自己管理が成り立っていること。から、スクラムマスター未経験のメンバーをアサインしてスクラムマスターとして実践を通して育てるくらいの余裕はある状態だったと思います。

そんな中で主に２つの要因から「You, スクラムマスターやっちゃいなよ！」とお声がけいただくことになります。

１つは、最近ジョインしてきたメンバーと古参メンバーの中間くらいのタイミングで僕がこのチームにジョインしたため、どちらの立ち位置の人の気持ちもわかりそうという期待から。

もう１つは、チームの中で行ったドラッガー風エクササイズの期待値からメンバーのサポート的な役割を期待されていたことからです。

ドラッガー風エクササイズとは？

以下、ドラッガー風エクササイズの成果物

ドラッガー風エクササイズの成果物

とはいえなんとなくのイメージでスクラムマスターとはファシリテーターやったり、スクラムの進行が捗るようにする人だ！くらいの認識だったので具体的に何をするか分からずだったので調べて自分なりに解釈した内容を簡単にまとめます。

そもそもスクラムとは？

スプリントと呼ばれる毎回固定された時間軸を繰り返していく中で透明性、検査、適用を徹底することで改善し、チームの安定した生産性を基盤とした生産性の向上を促進していくフレームワークです。

個人的には透明性、検査、適用はそれぞれ以下のように解釈しています。

  • 透明性：メンバーが持っている情報・作業の意思決定などをそれぞれのメンバー間でサイロ化せず、チームの共通認識とするように努めること

  • 検査：スプリント計画、スプリント期間中の日々の進捗や、作ったもの、スプリントのやり方など様々な振り返り

  • 適用：振り返りの内容に応じて出てきた課題に対して解決策の反映

詳細については公式の Scrum Guide 2020 にてご確認ください。

スクラムマスターとは？

スクラムチームの内訳はスクラムマスター1 ⼈、プロダクトオーナー1 ⼈、複数⼈の開発者で構成されます。

ではスクラムマスターってどんなことをする役割なのでしょうか？

スクラムマスターの定義については Scrum Guide 2020 にのっているのでこの記事ではお話しません。

個人的な解釈としては、抽象的ですがチームの模範行動をとる人であり、メンバーが行き詰まった時に解決・回避策の案を提示して背中を押してあげられるリーダーの枠割と捉えています。

それぞれ実践するためには以下のことが必要と考えます。

  • スクラムメンバーとしての模範行動を行うためには、アジャイルを理解する必要がある
  • コーチングを実践するためには、たくさんの知識の引き出しをもつ必要がある

これからスクラムマスターとして何をするのか？

引き継ぎの際に前任のスクラムマスターの方から「スクラムガイドに載っていることだけやればいいんだよ。」とアドバイスをいただきました。

とはいえ、スクラムガイドには具体的な方法は載っていないため、まずは自分なりに以下を意識して日々行動を起こしていけたらと考えています。

やらないこと

  • チームの管理、統率

やること

特にチームの模範行動については意識することで今から実践できることなので今日の業務から徹底できるよう心がけます。

Scrum Master Way を歩き出した新米スクラムマスターとして、より多くチームに貢献できるように精進していきたいと思います。

Nuxt3のuseFetchの型定義を探索してみたら結構面白かった話

Nuxt.js Vue.js TypeScript

この記事は個人ブログと同じ内容です

kotamat.com

先日10/12にNuxt3がpublic betaになりました！🎉 Nuxt2から抜本的に変更されたNuxt3では面白い変更点が多いのですが、今回はuseFetchの挙動に関して探索してみようと思います。

useFetchとは？

Data Fetchingにて紹介されている非同期データ取得APIのうちの一つ。

useFetch(url: string, options?)

というような形で呼び出す、非常にシンプルなAPIではあるのですが、useAsyncData や $fetch のラッパーであったり、自動生成されたローカルAPIのレスポンスの型を提供するということで、型定義はかなり複雑な形になっています。

まずは型定義から

useFetchの型定義は下記のようになっています。

export declare function useFetch<ReqT extends string = string, ResT = FetchResult<ReqT>, Transform extends (res: ResT) => any = (res: ResT) => ResT, PickKeys extends KeyOfRes<Transform> = KeyOfRes<Transform>>(url: ReqT, opts?: UseFetchOptions<ResT, Transform, PickKeys>): import("./asyncData").AsyncData<import("./asyncData").PickFrom<ReturnType<Transform>, PickKeys>>;

型パズル感すごいですね… ここからは一つずつ紐解いていくことにします

Transform剥がし

TransformはuseAsyncDataのオプションの一つである、返り値の変換器であるため、一旦削ってシンプルにしてみます。

export declare function _useFetch<ReqT extends string = string, ResT = FetchResult<ReqT>, PickKeys = KeysOf<ResT>>(url: ReqT, opts?: UseFetchOptions<ResT, (input: ResT)=> ResT, PickKeys>): import("./asyncData").AsyncData<import("./asyncData").PickFrom<ResT, PickKeys>>;

※この状況だとUseFetchOptionsの第3型引数がエラーになってしまいますが、型の抽象度を意図的に変えてしまったのが原因なので一旦無視します

返り値に注目

ここで返り値に注目してみます。

asyncData.d.tsでは下記のように定義されているため

// Tのうち、Kの配列の要素に指定されたkeyの要素だけを抽出して取り出す
export declare type PickFrom<T, K extends Array<string>> = T extends Record<string, any> ? Pick<T, K[number]> : T;
...
// Dataの型を、asyncDataが返してくれる型に変換する
export interface _AsyncData<DataT> {
    data: Ref<DataT>;
    pending: Ref<boolean>;
    refresh: (force?: boolean) => Promise<void>;
    error?: any;
}
export declare type AsyncData<Data> = _AsyncData<Data> & Promise<_AsyncData<Data>>;

返り値は「ResT から PickKeysで指定した要素だけをとりだし、asyncDataが返してくれる形に変換した型」となります。

ResTの探索

では ResT も探索してみます。

ResT = FetchResult<ReqT>;
export declare type Awaited<T> = T extends Promise<infer U> ? U : T;
export declare type FetchResult<ReqT extends string> = Awaited<ReturnType<$Fetch<unknown, ReqT>>>;

となっているため、「ResTは$Fetch<unknown, ReqT>の返り値のPromiseを剥がしたもの、もしくはそれ自体」となります。

$Fetchの探索

$Fetchを見てみると下記の様になっています。

export declare interface $Fetch<T = unknown, R extends FetchRequest = FetchRequest> {
  (request: R, opts?: FetchOptions): Promise<TypedInternalResponse<R, T>>
  raw (request: R, opts?: FetchOptions): Promise<FetchResponse<TypedInternalResponse<R, T>>>
}

ResTを当てはめResTの算出に必要なものだけを残すと

export declare interface $Fetch<unknown, ReqT> {
  (request: R, opts?: FetchOptions): Promise<TypedInternalResponse<ReqT, unknown>>
}

となります。

TypedInternalResponseを見ていくと下記になっているので

export declare type TypedInternalResponse<Route, Default> =
  Default extends string | boolean | number | null | void | object
    // Allow user overrides
    ? Default
    : Route extends string
      ? MiddlewareOf<Route> extends never
        // Bail if only types are Error or void (for example, from middleware)
        ? Default
        : MiddlewareOf<Route>
      : Default

今回の型で置き換えると

export declare type TypedInternalResponse<ReqT, unknown> =
    ReqT extends string
      ? MiddlewareOf<ReqT> extends never
        // Bail if only types are Error or void (for example, from middleware)
        ? unknown
        : MiddlewareOf<ReqT>
      : unknown

となります。neverを一旦無視するとMiddlewareOf<ReqT>が返ってくるといえそうです。

残りの型定義は下記となるので

export declare interface InternalApi { }

export declare type ValueOf<C> = C extends Record<any, any> ? C[keyof C] : never

export declare type MatchedRoutes<Route extends string> = ValueOf<{
  // exact match, prefix match or root middleware
  [key in keyof InternalApi]: Route extends key | `${key}/${string}` | '/' ? key : never
}>

export declare type MiddlewareOf<Route extends string> = Exclude<InternalApi[MatchedRoutes<Route>], Error | void>

MiddlewareOf<ReqT>に当てはめて考えてみると

export declare interface InternalApi { }

export declare type ValueOf<C> = C extends Record<any, any> ? C[keyof C] : never

export declare type MatchedRoutes = ValueOf<{
  // exact match, prefix match or root middleware
  [key in keyof InternalApi]: ReqT extends key | `${key}/${string}` | '/' ? key : never
}>

export declare type MiddlewareOf = Exclude<InternalApi[MatchedRoutes<ReqT>], Error | void>

となります。ざっくりいうと、「InternalApiの中にReqTがキーとなる物があればそれのValueを返却する」というふうに解釈できますね。

つまり「ResTはInternalApiのキーがReqTに相当するもののValueの型」と推察できます。

InternalApiの自動生成

上記型定義だとInternalApiはデフォルトで {}です。つまりこのままではなんの意味もないものになります。 この型定義をoverwriteしてくれるのがnitroというNuxt3のサーバーエンジンです。

nitroは色々な機能があるのですが、そのうちの一つが /server/ディレクトリに配置した関数の返り値を解釈し、型定義を生成してくれるというものです。

例えば/server/api/count.tsという下記のTSファイルを設置してみます。

let counter = 0;
export default (): { counter: number } => {
  counter++;
  return { counter };
};

すると.nuxt/nitro.d.tsという、下記の内容のファイルが生成されます。

declare module '@nuxt/nitro' {
  interface InternalApi {
    '/api/count': ReturnType<typeof import('../server/api/count').default>
  }
}
export {}

InternalApiが拡張され、/api/countに対する型定義が出現しました。 これにより、「ResTはInternalApiの中にあるReqTに相当するもののValueの型」は 「ResTはReqTが/api/countのとき/server/api/countの返り値」となることができました。

useFetchの返り値

ここでuseFetchを実際に使ってみてこの効果を探ってみます。

const {data} = await useFetch('/api/count')

としてみたとき、dataの型はシンプルに

Ref<Pick<{
    counter: number;
}, "counter">>

となります。 useFetchでは単にエンドポイントを指定しているだけに過ぎないのですが、その返り値がVue3で用いやすい型として抽出されている事がわかります。 \ InternalApiに型定義がなくても使えるような設計になっているため、useFetchの引数に対しての補完が効かないのが難点ではありますが、Nuxtのディレクトリ構造を探索すればファイル名だけである程度予想はできるので、便利に使えそうです。

とりあえず一旦まとめ

useFetchの返り値を探索するだけで結構長くなったので、一旦このへんで終わりにします。 \ useFetchはこれ以外にもuseAsyncDataや$fetchのオプションもサポートしているので、よかったら探索してみてください。オプションのサポートは上記に比べると大したことはないので、気構えずに見れると思います