在这篇文章中，我们将讨论SQL Server数据库引擎所支持的各种分析功能。顾名思义，这些都是一些特殊的功能，我们可以利用这些功能在数据集上执行分析查询并获得有用的结果。与标准的SQL查询相比，有时数据分析师有必要更深入地研究数据，并从分析的角度获得洞察力。这篇文章将讨论SQL Server支持的所有分析功能的一些细节。
SQL Server支持的分析功能
自从引入SQL Server 2012后，分析功能被添加到SQL Server数据库引擎中。在SQL Server 2012之后的任何版本的SQL Server都可以在上面执行分析性查询。这些函数用于计算数据集的聚合值，但基于特定的行集，而不是整个数据集。与像SUM、COUNT、AVG等返回标量记录的聚合函数相比，这些函数可以根据条件返回多条记录。使用这些函数的最常见的例子是寻找移动平均数、运行总数等。SQL Server支持以下分析函数。
- CUME_DIST- 查找一个数字列的累积分布
- FIRST_VALUE- 从组中找出某列的第一个值，并对每一行打印出相同的值
- LAST_VALUE- 从组中找出一列的最后一个值，并为每一行打印相同的内容。
- LAG- 读取某列指定行数之后的值
- LEAD- 读取某列的指定行数之前的数值
为了理解所有这些函数的实际用途，我们将在数据库中创建一些假数据，然后执行相应的查询。你可以使用下面的脚本来创建表和数据，以配合教程的学习。
https://gist.github.com/aveek22/f0d7cf29af149bbc11fb3ba0e2d5e4fc
一旦创建了表，并在表中填充了虚拟数据，你就可以进入下一节了。
使用CUME_DIST函数
CUME_DIST函数用于计算表格中一组行内某一数值的累积分布。在统计学中，累积分布函数，也被称为CDF，用于预测一个给定的随机变量的概率是由小于或等于该数字本身的数值的数量来决定的。简单来说，它可以通过计算样本中的总行数，然后用行的RANK除以样本中的总行数来获得。为了更好地理解，你可以运行以下查询。
选择 LaptopName, LaptopBrand, 发布年份。 设备类型。 价格。 CUME_DIST() OVER(ORDER BY Price) AS PriceDistribution FROM [dbo].[笔记本电脑］ |
图1 - 累积分布
正如你在上图中所看到的，累积分布由列显示 价格分布.这一栏提供了基于行的累积分布。 价格列。它表明分布不受组内记录数的影响，而是受计算分布的列的不同值的影响。
为了更好地解释这个查询的工作，我将尝试把计算分解成更简单的步骤，并解释如何实现分布。我将一个接一个地执行以下步骤，并获得与函数中相同的结果。
- 选择所有记录的唯一组合，包括要计算分布的列的值。在这种情况下，它是价格列
- 计算表格中每一行的RANK值
- 计算列表中记录的总数
- 用RANK除以总的记录数，得到累积分布值
选择 LaptopName, 价格 ,RANK() OVER(ORDER BY Price) AS RowRank ,8 AS TotalRecords ,RANK() OVER(ORDER BY Price)/8.0000 AS PriceDistribution_Manual ,CUME_DIST() OVER(ORDER BY 价格) AS PriceDistribution_ByCumeDist FROM [dbo].[笔记本电脑］ |
图2 - 手动计算累积分布
在上图中，你可以看到，手动计算的累积分布是通过将记录的RANK除以被观察的数据集中的总记录数而得到。例如，如果我们考虑第一条记录，这一行的等级是1，记录总数是8，因此，累积分布将是1/8 = 0.125。同样地，对于第4条记录，其数值为0.5。这意味着在这个观察下的所有记录中有50%位于这个值以下，为 价格.
我们还可以根据数据集中的一组记录来计算累积分布。这可以通过在PARTITION BY子句中指定要分组的列来实现。
SELECT LaptopName, LaptopBrand, 发布年份。 设备类型。 价格。 CUME_DIST() OVER(PARTITION BY DeviceType ORDER BY Price) AS PriceDistribution FROM [dbo].[笔记本电脑]。 |
图3 - 一个组的累积分布
从上图中可以看出，现在正在计算PriceDistribution的每一个值在 设备类型列中的每个值计算。由于 "Business"这个值有四行，所以每一行都是根据这4条记录计算的。通过这种方式，也可以计算特定组的累积分布。
使用FIRST_VALUE和LAST_VALUE分析函数
这是一个基于字符的分析函数，从一组记录中返回该列的第一行值。 例如，它将重复观察中指定的每条记录的第一条记录的值。 你可以参考下面的查询来演示FIRST_VALUE函数。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, FIRST_VALUE(LaptopName) OVER(ORDER BY Price) AS FirstValue FROM [dbo].[笔记本电脑］ |
图4 - 使用FIRST_VALUE函数
正如你在上图中看到的，该列的第一个值 笔记本名称出现在数据集中的每一条记录中。除此之外，FIRST_NAME函数还可以应用于数据集中的一组记录，就像我们对累积分布函数所做的一样。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, FIRST_VALUE(LaptopName) OVER(PARTITION BY LaptopBrand ORDER BY Price) AS FirstValue FROM [dbo].[笔记本电脑］ |
图5 - 每个笔记本电脑品牌的第一价值
正如你在上图中所看到的那样，现在的 第一价值列现在持有的数值是基于来自 笔记本电脑品牌列中的每个值。
与上面类似，SQL Server中的LAST_VALUE函数将基于整个数据集来计算最后的值，或者如果在PARTITION BY子句中提供了特定的列，则基于记录组来计算。然而，与FIRST_VALUE相比，它将只返回当前行的值，而不是整个数据集的最后值。你可以使用下面的查询来使用LAST_VALUE函数。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, LAST_VALUE(LaptopName) OVER(ORDER BY Price) AS LastValue FROM [dbo].[笔记本电脑］ |
图6 - 在查询中使用LAST_VALUE函数
为了打印整个数据集的最后值，你可以使用下面的查询。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, LAST_VALUE(LaptopName) OVER(ORDER BY Price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM [dbo].[笔记本电脑］ |
图7 - 使用LAST_VALUE函数
使用LEAD和LAG分析函数
Lead和Lag是两个常用的分析函数，分别用来寻找一个给定列的下一个或上一个值。这两个函数都接受一个偏移参数来决定在打印结果之前，领先或滞后函数应该考虑多少行。你可以使用下面的查询来使用LEAD和LAG函数。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, LEAD(LaptopName,1) OVER(ORDER BY Price) AS FirstOffset, LEAD(LaptopName,2) OVER(ORDER BY Price) AS SecondOffset FROM [dbo].[笔记本电脑］ |
图8 - 使用LEAD函数
同样的，LAG函数也可以使用，如下所示。
SELECT LaptopName, LaptopBrand, ReleasedYear, DeviceType, Price, LAG(LaptopName,1) OVER(ORDER BY Price) AS FirstOffset, LAG(LaptopName,2) OVER(ORDER BY Price) AS SecondOffset FROM [dbo].[笔记本电脑］ |
图9 - 使用LAG函数
总结
在这篇文章中，我们已经讨论了SQL Server支持的各种分析函数。这些是一些特殊的函数，用来对数据集中特定数量的行进行分析。这些分析函数是在SQL Server 2012版本中首次引入的。一旦你掌握了SQL Server中的这些函数，理解如何将这些函数应用于整个数据集或选定的数据集就变得非常容易。除了这些分析函数外，你还可以对执行分析查询后得到的结果进行简单的聚合。
要详细了解这些查询，你可以关注微软的官方文档。
Aveek是一名经验丰富的数据和分析工程师，目前在爱尔兰的都柏林工作。他的主要技术兴趣领域包括SQL Server、SSIS/ETL、SSAS、Python、Apache Spark、Kafka等大数据工具以及AWS/Amazon和Azure等云技术。
他是一个多产的作者，在各种技术博客上发表了100多篇文章，包括他自己的博客，并经常在不同的技术论坛上发表文章。
在闲暇时间，他喜欢业余摄影，主要是街头图像和静物。在Instagram上可以看到他的一些作品的缩影。你也可以在LinkedIn上找到他
