自学内容网 自学内容网
自学内容网 > 正文

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

🕗 发布于 2024-07-09 02:15 面试  大数据  数据库  

1. 元数据

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

元数据可以分为几类：

  1. 描述性元数据（Descriptive Metadata）：帮助识别和查找数据，如标题、作者、关键词和摘要等。

  2. 结构性元数据（Structural Metadata）：描述数据的组成结构，如文档的章节划分或数据库的表结构。

  3. 管理性元数据（Administrative Metadata）：涉及数据的管理，包括创建日期、修改日期、访问控制和版权信息等。

  4. 引用性元数据（Reference Metadata）：提供指向其他相关数据的链接或引用。

  5. 统计元数据（Statistical Metadata）：在数据分析领域，描述数据收集方法、质量和准确性等。

  6. 技术元数据（Technical Metadata）：关于数据仓库或系统的底层技术细节，如数据结构、转换规则和数据刷新规则等。

  7. 业务元数据（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后面制定了窗口的范围，通常包括以下三个部分：

  1. PARTITION BY：将结果集分割成不同的分区或组，每个组独立进行计算。
  2. ORDER BY：在每个分区内对行进行排序。
  3. 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 分类

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

  1. 排名函数：

    • ROW_NUMBER()：为每个行分配一个唯一的整数。
    • RANK()：根据指定的排序规则为行分配排名，跳过因重复值产生的排名。
    • DENSE_RANK()：类似于RANK，但是不会跳过排名。
    • PERCENT_RANK()：计算行在其分区中的相对排名。

  2. 偏移函数：

    • LAG()：访问当前行前的行的值。
    • LEAD()：访问当前行后的行的值。

  3. 框架函数：

    • FIRST_VALUE()：返回给定表达式在窗口中的第一个值。
    • LAST_VALUE()：返回给定表达式在窗口中的最后一个值。

  4. 聚集函数：

    • 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子句一起使用，以明确指出窗口的开始和结束位置。它有几种不同的用法：

  1. 固定行数：可以指定一个固定的行数作为窗口的大小。例如，ROWS BETWEEN 3 PRECEDING AND CURRENT ROW意味着当前行以及之前的3行会被包括在窗口中。

  2. 无限窗口：使用UNBOUNDED PRECEDING或UNBOUNDED FOLLOWING来表示无限向前或向后扩展的窗口。

  3. 当前行：CURRENT ROW表示当前正在处理的行。

  4. 滑动窗口：通过指定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;

在这个例子中，ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING定义了一个以当前行为中心的窗口，包括当前行前后的共11个交易（5前+5后+当前行），并计算这个窗口内交易金额的平均值。

ROWS子句的灵活性使得窗口函数能够处理各种复杂的分析需求，尤其是在时间序列数据和需要基于历史或未来数据点进行计算的场景中。

点赞关注收藏，获取更多干货知识~


原文地址：https://blog.csdn.net/weixin_64259675/article/details/140279942

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024