面试数据岗必知必会——元数据与开窗函数

1. 元数据

元数据（Metadata）是指用来描述其他数据的数据，即“data about data”。它是关于数据的组织、数据域及其关系的信息，旨在提供对数据的理解和管理所需的关键背景和上下文。元数据可以帮助人们了解数据的来源、创建时间、作者、格式、质量、位置、所有权、使用权限等，从而使得数据更加易于查找、访问、使用和管理。

元数据可以分为几类：

描述性元数据（Descriptive Metadata）：帮助识别和查找数据，如标题、作者、关键词和摘要等。
结构性元数据（Structural Metadata）：描述数据的组成结构，如文档的章节划分或数据库的表结构。
管理性元数据（Administrative Metadata）：涉及数据的管理，包括创建日期、修改日期、访问控制和版权信息等。
引用性元数据（Reference Metadata）：提供指向其他相关数据的链接或引用。
统计元数据（Statistical Metadata）：在数据分析领域，描述数据收集方法、质量和准确性等。
技术元数据（Technical Metadata）：关于数据仓库或系统的底层技术细节，如数据结构、转换规则和数据刷新规则等。
业务元数据（Business Metadata）：从商业角度描述数据，如业务术语、指标定义和业务规则等。

元数据的重要性在于它能够帮助数据使用者更有效地利用信息资源，特别是在大数据和信息管理领域，元数据是实现数据治理、数据质量管理和数据集成的基础。

2. 窗口函数（开窗函数）

2.1 概述

开窗函数（Window Functions），也称为窗口函数或者分析函数，是一种在数据库查询语言（如SQL）中使用的高级功能。它们允许在数据的一组行（称为窗口）上执行聚合操作，但与传统的聚合函数（如SUM, AVG, COUNT等）不同的是，开窗函数可以在结果集中为每一行返回一个值，而不是仅仅返回一个聚合后的单值。

开窗函数在处理需要上下文信息或跨行计算的复杂查询时特别有用，例如计算连续排名、移动平均、累计总和等。它们可以被看作是在查询结果的每一行上应用的局部聚合。

窗口函数的基本语法如下：

<function>([expression]) OVER (
    [PARTITION BY <partition_expression>]
    [ORDER BY <order_expression>]
    [ROWS|RANGE <frame_clause>]
)

其中function_name可以是各种聚合函数，如SUM, COUNT, AVG, MIN, MAX等，也可以是特定的窗口函数，如ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()等。

over后面制定了窗口的范围，通常包括以下三个部分：

PARTITION BY：将结果集分割成不同的分区或组，每个组独立进行计算。
ORDER BY：在每个分区内对行进行排序。
ROWS BETWEEN 或 RANGE BETWEEN：定义窗口的范围，可以是固定的行数或基于排序键值的范围。

例如，如果有一个销售记录表，你可能想计算每个产品的累计销售额，这时你可以使用类似如下的SQL语句：

SELECT product_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales
FROM sales;

在这个例子中，SUM(amount) OVER ...就是一个开窗函数，它计算了每个产品随时间的累计销售额。

开窗函数在现代的SQL数据库系统中广泛支持，如PostgreSQL, MySQL 8.0+, Oracle, SQL Server等。

2.2 分类

窗口函数可以大致分为以下几类：

排名函数：
- ROW_NUMBER()：为每个行分配一个唯一的整数。
- RANK()：根据指定的排序规则为行分配排名，跳过因重复值产生的排名。
- DENSE_RANK()：类似于RANK，但是不会跳过排名。
- PERCENT_RANK()：计算行在其分区中的相对排名。
偏移函数：
- LAG()：访问当前行前的行的值。
- LEAD()：访问当前行后的行的值。
框架函数：
- FIRST_VALUE()：返回给定表达式在窗口中的第一个值。
- LAST_VALUE()：返回给定表达式在窗口中的最后一个值。
聚集函数：
- SUM(), AVG(), MIN(), MAX(), COUNT()等，这些函数在窗口函数中使用时，会对窗口内的行进行聚合。

窗口函数在现代的数据库系统中得到了广泛的支持，包括PostgreSQL, MySQL 8.0+, Oracle, SQL Server等。

有些同学可能还是有些疑惑，尤其是对于PARTITION BY、ROWS，这是两个窗口函数中常用到的关键词，下面我再举两个例子说明

2.3 结合`PARTITION BY`使用

PARTITION BY是SQL中用于窗口函数的一个关键子句，它用于定义窗口函数作用的范围或者说数据的分组方式。当在窗口函数中使用PARTITION BY时，它会将数据集分割成多个独立的分区，然后在每个分区内部独立地应用窗口函数。

简单来说，PARTITION BY的作用类似于GROUP BY，但它是在窗口函数的上下文中使用，因此它不会像GROUP BY那样聚合数据并返回每个组的单个行，而是对每个分区内的数据应用窗口函数，同时保持所有原始行的结果。

例如，假设你有一个包含员工工资的表，你想要计算每个部门内所有员工的工资排名。你可以使用RANK()窗口函数结合PARTITION BY来实现这个需求：

SELECT 
    department_id, 
    employee_id, 
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM 
    employees;

在这个查询中，PARTITION BY department_id指定了窗口函数应该在每个部门内部独立计算排名，而ORDER BY salary DESC则确保了在每个部门内，工资最高的员工排名为1，接下来的员工按照工资递减排列。

PARTITION BY可以接受一个或多个列名作为参数，这意味着你可以根据一个或多个字段来分割数据。如果没有使用PARTITION BY，窗口函数默认会将整个结果集视为一个单独的分区。

总之，PARTITION BY是窗口函数中一个非常重要的概念，它允许你在处理数据时考虑上下文，比如在每个部门、每个地区或每个时间区间内独立地应用窗口函数，从而得到更有意义的分析结果。

2. 4 结合`ROWS`使用

在SQL的窗口函数中，ROWS是一个关键的概念，用于定义窗口函数计算时所考虑的行的范围。当与OVER子句一起使用时，ROWS可以指定窗口函数计算中包括的行数，这尤其在需要基于行的前后关系进行计算时非常重要，比如移动平均、累积和等。

ROWS子句通常与BETWEEN子句一起使用，以明确指出窗口的开始和结束位置。它有几种不同的用法：

固定行数：可以指定一个固定的行数作为窗口的大小。例如，ROWS BETWEEN 3 PRECEDING AND CURRENT ROW意味着当前行以及之前的3行会被包括在窗口中。
无限窗口：使用UNBOUNDED PRECEDING或UNBOUNDED FOLLOWING来表示无限向前或向后扩展的窗口。
当前行：CURRENT ROW表示当前正在处理的行。
滑动窗口：通过指定PRECEDING和FOLLOWING来创建一个滑动窗口，比如ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING会在当前行前后各取两行来计算。

例如，假设你有一个交易记录表，你想要计算每笔交易前后的5个交易的平均金额，可以使用如下SQL语句：

SELECT transaction_id, transaction_amount,
       AVG(transaction_amount) OVER (
           ORDER BY transaction_time
           ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING
       ) as moving_average
FROM transactions;