对于数据科学家来说，SQL（结构化查询语言）是他们工具箱中比较重要的工具之一。掌握SQL不仅可以帮助您在面试中脱颖而出，而且通过解决复杂的查询来深入了解SQL也可以让您在许多竞争中保持领先地位。本文将介绍SQL的5个棘手问题及其解决方案。请注意，每个查询都可以不同地编写。在参考本文的解决方案之前，您可以尝试自己思考一下。查询1下表由两列组成，名称和职业。所有名称都需要查询，并在“职业”栏中用括号括起相应的首字母。本文的解决方案是SELECTCONCAT(Name,'(',SUBSTR(Profession,1,1),')')FROMtable;因为需要把名字和职业结合起来，可以使用CONCAT。并且因为括号内只需要一个字母，所以可以使用SUBSTR来传递列名、起始索引和结束索引。由于只需要第一个字母，我们将传递1,1（包括开始索引，不包括结束索引）。查询2Tina需要从她创建的EMPLOYEES表中计算所有员工的平均工资，但是结果显示平均工资很低，这可能是因为键盘上的回零键无效。她希望我们帮助找出计算错误的平均值与实际平均值之间的差异。我们必须编写一个查询来查找错误（实际平均值-计算平均值）。本文的解决方案是SELECTAVG(Salary)-AVG(REPLACE(Salary,0,''))FROMtable；应该注意的是，只有一张表包含实际工资值。要创建错误场景，请将0替换为REPLACE。接下来传递列名、替换值和用于替换REPLACE方法的值。然后，使用聚合函数AVG求平均值的差异。Query3给定一张表，它是一棵二叉搜索树，由节点和父节点两列组成。需要编写查询以返回按节点值升序排序的节点类型。有3种类型：Root-如果节点是根Leaf-如果节点是叶Inner-如果节点既不是根也不是叶：如果给定节点N对应的P值为NULL，则它是根。而如果给定节点N存在于列P中，则它不是内部节点。根据这个想法写一个查询。SELECTCASEWHENPISNULLTHENCONCAT(N,'Root')WHENNIN(SELECTDISTINCTPfromBST)THENCONCAT(N,'Inner')ELSECONCAT(N,'Leaf')ENDFROMBSTORDERBYNasc;CASE可以用作开关函数。如前所述，如果给定节点N的P为空，则N为根。因此，我们使用CONCAT来组合节点值和标签。类似地，如果给定节点N存在于列P中，则它是内部节点。要获取列P中的所有节点，我们编写一个子查询以返回列P中的所有不同节点。由于需要按升序对输出进行排序节点值，则使用ORDERBY子句。查询4交易表由transaction_id、user_id、transaction_date、product_id、quantity（交易ID、用户ID、交易日期、商品ID和数量）组成。需要查询连续多天购买了一个产品的用户数（注意一个给定的用户一天内可以购买多个产品）。为了解决这个查询，本文的方案不能直接统计user_id出现的次数。由于给定用户一天可以购买多次，因此可能会多次返回user_id。因此，只有当有多个不同的日期与给定的user_id相关联时，才意味着用户在多天购买了该产品。按照相同的方法编写查询。（内部查询）SELECTCOUNT(user_id)FROM(SELECTuser_idFROMordersGROUPBYuser_idHAVINGCOUNT(DISTINCTDATE(date))>1)t1由于问题是询问user_id的数量，而不是user_id本身，因此在外部查询中使用COUNT。查询5有一个订阅表，其中包含每个用户订阅的开始和结束日期。需要编写一个查询，根据与其他用户的日期重叠为每个用户返回true/false。例如，如果user1的订阅期与任何其他用户重叠，则查询必须为user1返回true。经过本文方案的初步分析，我们可以知道每个订阅都要和其他订阅进行比较。考虑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。此处还必须确保不会将用户与其自己的订阅进行比较。还想运行一个leftjoin，能够自己匹配用户和其他符合条件的用户。现在，我们将创建同一个表的两个副本S1和S2。SELECT*FROMsubscriptionsASs1LEFTJOINsubscriptionsASs2ONs1.user_id!=s2.user_idANDs1.start_date<=s2.end_dateANDs1.end_date>=s2.start_date给定条件连接，日期之间存在重叠，对于S1中的每个user_id，应该有来自user_id的结果S2。输出可以看出，如果日期重叠，每个用户都有一个对应的用户。对于user1，有2行显示它匹配2个用户。对于用户4，对应的ID为空，说明他没有匹配到其他用户。现在，将它们放在一起，按s1.user_ID字段分组，并检查s2.user_ID不为空的用户的值是否为真。最后查询SELECTs1.user_id,(CASEWHENs2.user_idISNOTNULLTHEN1ELSE0END)ASoverlapFROMsubscriptionsASs1LEFTJOINsubscriptionsASs2ONs1.user_id!=s2.user_idANDs1.start_date<=s2.end_dateANDs1.end_date>=s2.start_dateGROUPBYs1.user_idClausetousegivenCA1and0最终输出如下:
