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

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

随着数据的指数增长，超快速地分析这些数据变得越来越重要。

1 zettabyte = 1 trillion gigabytes

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

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

对于下面的例子，我使用了下面的表格内容：

运行总数

你可能经常遇到这样的场景：必须从一个表中计算运行总数。这是为了知道每一个值是多少，相对于一个运行总数。

运行总计是指某列中位于下一个单元格之前的所有单元格的值总和。

下面是一个查询。

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

这就是输出的样子：

通用表表达式

通用表表达式（ Common Table Expressions ，简称 CTE ）用于简化复杂连接和子查询的可读性。

它基本上是一个临时的命名结果集，你可以在SELECT, INSERT, UPDATE或DELETE声明

考虑这个简单的查询

SELECT *
FROM bill
WHERE 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 bill
WHERE id in (SELECT id from idtempp)

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

对数据进行排名

数据工程师和分析师会同意，这是非常常见的价值排序基于一些参数，如工资或费用等。而且，掌握排名数据的知识可以为你节省大量查找准确查询的时间。

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

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

也可以用DENSE_RANK()其类似于RANK()除了如果两行的值相同，它不会跳过后续的秩。

添加Subtotals（小计）

同样，对于数据工程师和分析师来说，这是一个非常重要的查询。在我作为业务/数据分析师的 10 年职业生涯中，我用这个查询进行了很多分析。有一个小计可以帮助你把数据放在总的角度。

它是一个GROUP BY子句，可以将小计和总计添加到数据中。

SELECT  
  Type,
  id,
  SUM (Amount) AS total_amount
FROM bill
GROUP BY Type,id WITH ROLLUP

注：上面的查询是在 MySQL 中进行的。其他查询的汇总语法可能会有所不同。

在上面的查询中， type 和 id 都为空的行就是 total 。不管 id 列是什么，都有小计。即由第 4 行和倒数第二行代表。

Temporary Functions（临时函数）

临时函数允许你轻松修改数据，而无需编写庞大的 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 gender
FROM 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的任何内容。这也印证了它甚至还是主流，特别是考虑到它的年龄。

本文转载来自：https://betterprogramming.pub/6-sql-queries-every-data-engineer-should-be-aware-of-2d0a2cc5986e