随着数据量的不断增长，对合格数据专业人员的需求也将不断增加。具体而言，对精通SQL的专业人员的需求不断增长，而不仅仅是初级水平。因此，Stratascratch的创始人NathanRosidi和我认为是高级SQL概念的10个最重要和最相关的中级概念。1.CommonExpressions(CTEs)如果你想查询子查询，这就是CTEs发挥作用的时候——CTEs基本上创建了一个临时表。使用公用表表达式(CTE)是模块化和分解代码的好方法，就像将文章分解成段落一样。请在以下查询的Where子句中使用子查询。SELECTname,salaryFROMPeopleWHERENAMEIN(SELECTDISTINCTNAMEFROMpopulationWHEREcountry="Canada"ANDcity="Toronto")ANDsalary>=(SELECTAVG(salary)FROMsalariesWHEREgender="Female")难以理解，但是如果一个查询中有很多子查询怎么办？这就是CTE发挥作用的地方。withtoronto_pplas(SELECTDISTINCTnameFROMpopulationWHEREcountry="Canada"ANDcity="Toronto"),avg_female_salaryas(SELECTAVG(salary)asavgSalaryFROMsalariesWHEREgender="Female")SELECTname,salaryFROMPeopleWHEREnamein(SELECTDISTINCTFROMtoronto_ppl)ANDsalary>=(SELECTavgSalaryFROMavg_female_salary)现在很明显，Where子句过滤了Toronto的名称。如果您注意到，CTE很有用，因为您可以将代码分成更小的块，但它们也很有用，因为它允许您为每个CTE分配变量名（即toronto_ppl和avg_female_salary）同样，CTE允许您执行更高级的技术，例如创建递归表。2.递归CTE递归CTE是指它们自己的CTE，就像Python中的递归函数一样。递归CTE在查询层次结构数据（例如组织结构图、文件系统、网页之间的链接图等）时特别有用。递归CTE有3个部分：锚成员：返回CTE基本结果的初始查询递归成员：引用CTE的递归查询。这是一个递归CTE的示例，用于获取每个员工ID的经理ID：withorg_structureas(SELECTid,manager_idFROMstaff_membersWHEREmanager_idISNULLUNIONALLSELECTsm.id,sm.manager_idFROMstaff_memberssmINNERJOINorg_structureosONos.id=sm.manager_id3.临时函数如果您想了解更多有关临时函数的信息，请勾选此项，但了解如何编写临时函数很重要原因：它允许您将代码块分解为更小的代码块；它是适合编写干净的代码；它可以防止重复并允许您重用类似于在Python中使用函数的代码。考虑以下示例：SELECTname,CASEWHENtenure<1THEN"analyst"WHENtenureBETWEEN1and3THEN"associate"WHENtenureBETWEEN3and5THEN"senior"WHENtenure>5THEN"vp"ELSE"n/a"ENDASseniorityFROMemployeesOpposite，您可以利用临时函数来捕获case子句。创建临时函数get_seniority(tenureINT64)AS(CASEWHENtenure<1THEN"analyst"WHENtenureBETWEEN1and3THEN"associate"WHENtenureBETWEEN3and5THEN"senior"WHENtenure>5THEN"vp"ELSE"n/a"END);SELECTname,get_seniority(tenure)asseniorityFROMemployees查询本身更简单，通过临时函数更具可读性，您可以重用资历函数！4.使用CASEWHEN来转换数据您可能会看到许多问题要求在语句中使用CASEWHEN，因为它是一个通用的概念。如果您想根据其他变量分配某个值或类，则允许您编写复杂的条件语句。鲜为人知的是，它还允许您旋转数据。例如，如果您有一个月的列，并且您希望为每个月创建一个列，则可以使用语句向下钻取数据。示例问题：编写SQL查询以重新格式化表，以便每个月都有一个收入列。初始表：+-----+---------+------+|编号|收入|月|+------+---------+------+|1|8000|简||2|9000|简||3|10000|二月||1|7000|二月||1|6000|----+--------+--------+结果表：+------+------------+------------+------------+-----+------------+|编号|一月收入|Feb_收入|三月收入|...|十二月收入|+------+------------+------------+-------------+-----+------------+|1|8000|7000|6000|...|空||2|9000||...|空||3|空|10000|空|...|空|+------+------------+------------+------------+-----+------------+五、EXCEPTvsNOTIN除了它们几乎是不同的操作。它们都用于比较两个查询/表之间的行。也就是说，两人之间存在细微差别。首先，除了过滤会删除重复项并返回不同的行与不在其中的行。此外，除了查询/表中相同数量的列，不再有单个列与每个查询/表进行比较。6.自连接SQL表连接自身。您可能认为它没用，但您会惊讶于它的普遍性。在许多现实生活中，数据存储在一张大表中，而不是许多小表中。在这种情况下，可能需要自连接来解决独特的问题。让我们看一个例子。示例问题：给定下表中的员工，编写SQL查询以了解薪酬高于其经理的员工的薪水。对于上表，Joe是唯一一位收入高于其经理的员工。+----+--------+--------+----------+|编号|名称|工资|经理编号|+----+------+--------+----------+|1|乔|70000|3||2|亨利|80000|4||3|山姆|60000|空||4|最大|90000|空|+----+--------+--------+------------+答案：SELECTa.NameasEmployeeFROMEmployeeasaJOINEmployeeasbona.ManagerID=b.IdWHEREa.Salary>b.SalaryVII.RankvsDenseRankvsRowNumber是一种非常常见的行和值排名应用。以下是公司经常如何使用排名的一些示例：按购买量、利润等对最有价值的客户进行排名；按销售量对顶级产品进行排名；的热门视频。在SQL中，您可以通过多种方式为行分配“等级”，我们将使用示例来探讨这些方式。考虑以下查询和结果：SELECTName,GPA,ROW_NUMBER()OVER(ORDERBYGPAdesc),RANK()OVER(ORDERBYGPAdesc),DENSE_RANK()OVER(ORDERBYGPAdesc)FROMstudent_gradesROW_NUMBER()返回每个row一个唯一的数字开始。当关系存在时（例如，BOBvsCarrie），如果未定义第二个条件，ROW_NUMBER()会任意分配数字。Rank()从1开始为每一行返回一个唯一的数字，除非有关系，rank()将分配相同的数字。同样，差距将遵循重复的排名。dense_rank()与rank()类似，只是重复排名后没有差距。请注意，使用dense_rank()，Daniel排在第3位，而不是第4()位。8.计算Delta值，比较不同时期的数值。例如，本月和上月销售额之间的差异是多少？或者这个月和去年这个月是什么？这是Lead()和LAG()在比较不同时间段的值以计算增量时发挥作用的时候。以下是一些示例：#将每个月的销售额与上个月进行比较SELECTmonth,sales,sales-LAG(sales,1)OVER(ORDERBYmonth)FROMmonthly_sales#将每个月的销售额与去年同月进行比较SELECTmonth,sales,sales-LAG(sales,12)OVER(ORDERBYmonth)FROMmonthly_sales9.计算运行总计如果您了解row_number()和lag()/lead()，您可能不会感到惊讶。但如果您不这样做，这可能是最有用的窗口函数之一，尤其是当您想要可视化增长时！使用带SUM()的窗口函数，我们可以计算运行总计。请参见以下示例：SELECTMonth,Revenue,SUM(Revenue)OVER(ORDERBYMonth)ASCumulativeFROMmonthly_revenue10.日期时间操作您肯定会遇到涉及日期时间数据的某种SQL问题。例如，您可能需要将数据分组或将变量格式从DD-MM-Yyyy转换为简单的月份。你应该知道的一些函数是：extractYendate_add,date_sub.date_trunc。示例问题：给定一个天气表，编写一个SQL查询来查找与前一个（昨天）日期相比温度更高的所有日期的ID。+--------+----------------+----------------+|编号（整数）|记录日期（日期）|温度(INT)|+--------+----------------+---------------+|1|2015-01-01|10||2|2015-01-02|25||3|2015-01-03|20||2015-01-04|30|+--------+----------------+------------------+回答:SELECTa.IdFROMWeathera,WeatherbWHEREa.Temperature>b.TemperatureANDDATEDIFF(a.RecordDate,b.RecordDate)=1就是这样！我希望这对你的面试准备有所帮助——我相信如果你了解这10个内部概念，那么当涉及到大多数SQL问题时，你会做得很好。
