搞定SQL！5个棘手SQL查询的解决方法

骑猪看日落 2022-12-11 15:03 107阅读 0赞

![format_png][]

全文共**3023**字，预计学习时长**8**分钟

![format_png 1][]

图源：unsplash

对于数据科学家来讲，SQL（结构化查询语言）是其工具箱中最重要的工具之一。掌握SQL不仅有助于你在面试中脱颖而出，而且通过解决复杂查询达到对SQL的充分理解，还能在让你许多竞争中保持领先地位。

本文就将介绍5个有关SQL的棘手问题和其解决方法。注意，每个查询都能以不同方式编写。在参考本文解决方案之前，你可以先试着自己思考一下。

![format_png 2][]

**查询1**

下列表格由名字和职业两列组成。需要查询所有姓名，且使其后紧跟一个括号，括住“职业”列中对应的首字母。

![format_png 3][]

·        本文解决方案

SELECTCONCAT(Name, ’(‘, SUBSTR(Profession, 1, 1), ’)’)FROM table;

由于需要把名字和职业结合起来，可以使用CONCAT。而且因为括号内只需要一个字母，可以使用SUBSTR来传递列名、开始索引和结束索引。因为只需要首字母，所以我们将传递1，1（开始索引包括在内，结束索引不包括在内）。

![format_png 2][]

**查询2**

蒂娜需要从她创建的EMPLOYEES表中计算所有员工的平均工资，但结果显示的平均值很低，这可能是键盘上的回零键失效了。她希望我们帮助找出错误计算的平均值和实际平均值之间的差异。我们须编写一个查找错误的查询（实际平均值-计算平均值）。

![format_png 4][]

·        本文解决方案

SELECT AVG(Salary) - AVG(REPLACE(Salary, 0, ’’)) FROM table;

需要注意，只有一个表包含了实际工资值。为了创建错误场景，使用REPLACE替换0。接着传递列名、替换值以及用于替换REPLACE方法的值。然后，使用聚集函数AVG来求平均值的差。

![format_png 2][]

**查询3**

给定一个表，它是由节点和父节点两列组成的二元搜索树。需要编写一个查询，以返回按节点值进行升序排序的节点类型。有3种类型：

·        根（Root）——如果节点是根

·        叶（Leaf）——如果节点是叶

·        内部（Inner）——如果节点既不是根也不是叶

![format_png 5][]

·        本文解决方案

经过初步分析，可以得出结论：如果给定节点N的相应P值为NULL(空)，则它是根。而如果给定节点N存在于P列中，则它不是内部节点。基于此想法编写一个查询。

SELECT CASE    WHEN P IS NULL THENCONCAT(N,   Root )    WHEN N IN (SELECTDISTINCT P from BST) THEN CONCAT(N,   Inner )    ELSE CONCAT(N,   Leaf )    ENDFROM BSTORDER BY N asc;

可使用CASE作为开关函数。正如前文提到的，如果对于给定节点N，P为空值，则N是根。因此，我们使用CONCAT来组合节点值和标签。

类似地，如果给定节点N存在于P列中，则它是内部节点。为了获得P列中的所有节点，我们编写了一个返回P列中所有不同节点的子查询。由于要求按节点值升序对输出进行排序，因此要使用ORDER BY子句。

![format_png 2][]

**查询4**

该事务表由transaction\_id, user\_id, transaction\_date,product\_id, and quantity（交易ID，用户ID，交易日期，产品ID和数量）组成。需要查询多天来购买产品的用户数量（注意，给定用户可以在一天内购买多个产品）。

![format_png 6][]

·        本文解决方案

为了解决该查询，不能直接计算user\_id的出现次数，由于给定用户在一天中可以多次购买，user\_id或许会有多次返回。因此，只有当存在多个不同日期与给定的user\_id相关联时，才意味着该用户多天购买了产品。按照相同方法，进行查询编写。（内部查询）

SELECT COUNT(user_id)FROM(SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(DISTINCT DATE(date))> 1) t1

