GreatSQL 优化技巧：最值子查询与窗口函数相互转换

GreatSQL 优化技巧：最值子查询与窗口函数相互转换

导语

近期 SQL 优化过程中遇到两个案例，一个是将最值子查询改写为窗口函数，另一个是将窗口函数改写为最值子查询，觉得很有意思，特此记录分享一下。

SQL案例

两个 SQL 语句

SQL1：

SELECT imei, c1
  FROM (SELECT imei,
               c1,
               row_number() OVER(PARTITION BY imei ORDER BY statistic_time DESC) AS rn
          FROM t1)
 WHERE rn = 1;

SQL2：

 SELECT *
   FROM t1 a
  WHERE to_char(statistic_time, 'yyyymmdd') =
        (SELECT MAX(to_char(statistic_time, 'yyyymmdd'))
           FROM t1 b
          WHERE a.c2 = b.c2
                )
    AND a.imei = 'a';

这两个语句的真实场景并不是在一个系统中遇到的，这里只是用一张测试表来说明这两个 SQL 的问题。

测试表与测试数据如下：

CREATE TABLE t1(
imei VARCHAR(100),
statistic_time datetime,
c1 INT,
c2 INT,
PRIMARY KEY(imei,statistic_time)
);

 SET sql_mode=oracle;
 DELIMITER //
 CREATE OR REPLACE PROCEDURE p1 IS
 BEGIN
   FOR i IN 1..100000 LOOP
      INSERT INTO t1 VALUES('a',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),TRUNC(RAND()*1000));
   END LOOP;
    FOR i IN 1..100000 LOOP
      INSERT INTO t1 VALUES('b',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*200000),RAND()*1000);
   END LOOP;
    FOR i IN 1..100000 LOOP
      INSERT INTO t1 VALUES('c',TO_DATE('2025-01-01','yyyy-mm-dd')+ (1/24/60/60)*i, TRUNC(RAND()*20000),RAND()*1000);
   END LOOP;
END ;
//
DELIMITER ;

CALL p1;

数据统计分布如下：

greatsql> SHOW INDEX FROM T1;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+-------