【MySQL】グループごとの上位N件ずつを取得

Window 関数を使う方法

MySQL 8.0.2 で導入された Window 関数をまず使ってみます。

参考にしたのは次のページです。 「ROW_NUMBER() - Oracle, SQL Server and PostgreSQL」と書かれているので MySQL 以外でも共通で使えるはずです。

www.sqlines.com

SELECT city, country, population
FROM
 (SELECT city, country, population, 
               ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
   FROM cities) ranked
WHERE country_rank <= 2;

欧米の都市人口を使って例示されていましたが、少しわかりにくく感じたので、クラスごとのテスト点数上位2名ずつを取得する例にしました。
正規化していないのは例をシンプルにするためなので、ここでは無視してください。

CREATE TABLE students (
  id int,
  class varchar(8),
  name varchar(16),
  score int
);

INSERT INTO students VALUES
(1, 'A組', '佐藤', 95),
(2, 'A組', '鈴木', 87),
(3, 'A組', '高橋', 80),
(4, 'B組', '田中', 100),
(5, 'B組', '渡辺', 72),
(6, 'B組', '伊藤', 53);

上記の参考ページを真似すると、クエリは次のようになります。

SELECT class, name, score
FROM (
    SELECT
        class, name, score,
        ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) as class_rank
    FROM students
) AS ranked
WHERE class_rank <= 2;

class	name	score
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

取得結果は4件になりますが、テーブルに存在する全6件を走査していると思われ、パフォーマンスは悪そうです。 プライマリキーやインデックスを設定したら全然違う結果になるかもしれませんが未確認です。 使えそうかどうか実行計画を確認しながら進めると良いと思います。

Window 関数を使わない方法（異常あり版）

MySQL 8 より古いバージョンを使うことがまだよくあります。 Window 関数を使わない方法も見ておきます。

先ほどの参考ページに載っているもの:
MySQL Query to Get Top 2

SELECT city, country, population
FROM
  (SELECT city, country, population, 
               @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
               @current_country := country 
    FROM cities
    ORDER BY country, population DESC
  ) ranked
WHERE country_rank <= 2;

クラスの例に変えつつ少しだけ改善したもの：

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @current_class, @class_rank + 1, 1) AS class_rank,
        @current_class := class,
        class, name, score
    FROM students
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

初回実行

class	name	score
A組	佐藤	95
A組	鈴木	87
A組	高橋	80
B組	田中	100
B組	渡辺	72
B組	伊藤	53

あれ？ 全件取得してしまいました…。

2回目以降の実行

初回以外の実行では期待通りの結果になりました。 実行計画も Window 関数を使ったときと同じです。

class	name	score
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

初回と2回目以降の違い

最初の実行で何が起こっているのか見るために、サブクエリ部分だけを実行してみました。

SELECT
    @class_rank := IF(class = @current_class, @class_rank + 1, 1) AS class_rank,
    @current_class := class AS curr_class,
    class, name, score
FROM students
ORDER BY class, score DESC;

初回

class_rank	curr_class	class	name	score
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

2回目以降

class_rank	curr_class	class	name	score
1	A組	A組	佐藤	95
2	A組	A組	鈴木	87
3	A組	A組	高橋	80
1	B組	B組	田中	100
2	B組	B組	渡辺	72
3	B組	B組	伊藤	53

2回目は class_rank にクラスごとの順位が入っています。 一方、1回目は 1 ばかりになってしまっています。

もう一つの問題

レコードを追加したりしながらあれこれ見ていたら、問題点がもう一つ見つかりました。

INSERT INTO students VALUES
(7, NULL, '山本', 99),
(8, NULL, '中村', 96),
(9, NULL, '小林', 93);

グループとしてまとめる対象のカラム（ここでは class）に NULL が入っているレコードをこのように追加すると、次の結果になりました。

class	name	score
NULL	山本	99
NULL	中村	96
NULL	小林	93
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

A組とB組は上位2名ずつですが、class が NULL の分は3件全てを取得してしまっています。

サブクエリだけを実行すると次のようになっていました。