由于问题询问的是user\_id的数量，而不是user\_id本身，因此在外部查询中使用 COUNT 。

![format_png 2][]

**查询5**

给定一个订阅表，其中包含每个用户订阅的开始和结束日期。需要编写一个查询，根据与其他用户的日期重叠情况，为每个用户返回true/false。例如，如果user1的订阅周期与其他任何用户重叠，则查询必须为user1返回true。

![format_png 7][]

·        本文解决方案

经过初步分析，我们可以知道必须将每项订阅与其他订阅进行比较。将userA的开始和结束日期视为startA 和endA，类似地，userB也依此设为startB和endB。如果startA≤endB且endA≥startB，则可以说这两个日期范围重叠。我们来举两个例子，先比较一下U1和U3：

startA = 2020–01–01endA = 2020–01–31startB = 2020–01–16endB = 2020–01–26

这里可以看出，startA(2020–01–01)小于endB(2020–01–26)，那么同样，endA(2020–01–31)大于 startB(2020–01–16)，因此可以得出结论，日期重叠。类似地，如果比较U1和U4，上述条件就不成立，于是返回FALSE。

这里还必须确保不会将用户与其自己的订阅进行比较。同时希望运行一个左连接，能够自行将用户与满足条件的其他用户进行匹配。现在，我们将创建同一表的两个副本S1和S2。

SELECT *FROM subscriptions AS s1LEFT JOIN subscriptions AS s2    ON s1.user_id != s2.user_id        AND s1.start_date <=s2.end_date        AND s1.end_date >=s2.start_date

给定条件连接，在日期之间存在重叠的情况下，对于S1中的每个user\_id，应该存在来自S2的user\_id。

·        输出

![format_png 8][]

可以看到，以防日期重叠，每个用户都有一个对应用户。对于user1，有2行显示其与2个用户相匹配。对于用户4，对应的ID为空，表示他与其他任何用户都不匹配。现在，将其全部组合在一起，按照s1.user\_ID字段进行分组，并检查s2.user\_ID不为空的用户的值是否为真。

·        最终查询

SELECT    s1.user_id    , (CASE WHEN s2.user_idIS NOT NULL THEN 1 ELSE 0 END) AS overlapFROM subscriptions AS s1LEFT JOIN subscriptions AS s2    ON s1.user_id != s2.user_id        AND s1.start_date <=s2.end_date        AND s1.end_date >=s2.start_dateGROUP BY s1.user_id

使用 CASE子句根据给定用户的s2.user\_id值来标记1和0。最终输出如下：

![format_png 9][]

掌握SQL需要大量实践，今天就从解决这5个棘手问题开始吧！

![format_png 10][]

**推荐阅读专题**

[![format_png 11][]][format_png 11 1]

[![format_png 12][]][format_png 12 1]

[![format_png 13][]][format_png 13 1]

[![format_png 14][]][format_png 14 1]

![format_png 15][]

**留言点赞发个朋友圈**

**我们一起分享AI学习与发展的干货**

编译组：郝岩君、黄壹格

相关链接：

https://medium.com/towards-artificial-intelligence/5-tricky-sql-queries-solved-919266e2d524

如转载，请后台留言，遵守转载规范

**推荐文章阅读**

[ACL2018论文集50篇解读][ACL2018_50]

[EMNLP2017论文集28篇论文解读][EMNLP2017_28]

[2018年AI三大顶会中国学术成果全链接][2018_AI]

[ACL2017论文集：34篇解读干货全在这里][ACL2017_34]

[10篇AAAI2017经典论文回顾][10_AAAI2017]

**长按识别二维码可添加关注**

**读芯君爱你**

![format_png 16][]

