1.SQL中的排序
- Order by可以对一个列或者多个列进行排序;
- Order by后面的排序对象可以是列名、表达式、别名、序号;
- Order by默认升序排列,作用于后面所有排序对象;
- 如果要降序排列,可以在排序对象后面跟上desc;
- desc只作用于其前面最近的一个排序对象;
- 如果有多个对象都要降序则都要加上desc;
(1)单列排序
SQL> select * from emp order by sal desc;
SQL> select empno,ename,sal,sal12 from emp order by sal12 desc;
SQL> select empno,ename,sal,sal12 annsal from emp order by annsal desc;
SQL> select empno,ename,sal,sal12 annsal from emp order by 4 desc;
(2)多列排序
SQL> select * from emp order by deptno,sal;
SQL> select * from emp order by deptno,sal desc;
SQL> select * from emp order by deptno desc,sal desc;
2.MapReduce中的排序
MapReduce中的对象排序需要满足以下条件:
- 对象类实现接口WritableComparator;
- 对象类重写方法write()和readFields();
- 对象类重写方法compareTo();
- 任务只有Mapper,没有Reducer(防止去重);
示例1:单列排序:查询员工信息,按照工资降序排列。
//对象类:Employee.java,实现WritableComparable接口,重写compareTo()方法
package demo.sort.mr.object;
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import org.apache.hadoop.io.WritableComparable;
//实现接口:WritableComparable
public class Employee implements WritableComparable<Employee> {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private int sal;
private int comm;
private int deptno;
@Override
public String toString() {
return "[" + this.ename + "\t" + this.sal + "]";
}
@Override
public int compareTo(Employee other) {
// 按工资降序排列
if (this.sal >= other.getSal()) {
return -1;
} else {
return 1;
}
}
@Override
public void readFields(DataInput input) throws IOException {
// 反序列化
this.empno = input.readInt();
this.ename = input.readUTF();
this.job = input.readUTF();
this.mgr = input.readInt();
this.hiredate = input.readUTF();
this.sal = input.readInt();
this.comm = input.readInt();
this.deptno = input.readInt();
}
@Override
public void write(DataOutput output) throws IOException {
// 序列化
output.writeInt(this.empno);
output.writeUTF(this.ename);
output.writeUTF(this.job);
output.writeInt(this.mgr);
output.writeUTF(this.hiredate);
output.writeInt(this.sal);
output.writeInt(this.comm);
output.writeInt(this.deptno);
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
//Mapper文件:EmployeeSortMapper.java
package demo.sort.mr.object;
import java.io.IOException;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;
public class EmployeeSortMapper extends Mapper<LongWritable, Text, Employee, NullWritable> {
@Override
protected void map(LongWritable key1, Text value1, Context context)
throws IOException, InterruptedException {
// 把读入的数据封装成一个员工对象
// 数据格式:7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
String str = value1.toString();
// 分词
String[] words = str.split(",");
// 创建一个员工对象
Employee e = new Employee();
// 设置员工的属性
e.setEmpno(Integer.parseInt(words[0]));
e.setEname(words[1]);
e.setJob(words[2]);
try {
e.setMgr(Integer.parseInt(words[3]));
} catch (Exception ex) {
e.setMgr(0);
}
e.setHiredate(words[4]);
e.setSal(Integer.parseInt(words[5]));
try {
e.setComm(Integer.parseInt(words[6]));
} catch (Exception ex) {
e.setComm(0);
}
e.setDeptno(Integer.parseInt(words[7]));
// 输出:员工对象,null
context.write(e, NullWritable.get());
}
}