class_rank	curr_class	class	name	score
1	NULL	NULL	山本	99
1	NULL	NULL	中村	96
1	NULL	NULL	小林	93
1	A組	A組	佐藤	95
2	A組	A組	鈴木	87
3	A組	A組	高橋	80
1	B組	B組	田中	100
2	B組	B組	渡辺	72
3	B組	B組	伊藤	53

おそらく NULL の場合に class = @current_class の比較が正しくできていないのが原因です。
NULL との比較は IS NULL としないといけませんね。

Window 関数を使わない方法（完成版 と思ったのにダメだった版）

mysql - Selecting first N rows for each group - Database Administrators Stack Exchange
このベストアンサーに書かれているのが下記です。

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number, 
              @file_type := file_type as file_type, 
              file_name, id
      FROM 
          (SELECT @type_number := 1) x, 
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number <= 2;

これを参考にして次のようにしました。
これが自分的なファイナルアンサーです。
　↓
2019/6/16 異常が見つかりました（後述）。

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @current_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
        @current_class := class,
        class, name, score
    FROM (
        SELECT @class_rank := 0, class, name, score
        FROM students
    ) AS x
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

2つのサブクエリを CROSS JOIN している点など気になる部分は改善し、NULL の対策もしました。*1

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
|  3 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

処理が一つ増えてしまっていますが、サブクエリを追加したのでやむを得ません。

異常発覚（2019/6/16）

初回実行時に正しく取得されないことに気づきました…。
NULL 以外のクラスでは3人ずつ取得されてしまいます。

class	name	score
NULL	山本	99
NULL	中村	96
A組	佐藤	95
A組	鈴木	87
A組	高橋	80
B組	田中	00
B組	渡辺	72
B組	伊藤	53

サブクエリだけを初回実行した結果は下記です。

class_rank	curr_class	class	name	score
1	NULL	NULL	山本	99
2	NULL	NULL	中村	96
3	NULL	NULL	小林	93
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

Window 関数を使わない方法（真・完成版）

2019/6/16 考え直しました。

@current_class に入っているのは何か

参考サイトを真似して @current_class という変数を使ってきました。
訳すと「現在のクラス」ですが、変数の中身は「一つ前のクラス」に思えます。
これを検証するために、サブクエリ内で @current_class への代入より前に @current_class の取得を追加し、そのサブクエリだけを実行してみました。

