随着数据量的不断增长，对合格数据专业人员的需求也将不断增加。具体而言，对精通SQL的专业人员的需求不断增长，而不仅仅是初级水平。因此，Stratascratch的创始人NathanRosidi和我认为是高级SQL概念的10个最重要和最相关的中级概念。也就是说，我们开始吧！1.公用表表达式(CTE)如果您想查询子查询，这就是CTE的用武之地-CTE基本上创建一个临时表。使用公用表表达式(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子句是在多伦多的名称过滤进去如果您注意到，CTE很有用，因为您可以将代码分成更小的块，但它们也很有用，因为它允许您为每个CTE分配变量名（即toronto_ppl和avg_female_salary）同样，CTE允许您执行更高级的技术，例如创建递归表。2.递归CTE。递归CTE是引用自身的CTE，就像Python中的递归函数一样。递归CTE在查询层次结构数据（例如组织结构图、文件系统、网页之间的链接图等）时特别有用。递归CTE有3个部分：锚成员：返回CTE基本结果的初始查询递归成员：引用CTE的递归查询。以下是获取每个员工ID的经理ID的递归CTE示例：出，但知道如何编写临时函数很重要，原因之一是：它允许您将代码块分解为更小的代码块，并且有助于编写干净的代码它防止重复并允许您重用类似于使用函数的代码在Python中考虑以下示例：SELECTname,CASEWHENtenure<1THEN"analyst"WHENtenureBETWEEN1and3THEN"associate"WHENtenureBETWEEN3and5THEN"senior"WHENtenure>5THEN"vp"ELSE"n/a"ENDASseniorityFROMemployees相反，您可以利用临时函数来捕获case子句.CREATETEMPORARYFUNCTIONget_seniority(tenureINT64)AS(CASEWHENtenure<1THEN"分析师"WHENtenureBETWEEN1and3THEN"助理"WHENtenureBETWEEN3and5THEN"高级"WHENtenure>5THEN"vp"ELSE"n/a"END);,可读性更强，可以复用资历函数！4.使用CASEWHEN来转换数据您可能会看到很多问题要求在语句中使用CASEWHEN，仅仅是因为它是一个通用的概念。如果您想根据其他变量分配某个值或类，则允许您编写复杂的条件语句。鲜为人知的是，它还允许您旋转数据。例如，如果您有一个月的列，并且您希望为每个月创建一个列，则可以使用语句向下钻取数据。示例问题：编写SQL查询以重新格式化表，以便每个月都有一个收入列。Initialtable:+------+--------+------+|id|revenue|month|+------+----------+-------+|1|8000|一月||2|9000|一月||3|10000|二月||1|7000|二月||1|6000|三月|+------+--------+--------+结果表：+------+------------+------------+------------+-----+------------+|id|Jan_Revenue|Feb_Revenue|Mar_Revenue|...|Dec_Revenue|+------+------------+------------+-------------+-----+------------+|1|8000|7000|6000|...|null||2|9000|null|null|...|null||3|null|10000|null|...|null|+------+------------+-------------+------------+-----+------------+5。EXCEPT与NOTIN除了几乎不同的操作。它们都用于比较两个查询/表之间的行。也就是说，两人之间存在细微差别。首先，除了过滤会删除重复项并返回不同的行与不在其中的行。此外，除了查询/表中相同数量的列，不再有单个列与每个查询/表进行比较。6.自连接SQL表连接自身。您可能认为它没用，但您会惊讶于它的普遍性。在许多现实生活中，数据存储在一张大表中，而不是许多小表中。在这种情况下，可能需要自连接来解决独特的问题。让我们看一个例子。示例问题：给定下表中的员工，编写SQL查询以了解薪酬高于其经理的员工的薪水。对于上表，Joe是唯一一位收入高于其经理的员工。+----+--------+--------+----------+|Id|姓名|工资|ManagerId|+----+-------+--------+----------+|1|乔|70000|3||2|亨利|80000|4||3|Sam|60000|NULL||4|Max|90000|NULL|+----+--------+------+------------+答案:SELECTa.NameasEmployeeFROMEmployeeasaJOINEmployeeasbona.ManagerID=b.IdWHEREa.Salary>b.Salary7.RankvsDenseRankvsRowNumber这是对行和值进行排名的非常常见的应用程序。以下是公司经常如何使用排名的一些示例：按购买量、利润等排名。Topvaluedcustomers按销售额排名Topproductsbysales。在SQL中，您可以通过多种方式为行分配“等级”，我们将使用示例来探讨这些方式。考虑以下查询和结果：SELECTName,GPA,ROW_NUMBER()OVER(ORDERBYGPAdesc),RANK()OVER(ORDERBYGPAdesc),DENSE_RANK()OVER(ORDERBYGPAdesc)FROMstudent_gradesROW_NUMBER()返回每行开头的唯一数字。当关系存在时（例如，BOBvsCarrie），如果未定义第二个条件，ROW_NUMBER()会任意分配数字。Rank()为每一行返回一个从1开始的唯一数字，除非存在关系，rank()将分配相同的数字。同样，差距将遵循重复的排名。dense_rank()与rank()类似，只是重复排名后没有差距。请注意，丹尼尔使用dense_rank()排名第3，而不是第4()。8.计算Delta另一个常见的应用是比较不同时间段的值。例如，本月和上月销售额之间的差异是多少？或者去年这个月和这个月是什么时候？这是Lead()和LAG()发挥作用的情况。这里有一些例子：#Comparingeachmonth'ssalestolastmonthSELECTmonth,sales,sales-LAG(sales,1)OVER(ORDERBYmonth)FROMmonthly_sales#Comparingeachmonth'ssalestothesamemonthlastyearSELECTmonth,sales,sales-LAG(sales,12)OVER(ORDERBYmonth)FROMmonthly_sales你知道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。+--------+----------------+----------------+|Id(INT)|RecordDate(DATE)|Temperature(INT)|+--------+----------------+-------------------+|1|2015-01-01|10||2|2015-01-02|25||3|2015-01-03|20||4|2015-01-04|30|+--------+----------------+------------------+Answer:SELECTa.IdFROMWeathera,WeatherbWHEREa.Temperature>b.TemperatureANDDATEDIFF(a.RecordDate,b.RecordDate)=1原文链接：https://towardsdatascience.com/ten-advanced-sql-concepts-您应该知道的数据科学访谈4d7015ec74b0
