3年SQL经验，我总结了十条技巧

我在数据岗位工作了三年了，但在看到那些没有SQL知识或经验的人想进入数据领域时，我总是会很惊讶。因为我的观点是，无论你计划做什么数据类职位，学会SQL都是最基本的要求。

当然我也见过一些例外，虽然他们在SQL之外的领域有出色的技能，但在被聘用后也同样需要学习SQL，我认为不学SQL，几乎不可能成为一名数据从业人员。

本文中都是我经常用的SQL技巧，这些技巧可以适用于所有人，虽然不算特别新奇，但也比较有趣。为了方便起见，我把这些按难易程度列了出来。如果你想了解更多数据分析相关内容，可以阅读以下这些文章：
你知道吗？SQL也能做机器学习！
实用SQL函数清单——教你用SQL清理数据
手把手教你用Python创建SQL数据库！
微软数据科学家面试，都问什么SQL问题？

我将用这个SQLite 沙盒数据库展示我的代码：https://www.sql-practice.com/。

01 检查表中的不同值的数量

SELECT count(*), count(distinct patient_id) FROM patients

第一个示例展示了如何检查某个列是否是表中的主键（Primary Key）。这通常用于你自己创建的表中，因为大部分数据库都可以在信息的元数据中列出主键信息。

如果两列中的数字相等，那么你查询的第二部分中计算的列很可能就是主键。

当你有多个列创建一个主键时，会稍微复杂一点点。要解决这个问题，只需在 DISTINCT 关键字之后连接构成主键的列。一个简单的例子是连接沙箱表中的姓和名来创建一个主键。

SELECT count(*), count(distinct first_name || last_name) FROM patients

02 找到重复记录的例子

SELECT
    first_name
    , count(*) as ct
FROM patients
GROUP BY
    first_name
HAVING
    count(*) > 1
ORDER BY
    COUNT(*) DESC
;

沙箱里的表格是你在工作中使用的数据库的简化版本。工作的时候，你可能想研究一下数据库中出现重复值的原因。

可以使用HAVING关键字对那些重复的值进行排序。在沙箱数据库中，你可以看到名字“John”被重复得最多。然后运行另一个查询，过滤到’ John ‘，查看重复值的原因，你可以很快看到它们都有不同的姓氏和患者ID。

03 使用DISTINCT处理空值（Null）

with new_table as (
select patient_id from patients
UNION
select null
)
select
    count(*)
  , count(distinct patient_id)
  , count(patient_id)
from new_table

这个查询的输出会是COUNT(*)列的4531，和其余两个列是的4530。当指定列时，COUNT关键字将排除对空值的计数。但是当你使用星号时，NULLS将包含在计数中。

04 CTE 和子查询（Sub-queries）

-- Use of CTE
with combined_table as (
select
  *
FROM patients p
JOIN admissions a
  on p.patient_id = a.patient_id
)
, name_most_admissions as (
select
    first_name || ' ' || last_name as full_name
  , count(*)                       as admission_ct
FROM combined_table
)
select * from name_most_admissions
;
-- Use of sub-queries :(
select * from
   (select
        first_name || ' ' || last_name as full_name
      , count(*)                       as admission_ct
    FROM (select
             *
          FROM patients p
          JOIN admissions a
              on p.patient_id = a.patient_id
          ) combined_table
    ) name_most_admissions
;

3年前，我刚开始做数据分析师时，我编写的SQL查询包含了特别多的子查询。我很快了解到，这会导致代码不可读。大多数情况下，你更应该使用CTE(公共表表达式)而不是子查询。在用单行语句时，可以保留子查询。

05 同时使用SUM和CASE

select
     sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
   , sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end)   as allergies_oak
from patients

如果你只想把满足条件的患者数量相加到一起，WHERE子句就可以起作用。但是你如果要检查多个条件，可以同时使用SUM和CASE WHEN关键字，它们可以压缩代码，也很易于阅读。

这个组合也可以在WHERE子句中使用，就像下面的例子一样。

select
  *
FROM patients
WHERE TRUE
  and 1 = (case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end)

06 注意日期

with new_table as (
select
    patient_id
  , first_name
  , last_name
  , time(birth_date, '+1 second') as birth_date
from patients
where TRUE
   and patient_id = 1
UNION
select
    patient_id
  , first_name
  , last_name
  , birth_date
from patients
WHERE TRUE
  and patient_id != 1
)
select
  birth_date
from new_table
where TRUE
  and birth_date between '1953-12-05' and '1953-12-06'

这个沙盒数据集的所有日期都被截断在当天。也就是说，本例中的birth_date列的时间都显示的是00:00:00。然而在真实的数据集中，情况通常并非如此。

根据你的SQL开发IDE，你的设置可能会隐藏时间组件。但也仅仅是隐藏的，并不意味着时间不是数据的一部分。

在上面的例子中，我人为地给patient#1的时间数据中添加了一秒钟。如你所见，在使用BETWEEN语句时，这1秒足以将患者排除在结果之外。

我还发现另一个数据专业人员经常犯的错误，是join还带有时间组件的日期。大多数情况下，他们实际想在截断的日期加入，但最终没有得到想要的结果；更糟的是，他们可能根本没发现结果是错的。

07 不要忘记窗口函数

select
    p.*
  , MAX(weight) over (partition by city) as maxwt_by_city
 from patients p

窗口函数一个好办法，可以保存所有数据行，然后附加重要聚合到另一个列。在本例中，我们能够保留所有数据，但按城市列添加一个最大权重列。

我看到过一些分析人员在工作中灵活变通，用窗口函数可以使代码更短、更易读，并且很可能更节省他们的时间。

有很多不同的窗口函数，但上面的例子是一个常见而简单的用例。

08 尽量避免Distinct

下面的3个技巧没有特定的代码来展示，但是它们和上面的例子一样重要。在我的职业生涯中，我发现数据专家经常会在不了解数据的情况下添加一个distinct来防止重复。

这是不对的。如果你不能解释为什么数据中有重复的内容，那么可能会错误地从分析中排除一些有用的信息。你需要能解释为什么要在表中使用distinct，以及为什么会有重复的数据。通常情况下，首选WHERE子句，因为你可以看到被排除的内容。

09 SQL格式

可能你已经听过很多次了，但我还是要啰嗦一句。让你的SQL代码有一个统一的格式。最好是创建更多格式良好的行，而不是单纯将所有代码压缩到几行。

你可以在上面的代码片段中看到，我在WHERE子句中使用了TRUE关键字。这可以让WHERE子句中的所有参数都以AND开头。

另一个小技巧是在SELECT子句下面的行的开头添加一个逗号，这样可以让任何缺少的逗号的句子都很容易找到。

10 调试技巧

一些SQL查询可能会遇到非常困难的调试问题。我以前遇到这些问题时，对我帮助最大的是非常勤奋地记录我的步骤。

为了记录我的步骤，我会在查询的每一个部分写出注释，描述我在这部分想做的事情。然后，在运行查询之后，我将在每条注释下面写出我的答案。

这样，在调试时，你可以很容易看到你已经尝试过的内容；使用这种方法，你将会更快地解决问题。

结论

希望你从这篇文章中学到了一些有用的东西。在使用SQL编写代码时，你发现了哪些有用的技巧？我也很期待听到你的建议，请在评论中分享，谢谢！你还可以订阅我们的YouTube频道，观看大量数据科学相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Andreas Martinson
翻译作者：过儿
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://towardsdatascience.com/10-quick-sql-tips-after-writing-daily-in-sql-for-3-years-37bdba0637d0