当前位置: 首页 > 科技观察

获取SQL!5困难SQL查询的解决方案

时间:2023-03-20 20:36:54 科技观察

对于数据科学家来说,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最终输出如下: