質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.53%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

62245閲覧

SQLでの最大値を持つレコードの取得方法について

退会済みユーザー

退会済みユーザー

総合スコア0

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2017/02/25 12:48

題名の通りなのですが、あるグループ毎にあるカラムの値が最大のレコードを取得する方法についての質問です。
調べたり自分で考えてみたところ

SQL

1SELECT * 2FROM 3 ( 4 SELECT * 5 FROM product 6 ORDER BY price DESC 7 ) product 8GROUP BY type

SQL

1SELECT * 2FROM product 3INNER JOIN( 4 SELECT MAX(price) AS max_price, 5 * 6 FROM product 7 GROUP BY type 8 ) tmp 9ON product.id = tmp.id 10AND product.price = tmp.max_price

SQL

1SELECT * 2FROM product 3WHERE product.price = ( 4 SELECT MAX(price) 5 FROM product AS tmp 6 WHERE product.type = tmp.type 7 )

SQL

1SELECT * 2FROM product 3WHERE NOT EXISTS( 4 SELECT 1 5 FROM product AS tmp 6 WHERE product.type = tmp.type 7 AND product.price < tmp.price 8 )

とりあえずこれだけの方法がありました。
どれがコストなどの観点から見て良いものなのでしょうか?
またこれらより優れた方法があれば教えていただきたいです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

Postgresでのテストですので参考まで
SQL文は上から順に(1)(2)(3)(4)としました。
※SQL文の1番目のものは、PostgresではSQLエラーでした。ですので対象外といたしました。
postgresでは * のgroup byができませんし、出力順が保証されていません。

検査対象件数は1万件としました。(インデックスは有りません)
結果は、costを実行時間とすると、早いもの順で(4)<(2)<(3)となります。
特に(3)は、条件の部分でSQLその都度実行される事により、他の2つより大変時間が掛かる結果となっています。

EXPLAIN ANALYZEの結果
Execution time:実行時間です(ミリ秒)

sql

1NO.2 2 QUERY PLAN 3---------------------------------------------------------------------------------------------------------------------------------------- 4 Hash Join (cost=230.30..1015.16 rows=56 width=12) (actual time=48.100..85.661 rows=3383 loops=1) 5 Hash Cond: ((product.type = product_1.type) AND (product.price = (max(product_1.price)))) 6 -> Seq Scan on product (cost=0.00..167.20 rows=11220 width=12) (actual time=0.017..15.101 rows=10001 loops=1) 7 -> Hash (cost=227.30..227.30 rows=200 width=8) (actual time=48.040..48.040 rows=3334 loops=1) 8 Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 163kB 9 -> HashAggregate (cost=223.30..225.30 rows=200 width=8) (actual time=36.361..42.345 rows=3334 loops=1) 10 Group Key: product_1.type 11 -> Seq Scan on product product_1 (cost=0.00..167.20 rows=11220 width=8) (actual time=0.007..16.087 rows=10001 loops=1) 12 Planning time: 0.329 ms 13 Execution time: 90.400 ms 14(10 rows) 15 16NO.3 17 QUERY PLAN 18-------------------------------------------------------------------------------------------------------------------------- 19 Seq Scan on product (cost=0.00..2192583.25 rows=56 width=12) (actual time=4.054..12745.577 rows=3383 loops=1) 20 Filter: (price = (SubPlan 1)) 21 Rows Removed by Filter: 6618 22 SubPlan 1 23 -> Aggregate (cost=195.39..195.40 rows=1 width=4) (actual time=1.235..1.246 rows=1 loops=10001) 24 -> Seq Scan on product tmp (cost=0.00..195.25 rows=56 width=4) (actual time=0.577..1.174 rows=3 loops=10001) 25 Filter: (product.type = type) 26 Rows Removed by Filter: 9998 27 Planning time: 0.143 ms 28 Execution time: 12776.098 ms 29(10 rows) 30 31NO.4 32 QUERY PLAN 33---------------------------------------------------------------------------------------------------------------------------- 34 Hash Anti Join (cost=307.45..667.49 rows=9350 width=12) (actual time=36.436..78.295 rows=3383 loops=1) 35 Hash Cond: (product.type = tmp.type) 36 Join Filter: (product.price < tmp.price) 37 Rows Removed by Join Filter: 14529 38 -> Seq Scan on product (cost=0.00..167.20 rows=11220 width=12) (actual time=0.011..15.269 rows=10001 loops=1) 39 -> Hash (cost=167.20..167.20 rows=11220 width=8) (actual time=36.365..36.365 rows=10001 loops=1) 40 Buckets: 16384 Batches: 1 Memory Usage: 519kB 41 -> Seq Scan on product tmp (cost=0.00..167.20 rows=11220 width=8) (actual time=0.009..18.940 rows=10001 loops=1) 42 Planning time: 0.164 ms 43 Execution time: 83.163 ms 44(10 rows) 45

(2)につきましてもPostgres都合上、下記に修正いたしました。

SELECT product.* FROM product INNER JOIN( SELECT type, MAX(price) AS max_price FROM product GROUP BY type ) tmp ON product.type = tmp.type AND product.price = tmp.max_price;

投稿2017/02/25 22:04

編集2017/02/25 22:56
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/02/27 08:49

丁寧な回答ありがとうございます!参考にさせていただきます
guest

0

1つ目のSQLはGROUP BYで指定されておらず、集約関数でもリテラルでもないカラムをSELECT句に含むため、文法エラーになると思われます。MySQLだとエラーにならないかもしれませんが、それでも正しい結果は保障されません。

その他のSQLではどれかが決定的に早い or 遅いというものはないと思います。入っているデータ(統計情報)、張られている索引、DBMS次第でどれがベストかは変わってくるので、実行計画を確認するのが一番です。

投稿2017/02/25 15:45

編集2017/02/26 00:19
SVC34

総合スコア1149

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/02/27 08:48

1つ目のような書き方は駄目だったんですね…
guest

0

各クエリの実行計画をしらべればどれが低コストなのかがわかると思いますが。

投稿2017/02/25 12:57

turbgraphics200

総合スコア4267

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/02/27 08:47

全くの初心者なもので実行計画というものすら知りませんでした。すみません。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.53%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問