数据库内分析：利用 SQL 的分析功能

推荐：使用NSDT场景编辑器助你快速搭建可二次编辑的3D应用场景

我们都知道数据分析在当今数据驱动的世界中的重要性，以及它如何从可用数据中为我们提供有价值的见解。但有时，数据分析对于数据分析师来说变得非常具有挑战性和耗时。如今它变得忙碌的主要原因是生成的数据量激增，并且需要外部工具来对其执行复杂的分析技术。

但是，如果我们分析数据库本身中的数据，并使用大大简化的查询呢？这可以使用SQL分析函数来实现。本文将讨论可以在SQL Server中执行的各种SQL分析函数，并获得有价值的结果。

这些函数基于一组行计算聚合值，超出了基本的行操作。它们为我们提供了用于排名、时间序列计算、窗口化和趋势分析的工具。因此，在不浪费任何时间的情况下，让我们通过一些细节和实际示例开始逐一讨论这些功能。本教程的先决条件是 SQL 查询的基本实践知识。

创建演示表

我们将创建一个演示表并在此表上应用所有分析函数，以便您轻松学习本教程。

注意：本教程中讨论的某些函数在 SQLite 中不存在。因此，最好使用MySQL或PostgreSQL Server。

此表包含几个大学生的数据，包含四列学生证、学生姓名、主题和最终成绩（满分 100 分）。

创建包含 4 列的学生表：

CREATE TABLE students
  (
     id          INT NOT NULL PRIMARY KEY,
     NAME        VARCHAR(255),
     subject     VARCHAR(30),
     final_marks INT
  ); 

现在，我们将在该表中插入一些虚拟数据。

INSERT INTO Students (id, name, subject, final_marks)
VALUES (1, 'John', 'Maths', 89),
       (2, 'Kelvin', 'Physics', 67),
       (3, 'Peter', 'Chemistry', 78),
       (4, 'Saina', 'Maths', 44),
       (5, 'Pollard', 'Chemistry', 91),
       (6, 'Steve', 'Biology', 88),
       (7, 'Jos', 'Physics', 89),
       (8, 'Afridi', 'Maths', 97),
       (9, 'Ricky', 'Biology', 78),
       (10, 'David', 'Chemistry', 93),
       (11, 'Jofra', 'Chemistry', 93),
       (12, 'James', 'Biology', 65),
       (13, 'Adam', 'Maths', 90),
       (14, 'Warner', 'Biology', 45),
       (15, 'Virat', 'Physics', 56);

现在我们将可视化我们的表格。

SELECT *
FROM   students

输出：

我们已准备好执行分析功能。

RANK（） & DENSE_RANK（）

RANK()函数将根据指定的顺序为分区内的每一行分配特定的排名。如果行在同一分区中具有相同的值，则会为它们分配相同的等级。

让我们通过以下示例更清楚地理解它。

SELECT *,
       Rank()
         OVER (
           ORDER BY final_marks DESC) AS 'ranks'
FROM   students;

输出：

您可以观察到最终标记按降序排列，并且每行都与特定的等级相关联。您还可以观察到分数相同的学生获得相同的排名，跳过重复行后的下一个排名。

我们还可以找到每个科目的佼佼者，即我们可以根据学科划分排名。让我们看看如何做到这一点。

SELECT *,
       Rank()
         OVER (
           PARTITION BY subject
           ORDER BY final_marks DESC) AS 'ranks'
FROM   students;

输出：

在此示例中，我们根据主题对排名进行了分区，并且为每个主题单独分配排名。

注意：请注意，两名学生在化学科目中得分相同，排名为 1，下一行的排名直接从 3 开始。它跳过了 2 的排名。

这是函数的特征，并不总是需要连续生成排名。下一个排名将是前一个排名和重复数字的总和。RANK()

为了克服这个问题，引入了类似于函数的工作方式，但它总是连续分配秩。按照以下示例操作：DENSE_RANK()RANK()

SELECT *,
       DENSE_RANK()
         OVER (
           PARTITION BY subject
           ORDER BY final_marks DESC) AS 'ranks'
FROM   students;

输出：

上图显示所有排名都是连续的，即使重复标记在同一分区中也是如此。

