给数据科学家和数据分析师的 4 个 SQL 技巧

照片由Campaign Creators在Unsplash上提供

在过去十年中，SQL 已成为各行各业和各种职位的常见技能要求。

像亚马逊和谷歌这样的公司通常会要求他们的数据分析师、数据科学家和产品经理至少熟悉 SQL。这是因为 SQL 仍然是数据的语言。因此，为了实现数据驱动，人们需要知道如何访问和分析数据。

由于有如此多的人查看、切分、操作和分析数据，我们希望提供一些技巧来帮助改进您的 SQL。

这些技巧和窍门是我们编写 SQL 过程中积累的。有些是应该做的，有些是不应该做的，有些则是最佳实践。总而言之，我们希望它们能帮助您将 SQL 提升到一个新的水平。

有些技巧会告诉你即使你可能想做也不应该做的事情，而有些技巧则是帮助你确保数据可信的最佳实践。总而言之，这些技巧既能提供信息，又能减少未来可能出现的麻烦。

不要对平均值使用 Avg() ，这是不一样的

我们在用户查询中发现的一个常见错误是平均平均值。有些人可能认为不平均平均值是显而易见的。然而，网络上有很多讨论和整篇文章解释了为什么平均平均值是错误的。

那么，为什么在 SQL 中以及一般情况下，对平均值进行平均是错误的呢？因为基于较少的平均值计算出来的平均值可能会造成偏差。

例如，让我们看看下表。

我们已经在表格中按县级计算了每起索赔的平均成本。我们还可以看到，一个县的平均值基于 100 起索赔，另一个县的平均值基于 2 起索赔。通常情况下，此表可能不包含索赔总数，我们用它来显示平均值的偏差有多么容易。

如果我们想求所有县的平均值怎么办？如果你尝试取平均值，结果会是 525 美元。这似乎不对。

如果 100 项索赔的平均金额为 50 美元，而只有 2 项索赔的平均金额为 100 美元，那么所有这些索赔的平均值应该更接近 50 美元，而不是 500 美元。

实际上，这些索赔的平均金额约为 68 美元。但是，如果取平均值，就会得到几乎 10 倍的数字。

那么为什么人们甚至会问平均平均值是否可以呢？

我们认为有时平均值有时可以接近预期的输出。

让我们看一个 SQL 示例。

SELECT patient_county
    ,avg(avg_visits_per_patient)*1.0 avg_visits
    ,avg(avg_cost_per_patient)*1.0 avg_cost
    ,'Avg of Averages' table_type
FROM agg_patient_counties 
GROUP BY patient_county,table_type

在这种情况下，我们将使用一个表格，其中包含按县和年龄划分的每位患者的平均费用和每位患者的平均就诊次数。

然而，我们想了解县级每位患者的平均费用和每位患者就诊次数。

我们可以使用上面的查询来计算该表的平均值，它会给我们下面的输出。

现在，如果我们正确编写一个查询，以县粒度重新计算平均值，如下面的查询所示：

SELECT 
    patient_county
    ,count(*)*1.0/count(distinct c.patient_id) avg_total_visits
    ,sum(cast(claim_cost AS decimal))/count(distinct c.patient_id) avg_total_costs
    ,'Correct Way' as table_type
FROM patients p
JOIN claims c ON p.patient_id = c.patient_id
GROUP by patient_county,table_type

现在让我们将此查询输出与之前的输出进行比较。

您会注意到金县的输出存在一些差异。

因此，如果我们比较平均访问量，它们实际上看起来非常相似，分别为 2.4 和 2.6。这就是为什么我们认为有些人容易被平均值所迷惑。这些平均值有时可能更接近实际结果，因此使用这种方法可能很有吸引力。

然而，当我们查看每笔索赔的平均成本时，我们会发现560美元和620美元之间有近58美元的差额。这几乎是10%。说到成本节约，这是一个巨大的差异。

因此，尽管 2.4 与 2.6 看起来微不足道，但它可能会导致巨大的差异。

最后，不要平均平均值。

你可以在 Sum 中使用 Case 语句

