SQL中的OVER窗口函数、CTE与临时表Temp Table

在日常 SQL 查询中，我们经常遇到需要“累计计算”、“分组排名”或“分步逻辑处理”的场景，这时候 `OVER` 窗口函数、CTE（公用表表达式）和临时表（Temp Table）就派上了用场。接下来将举例讲解这三者的用法。

一、OVER窗口函数

首先我们简单了解窗口函数的概念。窗口函数是 SQL 中用于对每一行数据进行分析的一种强大工具。与聚合函数不同，它不会压缩结果为一行，而是在保留原始行的基础上增加一个“分析列”。

简单来说，它就像是“对每一行开一扇窗”，通过窗口看到这行与其他行的关系，从而进行排名、累计、移动平均等操作。而我们这里介绍的`OVER()` 即是它的核心语法结构。

基本语法结构：

函数名(...) OVER ([PARTITION BY ...] ORDER BY ...)

常见用法举例：

1、对每位销售按时间累计销售金额

SELECT 
    SalesPersonID,
    OrderDate,
    SUM(SalesAmount) OVER (PARTITION BY SalesPersonID ORDER BY OrderDate) AS RunningTotal
FROM Sales;

2、每个国家截至每一天的累计疫苗接种人数

SELECT dea.location,dea.date,dea.continent,dea.population,vac.new_vaccinations,
	SUM(cast(vac.new_vaccinations as bigint)) OVER (PARTITION BY dea.location order by dea.location,dea.date) AS RollingPeopleVaccinated
FROM [SQL Portfolio]..CovidDeaths dea
JOIN [SQL Portfolio]..CovidVaccinations vac
ON dea.location=vac.location
and dea.date=vac.date

这个代码下的运行结果如下：

如欧洲2021年1月10日的累计接种人数等于2021年1月1日为止累计接种人数加上1月10号当天新的接种人数。482093+1491952=1974045.

二、CTE（Common Table Expression，公共表表达式）

CTE是 SQL 中的一个临时结果集，仅在一条 SQL 语句中有效。可以在SELECT、INSERT、UPDATE和DELECT 语句中使用，通常用于结构更清晰的查询，特别是递归、分组排名等复杂查询。

基本语法结构：

WITH CTE名 AS (
    SELECT ...
    FROM ...
)
SELECT * FROM CTE名;

举例说明：

1、使用 CTE实现累计接种人数与接种率的计算

--USE CTE
With PopvsVac (continent,location,date, population, new_vaccinations, RollingPeopleVaccinated)--创建了一个 CTE，名字叫 PopvsVac，包含六列
as
(
SELECT dea.location,dea.date,dea.continent,dea.population,vac.new_vaccinations,
	SUM(cast(vac.new_vaccinations as bigint)) OVER (PARTITION BY dea.location order by dea.location,dea.date) AS RollingPeopleVaccinated
FROM [SQL Portfolio]..CovidDeaths dea
JOIN [SQL Portfolio]..CovidVaccinations vac
ON dea.location=vac.location
and dea.date=vac.date
--order by 1,2
)--使用窗口函数对每个国家 location 按照 date 顺序，对 new_vaccinations 累加，得到累计接种总人数
SELECT *,
       (CAST(RollingPeopleVaccinated AS float) / NULLIF(CAST(population AS float), 0)) * 100 AS vaccination_rate
FROM PopvsVac
--在 CTE 结果基础上，计算 接种率 vaccination_rate

三、临时表（Temp Table）

临时表是使用 # 创建的会话级别的真实表结构，存储在 tempdb 中。它可以像普通表一样进行插入、查询、更新等操作，但生命周期是临时的。

基本语法结构：

-- 创建临时表
CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(100)
);
-- 插入数据
INSERT INTO #TempTable
SELECT ID, Name
FROM OriginalTable
WHERE SomeCondition;
-- 使用
SELECT * FROM #TempTable;

-- 临时表在当前会话结束后自动删除

用法举例，依旧是实现上述CTE中对接种人数除以总人口计算疫苗接种率

--创建临时表USE TEMP TABLE
DROP TABLE if exists #percentpopulationVaccinated;
CREATE TABLE #percentpopulationVaccinated
(
    continent nvarchar(255),
    location nvarchar(255),
    date datetime,
    population numeric,
    new_vaccinations numeric,
    RollingPeopleVaccinated numeric
);
-- 插入处理后的数据
INSERT INTO #percentpopulationVaccinated
SELECT 
    dea.continent,
    dea.location,
    TRY_CAST(dea.date AS datetime) AS date,
    TRY_CAST(dea.population AS numeric) AS population,
    TRY_CAST(vac.new_vaccinations AS numeric) AS new_vaccinations,
    SUM(TRY_CAST(vac.new_vaccinations AS bigint)) 
        OVER (PARTITION BY dea.location ORDER BY dea.location, TRY_CAST(dea.date AS datetime)) AS RollingPeopleVaccinated
FROM [SQL Portfolio]..CovidDeaths dea
JOIN [SQL Portfolio]..CovidVaccinations vac
    ON dea.location = vac.location
   AND TRY_CAST(dea.date AS datetime) = TRY_CAST(vac.date AS datetime);
-- 查询结果并计算接种率
SELECT *,
       (CAST(RollingPeopleVaccinated AS float) / NULLIF(CAST(population AS float), 0)) * 100 AS vaccination_rate
FROM #percentpopulationVaccinated;

如果需要重新运行代码，无需对TempTable名称进行修改，只需加入一句即可：

DROP TABLE if exists #TempTable;

四、CTE 与Temp Table对比 

五、总结

在日常 SQL 编程中，合理使用OVER（） 窗口函数可以极大简化累计/排名逻辑；使用 CTE 能让查询结构更清晰易维护；使用临时表可以支撑更复杂、跨步骤的数据处理。

掌握这三者并灵活组合使用，你的 SQL 能力将迈上一个新台阶！

感谢各位小伙伴的阅读与点赞，关注我学习更多SQL等数据分析内容！