世界上70%的相关性数据可以被储存为表格数据,即类似ExcelCsv类型的数据,如何去查询和分析相关性的数据?对于少量数据,我们可以使用Excel,更多一点数据,我们可以使用Python, pandas 处理大数据;那么亿万条数据,我们此时就需要借助 SQL,那么掌握常用的 SQL 需要对处理大型数据非常必要。

下面我将介绍10SQL中常用的分析技巧,供大家学习参考。

1 SELECT all columns with LIMIT

一般,我们需要了解数据的大概,那么我们一般会选择前5行进行打印,看看具体内容包括哪些!SELECT 命令会选择所有的行、列,全部进行打印时,浪费时间、电脑内存,Limit命令可以查看数据中的前几行。

SELECT*FROMCustomersLIMIT3;

Limit在MySQL这种可以正常工作,在其他的SQL中可能需要使用TOP, FETCH FIRST ROWS ONLY

2 WHERE clause

WHERE可以对特定列的值进行过滤。例如,我们过滤来自西班牙的国家,并且返回对应的城市。

SELECTCityFROMCustomersWHERECountry="Spain";

我们也可以对城市进行 Unique

SELECTDISTINCTCityFROMCustomersWHERECountry="Spain";

3 GROUP BY and HAVING clause

当我们想要对数据中相同观测值进行分组时,我们可以使用 GROUP BY, HAVING 可以用来过滤加总的数据,常用的包括 sumcount

HAVING 用来处理加总数据,而WHERE用来处理非加总数据。

我们有世界各国的数据,想要输出人口大于500,000,000的洲:

SELECTcontinent,SUM(population)FROMworldGROUPBYcontinentHAVINGSUM(population)>500000000.--filtertheaggregatedvalues

4 Order By clause

Order By 将数据按照选择的列进行升序或者排列:

将选择的数据按照人口降序排列:

SELECTname,populationFROMworldWHEREpopulation>200000000ORDERBYpopulationDESC;

5 Date Function

时间处理函数依赖于SQL的内核,不同SQL的时间处理函数有所差异,DATEPART 可以提取时间中的年月日。

提取时间中的年、月:

SELECTwhn,DATEPART(YEAR,whn)ASyr,DATEPART(MONTH,whn)ASmnthFROMeclipse

6 Joins

Joins 包括笛卡尔积、内积、外积、Self Join,接下来我们将介绍 Innerleft join right join

inner

inner 可以输出两个表或者更多表中共同的行,类似数学中的韦恩图,取两个集合的交集。

当我们取两个集合共同id, 并且仅仅输出前五行:

SELECTOrders.OrderID,Customers.CustomerNameFROMOrdersINNERJOINCustomersONOrders.CustomerID=Customers.CustomerIDLIMIT5;

left join

left join 仅仅输出左边表中的所有行,输出结果中右边表不存在的值为Null

左连表, 按照 Customers.CustomerName进行升序排列:

SELECTCustomers.CustomerName,Orders.OrderIDFROMCustomersLEFTJOINOrdersONCustomers.CustomerID=Orders.CustomerIDORDERBYCustomers.CustomerNameLIMIT5;

right join

右连表与左连表相反,返回右表中的所有行,左表不存在的行将填充Null值。

SELECTOrders.OrderID,Employees.LastName,Employees.FirstNameFROMOrdersRIGHTJOINEmployeesONOrders.EmployeeID=Employees.EmployeeIDORDERBYOrders.OrderID;

7 Case When clause

Case When 可以让我们根据现有的数据生成一些新的列,类似于PythonExce中的if-else语法。

例如,基于国家人口数据,生成新列population_bucket:

SELECTname,population,CASEWHENpopulation<1000000THEN'small'WHENpopulation<10000000THEN'medium'ELSE'large'--ELSEisexecutedifnoconditionissatisfiedENDaspopulation_bucket--nameofthenewcolumnFROMworld

Remark: 没有else时,对应的输出将为 Null

8 Subqueries

子查询在SQL中尤为重要,一旦我们领悟,我们将读懂更为复杂的SQL语句。

例如,输出人口大于俄罗斯的国家的名字:

SELECTnameFROMworldWHEREpopulation>(SELECTpopulationFROMworldWHEREname='Russia')

9 Window function

窗口函数包括以下三种:

加总函数(Aggregate functions):SUM, AVG, MAX, MIN

排序函数(Ranking functions):RANK, ROW_NUMBER

分析函数(Analytic functions):LEAD, LED

另外,我们可以使用Over 语句去定义这些窗口函数,Over 语句和 PARTITION BY 常常联合使用。

例如,对每年每个党派的投票按照降序排列:

SELECTyr,party,votes,RANK()OVER(PARTITIONBYyrORDERBYvotesDESC)asposnFROMgeWHEREconstituency='S14000021'ORDERBYparty,yr

10 Union

Union 常常用来竖直方向组合多个数据集,输入的数据应该具有以下两个特征:

列的的名字和数量是形同的

每列的数据类型是相同的

SELECTDISTINCTCityFROM(SELECTCityFROMCustomersUNIONSELECTCityFROMSuppliers)LIMIT5;

例如,组合消费者和供应者所在的城市:

END