MySQL - グループ単位で上位n件を取得するSQL文を作成する過程での質問

Question

code毎にグルーピングし、cost_salesが0を除外し、release_atの上位2件を取得するSQL文作成しています。
下記を参考に次のSQLを作りました。

【MySQL】グループ単位で上位n件を取得するSQL文を作成したい。

SELECT
	id, code, release_at, cost_sales
FROM
	performance as p1
-- 評価行と同じcodeで、評価行よりrelease_atが新しいものの行数を数えて、それが2以下なら採用する
WHERE 
	2 >= (
		SELECT COUNT(*)
        FROM (SELECT * FROM performance WHERE cost_sales != 0) as p2 -- ★1
        -- FROM performance as p2 -- ★2
        WHERE 
			p1.code = p2.code
            AND p2.release_at >= p1.release_at
    ) 
	AND cost_sales != 0
ORDER BY
	code, release_at DESC;

★1だと結果は6917件で、★2にすると25件になります。

★1の結果

★2の結果

★1のほうが条件文を挿入しているので件数が少なくなりそうなのですが、実際はその逆になっているところが理解できません。
また、もっとよいSQLがありますでしょうか。

どなたかご教授してくださると嬉しいです。よろしくおねがいします。

Comments

[sazi]

MySQL8.0以降なら集計関数(rankなど）が使えます。

[yambejp]

サンプルがないと答えづらいですがランキング処理自体思いロジックは簡単ですが
重い処理なのでそこそこ面倒です（チューニングのために専用のテーブルで管理したり必要）

Answer 1

外側の WHERE の AND cost_sales != 0 を外してみるとわかりやすいと思います。

code = 1301 がこのようになっているとします。

release_at	const_sales
2019-05-13	232446
2019-05-10	231180
2019-05-20	0
2019-05-21	0

★1 は 4件すべて抽出されます。
★2 は 下の2件が抽出されます。

そして、AND cost_sales != 0 をつけると★1 は 2件、★2 は 0件になります、

★1 が要件を満たすならそのままでよさそうです。

Comments

[aya1]

すごい。まさにその通りの動作になります。

私には、なぜそのような動作になるのかいまいちわかりません。
そもそもWHERE句が何をしているのかがわかっていないので、そこから調べて勉強しようと思います。

こんな私に目を通しておいたほうがいいサイトや書籍などありますでしょうか。

「達人に学ぶSQL」や「反復学習ソフト付きSQL書き方ドリル」など一通り目を通してはいますが、もう一度復習する必要がありそうです。

[KOZ6.0]

うーん、生憎ですが、SQL 本を買って勉強したという覚えがありません。
ただ、書いたSQLを組み替えてみたり、実行計画をとったり、系統別にまとめてみたりして、気づいたことをメモしたりはしています。
あとは、こういうところで相談にあがったものに対してコメントは書かないにしても自分で書いて実行してみるといいのかもしれませんね。

[aya1]

とても参考になります。
ありがとうございます！！