MySQLがウィンドウ関数で合計関数を実行するときに発生する可能性のあるバグ

MySql のウィンドウ関数を使用して統計データを収集する際に、小さな問題が見つかったので、それについて話し合いたいと思います。

環境設定:

mysql-インストーラー-コミュニティ-8.0.20.0

問題: ウィンドウ関数で合計を実行すると、重複データがある場合、同じデータが段階的にではなく直接追加されます。

問題の説明

データ: 成績テーブルには、学生の s_id、コースの c_id、成績の s_score の 3 つのフィールドがあります。

クエリ条件は、各コースの学生のスコアランキングとスコアの概要を照会します。

クエリ結果: 同じコースに同じスコアがある場合、要約スコアは累積されず、一度に追加されることがわかりました。

データテーブルを作成する

テーブル「スコア」を作成します(
`s_id` VARCHAR(20)、
`c_id` VARCHAR(20)、
`s_score` INT(3)、
主キー(`s_id`,`c_id`)
）

データの挿入

-- Score に値 ('01', '01', 80) を挿入します。
スコア値('01'、'02'、90)に挿入します。
スコア値('01'、'03'、99)に挿入します。
スコア値('02', '01', 70)に挿入します。
スコア値('02', '02', 60)に挿入します。
スコア値('02', '03', 80)に挿入します。
スコア値('03', '01', 80)に挿入します。
スコア値('03', '02', 80)に挿入します。
スコア値に挿入します('03', '03', 80);
スコア値('04', '01', 50)に挿入します。
スコア値('04', '02', 30)に挿入します。
スコア値('04'、'03'、20)に挿入します。
スコア値('05'、'01'、76)に挿入します。
スコア値('05'、'02'、87)に挿入します。
スコア値('06'、'01'、31)に挿入します。
スコア値('06', '03', 34)に挿入します。
スコア値('07', '02', 89)に挿入します。
スコア値('07', '03', 98)に挿入します。

クエリデータ

c_id、s_id、s_score を選択、
first_value(s_score)をwに対してfirst_vとして計算し、
last_value(s_score)をwに対してlast_vとして計算し、
sum(s_score)をwに対してsum_vとして計算し、
max(s_score)をwに対してmax_vとして計算し、
min(s_score)をwで割ったものをmin_vとする
count(s_id)をwでcount_vとしてカウントし、
row_number() を row_id として w に適用し、
rank_id として w に対する rank() を実行する、
dense_rank() を、w をdense_id として計算する
スコア ウィンドウは (c_id によるパーティション、s_score による順序) でした。

クエリ結果

コース番号01の統計結果を見ると、データの最初の行のsum_v列の最初の2つのデータは両方とも160です。関数の原理によれば、データは80と160であるはずです。

コース番号 02 の統計結果を見ると、結果が正しいことがわかります。最初の sum_v は 90 で、2 番目は 179 です。

実際の表示が予想される結果と一致していないため、問題が発生しました。

c_id	s_id	スコア	最初_v	最後の	合計	最大	最小値	カウント	行ID	ランクID	密なID
01	01	80	80	80	160	80	80	2	1	1	1
01	03	80	80	80	160	80	80	2	2	1	1
01	05	76	80	76	236	80	76	3	3	3	2
01	02	70	80	70	306	80	70	4	4	4	3
01	04	50	80	50	356	80	50	5	5	5	4
01	06	31	80	31	387	80	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

思考の検証

コース番号 02 のデータは正しいですが、01 のデータは間違っています。01 と 02 の違いは、コース 01 の最初の 2 人の学生のスコアが同じ 80 であることです。

スコアが同じだったために合計に誤りが生じたのでしょうか?

この問題を確認するには、コース番号01と学生番号01のスコアを82に変更してクエリを実行します。結果は次のようになります。

sum_v 列には 82 と 162 が表示されており、これは予想される結果と一致しています。

これは、ウィンドウ関数で合計を実行すると、重複したデータがある場合、同じデータが段階的にではなく直接追加されることを示しています。

c_id	s_id	スコア	最初_v	最後の	合計_v	最大	最小値	カウント	行ID	ランクID	高密度ID
01	01	80	80	82	82	82	82	2	1	1	1
01	03	80	80	80	162	82	80	2	2	1	1
01	05	76	80	76	236	82	76	3	3	3	2
01	02	70	80	70	306	82	70	4	4	4	3
01	04	50	80	50	356	82	50	5	5	5	4
01	06	31	80	31	387	82	31	6	6	6	5
02	01	90	90	90	90	90	90	1	1	1	1
02	07	89	90	89	179	90	89	2	2	2	2
02	05	87	90	87	266	90	87	3	3	3	3
02	03	80	90	80	346	90	80	4	4	4	4
02	02	60	90	60	406	90	60	5	5	5	5
02	04	30	90	30	436	90	30	6	6	6	6
03	01	99	99	99	99	99	99	1	1	1	1
03	07	98	99	98	197	99	98	2	2	2	2
03	02	80	99	80	357	99	80	4	3	3	3
03	03	80	99	80	357	99	80	4	4	3	3
03	06	34	99	34	391	99	34	5	5	5	4
03	04	20	99	20	411	99	20	6	6	6	5

その他のSQL検証と比較

上記の検証後、Mysql では合計時にエラーが発生し、段階的に累積されませんでした。

Sqlite Expert 5.3 の他のプラットフォームでも同じ問題が発生するかどうかを確認しましたが、同じ結果が見つかりました。

これは奇妙です。Mysql の実装でエラーが発生した場合、Sqlite で同じエラーが発生する確率ははるかに低くなります。

これは、合計関数とウィンドウ関数を一緒に使用した場合の特性によるものでしょうか?一緒に議論したり研究したりすることを歓迎します。

要約する

MySQL のウィンドウ関数での合計関数の実行に関するバグの可能性に関するこの記事はこれで終わりです。MySQL のウィンドウ関数での合計関数の実行に関する詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: