次のクエリ:
SELECT
year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
ORDER BY id, rate DESC
結果:
year | id | rate
2006 | p01 | 8.0
2003 | p01 | 7.4
2008 | p01 | 6.8
2001 | p01 | 5.9
2007 | p01 | 5.3
2009 | p01 | 4.4
2002 | p01 | 3.9
2004 | p01 | 3.5
2005 | p01 | 2.1
2000 | p01 | 0.8
2001 | p02 | 12.5
2004 | p02 | 12.4
2002 | p02 | 12.2
2003 | p02 | 10.3
2000 | p02 | 8.7
2006 | p02 | 4.6
2007 | p02 | 3.3
各 ID の上位 5 行のみを取得します。
year | id | rate
2006 | p01 | 8.0
2003 | p01 | 7.4
2008 | p01 | 6.8
2001 | p01 | 5.9
2007 | p01 | 5.3
2001 | p02 | 12.5
2004 | p02 | 12.4
2002 | p02 | 12.2
2003 | p02 | 10.3
2000 | p02 | 8.7
各グループに適用される何らかの LIMIT のような修飾子を使用してこれを行う方法はありますか?
ベストアンサー1
グループごとに上位 n 行を検索します。この回答は、OP とは異なるサンプル データを使用した一般的なソリューションを提供します。
MySQL 8以降では、ROW_NUMBER、RANKまたはDENSE_RANK関数は、トップ 5 の正確な定義によって異なります。以下は、降順でvalueソートされたこれらの関数によって生成された数値です。同点の処理方法に注意してください。
| キッズ | ネコ科 | 価値 | 行番号 | ランク | 密なランク |
|---|---|---|---|---|---|
| 1 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 100 | *1 | *1 | *1 |
| 2 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 90 | *2 | *2 | *2 |
| 3 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 90 | *3 | *2 | *2 |
| 4 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 80 | *4 | *4 | *3 |
| 5 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 80 | *5 | *4 | *3 |
| 6 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 80 | 6 | *4 | *3 |
| 7 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 70 | 7 | 7 | *4 |
| 8 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 60 | 8 | 8 | *5 |
| 9 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 50 | 9 | 9 | 6 |
| 10 | 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 30 31 42 54 65 75 80 90 102 203 21 32 43 85 96 108 112 22 34 56 76 | 40 | 10 | 10 | 7 |
関数を選択したら、次のように使用します。
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY catid ORDER BY value DESC) AS n
FROM t
) AS x
WHERE n <= 5
MySQL 5.x では、パーティション上の貧者のランクを使用して、目的の結果を得ることができます。つまり、テーブルをそれ自体と外部結合し、各行について、その前の行の数をカウントします(たとえば、前の行の方が値が大きい行になります)。
次の関数は、次のRANK関数と同様の結果を生成します。
SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid
DENSE_RANK関数と同様の結果を生成するには、次の変更を加えます。
COUNT(DISTINCT b.value)
または、次の変更を加えると、ROW_NUMBER関数と同様の結果が生成されます。
ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)