在这篇文章中,我将向你展示最基本的SQL函数,你将使用这些函数来计算数据集中的集合--如SUM
,AVG
,COUNT
,MAX
,MIN
。然后我将向你展示这些函数如何与GROUP BY
子句一起工作,因此你将能够使用SQL进行分割项目。(*例如,你将学习,如何在SQL中用GROUP BY
计算平均数。)*最后,你将学习一些使用ORDER BY
和DISTINCT
的中级SQL动作。你将在这里学到很多新的东西......所以扣好安全带--因为你必须知道所有这些才能有效地使用SQL进行数据分析
哦,这将是超级令人兴奋的,因为我们仍将使用我们的7M多行的数据集!
注意:要想从这篇文章中获得最大的收获,你不应该只是阅读它,而应该和我一起实际操作编码部分!
在我们开始之前...
...我建议先浏览一下这些文章--如果你还没有这么做的话。
- 建立你自己的数据服务器。如何设置Python、SQL、R和Bash(针对非开发人员)。
- 安装SQL Workbench以更好地管理你的SQL查询。如何为PostgreSQL安装SQL Workbench
- 阅读《数据分析的SQL》系列的前两集:第一集和第二集
- 确保你已经导入了航班延误的数据集--如果你没有,请看这个视频。
/*! elementor - v3.6.5 - 27-04-2022 */ .elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-head .elementor-heading-title[class*=elementor-size-]>a{color:herit;font-size:herit;line-height:herit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading.elementor-heading-title.elementor-size-xxl{font-size:59px}
如何成为一名数据科学家
(Tomi Mester的50分钟免费视频课程)
/*! elementor - v3.6.5 - 27-04-2022 */ .elementor-widget-text-editor.elementor-drop-cap-view-stacked .elementor-drop-cap{background-color:#818a91;color:#fff}.elementor-widget-text-editor。elementor-drop-cap-view-framed .elementor-drop-cap{color:#818a91;border:3px solid;background-color:transparent}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) 。elementor-drop-cap{margin-top:8px}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap-letter{width:1em;height:1em}.elementor-widget-text-editor.elementor-drop-cap{float:left;text-align:center;line-height:1;font-size:50px}.elementor-widget-text-editor .elementor-drop-cap-letter{display:inline-block}。
只需在这里订阅Data36新闻通讯(免费)!
/*! elementor-pro - v3.7.1 - 16-05-2022 */ .elementor-button.elementor-hidden,.elementor-hidden{display:none}.e-form__step{width:100%}.e-form__step:not( .elementor-hidden){显示:-webkit-box;显示:-ms-flexbox;显示:flex;-ms-flex-wrap:wrap;flex-wrap:wrap}.e-form__buttons{-ms-flex-wrap:wrap;flex-wrap:wrap}.e-form__buttons,.e-form__buttons__wrapper{显示:-webkit-box;显示:-ms-flexbox;显示:flex}.e-form__indicators{-webkit-box-pack:justify;-ms-flex-pack:justify;justify-content:space-between;-ms-flex-wrap:nowrap;flex-wrap:nowrap;font-size:13px;margin-bottom:var(-e-form-steps-indicators-spacing)}.e-form__indicators,.e-form__indicators__indicator{显示:-webkit-box;显示:-ms-flexbox;显示:flex;-webkit-box-align:center;-ms-flex-align:center;对齐-项目:center}.e-form__indicators__indicator{-webkit-box-orient:vertical;-webkit-box-direction:normal;-ms-flex-direction:column;flex-direction:column;-webkit-box-pack:center;-ms-flex-pack:center;justify-content:center;-ms-flex-preferred-size:0;flex-base:0; padding:0 var(-e-form-steps-divider-gap) }.e-form__indicators__indicator__progress{width:100%;position:relative;background-color:var(--e-form-steps-indicator-progress-background-color); border-radius:var(--e-form-steps-indicator-progress-border-radius); overflow:hidden}.e-form__indicators__indicator__progress__meter{width:var(--e-form-steps-indicator-progress-meter-width,0);height:var(--e-form-steps-indicator-progress-height);line-height:var(-e-form-steps-indicator-progress-height);padding-right:15px;border-radius:var(--e-form-steps-indicator-progress-border-radius);background-color:var(--e-form-steps-indicator-progress-color);color:var(-e-form-steps-indicator-progress-meter-color);text-align:right;-webkit-transition:width .1s linear;-o-transition:width .1s linear;transition:width .1s linear}.e-form__indicators__indicator:first-child{padding-left:0}.e-form__indicators__indicator:last-child{padding-right:0}。e-form__indicators__indicator--state-inactive{color:var(-e-form-steps-indicator-inactive-primary-color,#c2cbd2)}.e-form__indicators__indicator--state-inactive [class*=indicator--shape-] :not(.e-form__indicators__indicator--shape--none){background-color:var(-e-form-steps-indicator--active-secondary-color,#fff)}.e-form__indicators__indicator--state-active object, .e-form__indicators__indicator--state-active svg{fill:var(-e-form-steps-indicator--active-primary-color,#c2cbd2)}。e-form__indicators__indicator--state-active{color:var(--e-form-steps-indicator-active-primary-color,#39b54a); border-color:var(--e-form-steps-indicator-active-secondary-color,#fff)}.e-form__indicators__indicator--state-active [class*=indicator--shape]:not(.e-form__indicators__indicator--shape-none){background-color:var(--form-steps-indicator-active-secondary-color,#fff)}.e-form__indicators__indicator--state-active object,.e-form__indicators__indicator--state-active svg{fill:var(-e-form-steps-indicator-active-primary-color,#39b54a)}.e-form__indicators__indicator--state-completed{color:var(-e-form-step-indicator-completed-secondary-color,#fff)}.e-form__indicators__indicator--state-completed [class*=indicator--shape-]:not(.e-form__indicators__indicator--shape--none){background-color:var(-e-form-step-indicator--completed-primary-color,#39b54a)}.e-form__indicators__indicator--state-completed .e-form__indicators__indicator__label{color:var(-e-form-step-indicator--completed-primary-color,#39b54a)}.e-form__indicators__indicator--state-completed .e-form__indicators__indicator--shape-none{color:var(-e-form-steps-indicator-completed-primary-color,#39b54a); background-color:initial}.e-form__indicators__indicator--state-completed object,.e-form__indicators__indicator--state-completed svg{fill:var(-e-form-steps-indicator-completed-secondary-color,#fff)}.e-form__indicators__indicator__icon{width:var(--e-form-steps-indicator-padding,30px);height:var(--e-form-steps-indicator-padding,30px);font-size:var(-e-form-steps-indicator-size);border-width:1px;border-style:solid;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-pack:center;-ms-flex-pack:center;justify-content:center;-webkit-box-align;-ms-flex-align;align-items:center;overflow:hidden;margin-bottom:10px}.e-form__indicators__indicator__icon img,.e-form__indicators__indicator__icon object,.e-form__indicators__indicator__icon svg{width:var(-e-form-steps-indicator-icon-size);height:auto}.e-form__indicators__indicator__icon .e-font-icon-svg{height:1em}。e-form__indicators__indicator__number{width:var(--e-form-steps-indicator-padding,30px);height:var(--e-form-steps-indicator-padding,30px); border-width:1px; border-style:solid; display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-pack:center;-ms-flex-pack:center;justify-content:center;-webkit-box-align;-ms-flex-align;align-items:center;margin-bottom:10px}.e-form__indicators__indicator--shape-circle{border-radius:50%}.e-form__indicators__indicator--shape-square{border-radius:0}.e-form__indicators__indicator--shape-rounded{border-radius:5px}.e-form__indicators__indicator--shape-none{border:0}。e-form__indicators__indicator__label{text-align:center}.e-form__indicators__indicator__separator{width:100%;height:var(-e-form-steps-divider-width);background-color:#c2cbd2}.e-form__indicators--type-icon, .e-form__indicators--type-icon_text, .e-form__indicators--type-number, .e-form__indicators--type-number_text{-webkit-box-align:start;-ms-flex-align:start; align-items:flex-start}.e-form__indicators--type-icon .e-form__indicators--type-icon .e-form__indicators__indicator__separator,.e-form__indicators--type-number .e-form__indicators__indicator__separator,.e-form__indicators--type-number_text .e-form__indicators__indicator__separator{margin-top:calc(var(--e-form-steps-indicator-padding, 30px) / 2 - var(--e-form-steps-divider-width, 1px) / 2)}。elementor-field-type-html{display:inline-block}.elementor-login .elementor-lost-password, .elementor-login .elementor-remember-me{font-size:.85em}.elementor-field-type-recaptcha_v3 .elementor-field-label{display:none}.elementor-field-type-recaptcha_v3 .grecaptcha-badge{z-index:1}.elementor-button .elementor-form-spinner{-webkit-box-ordinal-group:4;-ms-flex-order:3;order:3}.elementor-form .elementor-button>span{显示:-webkit-box;显示:-ms-flexbox;显示:flex;-webkit-box-pack:center;-ms-flex-pack:center;justify-content:center;-webkit-box-align:center;-ms-flex-align;align-items:center}.elementor-form.elementor-button .elementor-button-text{white-space:normal;-webkit-box-flex:0;-ms-flex-positive:0;flex-grow:0}.elementor-form .elementor-button svg{height:auto}.elementor-form .e-fon-icon-svg{height:1em}。
我接受Data36的隐私政策。(没有垃圾邮件。只有有用的数据科学相关内容。当你订阅后,我将每周给你发几封邮件,让你了解最新的信息。你会得到文章、课程、作弊器、教程和许多很酷的东西)。
现在就获取!
聚合数据的SQL函数
好了,让我们打开SQL Workbench并连接到你的数据服务器吧
你还记得我们的基本查询吗?
它是。
SELECT *
FROM flight_delays
LIMIT 10;
它返回了这个巨大数据集的前10行。
我们将修改这个查询,以获得5个重要问题的答案。
- 在我们的SQL表中有多少行?(我们将使用SQL
COUNT
函数来实现。) - 我们表中的所有航班的总飞行时间是多少?(这将是一个SQL
SUM
函数。) - 表中所有到达延误的平均数是多少--所有出发延误的平均数又是多少?(SQL
AVG
函数。) - 在我们的SQL表中,最大的距离值是多少?(SQL
MAX
函数。) - 在我们的SQL表中,最小的距离值是多少?(SQL
MIN
函数。)
获得所有这些问题的答案将是非常容易的,我保证。但再次强调:确保你和我一起做编码部分。编码是最容易通过做来学习的。因此,在这一点上请不遗余力:把你在这里看到的所有内容也输入你的SQL管理器,并建立一个坚实的知识基础
好了,让我们看看这个!
SQL COUNT函数。让我们来计算行数!
最简单的聚合函数是在你的SQL表中计算行数。而这正是COUNT
函数的作用。与上面的基本查询相比,你唯一需要改变的是你从你的表中SELECT
。记得吗?它可以是所有内容(*
),也可以是特定的列(arrdelay
,depdelay
, 等等)。现在,让我们用以下方式扩展这个列表 函数.把这个查询复制到SQL Workbench中并运行它。
SELECT
结果是:7275288
。
这个函数本身叫做COUNT
,它说要用每一列(*)
来计算行数......你可以把*
改为任何一列的名字(例如:arrdelay
)--你会得到非常相同的数字。试试这个。
SELECT
对吗?同样的结果:7275288
。
所以,是的,这意味着我们的flight_delays
表中有7275288
行。
注1:只有当你的表中没有NULL
(空单元格)时,这才是真的!(我们没有 )。(我们在flight_delays
数据集中根本就没有NULL
的值。)我稍后会回到NULL
的重要性。
注2:事实上,你不需要这个SQL查询中的LIMIT
子句,因为你的屏幕上只有一行数据。但我想,有时保留它可能会更好,所以即使你输入错误,你的SQL工作台也不会因为不小心试图返回7M多行的数据而冻结。
SQL SUM函数。计算总和!
现在我们想得到所有航班的飞行时间--加起来。换句话说:得到airtime
列中所有数值的总和。SUM
函数的工作逻辑与COUNT
类似。唯一的区别是,在SUM
的情况下,你不能使用*
- 你要指定一个列。 不得不指定一个列。在这种情况下,它将是airtime
列。
试试这个查询。
SELECT
总的通话时间是一个巨大的748015545
分钟。
SQL AVG函数。计算平均数......我是说*平均数。
我们的下一个挑战是计算平均到达延迟值和平均离开延迟值。重要的是要知道,数学中的统计平均数有很多类型。但我们通常指的是名为 平均值- 当我们在日常生活中说*"平均数 "时,通常指的是 "平均数"。(快速提醒:平均值的计算方法是计算数据集中所有数值的总和,然后除以数值的数量)。*
在SQL中,名为AVG
*(当然是 "平均 "的意思)*的函数返回平均值......所以平均类型是我们对它的期望。
注意:好吧,我必须补充一点,许多数据科学家认为,在SQL中,"平均 "这个通用词(AVG
)被用于一个特定的平均类型:平均数,这有点懒惰和含糊。而他们是对的!中位数和模式也是平均数。例如,在Python/Pandas中,计算平均数的函数实际上叫做mean
- 然后还有一个叫做median
的函数来计算中位数。这就更连贯了。好吧,不管你喜不喜欢,在SQL中我们用*AVG*来表示平均值。
其语法和逻辑与前两个SQL函数相同。
你可以通过运行这个查询来试试。
SELECT
其结果是11.36
。
当然,如果你打字,你会得到完全相同的值。
SELECT
但我们不要跑得那么远......与其这样,不如也计算一下arrdelay
的平均值。
SELECT
结果。10.19
很好!
SQL MAX和MIN函数。让我们得到最大值和最小值。
最后,让我们来找出某一列的最大值和最小值。找出这些航班的最大和最小距离,听起来足够有趣。从SQL语法上讲,MIN
和MAX
的操作与SUM
,AVG
和COUNT
一样。
这里是最小距离。
SELECT
结果:11
miles。(伙计,也许下次要骑自行车了。)
SELECT
结果。4962
好了!就是这样--这些是你必须知道的基本SQL函数。
COUNT
SUM
AVG
MAX
MIN
到目前为止,这并不难,所以现在是时候对其进行一些调整了......
介绍一下GROUP BY
子句!
SQL GROUP BY - 用于基本的分割分析和更多...
SQL GROUP BY--理论
作为一个数据科学家,你可能会经常运行细分项目。例如,了解所有航班的平均起飞延误时间是很有趣的(我们刚刚知道是11.36
)。但是当涉及到商业决策时,这个数字完全没有可操作性。
然而,如果我们把这些信息变成一个更有用的格式--比方说,我们把它按机场细分--它就会立即变成我们可以采取行动的东西
下面是一个简化的图表,显示了SQL如何使用GROUP BY
,以创建基于列值的自动细分。
这个过程有三个重要步骤。
第1步- 指定你想用哪些列作为输入。在我们的案例中,我们希望使用机场列表(origin
列)和出发延迟(depdelay
列)。
第2步- 指定你想从哪一(些)列创建你的分段。对我们来说,就是origin
列。SQL会自动检测这一列中的每一个唯一值(在上面的例子中,这些是机场1、机场2和机场3)。然后,它从这些值中创建组(段),并将你的数据表中的每一行排序到正确的组中。
第3步- 最后,它计算每个组(段)的平均数(使用SQLAVG
函数),并在屏幕上返回结果。
这里唯一的新东西是第2步的 "分组"。我们有一个SQL条款用于此。**它被称为GROUP BY
。**让我们看看它的作用。
SQL GROUP BY - 实践中
下面是一个结合了SQLAVG
函数和GROUP BY
子句的查询--做的正是我在上面的理论部分描述的事情。
SELECT
AVG(depdelay),
origin
FROM flight_delays
妙极了!
如果你滚动浏览结果,你会发现有一些机场的平均起飞延误时间超过了30
,甚至是40
。从商业角度来看,了解这些机场的情况是很重要的。另一方面,也值得仔细看看那些好的机场 (depdelay
接近0
) 是如何达到这个理想阶段的。(好吧,我知道,这个商业案例过于简单,但你明白这个道理)。
但是刚才在SQL方面发生了什么?
我们已经选择了两列-- *origin
*和 depdelay
. *origin
*我们选择了两列--.被用来创建段(**GROUP BY origin**
). *depdelay
*用来计算这些路段的平均到达时间(AVG(depdelay)
)。
注意:正如你所看到的,SQL的逻辑不像Python、pandas或bash那样是线性的。如果你写一个SQL查询,它的第一行可能高度依赖于最后一行。当你写长而复杂的查询时,这可能会导致一些意想不到的错误,当然也会让你有点头疼......但这就是为什么我觉得给自己足够的时间来练习基础知识,确保你完全理解SQL中不同子句、函数和其他东西之间的关系是非常非常重要的。
初级数据科学家的第一个月
一个100%实用的在线课程。一个为期6周的模拟在一个真实的创业公司担任初级数据科学家的课程。
"解决真正的问题,获得真正的经验 - 就像在一个真正的数据科学工作中一样。
了解更多...
测试自己 #1 (SQL SUM + GROUP BY)
这里有一个小任务来练习!让我们试着解决这个任务,并仔细检查你是否理解了到目前为止的一切!这很简单。
按月打印总的通话时间!
..
.
准备好了吗?
这是我的解决方案。
SELECT
month,
SUM(airtime)
FROM flight_delays
GROUP BY month;
我做了和以前差不多的事情,但是现在我根据月份创建了组/段--这次我必须使用SUM
,而不是AVG
。
测试自己 #2 (SQL AVG + GROUP BY)
这里是另一个练习。
再次按机场计算平均离港延误,但这次只使用那些飞行超过2000英里的航班(你可以在distance
列中找到这个信息)。
..
.
下面是查询结果。
SELECT
AVG(depdelay),
origin
FROM flight_delays
WHERE distance > 2000
GROUP BY origin;
这项任务有两个启示。
- 你可能已经怀疑过,但现在证实了:你可以用
GROUP BY
和SQL函数使用SQLWHERE
子句。 - 你可以用
WHERE
,甚至可以用那些不属于你的SELECT
语句的列进行过滤。
SQL ORDER BY - 根据一个(或多个)列的值对数据进行排序
比方说,我们想看看哪个机场在2007年是最繁忙的。
使用带有GROUP BY
子句的COUNT
函数,你可以非常容易地得到各机场的出发人数,对吗?我们以前在这篇文章中已经这样做了。
SELECT
COUNT(*),
origin
FROM flight_delays
GROUP BY origin;
问题是:这个列表没有默认排序......要做到这一点,你还需要添加一个SQL子句:ORDER BY
。当你使用它时,你总是要指定你想按哪一列排序......这很简单。
SELECT
COUNT(*),
origin
FROM flight_delays
GROUP BY origin
注意:在COUNT
函数之后得到的列将是一个新的列......而且它必须有一个名字--所以SQL自动将其命名为"count
" (查看上面的最新截图)。当你在ORDER BY
子句中提到这个列时,你必须使用这个新名字。我将在下一篇文章中详细介绍这个问题。如果你觉得奇怪,让我们试试同样的查询,但用ORDER BY origin
,你就会马上明白。
嗯,差不多了。但问题是,最不繁忙的机场在最上面--换句话说,我们得到了一个升序排列的列表。这就是ORDER BY
(至少在我们的PostgreSQL数据库中)的默认情况。但是你可以通过简单地在末尾添加DESC
关键字,将其改为降序。
SELECT
COUNT(*),
origin
FROM flight_delays
GROUP BY origin
ORDER BY count
非常好!这正是我们想看到的!
SQL DISTINCT - 只获取唯一值
这是今天的最后一个新东西。这将是简短而温馨的。
如果你想知道在你的表中有多少个不同的机场。
a) 你可以使用GROUP BY
子句来找出它。(你能想出办法吗? :-)
)b) 你可以通过以下方法更容易地找到它
DISTINCT
DISTINCT
删除所有重复的内容。试试这个。
SELECT DISTINCT(origin)
FROM flight_delays;
现在你有唯一的机场了
顺便说一下,GROUP BY
版本看起来像这样。
SELECT origin
FROM flight_delays
GROUP BY origin;
虽然从结果上看是差不多的,但首选的方法是使用DISTINCT
语法。(当写更复杂的查询时,DISTINCT
会帮助你保持你的查询更简单......但我将在以后的文章中再讨论这个问题)。
测试自己 #3
今天你已经学到了一大堆小而有用的东西。我再给你一个作业,它将总结几乎所有的东西--甚至是之前的两篇文章(第一集和第二集)。这将是一个困难的任务,但你可以做到的!如果做不到,可以尝试一下。如果做不到,试着把它分解成更小的任务,然后建立和测试你的查询,直到你得到结果。
这个任务是
列出。
- 前5架飞机(由
tailnum
) - 按降落次数
- 在
PHX
或SEA
机场 - 星期天
(例如,如果标有tailnumber
'N387SW'
的飞机在2007年的任何一个周日在PHX降落3次,在SEA降落2次,那么它的总数为5。而我们需要总数较高的前5架飞机)。准备好了吗?预备!开始!
.
.
.
完成了吗?这是我的解决方案。
SELECT
COUNT(*),
tailnum
FROM flight_delays
WHERE dayofweek = 7
AND dest IN ('PHX', 'SEA')
GROUP BY tailnum
ORDER BY count DESC
LIMIT 5;
还有一些解释。
SELECT
-" select...COUNT(*),
-" 这个函数计算给定组中的行数;要做到这一点,它需要后面的 子句。GROUP BY
tailnum
-" 这将有助于指定组(在后面的 函数中提到)。GROUP BY
FROM flight_delays
-" 当然是表的名称WHERE dayofweek = 7
- "只针对周日的过滤器AND dest IN ('PHX', 'SEA')
-" 只针对PHX和SEA目的地的过滤器GROUP BY tailnum
-" 这是帮助我们将线路按尾数归入不同组别的子句。ORDER BY count DESC
-" 并让我们按某组中的线路数量排序LIMIT 5;
-" 只列出前5个元素。
总结
就这样吧!你今天学到了很多东西--SQL聚合函数(MIN
,MAX
,COUNT
,SUM
,AVG
),GROUP BY
和两个更重要的SQL条款(DISTINCT
和ORDER BY
)。
如果你能自己完成最后一个练习,我可以告诉你,你已经有了非常好的SQL基础知识!祝贺你!如果没有,不要担心,只要确保你在继续学习第四集之前重新阅读这前三章(第一集,第二集,第三集)就可以了
- 如果你想了解更多关于如何成为一名数据科学家,请参加我的50分钟的视频课程。如何成为一名数据科学家。(它是免费的!)。
- 也可以看看我的6周在线课程。初级数据科学家的第一个月》视频课程。
干杯。
托米-梅斯特
The postSQL functions (SUM, COUNT, AVG, MIN, MAX) and GROUP BY | SQL for Data Analysis Tutorial, ep3appeared first onData36.