最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

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

XAMPP相关 admin 27浏览 0评论

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

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

drd061

代码如下:1 zettabyte = 1 trillion gigabytes

 

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

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

对于下面的例子,我使用了下面的表格内容:

drd0061

运行总数

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

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

下面是一个查询。

代码如下:

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

FROM bill

 

这就是输出的样子:

drd00061

通用表表达式

通用表表达式( Common Table Expressions ,简称 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 idFROM id

WHERE country = “US”

AND status = “Y”

)

 

SELECT *

FROM bill

WHERE id in (SELECT id from idtempp)

 

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

 

对数据进行排名

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

代码如下:

SELECTid,Amount,

RANK() OVER (ORDER BY Amount desc)

FROM bill

 

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

 

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

 

添加Subtotals(小计)

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

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

代码如下:

SELECTType,id,

SUM (Amount) AS total_amount

FROM bill

GROUP BY Type,id WITH ROLLUP

drd000061

注:上面的查询是在 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

 

方差和标准差

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

VARIANCEVAR_POPVAR_SAMP是聚合函数,即它们对数据进行分组。这些用于单独确定数据收集的方差、组方差和样本方差。

代码如下:

SELECTVARIANCE(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的任何内容。这也印证了它甚至还是主流,特别是考虑到它的年龄。

转载请注明:XAMPP中文组官网 » 每个数据工程师都应该知道的6个SQL查询