优化row_number()查询：如何直接获取排名为1的数据？

我需要优化一个仅获取排名第一数据的查询。现有查询用了row_number()窗口函数，目前的做法是把这个查询嵌套后通过WHERE rn = 1筛选出每组排名第一的数据。请问有没有办法在当前查询中直接获取rn为1的数据？

现有查询SQL：

SELECT a1.member_no 
     , row_number() OVER (PARTITION BY a1.member_no ORDER BY a1.avg_hit_rate desc , a1.top_hit_cnt ) as rn
     , a1.join_no
FROM ht_typing_contents_join_log a1
WHERE a1.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
AND a1.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
and a1.success_yn = 'Y'
AND a1.len_type = '1'

1. 使用CTE（公共表表达式） #

CTE写法比嵌套子查询更直观，逻辑连贯，算是最接近“直接获取”的写法：

WITH ranked_data AS (
    SELECT a1.member_no 
         , row_number() OVER (PARTITION BY a1.member_no ORDER BY a1.avg_hit_rate desc , a1.top_hit_cnt ) as rn
         , a1.join_no
    FROM ht_typing_contents_join_log a1
    WHERE a1.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
    AND a1.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
    AND a1.success_yn = 'Y'
    AND a1.len_type = '1'
)
SELECT member_no, join_no
FROM ranked_data
WHERE rn = 1;

2. 关联子查询直接定位最优行 #

如果不想依赖窗口函数的嵌套逻辑，可以用关联子查询直接找出每个member_no下符合排序规则的第一条数据：

SELECT a1.member_no, a1.join_no
FROM ht_typing_contents_join_log a1
WHERE a1.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
AND a1.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
AND a1.success_yn = 'Y'
AND a1.len_type = '1'
AND NOT EXISTS (
    SELECT 1
    FROM ht_typing_contents_join_log a2
    WHERE a2.member_no = a1.member_no
    AND a2.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
    AND a2.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
    AND a2.success_yn = 'Y'
    AND a2.len_type = '1'
    AND (a2.avg_hit_rate > a1.avg_hit_rate 
         OR (a2.avg_hit_rate = a1.avg_hit_rate AND a2.top_hit_cnt > a1.top_hit_cnt))
);

3. 日期条件优化（额外建议） #

原查询的日期处理可以简化，不用拼接字符串再转换，直接用日期函数匹配当天数据，效率更高：

-- 替换原日期条件的写法
WHERE DATE(a1.reg_date) = CURDATE()
AND a1.success_yn = 'Y'
AND a1.len_type = '1'

如果reg_date字段有索引，这种写法能更好地利用索引优化查询速度。

补充说明 #

MySQL目前不支持类似PostgreSQL的QUALIFY子句（可直接在窗口函数后过滤排名），所以CTE是当前最简洁清晰的写法；关联子查询则适合不支持窗口函数的旧版MySQL（5.7及以下）。

内容的提问来源于stack exchange，提问作者김민석