以卖香蕉为例,从4个方面了解SQL的数据汇总

导读:面对一个新数据集时,人们往往会关心数据中的异常值、数据的分布形式、行列之间的关系等。SQL是一种专为数据计算设计的语言,其中已经内置了许多数据汇总函数,也支持用户编写SQL命令实现更为复杂的汇总需求。

作者:Matt DeLand,Wagon联合创始人与数据科学家

来源:Datartisan数据工匠(ID:shujugongjiang)

许多电脑使用Excel在面对上千行数据时已力不从心,而R较难部署在集群上运行,人眼显然不可能直接从大量数据中总结出规律。如何才能快速理解你的数据集?SQL可以帮助你! 对数据进行统计汇总是能最快了解数据的方法。面对一个新数据集时,人们往往会关心数据中的异常值、数据的分布形式、行列之间的关系等。SQL是一种专为数据计算设计的语言,其中已经内置了许多数据汇总函数,也支持用户编写SQL命令实现更为复杂的汇总需求。本文以香蕉销售相关数据为例,从4个方面介绍如何用SQL进行数据汇总。

01 基础汇总

我们可以通过一段很短的SQL命令实现如计算个数(count)、去重(distinct)、求和(sum)、求平均(average)、求方差(variance)等汇总需求。假设我们有一个关于香蕉交易的数据表格,需要计算每天的顾客总数(num_customers)、去重顾客数(distinct_customers)、香蕉销量(total_bananas)、总收入(total_revenue)和每笔平均收入(revenue_per_sale),可以通过以下命令实现:

得到的结果如下:

仅通过一次命令请求,我们就可以在非常大的数据集上计算出这些重要的汇总结果。如果再加上where或join命令,我们还可以高效地对数据进行切分。当然,有些需求并不能完全由一般的SQL函数实现。

02 计算分位数

如果数据的分布存在较大的偏斜,平均值并不能告诉我们平均等待时间的分布情况。因此我们往往需要知道数据的25%、50%、75%分位数是多少。

许多数据库已经内建了分位数函数(包括Postgres 9.4、Redshift、SQL Server)。下面的例子使用percentile_cont函数计算等待时间的分位数。该函数是一个窗口函数,可以按天进行分组计算。

计算结果如下:

其他窗口函数的结构和percentile_cont函数类似,我们可以指定对数据如何排序、如何分组。如果我们想要增加更多分组维度(如具体时间段),只需要将它们添加到partition和group by子句中。对于不支持percentile_cont的数据库,命令会更复杂一些,但仍然可以实现。主要问题是如何将每天的订单各自按等待时间递增的顺序排序,然后取出其中位数值。在MySQL中我们可以使用局部变量来跟踪订单,在Postgres中,我们可以使用row_number函数:

计算结果如下:

03 直方图

直方图是大致了解数据分布的好方法。我们可以用以下命令来计算每笔交易收入的分布:

由于每个不同的收入都会占用一行,以上命令的结果行数将会非常多。我们需要将收入值分组以方便我们得到数据分布的大致印象,比如分为$0-$5、$5-$10等组。如何分组并没有一个标准的做法,需要我们自己根据需要,进行实验来选择。组别过多和过少都不合理,一般使用20个左右的组即可,也可以指定分组的宽度,分组越宽,分组数就越少。以下是指定分组宽度的例子:

计算结果如下:

这个命令将每个收入数据值向下取整到5的倍数并以此分组,即分组宽度为5。这种方法有个缺点,当某个区间内没有记录(比如在55-60美元之间没有人购买),那么结果中将不会有这个组别,这也可以通过编写更复杂的SQL语句来解决。如果我们想要自行选择区间的大小,首先需要计算数据的最大值和最小值,以便我们了解需要设定多少个区间。我们还可以用以下命令来使得每个区间有一个好看的标签:

得到的结果如下:

04 联合分布

比较两个不同的指标也是总结数据时的重要步骤。比如我们可能关心等待时间太久的人,最终是否会花费较少的钱。为了得到等待时间和收入之间的大致关系,我们可以使用以下命令:

得到的结果如下:

我们可能也关心诸如协方差、方差这类统计指标。大多数SQL实现已经内建了这些统计函数,比如在Postgres或Redshift中我们可以使用以下命令:

Postgres中内建了诸多汇总函数,甚至包括线性回归。

原文:http://blog.yhat.com/posts/summarizing-data-in-SQL.html

原文发布于微信公众号 - 大数据(hzdashuju)

原文发表时间:2018-04-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏UAI人工智能

[译] TensorFlow 白皮书

55660
来自专栏hightopo

数百个 HTML5 例子学习 HT 图形组件 – 3D 建模篇

30430
来自专栏机器之心

资源 | Facebook开源DrQA的PyTorch实现:基于维基百科的问答系统

选自GitHub 机器之心编译 参与:Panda 今年 4 月,斯坦福大学和 Facebook 人工智能研究所在 arXiv 发布了一个基于维基百科的开放域问题...

47050
来自专栏ACM算法日常

除以3,乘以2(STL+排序)- Codeforces 997D

Polycarp likes to play with numbers. He takes some integer number x, writes it d...

12420
来自专栏PPV课数据科学社区

数据挖掘系列(4)使用weka做关联规则挖掘

前面几篇介绍了关联规则的一些基本概念和两个基本算法,但实际在商业应用中,写算法反而比较少,理解数据,把握数据,利用工具才是重要的,前面的基础篇是对算法的理解,这...

53060
来自专栏小红豆的数据分析

acmer之路(3)四月第一周日志

这一周加入了院足球队,好久都没有感受到在赛场上奔腾的感觉了。不过正好赶上清明,因此也算有充足的时间来码代码。这个月一共码了20题,排名终于冲进了四万名。

13110
来自专栏一心无二用,本人只专注于基础图像算法的实现与优化。

SSE图像算法优化系列二:高斯模糊算法的全面优化过程分享(二)。

      相关链接: 高斯模糊算法的全面优化过程分享(一)      在高斯模糊算法的全面优化过程分享(一)一文中我们已经给出了一种相当高性能的高斯模糊过程,...

46460
来自专栏技术总结

算法(1)

24770
来自专栏ATYUN订阅号

NLP项目:使用NLTK和SpaCy进行命名实体识别

命名实体识别(NER)是信息提取的第一步,旨在在文本中查找和分类命名实体转换为预定义的分类,例如人员名称,组织,地点,时间,数量,货币价值,百分比等。NER用于...

98940
来自专栏决胜机器学习

机器学习(十四) ——朴素贝叶斯实践

机器学习(十四)——朴素贝叶斯实践 (原创内容,转载请注明来源,谢谢) 一、垃圾邮件分类 垃圾邮件分类,即通过读取邮件的内容,并打上标记其是垃圾邮件或者是正常...

36570

扫码关注云+社区

领取腾讯云代金券