概述... 2
Mysql 实现topK. 2
Python+hive 实现 top K问题... 4
概述
TopN子句用于规定要返回的记录的数目。针对某一列。
比如有一张表两个字段: studentId、score,topN问题就是查找分数前几的那些学生。
TopK带着分组的含义。针对两列的情况。
比如有一张表三个字段:studentid、courseName、score,TopK问题就是查找不同的course的分数前几的学生。
TopN比较简单,但是只有sql server支持top关键字。
Mysql 使用limit 关键字支持topn,postgresql使用limit offset 支持topn。Oracle使用ROWNUM关键字支持topn。
TopK问题就比较复杂了,要写多段sql
Mysql 实现topK
首先,我们的数据库很简单:
--
-- 表的结构 `ta`
--
CREATE TABLE `ta` (
`id` int(11) NOT NULL auto_increment,
`clsno` varchar(20) collate utf8_bin NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;
--
-- 导出表中的数据 `ta`
--
INSERT INTO `ta` (`id`, `clsno`, `score`) VALUES
(1, 'aa', 12),
(2, 'aa', 11),
(3, 'aa', 89),
(4, 'bb', 45),
(5, 'bb', 56),
(6, 'bb', 90);
Id为学生id,clsno 为课程名字,score为分数
Mysql不支持top关键字。 可以用limit
SELECT * FROM ta AS T WHERE id IN ( SELECT id FROM ta ORDER BY score DESC limit 0,2);
意思是从第一行起 (包括第一行)查询2条记录。
但这样查询失败。
报错#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
但是可以这样查询:
SELECT * FROM ta AS Tout WHERE id IN ( select t.id from (select * from ta ORDER BY score DESC limit 0,2) as t);
如果是Postgresql 是这样写的:
SELECT id FROM ta ORDER BY score DESC limit 2 offset 0 // 忽略第0条,查询2条记录
典型的topK问题:
SELECT *
FROM ta ass
WHERE 2 > (
SELECT COUNT( 1 )
FROM ta
WHERE clsno = a.clsno
AND score > a.score )
ORDER BY a.clsno, a.score DESC
LIMIT 0 , 30;
结果:
id clsno score
3 aa 89
7 aa 78
6 bb 90
5 bb 56
功能:得到两科目的各自前两名成绩。 俗称TopK问题
如果K=1,可以这么写:
select * from ta a
where not exists
(select 1 from ta where clsno=a.clsno and score>a.score);
Python+hive 实现 top K问题
准备好数据:(分隔符是“\t”)
cat city.txt
1 wh 500
2 bj 600
3 wh 100
4 sh 400
5 wh 200
6 bj 100
7 sh 200
8 bj 300
9 sh 900
> create table city(id int,cname string,value int) row format delimited fields terminated by '\t' STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH '/home/server0/hive/apache-hive-0.13.1-bin/input/city.txt' OVERWRITE INTO TABLE city;
rankk.py
#!/usr/bin/python
import sys
import datetime
count=0
lastkey=''
file = sys.stdin
for line in file:
if not line or not line.strip():
continue
try:
line = line.strip()
cname,value = line.split("\t");
key=cname
if key==lastkey:
count+=1
else:
count=0
lastkey=key
print cname+"\t"+str(count)+"\t"+value
except:
continue
网上大部分udf自定义函数都是用java写的,github都有代码。java写的有个很大的缺陷,因为你要编译代码,那么你会落入无尽的api版本的麻烦中(hadoop0.20、hadoop1.x、hadoop2.x),但是用python写,没有api版本问题。
添加py文件:
add file /home/server0/hadoop/python/rankk.py;
终极版本:
select cname,value from (select TRANSFORM(a.cname,a.value) USING 'python rankk.py' AS(cname,rank,value) from (select id, cname, value from city distribute by cname sort by cname,value desc) a) b where rank<2;
效果:
注意:分隔符只能是“\t”。python的transform只认“\t”做分隔符,什么其他的全都不认。
-end