数据科学所需的 SQL 知识

原文towardsdatascience.com/sql-knowledge-you-need-for-data-science-5cf0c15515e4根据 365DataScience文章该文章调查了 1,000 个 LinkedIn 数据科学职位发布其中 60%要求具备 SQL 技能。这告诉我们什么好吧如果数据科学家想要增加获得工作的机会SQL 是一项必备技能。在这篇文章中我将讨论你为了获得入门级数据科学职位所需具备的 SQL 知识并提供一些在我学习 SQL 时对我有帮助的资源和建议。cdn.embedly.com/widgets/media.html?srchttps%3A%2F%2Fwww.youtube.com%2Fembed%2FT60XcVStwto%3Ffeature%3Doembeddisplay_nameYouTubeurlhttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DT60XcVStwtoimagehttps%3A%2F%2Fi.ytimg.com%2Fvi%2FT60XcVStwto%2Fhqdefault.jpgkeya19fcc184b9711e1b4764040d3dc5c07typetext%2Fhtmlschemayoutube知识根据我的经验对于许多入门级数据科学职位来说你不需要成为 SQL 的专家你只需要知道如何查询并获取你需要分析以及构建机器学习模型的数据。例如创建 ETL提取、转换、加载管道和管理数据库通常是数据科学家职责之外的工作这更多是数据工程师的工作。然而如果你对这些类型的事情感兴趣这不应该阻止你学习它们概述首先你应该了解关系数据库和不同类型的 SQL 版本。我并不是说你需要学习这些版本只是理解它们之间的差异以及为什么存在某些版本。把这当作在深入主要内容之前的一些初步阅读根据我的经验我建议学习 MySQL 或 PostgreSQL因为这些都是整个行业中应用最广泛的。然而所有 SQL 版本中的语法和函数都是相同的所以你不必过于担心这一点。如果你学会了其中一个其他的就相对容易掌握了所以不要过度思考。如果你感兴趣想了解不同 SQL 版本的差异请参阅这篇文章。基本功能老实说我在日常工作中 95%的时间都在使用基本的 SQL 函数进行查询。正如我之前所说作为数据科学家我主要使用 SQL 来获取数据和进行基本转换。实际上我在 Pandas 中进行更复杂的数据转换和数据操作因为它更容易通过单元和集成测试进行测试。无论如何你应该知道的最重要的 SQL 命令是对于所有这些命令我都提供了使用假想的Employees表执行它们的示例。SELECTFROM*标准查询SELECT*FROM Employees这个命令从表中选择列。*命令从Employees表中获取所有列。ALTER, INSERT, CREATE修改表--CREATE table CREATE TABLE Employees(EmployeeID INT PRIMARY KEY,FirstName NVARCHAR(50),LastName NVARCHAR(50),BirthDate DATE,Salary DECIMAL(10,2));--INSERT a new record into the Employees table INSERT INTO Employees(EmployeeID,FirstName,LastName,BirthDate,Salary)VALUES(1,John,Doe,1980-01-01,50000.00);--ALTER the Employees table to add a new column ALTER TABLE Employees ADD Department VARCHAR(50);在这里我们创建一个名为Employees的表并定义列及其主键。然后我们使用INSERT INTO向这个数据框添加一行。最后我们使用ALTER命令向表中添加一个名为 Department 的新列。GROUP BY, ORDER BY--GROUP BY SELECT Department,COUNT(*)AS NumberOfEmployees FROM Employees GROUP BY Department;--ORDER BY SELECT*FROM Employees ORDER BY LastName ASC,FirstName ASC;这些命令相当直观。ORDER BY命令只是对表中的一列进行排序而GROUP BY命令只是按列进行聚合。在这里我们按部门进行员工计数。WHERE, AND, OR, BETWEEN, IN, HAVING过滤表--WHERE,AND,andOR SELECT*FROM Employees WHERE Salary40000AND DepartmentSales;--BETWEEN SELECT*FROM Employees WHERE BirthDate BETWEEN1970-01-01AND1990-12-31;--IN SELECT*FROM Employees WHERE Department IN(Sales,HR);--GROUP BYandHAVING SELECT Department,AVG(Salary)AS AverageSalary FROM Employees GROUP BY Department HAVING AVG(Salary)45000;WHERE和HAVING命令通过一列来过滤数据框。在这个例子中我们通过薪水、生日和部门进行过滤。AVG, COUNT, MIN, MAX, SUM聚合函数--AVG,COUNT,MIN,MAX,SUM SELECT AVG(Salary)AS AverageSalary,COUNT(EmployeeID)AS NumberOfEmployees,MIN(Salary)AS MinimumSalary,MAX(Salary)AS MaximumSalary,SUM(Salary)AS TotalSalary FROM Employees;s这些命令从数据集中创建统计信息这个返回值只有一行包含平均薪水、总员工数、最低薪水、最高薪水和总薪水。DISTINCTSELECT DISTINCT Department FROM Employees;如果某一列中有重复条目DISTINCT命令将只获取唯一值。DATEADD, DATEDIFF, DATEPART日期和时间函数--DATEADD:Add one year to the birthdate SELECT EmployeeID,FirstName,LastName,DATEADD(year,1,BirthDate)AS BirthDatePlusOneYear FROM Employees;--DATEDIFF:Calculate age of employees SELECT EmployeeID,FirstName,LastName,DATEDIFF(year,BirthDate,GETDATE())AS Age FROM Employees;--DATEPART:Extract the year of birth SELECT EmployeeID,FirstName,LastName,DATEPART(year,BirthDate)AS BirthYear FROM Employees;在这里DATEADD函数是将生日增加一年DATEDIFF函数是获取员工生日和当前日期之间的年差而DATEPART函数是从生日中获取出生年份。CASE在其他语言中基本上是 if-else 语句SELECT EmployeeID,FirstName,LastName,CASE WHEN Salary60000THENHighWHEN Salary BETWEEN40000AND60000THENMediumELSELowEND AS SalaryCategory FROM Employees;CASE的作用类似于条件 if-else。在这种情况下没有开玩笑如果薪水超过 60,000 则为 “高”在 40,000 到 60,000 之间为 “中”否则为 “低”。为了清楚起见我制作的这些完全随机的过滤器不应该被认真对待。FULL JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN, UNION所有类型的连接--INNER JOIN SELECT e.EmployeeID,e.FirstName,e.LastName,d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentIDd.DepartmentID;--LEFT JOIN SELECT e.EmployeeID,e.FirstName,e.LastName,d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentIDd.DepartmentID;--RIGHT JOIN SELECT e.EmployeeID,e.FirstName,e.LastName,d.DepartmentName FROM Employees e RIGHT JOIN Departments d ON e.DepartmentIDd.DepartmentID;--FULL JOIN SELECT e.EmployeeID,e.FirstName,e.LastName,d.DepartmentName FROM Employees e FULL JOIN Departments d ON e.DepartmentIDd.DepartmentID;--UNION SELECT FirstName,LastName FROM Employees UNION SELECT FirstName,LastName FROM Managers;JOIN命令简单地将两个表在共同的列和 id 上连接起来。请参阅这里了解不同类型及其底层操作的视觉解释。连接操作可能是最棘手的所以请确保你理解它们我仍然会犯错误在我第一份工作中就因为它们而有过糟糕的经历但我已经在这方面变得更好能够更好地捕捉到它们。所以不要像我一样要彻底地学习它们了解所有这些基本函数可能使你能够通过任何包含 SQL 练习的入门级数据科学面试。高级函数一旦你掌握了基础知识投入更多时间学习一些更高级的函数是值得的。我在日常工作中经常使用其中的一些因此它们非常有用尤其是在构建大型数据集时因为 SQL 仓库中的计算时间比 Python 快得多。公用表达式表 (CTE) 和子查询--Common Table Expression(CTE)WITH SalesCTE AS(SELECT EmployeeID,SUM(SalesAmount)AS TotalSales FROM Sales GROUP BY EmployeeID)SELECT e.EmployeeID,e.FirstName,e.LastName,s.TotalSales FROM Employees e JOIN SalesCTE s ON e.EmployeeIDs.EmployeeID;--Subquery SELECT EmployeeID,FirstName,LastName,Salary FROM Employees WHERE Salary(SELECT AVG(Salary)FROM Employees);这里发生的事情是我们创建了一个名为SalesCTE的新表然后我们将这个新表与Employees表连接起来。这是一个 CTE公用表达式表的例子。子查询并不是创建一个新表而是从一个表中提取一个值来过滤。在这种情况下它是平均薪水。用户定义函数 (UDFs)CREATE FUNCTION dbo.GetEmployeeFullName(EmployeeID INT)RETURNS NVARCHAR(100)AS BEGIN DECLARE FullName NVARCHAR(100);SELECT FullNameFirstName LastName FROM Employees WHERE EmployeeIDEmployeeID;RETURN FullName;END;--Using the UDF SELECT dbo.GetEmployeeFullName(EmployeeID)AS FullName FROM Employees;UDFs用户定义函数在其他语言中的工作方式与常规函数非常相似。在这里我们创建一个函数该函数获取员工的完整姓名并将其返回为单列。窗口函数 (RANK, ROW_NUMBER, DENSE_RANK)--ROW_NUMBER SELECT EmployeeID,FirstName,LastName,Salary,ROW_NUMBER()OVER(PARTITION BY Department ORDER BY Salary DESC)AS RowNum FROM Employees;--RANK SELECT EmployeeID,FirstName,LastName,Salary,RANK()OVER(PARTITION BY Department ORDER BY Salary DESC)AS Rank FROM Employees;--DENSE_RANK SELECT EmployeeID,FirstName,LastName,Salary,DENSE_RANK()OVER(PARTITION BY Department ORDER BY Salary DESC)AS DenseRank FROM Employees;窗口函数在多个行上执行与当前分析行相关的计算。所以在这种情况下我们使用ROW_NUMBER()函数根据员工在其相应部门的薪水进行编号。RANK()做类似的事情并按每个部门的薪水对员工进行排名。DENSE_RANK()类似但如果两个员工有相同的薪水他们的排名相同。字符串操作和正则表达式--String operations:CONCAT,SUBSTRING,REPLACE,etc.SELECT CONCAT(FirstName, ,LastName)AS FullName,SUBSTRING(FirstName,1,1)AS FirstInitial,REPLACE(LastName,a,o)AS ModifiedLastName FROM Employees;--Regex using LIKE SELECT EmployeeID,FirstName,LastName FROM Employees WHERE FirstName LIKEJ%;--First name startswithJ--Regex using PATINDEX(SQL Server)SELECT EmployeeID,FirstName,LastName FROM Employees WHERE PATINDEX(%[0-9]%,LastName)0;--Last name contains a digitCONCAT将两个字符串合并在一起SUBSTRING过滤字符串REPLACE替换字符串中的字母。通过LIKE J%获取对应列中以 J 开头的值。最后PATINDEX(%[0-9]%, LastName) 0这是一个模式索引搜索用于在LastName中找到 0 到 9 之间的任何数字的第一个出现。还有许多其他东西要学习但这些高级技术是我作为数据科学家最常使用的技术。我相信根据你的行业和组织还会有其他事情出现。SQL 不是一个庞大的语言但这里仍然有大量的函数。如果你感兴趣请查看这里以获取完整列表。其他事项如果你手头有更多时间你可以尝试学习围绕 SQL 的基础设施例如 SQL 服务器、数据库管理、数据仓库、数据平台扩展、查询优化和构建 ETL 管道。上面的列表中的大部分都是数据工程角色的部分。如果你对此感兴趣这篇博客提供了一个如何成为数据工程师的优秀路线图。说实话我对这些主题没有深入的了解因为这些是数据工程师比数据科学家更关注的事情。但如果它们对你来说很有趣请随意学习它们我也计划在将来某个时候介绍它们因为这将使我成为一个更全面的数据科学家并增加我的价值。但是正如我经常说的你不可能学会所有东西资源关于资源我在学习 SQL 时使用了W3Schools和Tutorialspoint。它们是完全免费的并且真正很好地涵盖了基础知识。我更喜欢把它们看作是参考文本而不是完整的课程但如果你是初学者它们是一个很好的起点并且包含供你练习的练习题。SQL 教程SQL 教程如果你想在课程之外进行更多练习那么像 Hacker Rank 和Leetcode这样的网站是很好的。在我感到舒适回答入门级面试问题之前我在 Hacker Rank 上做了大约 50 个 SQL 问题。我不会太担心这个数字它可能比你预期的 50 个问题多或少专注于理解当你感到舒适时你就会知道。解决 SQL 代码挑战建议我会给刚开始学习 SQL 的人提供的三条主要建议是不要担心或浪费太多时间挑选“正确”的课程选择你喜欢的然后开始。作为一个初学者任何入门课程都会有所帮助并且很可能会涵盖相同的话题。每天练习并确保你做练习题来理解 SQL一万小时规则一万次迭代规则在这里适用就像在其他所有事情上一样。有耐心我保证一切最终都会慢慢理解。你不可能在一天之内学会所有东西或掌握 SQL但你可以用两周的时间学习足够的 SQL 知识以获得一个入门级职位。这正是我所做的你可以在下面的帖子中阅读我整个 SQL 之旅。两周内从零开始学习 SQL我希望这篇文章已经给了你足够的 SQL 知识让你能够获得那个入门级的数据科学职位你需要的 SQL 技能没有 Python 那么广泛而且由于 SQL 稍微容易管理且语言规模较小它们应该需要更少的时间来学习但无论如何它们仍然是必不可少的。另一件事我有一个免费的通讯Dishing the Data我在那里分享成为更好的数据科学家每周的小贴士我在该领域的总体经验以及我过去一周的一些想法。Dishing The Data | Egor Howell | Substack与我联系LinkedIn,X (Twitter), 或Instagram。我的**YouTube 频道**学习技术数据科学和机器学习概念