MapReduceを使用したグループランキング
26088 ワード
タイトル:
1.男女各上位3位を出力する.
2.グループ降順で全員の成績を出力する.
3.対応するSQL文.
データ:
scores: name,age,gender,score
回答は次のとおりです.
1.男女各上位3位を出力する.
Javaコード
SQLコード
2.グループ降順で全員の成績を出力する.
Javaコード
SQLコード
添付:Javaコードで使用されるContestPersonクラス
1.男女各上位3位を出力する.
2.グループ降順で全員の成績を出力する.
3.対応するSQL文.
データ:
scores: name,age,gender,score
回答は次のとおりです.
1.男女各上位3位を出力する.
Javaコード
1 class ContestStatTop3Mapper extends Mapper
SQLコード
1 # option 1
2 select m.name,m.age,m.gender,m.score from scores as m
3 where 3>= (
4 select count(*) from scores as s where m.gender = s.gender and m.score <= s.score
5 )
6 order by m.gender,m.score desc,m.age,m.name;
7
8 # option 2
9 select m.name,m.age,m.gender,m.score from scores as m
10 left join scores as s on m.gender = s.gender and m.score <= s.score
11 group by m.name,m.age,m.gender,m.score
12 having count(*) <=3
13 order by m.gender,m.score desc,m.age,m.name;
2.グループ降順で全員の成績を出力する.
Javaコード
1 class ContestStatSortMapper extends Mapper<Object, Text, Text, Text> {
2
3 private Text outKey = new Text();
4 private Text outValue = new Text();
5
6 @Override
7 public void map(Object key, Text value, Context context) throws IOException, InterruptedException {
8 // name, age, gender, score
9 String[] arr = value.toString().split(",");
10 outKey.set(arr[2]); // gender
11 outValue.set(arr[0] + "," + arr[1] + "," + arr[3]); // name,age,score
12 context.write(outKey, outValue);
13 }
14 }
15
16 class ContestStatSortReducer extends Reducer<Text, Text, Text, Text> {
17
18
19 private Text outKey = new Text();
20 private Text outValue = new Text();
21
22 private int maxSize = 3;
23
24 private List<ContestPerson> list = new ArrayList<>(maxSize);
25
26 @Override
27 public void reduce(Text key, Iterable<Text> values, Context context) throws IOException, InterruptedException {
28 String gender = key.toString();
29 list.clear();
30 for (Text t : values) {
31 String[] arr = t.toString().split(",");
32 // name,age,score
33 String name = arr[0];
34 int age = Integer.parseInt(arr[1]);
35 int score = Integer.parseInt(arr[2]);
36 ContestPerson person = new ContestPerson(name,age,gender,score);
37 list.add(person);
38 }
39 //sort by score desc, then by age asc, then by name asc
40 Collections.sort(list, new Comparator<ContestPerson>() {
41 @Override
42 public int compare(ContestPerson o1, ContestPerson o2) {
43 if (o1.score > o2.score) {
44 return -1;
45 } else if (o1.score < o2.score) {
46 return 1;
47 } else {
48 if (o1.age > o2.age) {
49 return 1;
50 } else if (o1.age < o2.age) {
51 return -1;
52 } else {
53 return o1.name.compareTo(o2.name);
54 }
55 }
56 }
57 });
58 //output
59 outKey.set(gender);
60 for(ContestPerson person: list){
61 outValue.set(person.toString());
62 context.write(outKey, outValue);
63 }
64 }
65 }
SQLコード
1 select m.name,m.age,m.gender,m.score from scores as m
2 order by m.gender,m.score desc,m.age,m.name;
添付:Javaコードで使用されるContestPersonクラス
1 class ContestPerson extends Object{
2 public String name;
3 public int age;
4 public String gender;
5 public int score;
6 public ContestPerson(String name, int age, String gender,int score){
7 this.name = name;
8 this.age = age;
9 this.gender = gender;
10 this.score = score;
11 }
12 public String toString(){
13 return this.name + ","+ this.age + "," + this.gender + "," + this.score;
14 }
15 }