[format_png]: /images/20221123/bcb0049ea213413d9b0762d18cfa1271.png
[format_png 1]: /images/20221123/20cc5f0b06ed4a11b2d642066a1a55bb.png
[format_png 2]: /images/20221123/7c350ce2b10e4078b73c4737fbf07ba9.png
[format_png 3]: /images/20221123/48d5714e2c264516aa52d4b03c80241f.png
[format_png 4]: /images/20221123/76307774b874400ca2fb23cf16adb56f.png
[format_png 5]: /images/20221123/fbcd58c0d84041dab3749c4c2a2c8cc4.png
[format_png 6]: /images/20221123/623ab457a1ea4d55b4a99f92d89337e3.png
[format_png 7]: /images/20221123/e692783b07664cdf929c8ffee1027f38.png
[format_png 8]: /images/20221123/af49ae1a158b4a09be4c5f34733f7f57.png
[format_png 9]: /images/20221123/328180a4a75948a78a8291e78335f13d.png
[format_png 10]: /images/20221123/44dcf6487da64b7787183f0e886a12fa.png
[format_png 11]: /images/20221123/842b61d914514f35b85c9ad1dc609c97.png
[format_png 11 1]: http://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea841ec4ddf397d2bb194670056d7ed973cf4349656e20d98bd139d8a5d99526a799fb243f8d&idx=1&mid=2247493138&scene=21&sn=d2fa895e71260f2576fa952cf2487522#wechat_redirect
[format_png 12]: /images/20221123/c34e99a87e92474990c529a66a2a6072.png
[format_png 12 1]: http://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea841ed9ddf397cf9a7136dfb1352cf9fb1127b3061293cf716f8ee95b4560e694fce20a1e24&idx=2&mid=2247493135&scene=21&sn=5711dc1074411b7e45b40eb34fecc648#wechat_redirect
[format_png 13]: /images/20221123/65314cbe72e64051ae36f49424a338e6.png
[format_png 13 1]: http://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea841ec4ddf397d28ed77a111732be52384c9be414e0aad59c570ada6b29278755d066ae2090&idx=2&mid=2247493138&scene=21&sn=e45fe93b00342ba9eac222f24369d036#wechat_redirect
[format_png 14]: /images/20221123/11dd896f3ecf4501bf46b30361b42574.png
[format_png 14 1]: http://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea841ed9ddf397cfbc27f47a160ee926a706f1b3151c53f707c8f221fde0ea51446aabe2148d&idx=1&mid=2247493135&scene=21&sn=cce4cc400933c960a3200fd0dfa4d0a7#wechat_redirect
[format_png 15]: /images/20221123/63001277add440199f1ee50ff5e20222.png
[ACL2018_50]: https://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea87ee2addf0673cae27c598a097f5addad5d81b2ed766e570f20fc0fb12b77c5cf34098022e&idx=1&lang=zh_CN&mid=2247489276&scene=21&sn=98e22e2b69478303f1f1a0a51e712570&token=192322725#wechat_redirect
[EMNLP2017_28]: https://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea87eaadddf063bb3ab4ec5af616612a45f8d4fe9a5ad558f66278478296060253c1564bd05c&idx=1&lang=zh_CN&mid=2247488123&scene=21&sn=a55185f3a693f7a5d85b1abe3dd36177&token=140500370#wechat_redirect
[2018_AI]: https://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea87f6ebddf07ffd8e84bcc18d7122f894b6cf5dc967daad9c2e122edb22968fd6f589d6852f&idx=1&mid=2247487037&scene=21&sn=37cd38b305aa874c29d764361c88a202#wechat_redirect
[ACL2017_34]: https://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&chksm=ea87f59addf07c8c2480dd46784a431612d099d3d0531449aa8e64f68b3ba340363578d95659&idx=1&mid=2247486796&scene=21&sn=a8ae95efbf16985a259c4cd66bf919f2#wechat_redirect
[10_AAAI2017]: https://mp.weixin.qq.com/s?__biz=MzI2NjkyNDQ3Mw%3D%3D&idx=1&lang=zh_CN&mid=2247487100&scene=21&sn=3bd640ec4f00c5104664687194dd9a51&token=77118319#wechat_redirect
[format_png 16]: /images/20221123/04fcad0ed54a411d912d4bf47aa7d67f.png