NTILE（）

NTILE()函数用于将行划分为大小大致相等的指定数量的 （N） 个桶。每行分配一个从 1 到 N（存储桶总数）的存储桶编号。

我们还可以在特定的分区或顺序上应用函数，这些函数在 PARTITION BY 和 ORDER BY 子句中指定。NTILE()

假设 N 不能完全被行数整除。然后，该函数将创建不同大小的存储桶，相差一个。

语法：

NTILE(n) OVER (PARTITION BY c1, c2 ORDER BY c3)

该函数采用一个必需的参数 N，即存储桶的数量和一些可选参数，如 PARTITION BY 和 ORDER BY 子句。 将根据这些子句指定的顺序划分行。NTILE()NTILE()

让我们以考虑我们的“学生”表为例。假设我们想根据学生的最终成绩将他们分成几组。我们将创建三个组。第 1 组将包含得分最高的学生。第 2 组将包含所有平庸的学生，第 3 组将包括低分学生。

SELECT *,
       NTILE(3)
         OVER (
           ORDER BY final_marks DESC) AS bucket
FROM   students; 

输出：

上面的示例显示所有行都按顺序排序并分为三组，每组包含五行。final_marks

NTILE()当我们想根据某些指定的标准将某些数据分成相等的组时很有用。它可用于基于购买物品的客户细分或对员工绩效进行分类等应用程序。

CUME_DIST（）

该函数查找指定分区或顺序内每行中特定值的累积分布。累积分布函数 （CDF） 表示随机变量 X 小于或等于 x 的概率。它用 F（x） 表示，其数学公式表示为：CUME_DIST()

P（x） 是概率分布函数。

在简单语言中，函数返回值小于当前行值的行的百分比。这将有助于分析数据的分布以及值与集合的相对位置。CUME_DIST()

SELECT *,
       CUME_DIST()
         OVER (
           ORDER BY final_marks) AS cum_dis
FROM   students; 

输出：

上面的代码将根据所有行进行排序并找到累积分布，但是如果要根据主题对数据进行分区，则可以使用 PARTITION BY 子句。下面是如何执行此操作的示例。final_marks

SELECT *,
       CUME_DIST()
         OVER (
           PARTITION BY subject
           ORDER BY final_marks) AS cum_dis
FROM   students; 

输出：

在上面的输出中，我们看到了按主题名称分区的累积分布。final_marks

STDDEV（） 和 VARIANCE（）

该函数用于查找分区中给定值的方差。在统计学中，方差表示数字与其平均值相距甚远的程度，或者表示数字之间的扩散程度。它由 ？^2 表示。VARIANCE()

该函数用于查找分区内给定值的标准偏差。标准差还测量数据中的变异，它等于方差的平方根。它由 ？.STDDEV()

这些参数可以帮助我们找到数据中的离散性和可变性。让我们看看我们如何实际做到这一点。

SELECT *,
       STDDEV(final_marks)
         OVER (
           PARTITION BY subject) AS marks_stddev,
       VARIANCE(final_marks)
         OVER (
           PARTITION BY subject) AS marks_variance
FROM   students; 

输出：

上面的输出显示了每个科目的最终分数的标准变异和方差。

FIRST_VALUE（） 和 LAST_VALUE（）

该函数将根据特定顺序输出分区的第一个值。同样，该函数将输出该分区的最后一个值。当我们想要识别指定分区的第一次和最后一次出现时，可以使用这些函数。FIRST_VALUE()LAST_VALUE()

语法：

SELECT *,
       FIRST_VALUE(col1)
         OVER (
           PARTITION BY col2, col3
           ORDER BY col4) AS first_value
FROM   table_name

结论

SQL分析函数为我们提供了在SQL服务器中执行数据分析的功能。使用这些功能，我们可以释放数据的真正潜力，并从中获得有价值的见解，以增加我们的业务。除了上面讨论的功能之外，还有更多优秀的功能可以非常快速地解决您的复杂问题。您可以从本文中阅读有关这些分析函数的更多信息 Microsoft.

 

原文链接：数据库内分析：利用 SQL 的分析功能 (mvrlink.com)