How to select the first least max top N rows from each group in SQL

グループから極値クエリー/先頭Nクエリーを選択することはよくある問題ですが、このSQLの書き方を簡単に例に挙げて示します.
例を示す
type
variety
price
apple
gala
2.79
apple
fuji
0.24
apple
limbertwig
2.87
orange
valencia
3.59
orange
navel
9.36
pear
bradford
6.05
pear
bartlett
2.14
cherry
bing
2.55
cherry
chelan
6.33
Selecting the one minimum row from each group
期待される結果
type
variety
price
apple
fuji
0.24
orange
valencia
3.59
pear
bartlett
2.14
cherry
bing
2.55
メソッドの過剰グループサブクエリの実装

select f.type, f.variety, f.price
from (
select type, min(price) as minprice
from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

結果:
type
variety
price
apple
fuji
0.24
cherry
bing
2.55
orange
valencia
3.59
pear
bartlett
2.14
メソッド2関連サブクエリによる実装

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);

結果:
type
variety
price
apple
fuji
0.24
orange
valencia
3.59
pear
bartlett
2.14
cherry
bing
2.55
以上の2つのクエリは等価である.
Select the top N rows from each group
各グループの前のN個のクエリは比較的苦痛な問題であり、集約関数は1個の値しか返さないため、集約関数グループを通じて前の数個のデータをクエリことは不可能である.
例えば、私は各タイプで一番安い果物を2つ選びます.
SQLの書き方を変えることで実現できます
方法1:

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
or price = (select min(price) from fruits as f where f.type = fruits.type
and price > (select min(price) from fruits as f2 where f2.type = fruits.type));

結果:
type
variety
price
apple
gala
2.79
apple
fuji
0.24
orange
valencia
3.59
orange
navel
9.36
pear
bradford
6.05
pear
bartlett
2.14
cherry
bing
2.55
cherry
chelan
6.33
この大量のサブクエリの方法は性能が悪く,上位3個,上位4個などのクエリでは実現できない.
方法2各品種の果物から,品種が第2の安価な照会を超えず,関連サブ照会により実現する.

select type, variety, price
from fruits
where (
 select count(1) from fruits as f
 where f.type = fruits.type and f.price < fruits.price
) <= 2;

2つ目の方法はfruitsテーブルが大きい場合に効果がありません
方法3
union all実装を使用できます(union allとunionの違いは、前者がソートによって重複を除去しないことです)

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

グループ(ここでは果物の種類)の数が少ない/ページ分けの場合、unionを使用してデータを多段に分割してクエリーすることができ、良いインデックスサポートの下で効率的である.
(テストでは、各クエリのカッコを外すと、limitは各クエリではなく結果セット全体の戻り行数を制限することが分かった.)
union allコンビネーションクエリを使用することはN+1の問題を解決する利器である(特に1-nでn方データ量が特に大きい場合)が、JPAフレームワークはunion allクエリを処理する際にバグがある(悲しい)
h3. 実際のプロジェクトの使用状況:
CRMの中で商店のページの商店と支店は典型的なN+1の照会の問題で、一部の商店の支店数が比較的に多いため、ページの中で前の3つの支店だけを展示して、この時union allの照会の最適化を使って、1本のSQLを通じてすべての商店の前の3つの支店に戻ることができます.
インスタンスは、Baron Schwartzのブログを参照してください.
http://www.xaprb.com/blog