每个数据工程师都应该知道的 6 个 SQL 查询

无论您是刚开始工程生涯的初学者，还是经验丰富的数据工程师或数据分析师，都必须了解高级 SQL 语法。

随着数据呈指数级增长，超快速地分析这些数据变得更加重要。

此图中的单位是 zettabytes。

1 ZB = 1 trillion GB

人们可能会说 SQL 已经死了，但现实是目前没有系统可以替代它。有许多非常强大的 NoSQL 存储可以很好地完成他们的工作，以低成本支持大规模横向扩展。然而，它们并没有取代基于 SQL 的高质量存储——它们是对它们的补充。SQL 的 ACID 属性使其成为一种相对自然地对数据建模的高度可靠的方法。

作为一名数据工程师，我使用 SQL 已经有一段时间了，我知道更快地编写复杂查询的重要性。所以，这里有一些高级 SQL 语法肯定会派上用场。

对于以下示例，我使用了下表内容：

一、总计

您经常会遇到必须从表中计算运行总计的情况。

运行总计是指在该特定列中的下一个单元格之前，所有单元格中的值的总和。

这个简单的查询案例可以做到这一点。

SELECT id,month , Amount , SUM(Amount) OVER (ORDER BY id) as total_sumFROM bill

输出如下

二、常用的表达式

通用表表达式或简称 CTE 用于简化复杂连接和子查询的可读性。

它基本上是一个临时命名的结果集，你可以在内部引用SELECT，INSERT，UPDATE，或DELETE语句。

考虑这个简单的查询，

SELECT *FROM billWHERE id in   (SELECT DISTINCT id    FROM id    WHERE country = "US"   AND status = "Y"  )

现在想象一下，如果我们在后续查询中多次使用这个子查询。如果我们可以将它用作临时表会不会更容易？CTW 解决了这个问题。

WITH idtempp as (  SELECT id as id  FROM id   WHERE country = "US"  AND status = "Y")SELECT *FROM billWHERE id in (SELECT id from idtempp)

这是一个小例子，但这通常对于更大和复杂的子查询非常有用。

三、对数据进行排名

SELECT   id,  Amount,  RANK() OVER (ORDER BY Amount desc)FROM bill

在下面的查询中，我根据数量列对数据集进行了排名。

您还可以使用DENSE_RANK()which ，类似于RANK()，除了如果两行具有相同的值它不会跳过后续排名。

四、添加小计

同样，对于数据工程师和分析师来说，这是一个非常重要的查询。在我作为业务/数据分析师的 10 年职业生涯中，我使用此查询进行了大量分析。拥有小计可帮助您从总计的角度看待数据。

它是一个GROUP BY子句的扩展，能够将小计和总计添加到您的数据中。

SELECT    Type,  id,  SUM (Amount) AS total_amountFROM billGROUP BY Type,id WITH ROLLUP

注意：上面的查询是在 MySQL 中的。其他数据库系统的汇总语法可能会有所不同。

在上面的查询中，type 和 id 都为 null 的行是总数。无论 id 列如何，都有小计。这由第 4 行和倒数第二行表示。

五、临时函数

临时函数允许您轻松修改数据，而无需编写大量 case 语句。

在下面的示例中，临时函数用于将类型转换为性别。这可以在查询中使用 case 语句内联来完成，但阅读起来会很混乱。

CREATE TEMPORARY FUNCTION get_gender(type varchar) AS (   CASE WHEN type = "M" THEN "male"        WHEN type = "F" THEN "female"        ELSE "n/a"   END)SELECT   name,  get_gender(Type) as genderFROM bill

六、方差和标准差

对于数据科学家和分析师来说，获得方差和标准差的能力至关重要。值得庆幸的是，有一些函数可以获取这些值。

的VARIANCE，VAR_POP和VAR_SAMP是集合函数即它们组的数据。这些用于分别确定一组数据的方差、组方差和样本方差。

SELECT   VARIANCE(amount) AS var_amount,  VAR_POP(amount) AS pop_amount,  VAR_SAMP(amount) AS samp_amount,  STDDEV_SAMP(amount) as stddev_amount,  STDDEV_POP(amount) as stddev_amount,FROM bill

VAR_POP: 样本方差

VAR_SAMP: 总体方差

STDDEV_SAMP: 样本标准差

STDDEV_POP: 总体标准差

这些是我在数据工程生涯中经常使用的一些顶级 SQL 命令。这些对于解决许多业务问题非常方便。统计数据显示，超过 60%的组织使用 SQL 工具生态系统，包括从 Excel 和 Tableau 到 SparkSQL 的任何内容。考虑到 SQL 语言的古老性，这些成就是非常值得称道的。

所以，如果你是一名数据工程师，我相信你会发现这些命令很有用。

文丨 Soundhearer

图丨来源于网络

成为云上原住民~

公众号：云原生

官网：knative.cn

 欢迎关注我们