SELECT
    @class_rank := IF(class = @current_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
    @current_class,
    @current_class := class AS curr_class,
    class, name, score
FROM (
    SELECT @class_rank := 0, class, name, score
    FROM students
) AS x
ORDER BY class, score DESC;

2回目以降の実行時の結果

class_rank	@current_class	curr_class	class	name	score
1	B組	NULL	NULL	山本	99
2	NULL	NULL	NULL	中村	96
3	NULL	NULL	NULL	小林	93
1	NULL	A組	A組	佐藤	95
2	A組	A組	A組	鈴木	87
3	A組	A組	A組	高橋	80
1	A組	B組	B組	田中	100
2	B組	B組	B組	渡辺	72
3	B組	B組	B組	伊藤	53

@current_class には一つ上の行の curr_class と同じ値が入っています。
やはり「一つ前のクラス」のようです。
先頭行の B組 は、前回実行時の最終行で代入したものが残っているものと思われます。

代入の時点では「現在」ですが、各行の処理時には「一つ前」ということですね。
ちょっとした呼び方の違いですが、「一つ前」と捉えるほうがわかりやすそうです。
ここから先は変数名を @prev_class にします。

@class_rank を 0 で初期化している理由

0 を代入しないでサブクエリを実行してみます（初回実行）。

SELECT
    @class_rank := IF(class = @prev_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
    @prev_class := class AS prev_class,
    class, name, score
FROM students
ORDER BY class, score DESC;

class_rank	prev_class	class	name	score
NULL	NULL	NULL	山本	99
NULL	NULL	NULL	中村	96
NULL	NULL	NULL	小林	93
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

@class_rank + 1 という計算ができていないようです。
何も値を入れていない変数の中身は NULL なので、NULL + 1 という不正な計算になったと考えられます。

@class_rank の設定

@class_rank := IF(class = @prev_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,

ここで行っているのは

• 次の条件に該当していれば @class_rank を一つ増やす
  　　・一つ前のクラスが現在と同じ場合（NULL 以外）
  　　　または
  　　・現在のクラスが NULL の場合
• そうでなければ @class_rank を 1 にする

ということです。

しかし、これでは NULL の考慮が不十分です。
次のようになっていないといけません。

• 次の条件に該当していれば @class_rank を一つ増やす
  　　・一つ前のクラスが現在と同じ場合（NULL 以外）
  　　　または
  　　・現在のクラスも一つ前のクラスも NULL の場合
• そうでなければ @class_rank を 1 にする

つまり、次のようになります。

@class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,

@prev_class の初期化

@prev_class には class の値が代入されます。
そのため、初期化時には class に含まれるどの値にも該当しないものを入れておかないといけません。

ただし、class に含まれない値なら何でも良いわけではありません。
試しに -1 という数値で初期化してみると正しい結果になりませんでした。
class の値を入れる変数なので、それと同じ型の値（文字列）にしておかないといけないようです。

それを考慮して空文字を入れておくとうまくいきました。

@prev_class := ''

最終形①

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,
        @prev_class := class,
        class, name, score
    FROM (
        SELECT
            @class_rank := 0,    # NULL+1という計算はできないので先に0にしておく
            @prev_class := '',   # classと同じ文字列型かつclassに存在しない値で初期化
            class, name, score
        FROM students
    ) AS x
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
|  3 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

最終形②

最終型①との違いは、一つのクエリで済まそうとせずに先に変数の初期化を行っている点だけです。

SET @class_rank = 0;
SET @prev_class = '';

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,
        @prev_class := class,
        class, name, score
    FROM students
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

実行計画
サブクエリが一つ少ない分、実行計画でも DERIVED が一つ少なく済んでいます。

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

インデックスの効果

class と score の複合インデックスを設定

ALTER TABLE students ADD INDEX idx_class_score(class, score);

しかし、実行計画に変化はありませんでした…。

まとめ

Window 関数を使わないとかなり強引になります。
強引ゆえに、これで本当に正しいの？という不安が生じます。
最初の参考ページのクエリにも問題点が見つかったように、正しく書けたと思っても何か落とし穴が残っているかもしれません。

当たり前すぎる結論になりますが、Window 関数を利用できるバージョンならそれを使いましょう。

2019/6/16 追記

ユーザ定義関数を使わない方法の記事を書きました。
そちらの方法は MySQL 以外の RDBMS でも使えると思います。

kabochapo.hateblo.jp

*1:class IS NULL の条件を加えました。また、変数が 1 で初期化されていると NULL の分の class_rank が 2 以上になってしまうため 0 にしました。

【Dart/Flutter】静的解析の強化と注意

静的解析の機能は Dart や Flutter のツールに組み込まれていて、IDE でもプラグインで提供されていますが、そのまま使っても足りないことがわかりました。

例えば、const を付けるのが望ましい箇所であっても Android Studio や VS Code は教えてくれませんでした。

Container(
  padding: const EdgeInsets.all(16.0),
  ...
)

アプリのパフォーマンスに影響するので、付け忘れないようにしたいものですね。

ということで、analysis_options.yaml にはどう書けばいいのかを調べました。 自分的な結論は記事の末尾に書いています。 が新たなパッケージが最近出てきたので、このすぐ後の最新情報のみでほぼ足ります。

最新情報（2021/7/1 追記）

厳選されたルールをまとめた公式のパッケージが数ヶ月前に登場しました。

Dart 用は lint と Flutter 用は flutter_lints です。

pub.dev

pub.dev

その一方で、この記事に登場する pedantic は deprecated となりました。

flutter_lints 等を使ってみたところ、今までの自分でまとめたルールと変わらない満足度でした。 今後はこれをベースにして好みで特定のルールを有効化/無効化するのが良いと思います。

※足りないルールが結構多いことに後で気づきました。

近いうちにデフォルトに

Dart 2.13 のアナウンスの記事 によれば、次の stable リリース以降はプロジェクトを作成するとデフォルトで適用される予定のようです。

We’re currently working on defining two canonical sets of lints that we’ll apply by default in Dart and Flutter projects. We expect this to be enabled by default in the next stable release.

使い方

pubspec.yaml の dev_dependencies にパッケージを追記し、バージョンを指定します。

dev_dependencies:
  flutter_lints: ^x.x.x  # 適切なバージョンを指定してください

あとはプロジェクトのルートに analysis_options.yaml を作成して include 一行を書くだけです。

include: package:flutter_lints/flutter.yaml

これで効くはずですが、すぐに反映されなければ解析サーバを再起動してみてください。 Android Studio / IntelliJ IDEA では「Dart Analysis」のタブの左端にある更新風の赤いアイコンを押すと再起動できます。

カスタマイズ

analysis_options.yaml は prefer_const_constructors 等の個々のルール設定の他に implicit-casts の有効化/無効化などの analyzer の設定を行えます。

lints や flutter_lints を使うだけではその設定は自動的に行われないので、そこは自分で設定します。

また、個人的に use_key_in_widget_constructors のルールが有効になっているのが邪魔だったので、そのルールだけを無効化する指定をしました。 それ以外に困ること（不足など）はまだ出てきていません。

例

include: package:flutter_lints/flutter.yaml

analyzer:
  strong-mode:
    implicit-casts: false
    implicit-dynamic: false
  errors:
    missing_required_param: error
    missing_return: error
    todo: ignore
    deprecated_member_use_from_same_package: ignore

linter:
  rules:
    use_key_in_widget_constructors: false

個々のルールを上書きする形で true / false を設定する場合、通常のルール追加と違って先頭に - を付けてはいけません。 ご注意ください。

※strong-mode は deprecated になりました。

implicit-castsはdeprecatedで、Dart 2.16以降ならstrict-castsでいいみたい。
implicit-dynamicもそうとは書かれていないけどたぶん同じだから下記でOK。

analyzer:
language:
strict-casts: true
strict-inference: true
strict-raw-types: truehttps://t.co/jUwF3HMj8j pic.twitter.com/demzCbKmbO

— Kabo (@kabochapo) 2022年10月23日

利用できないケース

package（アプリプロジェクト等）の中に別の package が入れ子になっているプロジェクトでは analysis_options.yaml で include するところでエラーが出てしまいます。

気になる方は下の issue を subscribe しておきましょう。

github.com

---

最新情報の追記は以上です。

lints や flutter_lints でルール設定が楽になって嬉しいですね。 それらでほぼ足りるようになったので、この先はじっくり読まなくて大丈夫です。

① Flutter のリポジトリにある analysis_options.yaml

flutter/analysis_options.yaml at master · flutter/flutter · GitHub

一つはこれです。 Flutter のプロジェクトのルートに置くと、Android Studio でも VS Code でもちゃんと問題点を解析して教えてくれました。

ただ、型を指定する必要のないところまでいちいち言われたり *1、コンストラクタを書く位置についてうるさかったり *2 します。 そこで、次の2つを自分でコメントアウトしました。

• always_specify_types
• sort_constructors_first

2020/8/8 追記

• always_specify_types
  • 後述の pedantic にも Effective Dart にも含まれていないことに後で気づきました。 この逆のルールである omit_local_variable_types を有効にするのが良いようです。
• sort_constructors_first
  • そのルールに従って書くようになったので今では有効にしています。

なぜ有効化されているのか理解しにくいルールも一部あるものの、それら以外は「not yet tested」のようなコメントが添えられていて理由がわかりやすいです。 カスタマイズする際のベースとして使うと良さそうです。

② Google 内部で使われている analysis_options.yaml

pub.dartlang.org

これ自体はパッケージで、Google 内部で使われる Dart の静的解析のドキュメントなども含んだものです。 この中に、そのパッケージの各メジャーバージョンの analysis_options.yaml がすべて残っています。

pedantic/lib at master · google/pedantic · GitHub

特定のバージョン、あるいはバージョン指定なしのもの（＝ 最新）をインポートして使えます。

include: package:pedantic/analysis_options.1.8.0.yaml

使ってみたのですが、const の付け忘れは指摘してくれませんでした。それどころか unawaited_futures というオプションが有効になっていて邪魔くさかったです。*3

これの Readme に書かれている説明はとても参考になります。

2020/8/8 追記

• ①と違ってコメントがないが、README の Unused Lints のところに不採用になったルールの説明がある
• 大半は Effective Dart に沿っているが、含まれないルールも多数あるため、pedantic だけでは不十分
• 更新頻度が低い（この追記時点から見て最後の更新は8ヶ月前）

③ 様々なサンプルで使われている analysis_options.yaml

github.com

https://github.com/flutter/samples/blob/master/INDEX.md

Flutter チームがメンテしているリンク集で、Flutter チーム以外の人が作ったものも含まれています。
どのようなルールにしているか参考になると思います。

2020/8/8 追記

リンク集が消えていたので Flutter 公式のサンプルのリポジトリへのリンクを貼り直しました。

Flutter Gallery 等の個々のサンプル内に置かれている analysis_options.yaml がどうなっているかを確認して取り入れると良いでしょう。

④ effective_dart パッケージ

2019 年 10 月にリリースされたパッケージです。

公式のパッケージではないようですが、公式の Customizing static analysis のページで紹介されています。 これをベースにしても良いと思います。

注意

① で良さそうに思えたのですが、yaml ファイル内には次のように書かれていました。

Until there are meta linter rules, each desired lint must be explicitly enabled. See: https://github.com/dart-lang/linter/issues/288

リンク先の Issue を見ると、Linter のルールについては様々な意見があって決着していないようです。

また、Dart のドキュメント には次のような説明があります。

A wide variety of linter rules are available. Linters tend to be nondenominational—rules don’t have to agree with each other. For example, some rules are more appropriate for library packages and others are designed for Flutter apps.

多数のルールがあって、用途によって適切なルールが異なるということですね。

さらに、② の Readme の最後 には次の注意書きがあります。

Note on Flutter SDK Style: some lints were created specifically to support Flutter SDK development. Flutter app developers should instead use standard Dart style as described in Effective Dart, and should not use these lints.

② は Flutter SDK 自体の開発のためのものを含んでいるので、アプリ開発者はそれを使わないで代わりに Effective Dart に書かれている Dart の標準スタイルを使うように、ということです。

結論（記事初出時版）

• まず第一に Effective Dart をちゃんと読む！
• そこに書かれているルールに沿うように analysis-options.yaml に自分で設定する
• ② の Readme の説明も参考にする

私は結局、① を少しアレンジして使っています。 ベースとしてどれがいいかというだけなので、② や ③ が合っていればそれをベースにアレンジしてもいいかと思います。

結論（2020年8月版）

基本は同じですが、カスタマイズ方法を改善しています。

• Effective Dart が大事だと捉えておく
• ①をベースにする
  • ①の更新を時々確認する
• Lint ルールのページ の各ルールに付いているバッジを参考にする
  • flutter・pedantic・effective dart のいずれかのバッジがあれば基本的に有効にする
  • ただし、避けたほうが良さそうな説明書きが①のファイル内にあれば外す
    • 「not yet tested」「too many false positives」「conflicts with xxxxx」など
• 迷ったら③などを参考にする
• Dart の 変更履歴 で新たなルールを見つけたら追加を検討する

おまけ1

他の analysys_options.yaml をインポートしてからカスタマイズするような場合に個々のルールを有効化/無効化するには、ルールの後ろに : true か : false を付けます。 詳しくは こちら をご覧ください。

おまけ2

ファイルの先頭にある analyzer の errors の部分は①では warning になっていますが、私は厳しいルールが好みなので error にしています。

analyzer:
  (略)
  errors:
    missing_required_param: error
    missing_return: error

*1:② の Readme では「always_specify_types violates Effective Dart "AVOID type annotating initialized local variables" and others.」とされています。

*2:② の Readme では「sort_constructors_first does not reflect common usage.」とされています。

*3:unawaited_futures は ① のほうのファイルでは「too many false positives」という説明付きで無効にされていました。

【Go】GoLandとVS Codeでinterfaceの定義や実装に飛ぶ方法

interface の実装を見たいときや、逆に定義を見たいときがありますが、簡単にジャンプできると便利ですね。

実装へジャンプ

GoLand

定義（interface 自体やメソッドの定義）にカーソルがある状態で次のいずれかの操作をします。

• Ctrl + Alt + B
• Alt + Enter
  　　出てきたメニューで Go to implementation(s) を選ぶ
• メニュー Navigation > Implementation(s)

先にカーソルを置かずに対象をマウスクリックで選びたければ次の方法が使えます。

• Ctrl + Alt　＆　定義を 左クリック

Visual Studio Code

「IntelliJ IDEA Keybindings」という Extension を入れると GoLand と同様に次のキーが使えます。 最初は走査するのかちょっと時間がかかりましたが、一度実行すると待たされなくなりました。

• Ctrl + Alt + B

その Extension を使わないデフォルトのショートカットキーは未確認ですが、

• メニュー Go > Implementation

を選ぶ方法も可能で、そのメニュー項目の横にキーの組み合わせが表示されているはずです。

定義へジャンプ

GoLand

• Ctrl + U
• Alt + Enter
  　　出てきたメニューで Go to method specification(s) を選ぶ
• メニュー Navigation > Super Method

Visual Studio Code

探しても見当たりませんでした…。

【MySQL】NOT EXISTSのサブクエリでOR条件を指定すると遅い

3年以上前に Twitter にメモ的に書いたものなのですが、忘れたときに遡って探し出すのが大変なのでこちらに再メモ。

SELECT * FROM tb1
WHERE NOT EXISTS (
    SELECT * FROM tb2
    WHERE tb2.col1 = tb1.col OR tb2.col2 = tb1.col
);

くっそ遅くて、実行計画を見たらテーブル全体を走査する感じになっていました。

次のようにすると改善します。

SELECT * FROM tb1
WHERE NOT EXISTS (SELECT * FROM tb2 WHERE tb2.col1 = tb1.col)
    AND NOT EXISTS (SELECT * FROM tb2 WHERE tb2.col2 = tb1.col);

このように二つの NOT EXISTS に分けますが、ここで注意が必要なのが AND にする点です。

• A OR B という条件に該当しない（＝A にも B にも該当しない）

　　↓ つまり

• Aに該当しない AND Bにも該当しない

何年も前にその時点の MySQL 最新版でもなく更に古いもので確認して得た知識です。 新しいバージョンではオプティマイザが賢くなって解決している可能性もあります。 ちゃんと自分で実行計画などを確認しながら書きましょう。

【Flutter】SliverAppBarとTabBarを組み合わせたときに各タブのスクロール位置を保持したい

Flutterでは伸び縮みするApp Barを使うのも簡単に実装できます。

でも、タブを表示して中にListViewなどを表示すると、各タブ内のスクロール位置が連動してしまいました…。 例えば、1つ目のタブのリストでスクロールして2つ目のタブに切り替えると、そのタブでも同じ位置までスクロールした状態になっています。

やり方が悪い可能性も考えて公式ドキュメントでコード例を探してみたところ、NestedScrollView のところにありました。

PageStorageKeys are used to remember the scroll position of each tab's list.

「各タブのリストのスクロール位置を保つには PageStorageKeys を使います」と書かれています。 実現したいのはまさにこれです。

上のリンク先に書かれている例は DefaultTabController 以下のみの断片的なコードなので、動くコードにして試しました。

DartPad

できた！

と一瞬思ったのですが、ぬか喜びでした。

1. Tab1 で Item 3 までスクロール
2. Tab2 で Item 5 までスクロール
3. 各タブで Item 3、Item 5、Item 0 の位置になっている（ここまでOK）
4. Tab3 で Item 8～9 あたりまでスクロール
5. Tab2 が Item 14 までスクロールしてしまっている
6. Tab2 で Item 12 まで戻す
7. Tab1 も Tab3 も先頭（Item 0）まで戻ってしまっている

公式ドキュメントのサンプルでも起こるならFlutter自体のバグですね。

なお、この問題は既に報告されていました。

github.com

Issue の報告者自身が解決策を書いていますが、Pull Request ではなく新たなパッケージのような形にされています。 また、それを使ってみましたが解決しませんでした。 使い方の理解が足りないのかそのパッケージの問題なのかわかりません。 Flutter 本体が修正されるのはまだ先になりそうです。

改善されたかも（2019/6/27 追記）

数ヶ月前

　同じコードを実行すると、上記のおかしな挙動の頻度が減っていて、少し改善されているようでした。

本日

　久しぶりに再び実行してみたところ、しばらく操作しても再現しませんでした。
　見落としかもしれませんが、GitHub の Issues や Releases にも関連情報が見当たりません。
　完全に直ったとは判断できませんが、そろそろ使っても良さそうです。

【Go】gorp でプレースホルダを使う

テーブルは前回の記事のものを引き続き使用することにします。 kabochapo.hateblo.jp

値を SQL 文中に直書き

rows, err := dbmap.Select(User{}, `SELECT id, name FROM user WHERE id IN (1, 3)`)

プレースホルダ

rows, err := dbmap.Select(User{}, `SELECT id, name FROM user WHERE id IN (?, ?)`, 1, 3)

このように値の代わりに ? をあてがっておき、そこに値をバインドできます（database/sql でも同様）。

では、もしバインドしたい値がスライスに入っていたらどうすればいいのでしょうか。 Select() の第3引数は args ...interface{} となっているので、スライスをそのまま渡しても当然ダメなんですが、とりあえず試してみます。

bind := []uint32{1, 3}
rows, err := dbmap.Select(User{}, `SELECT id, name FROM user WHERE id IN (?, ?)`, bind...)

cannot use bind (type []uint32) as type []interface {} in argument to dbmap.Select

やはりダメでした。

では素直に []interface{} に変換してみます。

s := []uint32{1, 3}
bind := make([]interface{}, len(s))
for i, v := range s {
    bind[i] = v
}
rows, err := dbmap.Select(User{}, `SELECT id, name FROM user WHERE id IN (?, ?)`, bind...)

今度は OK でした。 これはちょっとだけ手間ですね。 何か良い方法があるのかもしれません。

では、エスケープのほうはどうなのでしょうか。 次のように変えて動作を確認してみます。

bind := []interface{}{1, "3abc"}

id が 1 と 3 のレコードが得られました。 "3abc" という文字列であっても数値の 3 として扱ってくれたようです（数値でないのにエラーにならないのが良いかどうかは置いておいて）。

名前付きプレースホルダ

普段、PHP の PDO でも名前付きのプレースホルダを好んで使っています。 幸い gorp でも同じことができます。便利ですね。

これを使って、名前が「郎」で終わる30代の人だけを取得してみます。

bind := map[string]interface{}{
    "name":    "%郎",
    "age_min": 30,
    "age_max": 39,
}
rows, err := dbmap.Select(User{}, `
    SELECT id, name FROM user
    WHERE name LIKE :name
        AND age BETWEEN :age_min AND :age_max
`, bind)

名前付きだと、当然ですが

bind := map[string]interface{}{
    "age_max": 39,
    "age_min": 30,
    "name":    "%郎",
}

のように SQL 内での指定順と異なっていても問題ありません。

まとめ

プレースホルダの使用は SQL インジェクション対策として必須です。 gorp では今回見たとおり簡単にプレースホルダを利用できて安心ですね。

今後 gorp を実際に使ってみて、気づいたことや気をつける点などがあれば、また書くかもしれません。

【Go】gorp を使って DB 操作を少し楽にする

github.com

---

gorp の気に入ったところ

ORM は便利ですが、私は SQL 文を書いてパフォーマンスを調整したいので、ORM のためのパッケージだと自分の使い方に合いません。

その点で gorp は合格でした。
"an ORM-ish library" とのことで、がっつり ORM ではないようです。
Go 標準の database/sql パッケージより少し便利にしたものという感覚で使えそうだと思いました。

---

お試し用のテーブルを用意

使い慣れていて環境を用意しやすかったので MySQL を使いました。

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

ユーザ情報のテーブルという想定です。 次のようにデータを入れておきます。

id	name	age
1	太郎	34
2	次郎	32
3	三平	30
4	四郎	28

すみませんが、Go のコードによるテーブル作成やデータ追加は今回説明しません。 手を抜いて MySQL Workbench で済ませたので…。

---

SELECT を試す

id が 1 と 3 のレコードを取得してみます。

database/sql をそのまま使う場合

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
    "fmt"
)

func main() {
    db, err := sql.Open("mysql", "user:pw@tcp(host:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    var (
        id   uint32
        name string
    )

    rows, err := db.Query(`SELECT id, name FROM user WHERE id IN (1, 3)`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        err := rows.Scan(&id, &name)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("id: %d, name: %s\n", id, name)
    }

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}

database/sql はなんだかやらなきゃいけないことが多いですね。毎回こんな風に書くことになるならウンザリしそうです。 特に ↓ この部分。

err := rows.Scan(&id, &name)

変数名を取得対象カラムと同じにしているのに、Scan() でちゃんと指定しないといけないなんて…。

しかも、この引数を SELECT 文での指定と異なる順にするとエラーになります。

rows, err := db.Query(`SELECT id, name FROM user`)
// (中略)
err := rows.Scan(&name, &id)

2017/09/17 01:23:45 sql: Scan error on column index 1: converting driver.Value type []uint8 ("太郎") to a uint32: invalid syntax

gorp の場合

package main

import (
    "database/sql"
    "fmt"
    "github.com/go-gorp/gorp"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type user struct {
    Id        uint32 `db:"id"`
    FirstName string `db:"name"` // FirstName に name というカラムを紐付ける
}

func main() {
    db, err := sql.Open("mysql", "user:pw@tcp(host:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }

    dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{}}
    defer dbmap.Db.Close()

    rows, err := dbmap.Select(user{}, `SELECT id, name FROM user WHERE id IN (1, 3)`)
    if err != nil {
        log.Fatalln(err)
    }
    for i, r := range rows {
        row := r.(*user)
        fmt.Printf("[%d] id: %d, name: %s\n", i, row.Id, row.FirstName)
    }
}

先ほどより少し短く書けました。 マッパー（DbMap）のところが増えていますが、SELECT 文以降はとてもシンプルになりましたね。

構造体を用意して db:"カラム名" のように書くことでテーブルのカラムと紐付けることができます。それを dbmap.Select() で指定すれば、データをその構造体の構造で取り出せます。

なお、紐付けは次のように行うこともできます。

table := dbmap.AddTable(user{})
table.ColMap("Id").Rename("id")
table.ColMap("FirstName").Rename("name")

ちなみに、構造体名とテーブル名が異なる場合は AddTableWithName() が使えます。 構造体が myuser、テーブルが user なら次のようになります。

table := dbmap.AddTableWithName(myuser{}, "user")

---

取得カラム数と入れ物の数が合わない場合

取得したデータを入れる変数や構造体のフィールドを上記のように2つしか用意していないまま、テーブルが持つ3カラム全てを取得しようとしたらどうなるでしょうか。

database/sql

rows, err := db.Query(`SELECT * FROM user`)
// (中略)
err := rows.Scan(&id, &name)

2017/09/17 01:23:45 sql: expected 3 destination arguments in Scan, not 2

gorp

type user struct {
    Id        uint32 `db:"id"`
    FirstName string `db:"name"` // name というカラムを FirstName に割り当てる
}

rows, err := dbmap.Select(user{}, `SELECT * FROM user`)

2017/09/17 01:23:45 gorp: no fields [age] in type user

全カラムを取得対象にしておいて後で欲しいカラムだけを取り出そうなんて、都合の良すぎる話なんですね。 PHP ではできますが。

エラーは gorp のほうがわかりやすいです。 足りないカラム名まで教えてくれています。

---

まとめ

今回は基本的な挙動を理解しやすい程度にまとめてみました。 database/sql は使いやすいとは言えませんが、gorp を使えば随分と楽に書くことができ、実用できそうだと感じました。

それでも ORM-ish でない本物の ORM が必要な人にとっては不十分かもしれません。 少し使って試すといいでしょう。

次回は、より実践で使えそうなプレースホルダを見てみたいと思います。

⇒ 書きました。 kabochapo.hateblo.jp