10 个有用的 SQL 技巧

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

这些技巧解决了您作为数据专业人员会遇到的常见 SQL 问题

我已经成为数据专业人士 3 年了，看到其他人在几乎没有 SQL 知识或经验的情况下进入数据相关领域，我仍然感到惊讶。这一点我怎么强调都不为过，无论您渴望获得什么特定的数据角色，SQL 都是基础。

诚然，我见过一些在 SQL 以外的其他领域拥有出色技能的人获得了这份工作，但他们在被录用后仍然必须学习 SQL。我认为在不必学习 SQL 的情况下成为一名数据专业人士几乎是不可能的。

这些 SQL 技巧适用于所有人，无论您有多少经验。这些是我实际上经常使用的 SQL 技巧，不是一些新奇的东西，可能很有趣，但实际上并不适合您的工作流程。为方便起见，我按难度顺序列出了这些。


1. 检查表中的不同计数

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

复制代码

第一个示例显示了如何检查列是否是表中的主键。当然，这通常会在您创建的表中使用，因为大多数数据库都可以选择在信息模式元数据中列出主键。

如果两列中的数字相等，那么您在查询的第二部分中计算的列可能是主键。这不是保证，但它可以帮助您解决问题。

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

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

复制代码

2. 查找重复记录的示例

1. SELECT
   
2.     first_name
   
3.     , count(*) as ct
   
4.    
   
5. FROM patients
   
6. GROUP BY
   
7.     first_name
   
8. HAVING
   
9.     count(*) > 1
   
10. ORDER BY
    
11.     COUNT(*) DESC
    
12. ;

复制代码

沙盒表是您将在工作中使用的数据库的过度简化版本。很多时候，您会想要查看数据库中重复值的原因。这就是下一个查询派上用场的地方。

您可以使用 HAVING 关键字对重复的值进行排序。在沙盒数据库中，您可以看到名字“John”的重复频率最高。然后，您将运行另一个查询，在其中过滤到“John”以查看重复值的原因，并且您可以快速看到它们都有不同的姓氏和患者 ID。


3.用 DISTINCT 处理 NULLS

1. with new_table as (
   
2. select patient_id from patients
   
3. UNION
   
4. select null
   
5. )
   
6. 
   
7. select
   
8.     count(*)
   
9.   , count(distinct patient_id)
   
10.   , count(patient_id)
    
11. 
    
12. from new_table

复制代码

此查询的输出将为 COUNT(*) 列的 4531 和其余两个列的 4530。当您指定一列时，COUNT 关键字将排除计数空值。但是，当您使用星号时，NULLS 包含在计数中。在检查列是否为主键时，这可能会令人困惑，这就是我想提及它的原因。

4. CTE > 子查询

1. -- Use of CTE
   
2. with combined_table as (
   
3. select
   
4.   *
   
5. 
   
6. FROM patients p
   
7. JOIN admissions a
   
8.   on p.patient_id = a.patient_id
   
9. )
   
10. 
    
11. , name_most_admissions as (
    
12. select
    
13.     first_name || ' ' || last_name as full_name
    
14.   , count(*)                       as admission_ct
    
15.   
    
16. FROM combined_table
    
17. )
    
18. 
    
19. select * from name_most_admissions
    
20. ;
    
21. 
    
