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

可维护、可读SQL代码的十个良好实践

时间:2023-03-15 21:07:40 科技观察

如果没有适当的指导,很容易混淆SQL。由于团队中的每个人都可能有自己编写SQL的习惯,因此您很快就会编写出无人理解的混乱代码。您可能已经意识到遵循一套良好做法的重要性。本文为您提供了您正在寻找的指导!1.使用大写关键字让我们从基础开始:使用大写SQL关键字,小写表和列。使用SQL函数(First_Value()、date_trunc()等)的大写也是一种很好的做法。Avoid:selectid,namefromcompany.customersinstead:SELECTid,nameFROMcompany.customers2.使用SnakeCase写Schema,tables,columns编程语言在case类型方面有最佳实践:Camelcase,Pascalcase,KebabuicandSnake_Case是最多的常见的。对于SQL,蛇形大小写(有时称为下划线大小写)是使用最广泛的约定。避免:SELECTCustomers.id,Customers.name,COUNT(WebVisit.id)asnbVisitFROMCOMPANY.CustomersJOINCOMPANY.WebVisitONCustomers.id=WebVisit.customerIdWHERECustomers.age<=30GROUPBYCustomers.id,Customers.name相反:SELECTcustomers.id,customers.name,COUNT(web_visit.id)asnb_visitFROMcompany.customersJOINcompany.web_visitONcustomers.id=web_visit.customer_idWHEREcustomers.age<=30GROUPBYcustomers.id,customers.name虽然有些人喜欢包含区分架构、表和列的变体,但我建议使用SnakeCase。3.在提高可读性的同时使用别名众所周知,别名是重命名无意义的表或列的一种便捷方式。当它们的名字没有意义时,请随意给你的表和列起别名和别名。避免:SELECTcustomers.id,customers.name,customers.context_col1,nested.f0_FROMcompany.customersJOIN(SELECTcustomer_id,MIN(date)FROMcompany.purchasesGROUPBYcustomer_id)ONcustomer_id=customers.id相反:SELECTcustomers.id,customers.name,customers.context_col1asip_addfirst_purchase。dateasfirst_purchase_dateFROMcompany.customersJOIN(SELECTcustomer_id,MIN(date)asdateFROMcompany.purchasesGROUPBYcustomer_id)ASfirst_purchaseONfirst_purchase.customer_id=customers.id我通常将列别名设为小写,将表格设为大写。4.格式化:小心使用缩进和空格即使这是一个基本原则,让你的代码更具可读性也是一个快速的胜利。正如您使用Python一样,您应该识别您的SQL代码。关键字,以及在使用子查询或派生表之后。避免:SELECTcustomers.id,customers.name,customers.age,customers.gender,customers.salary,first_purchase.dateFROMcompany.customersLEFTJOIN(SELECTcustomer_id,MIN(date)asdateFROMcompany.purchasesGROUPBYcustomer_id)ASfirst_HEREPurchaseONWfirst_purchase.customers.customers_id=customers_id相反:SELECTcustomers.id,customers.name,customers.age,customers.gender,customers.salary,first_purchase.dateFROMcompany.customersLEFTJOIN(SELECTcustomer_id,MIN(date)asdateFROMcompany.purchasesGROUPBYcustomer_id)ASfirst_HERE.Wcustomers.customers.customers_id=customers_id30另外,请注意我们如何使用白色where子句中的空格。避免:SELECTidWHEREcustomers.age<=30相反:SELECTidWHEREcustomers.age<=305。避免选择*这个好习惯值得提醒。你应该明确选择什么,所以避免SELECT*。SELECT使您的请求不清楚,因为它隐藏了查询背后的意图。另外,请记住,您的桌子可能会发生变化并影响选择。这就是为什么我不喜欢except()教学的原因。避免:SELECT*EXCEPT(id)FROMcompany.customers首选:SELECTname,age,salaryFROMcompany.customers6.使用ANSI-92Join语法...代替Join表的SQLwhere子句。虽然您可以使用where子句和连接子句来连接表,但最好使用Join/ansi-92语法。虽然在性能方面没有区别,但Join子句将关系逻辑与过滤器分开并提高了可读性。避免:SELECTcustomers.id,customers.name,COUNT(transactions.id)asnb_transactionFROMcompany.customers,company.transactionsWHEREcustomers.id=transactions.customer_idANDcustomers.age<=30GROUPBYcustomers.id,customers.name相反:SELECTcustomers.id,customers.name,COUNT(transactions.id)asnb_transactionFROMcompany.customersJOINcompany.transactionsONcustomers.id=transactions.customer_idWHEREcustomers.age<=30GROUPBYcustomers.id,customers.name“Wherebasedonterms”语法-也称为ANSI-89-大于新的ANSI-92,这就是为什么它仍然很常见。如今,大多数开发人员和数据分析师都在使用Join语法。7.使用公用表表达式(CTE)CTE允许您定义和执行查询,其中结果暂时存在并可用于更大的查询。大多数现代数据库都提供CTE。它类似于派生表,有两个优点:使用CTE提高查询可读性CTE定义一次,然后可以多次引用您使用WITH...AS:WITHmy_cteAS(SELECTcol1,col2FROMtable)SELECT*FROMmy_cte避免:SELECTcustomers.id,customers.name,customers.age,customers.gender,customers.salary,persona_salary.avg_salaryaspersona_avg_salary,first_purchase.dateFROMcompany.customersJOIN(SELECTcustomer_id,MIN(date)asdateFROMcompany.purchasesGROUPBYcustomer_id)ASfirst_purchaseONfirst_purchase.customerid_OIN=客户。,gender,AVG(salary)asavg_salaryFROMcompany.customersGROUPBYage,gender)ASpersona_salaryONpersona_salary.age=customers.ageANDpersona_salary.gender=customers.genderWHEREcustomers.age<=30并且是:WITHfirst_purchaseAS(SELECTcustomer_id,MIN(date)asdateFROMcompany.purchases_personalSELECTas(agecustomer),gender,AVG(salary)asavg_salaryFROMcompany.customersGROUPBYage,gender)SELECTcustomers.id,customers.name,customers.age,customers.性别,customers.salary,persona_salary.avg_salaryaspersona_avg_salary,冷杉st_purchase.dateFROMcompany.customersJOINfirst_purchaseONfirst_purchase.customer_id=customers.idJOINpersona_salaryONpersona_salary.age=customers.ageANDpersona_salary.gender=customers.genderWHEREcustomers.age<=308。有时可能值得将其拆分为多个查询,所以通常让我们提供一些背景知识:我使用Airflow在BigQuery上执行SQL查询,转换数据并准备数据以进行可视化。我们有一个workflowOrchestrator(airflow)以定义的顺序执行请求。在某些情况下,我们选择将复杂的查询拆分为多个较小的查询。代替:CREATETABLEcustomers_infosASSELECTcustomers.id,customers.salary,traffic_info.weeks_since_last_visit,category_info.most_visited_category_id,purchase_info.highest_purchase_valueFROMcompany.customersLEFTJOIN([..])AStraffic_infoLEFTJOIN([..])AScategory_infoLEFTJOIN([..])ASpurchase_info你可以使用:##STEP1:CreateinitialtableCREATETABLEpublic.customers_infosASSELECTcustomers.id,customers.salary,0asweeks_since_last_visit,0asmost_visited_category_id,0ashighest_purchase_valueFROMcompany.customers##STEP2:UpdatetrafficinfosUPDATEpublic.customers_infosSETweeks_since_last_visit=DATE_DIFF(CURRENT_DATE,last_visit.date,WEEK)FROM(SELECTcustomer_id,max(visit_date)asdateFROMweb.traffic_infoGROUPBYcustomer_id)ASlast_visitWHERElast_visit.customer_id=customers_infos.id##STEP3:UpdatecategoryinfosUPDATEpublic.customers_infosSETmost_visited_category_id=[...]WHERE[...]##STEP4:UpdatepurchaseinfosUPDATEpublic.customers_infosSEThighest_purchase_value=[...]WHERE[...]警告:即使用这种方法在简单化复在进行各种查询时,它可能会带来可读性/性能的折衷。如果您使用的是OLAP或任何面向列的数据库,针对聚合和分析查询(SELECT、AVG、MIN、MAX、...)进行了优化,但在对话(更新)时性能较低,则尤其如此。虽然在某些情况下,它也可能会提高你的表现。即使使用现代的面向列的数据库,这也会导致过多的内存或性能问题。在这些情况下,拆分您的请求通常有助于提高性能和内存。此外,值得一提的是,您需要某种程序或Orchestrator来按定义的顺序执行查询。9.根据您自己的约定用有意义的名称正确命名您的模式和表是很困难的。使用哪种命名约定是一件很难的事,但选择一个并坚持它却不是。您应该定义自己的例行程序并将其传递给您的团队。计算机科学中只有两个难题:缓存失效和命名事物。-PhilCarlton以下是我使用的约定示例:(1)如果您将分析数据库用于多种用途,模式是一种以有意义的模式组织表的良好实践。在我们的BigQuery数据库中,每个数据源都有一个模式。更重要的是,我们根据他们的目的以不同的模式输出结果。应由第三方工具访问的任何表都在公共模式中进行了布局。DataSudio或Tableau等Dataviz工具从这里获取数据。由于我们将机器学习与BQML结合使用,因此我们拥有专用的Machine_Learning架构。(2)表格表格本身应该是约定俗成的,我们有几个用于数据可视化的仪表板,每个仪表板都有自己的用途:营销仪表板、产品仪表板、管理仪表板等等。我们公共模式中的每个表都以仪表板的名称为前缀。一些示例可能包括:product_inbox_usageproduct_addon_competitor_statsmarketing_acquisition_agenciesexecutive_funnel_overview与团队合作时,值得花时间定义您的参与。在命名新表时,切勿使用快速和肮脏的名称,您会“改变”:您可能不会。请随意使用这些示例来定义您的约定。10.最后,写一些有帮助的注释……但不要太多我同意这样的观点,即编写良好且命名恰当的代码不需要注释。阅读代码的人甚至应该在代码本身之前理解逻辑和意图。尽管如此,注释在某些情况下还是很有用的。但是你也绝对应该避免评论的陷阱。避免:WITHfpAS(SELECTc_id,#customeridMIN(date)asdt#dateoffirstpurchaseFROMcompany.purchasesGROUPBYc_id),psAS(SELECTage,gender,AVG(salary)asavgFROMcompany.customersGROUPBYage,gender)SELECTcustomers.id,ct.name,ct.c_age,#customeragect.gender,ct.salary,ps.avg,#averagesalaryofasilimarpersonafp.dt#dateoffirstpurchaseforthisclientFROMcompany.customersct#jointhefirstpurchaseonclientidJOINfpONc_id=ct.id#matchpersonabasedonsameageandgenreJOINps.age=c_ageANDps.gender=ct.genderWHEREc_age<=30而compase_id是:WITH(first_purchaseAS,anycustomerpurchase是:WITH(客户购买日期).purchasesGROUPBYcustomer_id),persona_salaryAS(SELECTage,gender,AVG(salary)asavg_salaryFROMcompany.customersGROUPBYage,gender)SELECTcustomers.id,customers.name,customers.age,customers.gender,customers.salary,persona_salary.avg_salaryaspersona_avg_salary,first_purchase.dateJROMOCompanyfirst.customers.id,customers.name,customers.age,customers.gender,customers.salary,persona_salary.avg_salaryaspersona_avg_salary,first_purchase.dateJROMOFirst.客户.customer_id=customers.idJOINpersona_salaryONpersona_salary.age=customers.ageANDpersona_salary.gender=customers.genderWHEREcustomers.age<=30结论SQL很棒它是数据分析、数据科学、数据工程甚至软件开发的基础之一:它不会等待。它的灵活性是一种优势,但也可能是一个陷阱。一开始您可能没有意识到这一点,特别是如果您是唯一对自己的代码负责的人。但在某些时候,当与团队合作或有人继续工作时,没有一套最佳实践的SQL代码将成为一种负担。在本文中,我总结了编写SQL的最常见的最佳实践。当然,有些是有争议的或基于个人意见:您可能想从中汲取灵感并定义与您的团队不同的东西。我希望它可以帮助您将SQL质量提升到一个新的水平!原文链接:https://towardsdatascience.com/10-best-practices-to-write-readable-and-maintainable-sql-code-427f6bb98208