作者简介: Matt DeLand,Wagon 联合创始人与数据科学家, 毕业于哥伦比亚大学, 曾任教于密歇根大学, 其团队开发了一套 SQL 协同编辑器
文章地址: http://blog.yhat.com/posts/summarizing-data-in-SQL.html 有删改
许多电脑使用 Excel 在面对上千行数据时已力不从心, 而 R 较难部署在集群上运行, 人眼显然不可能直接从大量数据中总结出规律如何才能快速理解你的数据集? SQL 可以帮助你!
对数据进行统计汇总是能最快了解数据的方法面对一个新数据集时, 人们往往会关心数据中的异常值数据的分布形式行列之间的关系等 SQL 是一种专为数据计算设计的语言, 其中已经内置了许多数据汇总函数, 也支持用户编写 SQL 命令实现更为复杂的汇总需求本文以香蕉销售相关数据为例, 从 4 个方面介绍如何用 SQL 进行数据汇总
一基础汇总
我们可以通过一段很短的 SQL 命令实现如计算个数 (count) 去重 (distinct) 求和 (sum) 求平均 (average) 求方差 (variance) 等汇总需求假设我们有一个关于香蕉交易的数据表格, 需要计算每天的顾客总数 (num_customers) 去重顾客数 (distinct_customers) 香蕉销量 (total_bananas) 总收入 (total_revenue) 和每笔平均收入(revenue_per_sale), 可以通过以下命令实现:
- SELECT
- date,
- count(*) as num_customers,
- count(distinct user_id) as distinct_customers,
- sum(bananas_sold) as total_bananas,
- sum(revenue) as total_revenue,
- avg(revenue) as revenue_per_sale
- FROM banana_sales
- GROUP BY date
- ORDER BY date;
得到的结果如下:
仅通过一次命令请求, 我们就可以在非常大的数据集上计算出这些重要的汇总结果如果再加上 where 或 join 命令, 我们还可以高效地对数据进行切分当然, 有些需求并不能完全由一般的 SQL 函数实现
二计算分位数
如果数据的分布存在较大的偏斜, 平均值并不能告诉我们平均等待时间的分布情况因此我们往往需要知道数据的 25Pu% 分位数是多少
许多数据库已经内建了分位数函数 (包括 Postgres 9.4RedshiftSQL Server) 下面的例子使用 percentile_cont 函数计算等待时间的分位数该函数是一个窗口函数, 可以按天进行分组计算
- SELECT
- date,
- percentile_cont (0.25) WITHIN GROUP
- (ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_25,
- percentile_cont (0.50) WITHIN GROUP
- (ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_50,
- percentile_cont (0.75) WITHIN GROUP
- (ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_75,
avg(wait_time) as avg -- 用于比较
- FROM banana_sales
- GROUP BY date
- ORDER BY date;
计算结果如下:
其他窗口函数的结构和 percentile_cont 函数类似, 我们可以指定对数据如何排序如何分组如果我们想要增加更多分组维度(如具体时间段), 只需要将它们添加到 partition 和 group by 子句中对于不支持 percentile_cont 的数据库, 命令会更复杂一些, 但仍然可以实现主要问题是如何将每天的订单各自按等待时间递增的顺序排序, 然后取出其中位数值在 MySQL 中我们可以使用局部变量来跟踪订单, 在 Postgres 中, 我们可以使用 row_number 函数:
- SELECT
- t1.date,
- t1.wait_time as median
- FROM (
- SELECT
- date,
- wait_time,
- ROW_NUMBER() OVER(ORDER BY wait_time PARTITION BY date) as row_num
- FROM banana_sales
- ) t
- JOIN (
- SELECT
- date,
- count(*) as total
- FROM banana_sales
- GROUP BY date
- ) t2
- ON
- t1.date = t2.date
- WHERE t1.row_num =
- CASE when t2.total % 2 = 0
- THEN t2.total / 2
- ELSE (t2.total + 1) / 2
- END;
计算结果如下:
三直方图
直方图是大致了解数据分布的好方法我们可以用以下命令来计算每笔交易收入的分布:
- select
- revenue,
- count(*)
- from banana_sales
- group by revenue
- order by revenue;
由于每个不同的收入都会占用一行, 以上命令的结果行数将会非常多我们需要将收入值分组以方便我们得到数据分布的大致印象, 比如分为 5 10 等组如何分组并没有一个标准的做法, 需要我们自己根据需要, 进行实验来选择组别过多和过少都不合理, 一般使用 20 个左右的组即可, 也可以指定分组的宽度, 分组越宽, 分组数就越少以下是指定分组宽度的例子:
- select
- floor(revenue/5.00)*5 as bucket_floor,
- count(*) as count
- from banana_sales
- group by 1
- order by 1;
计算结果如下:
这个命令将每个收入数据值向下取整到 5 的倍数并以此分组, 即分组宽度为 5 这种方法有个缺点, 当某个区间内没有记录(比如在 55-60 美元之间没有人购买), 那么结果中将不会有这个组别, 这也可以通过编写更复杂的 SQL 语句来解决如果我们想要自行选择区间的大小, 首先需要计算数据的最大值和最小值, 以便我们了解需要设定多少个区间我们还可以用以下命令来使得每个区间有一个好看的标签:
- select
- bucket_floor,
- CONCAT(bucket_floor, 'to', bucket_ceiling) as bucket_name,
- count(*) as count
- from (
- select
- floor(revenue/5.00)*5 as bucket_floor,
- floor(revenue/5.00)*5 + 5 as bucket_ceiling
- from web_sessions_table
- ) a
- group by 1, 2
- order by 1;
得到的结果如下:
四联合分布
比较两个不同的指标也是总结数据时的重要步骤比如我们可能关心等待时间太久的人, 最终是否会花费较少的钱为了得到等待时间和收入之间的大致关系, 我们可以使用以下命令:
- select
- floor(wait_time/10.00)*10 as wait_time_bucket,
- avg(revenue) as avg_revenue
- from banana_sales
- group by 1
- order by 1;
得到的结果如下:
我们可能也关心诸如协方差方差这类统计指标大多数 SQL 实现已经内建了这些统计函数, 比如在 Postgres 或 Redshift 中我们可以使用以下命令:
- select
- corr(wait_time, revenue) as correlation,
- covar_samp(wait_time, revenue) as covariance
- from banana_sales;
Postgres 中内建了诸多汇总函数, 甚至包括线性回归
更多课程和文章尽在微信号: datartisan 数据工匠
来源: http://www.tuicool.com/articles/fqQraab