22. -- Use of sub-queries :(
    
23. select * from
    
24.    (select
    
25.         first_name || ' ' || last_name as full_name
    
26.       , count(*)                       as admission_ct
    
27.   
    
28.     FROM (select
    
29.              *
    
30. 
    
31.           FROM patients p
    
32.           JOIN admissions a
    
33.               on p.patient_id = a.patient_id
    
34.           ) combined_table
    
35.     ) name_most_admissions
    
36. ;

复制代码

3 年前我刚开始担任数据分析师时，我编写的 SQL 查询中的子查询比我想承认的要多。我很快了解到这不会产生可读的代码。在大多数情况下，您希望使用 CTE（公用表表达式）而不是子查询。为要包含的单行语句保留子查询。

5. 一起使用 SUM 和 CASE

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

复制代码

如果您想对满足特定条件的患者数量求和，WHERE 子句可以工作。但是如果要检查多个条件，可以同时使用 SUM 和 CASE WHEN 关键字。这压缩了代码并且易于阅读。

这种组合也可以用在 WHERE 子句中，如下例所示。

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

复制代码

6.注意日期

1. with new_table as (
   
2. select
   
3.     patient_id
   
4.   , first_name
   
5.   , last_name
   
6.   , time(birth_date, '+1 second') as birth_date
   
7. 
   
8. from patients
   
9. where TRUE
   
10.    and patient_id = 1
    
11. 
    
12. UNION
    
13.   
    
14. select
    
15.     patient_id
    
16.   , first_name
    
17.   , last_name
    
18.   , birth_date
    
19. 
    
20. from patients
    
21. WHERE TRUE
    
22.   and patient_id != 1
    
23. )
    
24. 
    
25. select
    
26.   birth_date
    
27.   
    
28. from new_table
    
29. where TRUE
    
30.   and birth_date between '1953-12-05' and '1953-12-06'

复制代码

此沙盒数据集将所有日期截断为当天。这意味着此示例中的birth_date 列的时间分量都是 00:00:00。然而，在现实世界的数据集中，通常情况并非如此。

根据您的 SQL 开发 IDE，您的设置可能会隐藏显示时间组件。但仅仅因为时间是隐藏的，并不意味着它不是数据的一部分。
在上面的示例中，我人为地为患者 #1 添加了第二个。如您所见，当使用 BETWEEN 关键字时，这 1 秒足以将患者从结果中排除。

我看到数据专业人员缺少的另一个常见示例是加入仍然具有时间组件的日期。大多数情况下，他们实际上打算在截断日期加入，但最终没有得到他们想要的结果；或者更糟糕的是，他们没有意识到他们没有得到正确的结果。


7.不要忘记窗口函数

1. select
   
2.     p.*
   
3.   , MAX(weight) over (partition by city) as maxwt_by_city
   
4.    
   
5. from patients p

复制代码

窗口函数是保留所有数据行然后附加具有重要聚合详细信息的另一列的好方法。在这种情况下，我们能够保留所有数据，但按城市列添加最大权重。

我见过一些分析师尝试变通方法，因为窗口函数可以使代码更短、更易读，并且很可能也节省了他们的时间。

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


8. 尽可能避免 DISTINCT
以下最后 3 个提示没有要显示的特定代码片段，但与上述示例一样重要。到目前为止，我在我的职业生涯中发现，数据专业人员经常会在不了解数据的情况下添加不同的内容以防止重复。

这是个错误。如果您一开始就无法解释为什么数据中存在重复项，那么您可能会从分析中排除一些有用的信息。你应该总是能够解释为什么你在一张桌子上放一个 distinct 以及为什么会有重复。WHERE 子句通常是首选，因为您可以看到被排除的内容。


9. SQL 格式化
这已经说了很多，但需要重复。确保格式化您的 SQL。最好创建更多具有良好格式的行，而不是尝试将所有代码压缩在几行上。它将使您和他人的发展更快。最好创建更多具有良好格式的行，而不是尝试将所有代码压缩在几行上。它将使您和他人的发展更快。

您可以在上面的代码片段中看到，我在 WHERE 子句中使用了 TRUE 关键字。这是为了让 WHERE 子句中的所有参数都以 AND 开头。这样，论点从同一点开始。

另一个快速提示是在 SELECT 子句的列开头添加逗号。这使得任何缺少的逗号都很容易找到，因为它们都排成一行。

10. 调试提示
一些 SQL 查询可能是非常难以调试的问题。当我过去遇到这些问题时，对我帮助最大的是非常勤奋地记录我的步骤。
为了记录我的步骤，我将在查询前的注释中对一段代码进行编号。该评论描述了我在该查询部分中尝试执行的操作。然后我会在运行查询后在评论标题下方写下我的答案。

在调试时很容易看到你已经尝试过的东西，我保证你会用这种方法更快地解决它。

结论
希望您通过这些技巧学到了一些有用的东西。在使用 SQL 编码时，您发现了哪些有用的提示？最后，你想了解更多关于数据库的内容，请点击下方地址：
cda
https://edu.cda.cn/goods/show/651?targetId=2470&preview=0
题库

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏2 回帖

关键词：sql Admission partition patients Distinct

SQL必备

数据分析的第一步就是取数

特征工程的第一步是数据清洗，取数工作做的好，清洗也容易

感谢分享

感谢分享