基于SALES表的CTE查询优化：能否改用子查询简化？

嘿，我看了你的CTE查询，先帮你把原代码里的语法小问题修正下（原代码里SALEDATE前多了个左括号，RN的别名位置也有点小问题），修正后的完整CTE代码应该是这样：

WITH RECENT_SALE AS (
    SELECT 
        AGENTID,
        SALEDATE,
        ROW_NUMBER() OVER (PARTITION BY AGENTID ORDER BY SALEDATE DESC) AS RN
    FROM SALES
), COUNT_SALE AS (
    SELECT 
        AGENTID,
        COUNT(ORDERID) AS COUNTORDERS
    FROM SALES
)
SELECT 
    rs.AGENTID,
    rs.SALEDATE AS RECENT_SALEDATE,
    cs.COUNTORDERS
FROM RECENT_SALE rs
JOIN COUNT_SALE cs ON rs.AGENTID = cs.AGENTID
WHERE rs.RN = 1;

接下来给你两种用子查询替代的写法，都比原CTE更紧凑：

方式一：两个独立子查询关联 #

这种写法和原CTE逻辑完全对应，只是把CTE换成了FROM子句里的子查询：

SELECT 
    rs.AGENTID,
    rs.SALEDATE AS RECENT_SALEDATE,
    cs.COUNTORDERS
FROM (
    SELECT 
        AGENTID,
        SALEDATE,
        ROW_NUMBER() OVER (PARTITION BY AGENTID ORDER BY SALEDATE DESC) AS RN
    FROM SALES
) rs
JOIN (
    SELECT 
        AGENTID,
        COUNT(ORDERID) AS COUNTORDERS
    FROM SALES
) cs ON rs.AGENTID = cs.AGENTID
WHERE rs.RN = 1;

方式二：单扫描表的高效写法（更推荐） #

其实我们可以用窗口函数一次性计算出最近销售日期和订单总数，只需要扫描一次SALES表，性能更好：

SELECT DISTINCT
    AGENTID,
    FIRST_VALUE(SALEDATE) OVER (PARTITION BY AGENTID ORDER BY SALEDATE DESC) AS RECENT_SALEDATE,
    COUNT(ORDERID) OVER (PARTITION BY AGENTID) AS COUNTORDERS
FROM SALES;

或者用子查询过滤行号的方式，同样只扫一次表：

SELECT 
    AGENTID,
    SALEDATE AS RECENT_SALEDATE,
    COUNTORDERS
FROM (
    SELECT 
        AGENTID,
        SALEDATE,
        COUNT(ORDERID) OVER (PARTITION BY AGENTID) AS COUNTORDERS,
        ROW_NUMBER() OVER (PARTITION BY AGENTID ORDER BY SALEDATE DESC) AS RN
    FROM SALES
) t
WHERE RN = 1;

这两种子查询写法都能达到和原CTE一样的效果，其中方式二因为只扫描一次表，在数据量较大时性能优势会更明显～

内容的提问来源于stack exchange，提问作者user63516