SQL窗口函数详解

详细说明在sql中窗口函数是什么，为什么需要窗口函数，有普通的聚合函数了那窗口函数的意义在哪，窗口函数的执行逻辑是什么，over中的字句是如何使用和理解的（是不是句句戳到你的痛点，哼哼～）

1.什么是窗口函数？

窗口函数（Window Functions）是SQL中的一种功能强大的工具，用于对查询结果集中的每一行进行计算，而无需对数据进行分组（GROUP BY）。与聚合函数不同，窗口函数不会将行汇总到一条记录中，而是保留原始行，并在此基础上添加计算结果。窗口函数常用于排名、累积和、滑动窗口计算等。

2.为什么需要窗口函数（为什么普通的聚合函数不行）

2.1四点原因

按行计算而不改变行的结构：
    •    窗口函数能够在不改变行结构的情况下对数据进行计算。与聚合函数（如SUM、AVG等）不同，窗口函数不会将多行数据聚合成一行，而是对每一行进行计算并返回结果，同时保留原始行。这在许多分析场景中非常有用，例如累积和、排名计算等。
支持复杂的分析计算：
    •    窗口函数可以进行复杂的分析计算，包括排名、累积和、滑动平均等。这些计算在财务分析、时间序列分析和数据挖掘中非常常见。例如，使用窗口函数可以计算每个销售员的销售排名、某产品在不同时间段的累积销售量等。
灵活的分区和排序机制：
    •    窗口函数支持按分区和排序进行计算，提供了极大的灵活性。可以根据需要按特定列进行分区（PARTITION BY），并在每个分区内按某列排序（ORDER BY）。这使得可以在多个维度上进行复杂的数据分析。例如，可以按客户ID和时间进行分区和排序，从而分析每个客户在不同时间段的行为变化。
保持数据的上下文关系：
    •    窗口函数能够保持数据的上下文关系。在进行累积计算或滑动窗口计算时，窗口函数能够在当前行的基础上考虑前后多行的数据。这在时间序列分析中尤为重要，例如计算滚动平均值、滚动总和等。
提高查询的可读性和维护性：
    •    使用窗口函数可以使SQL查询更加简洁和可读。相比于嵌套的子查询或复杂的JOIN操作，窗口函数提供了一种更直观的方式来表达复杂的计算逻辑。这不仅提高了查询的可读性，还降低了维护的难度。

2.2举几个例子

例子1:计算每篇文章在每个时间点的累积观看人数

SELECT 
    artical_id,
    dt,
    SUM(diff) OVER (PARTITION BY artical_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_viewers
FROM combined
ORDER BY artical_id, dt;

在这个例子当中，窗口函数可以很方便的选择包括当前行在内的其以前的所有行

例2:计算每个销售员的销售额排名

SELECT 
    salesperson,
    sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank
FROM sales_data;

在这个例子当中，使用窗口函数做一个“开窗”，类似单独拖出一个小窗口，在这个小窗口中选定一个标准对销售员进行排序，在这个小窗口中，每一行数据都得到了自己的排名，然后带着这个排名回到主查询中；这个过程中，主查询既没有使用group by分组，也没有order by来产生排名，也没有使用子查询，但是每一个销售员却得到了自己地区中的排名，这就是刚刚提到的对每一行进行计算并返回结果，同时保留原始行。

3.窗口函数的执行逻辑是什么(over怎么用)

只有了解了窗口函数的执行逻辑才能在复杂的查询中正确运用窗口函数

3.1 生成基础结果集

首先，SQL引擎会执行查询语句中的FROM子句、WHERE子句、GROUP BY子句和HAVING子句，生成基础结果集。这个结果集包含了所有符合条件的数据。所以窗口函数会在拿到所在主查询的基础结果集后才生效！

3.2 分区（Partitioning）

在执行窗口函数时，OVER子句中的PARTITION BY子句会将基础结果集划分为多个分区。每个分区独立处理，窗口函数将在每个分区内单独执行。如果没有指定PARTITION BY，则视为一个整体分区。注意这个分区是在窗口内单独做的，并没有对主查询中的结果或者说每一行产生影响。

3.3 排序（Ordering）

在每个分区内，根据OVER子句中的ORDER BY子句进行排序。排序定义了窗口函数的计算顺序，确保计算按指定的顺序进行。注意这个排序也是在窗口内单独做的，并没有对主查询中的结果或者说每一行产生影响。

3.4 定义窗口帧（Frame）

窗口帧定义了窗口函数计算的行范围。窗口帧由ROWS或RANGE子句指定。

从2、3中，不难看出，这有一个易混淆的点（也是本人混淆过的点），那就是over中的分区和排序 本质上 是在定义窗口函数的计算方式，和主查询中的group by和order by（如果有的话）并无关系！！！！！

3.5 举个例子

这个例子是在一个uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间组成的表的基础上，统计每篇文章同一时刻最大在看人数

SELECT
  artical_id,
  MAX(instant_viewer_cnt) max_uv
FROM (
  SELECT
    artical_id,
    dt,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
    SELECT 
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT 
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0) t1 
  group by
    artical_id,dt
) t2
GROUP BY 1
ORDER BY 2 DESC

这里t1表是通过编码操作和union操作生成的瞬时动作表，我们为了从t1的瞬时动作得到每个时刻的状态表t2，所以需要窗口函数来做聚合，窗口函数中的order by只是定义sum的计算方式，如果要想主查询中也是按照artical_id和dt的顺序来呈现结果那么，是需要显式地在真正的order by中写出来的