编写 SQL 的另一个重要技巧是学习如何在 sum 子句中使用 case 语句。当你尝试编写带有比率或分子的指标时，这会非常有用。

例如，让我们看看下面的查询。你会发现，我们需要两次访问 claims 表才能获得我们尝试过滤的值的数量以及总行数。但是，我们可以减少这个次数。

SELECT total_claims_over_500 * 100.0 / COUNT(*)
FROM claims
JOIN (
    SELECT COUNT(*) total_claims_over_500
    FROM claims
    WHERE CAST(claim_cost AS INT) > 500
    ) t1 ON 1 = 1
GROUP BY total_claims_over_500

我们可以编写一个 case 语句来计算条件为真的总值，然后除以总数，就像下面的查询一样。

SELECT SUM(CASE 
        WHEN CAST(claim_cost AS INT) > 500
            THEN 1
        ELSE 0
        END) * 100.0 / COUNT(*) perc_claims_over_500
FROM claims

你会注意到，我们不需要两次访问表格来获取这两个数字。此外，这更易于阅读。

根据我们的经验，大多数 SQL 开发人员通常会在使用 SQL 的第一年或第二年内掌握这个技巧。

它在编写用于统计一行中空值百分比的代码或计算仪表板指标时非常有用。因此，许多分析师和数据工程师只要会编写大量 SQL 代码，而不是仅仅使用拖放式解决方案，就会熟悉这个技巧。

理解数组以及如何操作它们

数据库表中的数组和映射并不常见。然而，我们注意到越来越多的团队依赖于非结构化数据，而这些数据通常可以利用数组和数组函数等数据结构。

这是因为像 Postgres 这样的数据库和像 Presto 这样的 SQL 引擎允许您在查询中处理数组。

尽管数组和映射不是一个新概念，但对于一些不太熟悉编程的分析师和数据科学家来说，它们是一个有点新的概念。

这意味着您可能需要偶尔学习一些数组和映射函数来提取数据。

让我们先学习如何在 Presto 中解除 Map 的嵌套。Map 是一种提供键值关系的数据结构。这意味着您可以提供一个唯一的键，例如对值的具体描述，例如 "first_name":"George"。Map 还可以包含多个键值对，如下图所示。

在这种情况下，我们有两个想要访问的键，dob 和 friend_ids。

那么我们如何访问这些数据呢？让我们看看下面的查询。


SELECT username
    ,key
    ,value
FROM user_info u
    ,json_each_text(user_data) i

如您所见，您可以为键和值定义一行。因此，当我们提取数据时，您可以获取特定的数据类型。

输出如下图所示。

您还可以检查数组的长度、查找特定的键等等（点击此处了解更多关于 Presto 数组的信息）。我们建议您不要仅仅使用 Map 和数组来替代良好的数据建模。然而，当您处理不需要特定模式的数据时，它们会非常有用。

提前和滞后以避免自连接

最后，让我们讨论一下使用 Lead 和 Lag 窗口函数来避免自连接。

当您进行分析时，您经常需要比较两个事件的输出或计算两个事件之间的时间量。

一种方法是将一个表自连接起来，并连接两行。此外，另一个好用的 SQL 函数是 Lag 和 Lead 函数。

这些允许用户引用指定的滞后值或前导值。您还可以指定获取滞后值和前导值的粒度。

例如，在下面的查询中，我们根据 patient_id 对滞后值和领先值进行划分。这意味着我们只在患者层面查看滞后和领先的 claim_dates 和 claim_costs。


WITH claim_cte
AS (
    SELECT patient_id
        ,claim_date claim_date
        ,claim_cost
        ,lag(claim_date) OVER (
            PARTITION BY patient_id ORDER BY claim_date
            ) previous_claim_date
        ,lag(claim_cost) OVER (
            PARTITION BY patient_id ORDER BY claim_date
            ) previous_claim_cost
    FROM claims
    )
SELECT claim_date - previous_claim_date days_between_dates
    ,patient_id
    ,claim_date
    ,claim_cost
    ,previous_claim_date
    ,previous_claim_cost
FROM claim_cte