SQLの書き方--行ごとの比較

環境整備

　　データベースのバージョン

MySQL 5.7.20-log

　　テーブルSQLの構築

DROP TABLE IF EXISTS `t_ware_sale_statistics`;
CREATE TABLE `t_ware_sale_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  `business_id` bigint(20) NOT NULL COMMENT 'business organization code',
  `ware_inside_code` bigint(20) NOT NULL COMMENT 'commodity self-code',
  `weight_sale_cnt_day` double(16,4) DEFAULT NULL COMMENT 'Average daily sales',
  `last_thirty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Last 30 days sales',
  `last_sixty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Last 60 days sales',
  `last_ninety_days_sales` double(16,4) DEFAULT NULL COMMENT 'Last 90 days sales',
  `same_period_sale_qty_thirty` double(16,4) DEFAULT NULL COMMENT '30 days sales in the same period last year',
  `same_period_sale_qty_sixty` double(16,4) DEFAULT NULL COMMENT '60 days sales in the same period last year',
  `same_period_sale_qty_ninety` double(16,4) DEFAULT NULL COMMENT '90 days sales in the same period last year',
  `create_user` bigint(20) DEFAULT NULL COMMENT 'creator',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  `modify_user` bigint(20) DEFAULT NULL COMMENT 'final modifier',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'final modification time',
  `is_delete` tinyint(2) DEFAULT '2' COMMENT 'Whether to delete, 1: yes, 2: no',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_business_ware` (`business_id`,`ware_inside_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Merchandising Statistics';

　　初期化データ

　　　　769063個のデータを準備

要求事項の背景

　　同じ組織が異なる製品を販売することができ、同じ製品が異なる組織で販売することができる、つまり：ビジネス組織と製品は多対多の関係です。

　　n個の組織があり、各組織がいくつかの商品を持っているとします。

　　具体的には、次のようなことです。

　　調べ方 100001 製品下 1000, 1001, 1003 , 100002 アンダープロダクト 1003, 1004 , 100003 製品の下 1006, 1008, 1009 の売上高

　　は、二層リスト (企業組織のリストの中にある製品のリスト) のクエリと同等です。企業組織のリストと製品のリストは両方とも固定ではなく、動的です

　　そこで問題なのは、複数の企業組織のもとで、ある品目の売上を照会するにはどうすればいいかということです

　　(説明するともっと漠然とした質問になると思いますので、イメージだけでも掴んでください！)

円形クエリ

　　これは、事業組織のリストをコードレベルでループし、事業組織ごとに一度だけデータベースをチェックする、次のような疑似コードで考えるとわかりやすいでしょう。

　　具体的なSQLは以下のようなものです。

　　SQLはインデックスされることができる

　　実装もシンプルでわかりやすいし、SQLもインデックス付きだし、すべてにおいて完璧な感じです

　　しかし、現実は、部門の開発仕様でデータベースを周期的にチェックすることが制約されているため

　　まあ、あきらめて別の方法を探すしかないでしょう」。

ORスプライス

　　SQLのスプライシングは、MyBatis のダイナミック SQL 機能を使って、次のように行います。

　　具体的なSQLは以下のようなものです。

　　SQLもインデックスされるようになる

　　実装が簡単で、わかりやすく、SQLにインデックスを付けられるし、データベースへの問い合わせは一度だけなので、うまくいきそうな気がする

　　ただ一つ残念なのは、少しORが多いことと、組織が多いとSQLが長くなることです

　　候補の1つとして、次に進みましょう

ミックスチェックフィルタリング

　　ここでも Mybatis ダイナミックSQLでは business_id を一緒にリストアップします。 ware_inside_code このようにまとめる

<イグ

　　具体的なSQLは以下のようなものです。

　　SQLもインデックスされるようになる

　　実装が簡単でわかりやすく、SQLはインデックスを作成でき、データベースへの問い合わせは一度だけなので、うまくいきそうです

　　しかし：結果セットは我々が望む結果セットよりも大きいので、それを味わうのだ！味わうのだ

　　とりあえず候補の一つとして、次に進みましょう

行ごとの比較

　　行間比較はSQL-92で追加され、比較述語=, <, >とIN述語の引数がスカラー値だけでなく、値のリストにもできるようになった。

　　もちろん、これまで通り Mybatis の動的SQLは、次のようなものです。

　　具体的なSQLは以下のようなものです。

　　SQLもインデックスされるようになる

　　実装がシンプルで、SQLにインデックスを付けられるし、データベースへの問い合わせも一度だけなので、うまくいった感がある

　　ただ、普段あまり使わないので、この書き方だと変な感じがして、ちょっとわかりにくいですね。

　　また、行ごとの比較はSQLの仕様であって、どこかのリレーショナルデータベースの仕様ではないので、すべてのリレーショナルデータベースはこの書き方をサポートするはずです

概要

　　1. 最終的に、行単位の比較は、要件を実装する方法として選択されました

　　　　なぜかというと、「頼むから限界までやってくれ！」ということです。

　　2. 要件を実現するには多くの方法がある場合が多く、ビジネスや様々な制約の中で検討し、最も適切なものを選択する必要がある

　　　　行ごとの比較は新しい機能ではなく、昔からある基本的な機能なのです

　　SQL上級チュートリアル

魔法のSQLを実現するMySQL実行計画 → EXPLAINで、SQLの実行プロセスを理解しよう!

Magical SQLのパフォーマンス最適化 → SQLを飛ばそう

SQLが肩を並べる魔法 → インデックスって本当に使うの？

SQLの書き方～行間比較～の記事は以上となります。SQLの行間比較については、スクリプトハウスの過去記事を検索していただくか、引き続き以下の関連記事をご覧ください。