换下风格^_^

大数据分析之-分组求TOPN

数据分析 admin 828℃ 0评论

本章大数据分析我们来讨论下如何求分组请求TOPN的问题。

问题描述

我们有一个部门(数据部、BI部,开发部),部门里有各有几个同事,每个同事的工资各不相同。那么如果财务想统计下每个部门工资最高的前3位。好,现在交给你开发分析方法。

数据:

depart name money
数据部 D1 10000
数据部 D2 30000
数据部 D3 50000
数据部 D4 20000
数据部 D5 15000
BI部 B1 30000
BI部 B2 10000
BI部 B3 120000
BI部 B4 20000
开发部 K1 10000
开发部 K2 30000
开发部 K3 50000
开发部 K4 20000
开发部 K5 15000

我们想得到的结果是:

depart name money
数据部 D3 50000
数据部 D2 30000
数据部 D4 20000
BI部 B3 120000
BI部 B1 30000
BI部 B4 20000
开发部 K3 50000
开发部 K2 30000
开发部 K4 20000

首先我们肯定不能统一来一次group by ,因为我们我们需要分组情况下求排名。那么我们该怎么做呢?

方法1-OVER(PARTITION BY)

熟悉oracle和postgreSQL的开窗函数 OVER(PARTITION BY)功能应该知道怎么来实现。开窗函数介绍:http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

SELECT * FROM ( 
               SELECT * row_number  OVER(PARTITION BY depart  ORDER BY money DESC) as rank 
               FROM depart_table 
) WHERE rank <=3 ;

方法2-mysql

然而在mysql里面是没有开窗函数这个用法的。那么mysql里面我们可以使用例外一个特性(group_concat)来实现该功能。
什么?你没用过这个函数?好吧,我先简单介绍下:
GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col …]]
[SEPARATOR str_val])
在 MySQL 中,你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序,可以使用 ORDER BY 子句。
SEPARATOR 是一个字符串值,它被用于插入到结果值中。缺省为一个逗号 (“,”),可以通过指定 SEPARATOR “” 完全地移除这个分隔符。
可以通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下: SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
如果最大长度被设置,结果值被剪切到这个最大长度。如果分组的字符过长,可以对系统参数进行设置:
SET GLOBAL group_concat_max_len=40000;
GROUP_CONCAT和CONCAT的区别可以参考:《MySQL中函数CONCAT及GROUP_CONCAT

好了,知道了GROUP_CONCAT了,那么我们就可以实现上面TOPN的需求了吗?呵呵,我们还得差点。差什么呢?先不急,我们先一步步来。先对部门分组排序:

select depart, group_concat(name order by money desc) as names, group_concat(money, order by money) group by depart_table;

说好的,求TOPN呢。我们在改下:

select depart, substring_index(group_concat(name order by money desc) , "," , 3) as names, 
substring_index(group_concat(money, order by money) , "," , 3) group by depart_table;

好了,TOP 3我们取到了,剩下就是提取他们。唯一不足的地方是不像上面使用开窗函数易于使用。

说了这么多,但是和本文的标题 《大数据分析之-分组求TOPN》核心大数据不沾边啊。下面我们分别来使用Pig,hive来实现下。

方法3-pig

Pig是一个基于Hadoop的大规模数据分析平台,它提供的SQL-LIKE语言叫Pig Latin,该语言的编译器会把类SQL的数据分析请求转换为一系列经过优化处理的MapReduce运算。Pig为复杂的海量数据并行计算提供了一个简单的操作和编程接口。详细的介绍请移步Pig官网

log = LOAD '$INPUT' USING PigStorage(",");
a = GROUP log BY (depart);
b = FOREACH a {
   sorted = ORDER a BY money desc;
   topn = LIMIT sorted 3;
   GENERATE FLATTEN(topn);
}

方法4-hive

Hive是一个基于Hadoop的数据仓库平台。通过hive,我们可以方便地进行ETL的工作。hive定义了一个类似于SQL的查询语言:HQL,能 够将用户编写的QL转化为相应的Mapreduce程序基于Hadoop执行。

Hive是Facebook 2008年8月刚开源的一个数据仓库框架,其系统目标与 Pig有相似之处,但它有一些Pig目前还不支持的机制,比如:更丰富的类型系统、更类似SQL的查询语言、Table/Partition元数据的持久化等。《Hive官网

hive实现上面的需要其实也是使用类似Oracle的 OVER(PARTITION BY)。

select * from (
          select depart,name,money ,rank() over (partition by depart order by money) as rank from depart_table ) tmp
where rank <=3 ;

或者

select * from (
           select depart,name,money ,rank() over (distribute by depart sort by money) as rank from depart_table) tmp
 where rank <=3;

更多使用参考:
LanguageManual WindowingAndAnalytics
EXTRACT TOP N RECORDS IN EACH GROUP IN HADOOP/HIVE
Hive row_number()使用方法

转载请注明:极豆技术博客 » 大数据分析之-分组求TOPN

喜欢 (2)
捐助本站极豆博客全站无广告。如果您觉得本博客的内容对您小有帮助,可以对我小额赞助,您的赞助将用于维持博客运营。

极豆博客

发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址