[解決済み] avgとgroup byを使ったSQLクエリ
2022-03-07 11:20:11
質問
MySQLのSQLクエリを作成する際に、いくつかの問題があります。次のような構造を持つテーブルがあります。
mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id | pass | val |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 | 44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 | 44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 | 76.1485000000 |
| DA02959106 | 5.0000000000 | 44.4007000000 |
| DA02959106 | 4.0000000000 | 76.6485000000 |
テーブルから次の情報を抽出するクエリを作成したい。
id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc
クエリの結果は次のようになります。
+------------+---------+---------+---------+---------+---------+---------+---------+
| id | val_1 | val_2 | val_3 | val_4 | val_5 | val_6 | val_7 |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0 | 0 |
+------------+---------+---------+---------+---------+---------+---------+---------+
もちろん、ユニークな'id'ごとにより多くの行を持つように。
すでに以下のようなクエリを試してみました。
SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;
これは正しい結果を返しますが、「pass」の他の可能な値 (最大7個) の結果で拡張する必要があります。
AVGの中でネストしたSELECTを使おうとしましたが、現在の'id'に正しく限定する方法がわからなかったので、うまくいきませんでした。
次に、'pass' = 1、'pass' = 2 などの各クエリの結果を表すViewsを作成しました。しかし、ほとんどのidで'pass'の最高値は5です。 JOINクエリを使用してViewから最終結果を取得すると、いくつかのViewが空である/特定のidの値を持っていないため、空の結果セットを受け取りました。
何か思い当たることはありますか?
解決方法は?
もし私があなたの必要なものを理解しているなら、これを試してみてください。
SELECT id, pass, AVG(val) AS val_1
FROM data_r1
GROUP BY id, pass;
あるいは、すべてのidに対して1行だけでいいなら、こうなります。
SELECT d1.id,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 1) as val_1,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 2) as val_2,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 3) as val_3,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 4) as val_4,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 5) as val_5,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 6) as val_6,
(SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id
関連
-
[解決済み] MySQL エラー 1093 - FROM 句で更新のターゲット テーブルを指定できません。
-
[解決済み] MySQLから最後のN行を選択する
-
[解決済み] SQL ServerでSELECTからUPDATEする方法とは?
-
[解決済み] PHPでSQLインジェクションを防ぐにはどうしたらいいですか?
-
[解決済み] MySQLでコマンドラインを使用してSQLファイルをインポートするにはどうすればよいですか?
-
[解決済み] 各GROUP BYグループの最初の行を選択しますか?
-
[解決済み] 複数の列でgroup byを使用する
-
[解決済み] MySQLで'insert if not exists'を行うにはどうしたらいいですか?
-
[解決済み】「INNER JOIN」と「OUTER JOIN」の違いは何ですか?
-
[解決済み】SQL Serverで既存のテーブルにデフォルト値を持つカラムを追加する
最新
-
nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)
-
htmlページでギリシャ文字を使うには
-
ピュアhtml+cssでの要素読み込み効果
-
純粋なhtml + cssで五輪を実現するサンプルコード
-
ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード
-
タイピング効果を実現するピュアhtml+css
-
htmlの選択ボックスのプレースホルダー作成に関する質問
-
html css3 伸縮しない 画像表示効果
-
トップナビゲーションバーメニュー作成用HTML+CSS
-
html+css 実装 サイバーパンク風ボタン
おすすめ
-
[解決済み】エラーコード1292 - 不正なDOUBLE値の切り捨て - Mysql
-
[解決済み】SQL文が動作しない-「オペランド型の衝突:dateはintと互換性がない」。
-
[解決済み】SQL ZOO 各大陸とアルファベット順で1位の国名をリストアップする
-
[解決済み] mysql.pluginテーブルを開くことができません。mysql_upgradeを実行し、作成してください。
-
[解決済み] エラー 1115 (42000)。不明な文字セットです。'utf8mb4'
-
[解決済み] SQLZOO - select from world チュートリアル #13
-
[解決済み] SQLキー、MUL vs PRI vs UNI
-
[解決済み] MYSQLのTIMESTAMP比較
-
[解決済み] MySQLクエリ GROUP BY 日/月/年
-
[解決済み] エラーコード1111。グループ関数の無効な使用