二十四、案例:分析日志数据,降序输出TopN

本文介绍了一种基于Hadoop MapReduce实现流量使用量前5名用户的TopN统计方法。通过自定义Bean类、Mapper、Reducer及比较器,实现了用户流量数据的高效处理。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

咱们Hadoop篇正文内容就这么多了,接下来的三篇是三个MapReduce的案例,以代码为主,实现过程相对简单。关注专栏《破茧成蝶——大数据篇》查看相关系列的文章~


目录

一、需求分析

二、代码实现


 

一、需求分析

现有如下四列的日志数据:

分别代表手机号码、上行流量、下行流量、总流量。现在需要对此数据进行加工,输出流量使用量前5的用户信息。

二、代码实现

2.1 首先定义一个Bean类,值得注意的是这里面有一个compareTo的比较方法,实现降序排序。

package com.xzw.hadoop.mapreduce.topn;

import org.apache.hadoop.io.WritableComparable;

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;

/**
 * @author: xzw
 * @create_date: 2020/12/7 10:40
 * @desc:
 * @modifier:
 * @modified_date:
 * @desc:
 */
public class TopNBean implements WritableComparable<TopNBean> {
    private long upFlow;
    private long downFlow;
    private long sumFlow;

    public TopNBean() {
    }

    public TopNBean(long upFlow, long downFlow) {
        super();
        this.upFlow = upFlow;
        this.downFlow = downFlow;
    }

    public long getUpFlow() {
        return upFlow;
    }

    public void setUpFlow(long upFlow) {
        this.upFlow = upFlow;
    }

    public long getDownFlow() {
        return downFlow;
    }

    public void setDownFlow(long downFlow) {
        this.downFlow = downFlow;
    }

    public long getSumFlow() {
        return sumFlow;
    }

    public void setSumFlow(long sumFlow) {
        this.sumFlow = sumFlow;
    }

    @Override
    public String toString() {
        return upFlow + "\t" + downFlow + "\t" + sumFlow;
    }

    @Override
    public int compareTo(TopNBean o) {//比较方法,降序排序
        return Long.compare(o.sumFlow, this.sumFlow);
    }

    public void set(long upFlow, long downFlow) {
        this.downFlow = downFlow;
        this.upFlow = upFlow;
        this.sumFlow = downFlow + upFlow;
    }


    @Override
    public void write(DataOutput dataOutput) throws IOException {
        dataOutput.writeLong(upFlow);
        dataOutput.writeLong(downFlow);
        dataOutput.writeLong(sumFlow);
    }

    @Override
    public void readFields(DataInput dataInput) throws IOException {
        upFlow = dataInput.readLong();
        downFlow = dataInput.readLong();
        sumFlow = dataInput.readLong();
    }
}

2.2 定义Mapper类

package com.xzw.hadoop.mapreduce.topn;

import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;

import java.io.IOException;

/**
 * @author: xzw
 * @create_date: 2020/12/7 10:57
 * @desc:
 * @modifier:
 * @modified_date:
 * @desc:
 */
public class TopNMapper extends Mapper<LongWritable, Text, TopNBean, Text> {
    private TopNBean k = new TopNBean();
    private Text v = new Text();

    @Override
    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
        String[] fields = value.toString().split("\t");

        v.set(fields[0]);
        k.set(Long.parseLong(fields[1]), Long.parseLong(fields[2]));

        context.write(k, v);
    }
}

2.3 定义让所有数据分到同一组的比较器

package com.xzw.hadoop.mapreduce.topn;

import org.apache.hadoop.io.WritableComparable;
import org.apache.hadoop.io.WritableComparator;

/**
 * @author: xzw
 * @create_date: 2020/12/7 11:16
 * @desc: 让所有数据分到同一组的比较器
 * @modifier:
 * @modified_date:
 * @desc:
 */
public class TopNComparator extends WritableComparator {
    protected TopNComparator() {
        super(TopNBean.class, true);
    }

    @Override
    public int compare(WritableComparable a, WritableComparable b) {
        return 0;
    }
}

2.4 定义Reducer类

package com.xzw.hadoop.mapreduce.topn;

import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Reducer;

import java.io.IOException;
import java.util.Iterator;

/**
 * @author: xzw
 * @create_date: 2020/12/7 11:04
 * @desc:
 * @modifier:
 * @modified_date:
 * @desc:
 */
public class TopNReducer extends Reducer<TopNBean, Text, Text, TopNBean> {
    @Override
    protected void reduce(TopNBean key, Iterable<Text> values, Context context) throws IOException,
            InterruptedException {
        Iterator<Text> iterator = values.iterator();
        for (int i = 0; i < 5; i++) {
            if (iterator.hasNext()) {
                context.write(iterator.next(), key);
            }
        }
    }
}

2.5 定义Driver类

package com.xzw.hadoop.mapreduce.topn;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;

import java.io.IOException;

/**
 * @author: xzw
 * @create_date: 2020/12/7 11:21
 * @desc:
 * @modifier:
 * @modified_date:
 * @desc:
 */
public class TopNDriver {
    public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
        args = new String[]{"e:/input/topn.txt", "e:/output1"};

        Job job = Job.getInstance(new Configuration());

        job.setJarByClass(TopNDriver.class);
        job.setMapperClass(TopNMapper.class);
        job.setReducerClass(TopNReducer.class);

        job.setMapOutputKeyClass(TopNBean.class);
        job.setMapOutputValueClass(Text.class);
        job.setOutputKeyClass(Text.class);
        job.setGroupingComparatorClass(TopNComparator.class);
        job.setOutputValueClass(TopNBean.class);

        FileInputFormat.setInputPaths(job, new Path(args[0]));
        FileOutputFormat.setOutputPath(job, new Path(args[1]));

        boolean b = job.waitForCompletion(true);
        System.exit(b ? 0 : 1);
    }
}

2.6 测试结果

<think>好的,用户现在想了解TopN的SQL写法。之前他们已经问过数仓工程师面试的常见SQL题,其中在进阶篇提到了使用窗口函数处理TopN的问题。现在用户可能是在准备面试,或者在实际工作中遇到了需要获取前几名数据的需求,所以需要更详细的解释和示。 首先,我需要回顾TopN的常见实现方法,主要是使用窗口函数,比如ROW_NUMBER、RANK、DENSE_RANK。不同的函数会影响排名结果,尤其是在有并列的情况下。如,ROW_NUMBER会生成唯一的序号,而RANK允许并列但会跳过后续名次,DENSE_RANK则不会跳过名次。用户可能对这三者的区别不太清楚,需要详细说明。 然后,用户可能想知道在不同的数据库系统中如何应用这些函数,比如MySQL、PostgreSQL、Hive等,语法是否一致。此外,除了窗口函数,是否还有其他方法实现TopN,比如使用子查询和LIMIT(在MySQL中)或者使用变量(在旧版本的MySQL中)。不过窗口函数更为通用和高效,尤其是在处理分组的TopN时。 另外,用户可能会遇到需要处理并列情况的需求,比如前两名如果有三个人的分数相同,是否需要都选上。这时候可能需要用RANK或DENSE_RANK,并根据具体需求调整。同时,示中的部门前三名员工问题,是否允许并列,或者必须严格前三,这也会影响函数的选择。 还要考虑性能问题,尤其是在大数据量的情况下,窗口函数和子查询哪种更高效。可能还需要提到索引和分区对查询性能的影响,尤其是在数仓环境中,数据量通常较大,优化策略很重要。 用户可能需要实际应用的子,比如销售排名、成绩排名、访问量TopN的页面等,不同场景下的实现方式是否不同。同时,可能需要注意在编写SQL时的常见错误,比如忘记PARTITION BY导致全局排名而不是分组排名,或者ORDER BY的方向错误(升序还是降序)。 最后,是否需要对比不同数据库系统的语法差异,比如Hive中使用DISTRIBUTE BY和SORT BY来替代PARTITION BY和ORDER BY,或者某些数据库对窗口函数的支持情况。这些细节对于用户的实际应用可能很重要,尤其是在跨平台开发时。 总结下来,用户的需求不仅仅是获取TopN的SQL写法,还包括理解不同函数的区别、应用场景、性能优化以及跨数据库的兼容性。因此,在回答时需要涵盖这些方面,提供清晰的示,并指出常见注意事项和最佳实践。</think>以下是关于 **TopN查询** 的SQL实现方法详解,涵盖常见场景和优化技巧: --- ### **一、基础TopN实现** #### **1. 使用`ROW_NUMBER()`窗口函数** **场景**:严格取每个分组的第1~N名(无并列)。 **示**:查询每个部门工资最高的3名员工 ```sql SELECT dept_id, emp_name, salary FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn <= 3; ``` #### **2. 允许并列的排名(`RANK()`与`DENSE_RANK()`)** - **`RANK()`**:允许并列且跳过后续名次(如:1,1,3) - **`DENSE_RANK()`**:允许并列但不跳名次(如:1,1,2) ```sql -- 取工资排名前3(包含所有并列情况) SELECT dept_id, emp_name, salary FROM ( SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM employee ) t WHERE dr <= 3; ``` --- ### **二、特殊场景处理** #### **1. 非窗口函数实现(MySQL 5.x等低版本)** **方法**:通过变量计算排名 ```sql SET @dept_rank = 0, @prev_dept = NULL; SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, @dept_rank := IF(@prev_dept = dept_id, @dept_rank + 1, 1) AS rn, @prev_dept := dept_id FROM employee ORDER BY dept_id, salary DESC ) t WHERE rn <= 3; ``` #### **2. 分组TopN且限制总数** **场景**:全公司取工资最高的10人,但每个部门最多选2人 ```sql WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rn, ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rn FROM employee ) SELECT * FROM ranked WHERE dept_rn <= 2 AND global_rn <= 10; ``` --- ### **三、性能优化技巧** #### **1. 减少数据扫描范围** - 先按分区字段和排序字段过滤,再计算排名: ```sql SELECT dept_id, emp_name, salary FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee WHERE dept_id IN (目标部门列表) -- 提前过滤分区 ) t WHERE rn <= 3; ``` #### **2. 利用索引加速** - 对`(dept_id, salary)`建立复合索引,避免全表扫描。 #### **3. 分页优化** **场景**:分页查询每页Top10 ```sql -- 使用OFFSET(小数据量适用) SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn FROM sales_data ) t WHERE rn BETWEEN 11 AND 20; -- 大数据量推荐:记住上一页最后一条的排序值 SELECT * FROM sales_data WHERE sales < 上一页最小值 ORDER BY sales DESC LIMIT 10; ``` --- ### **四、实战案例** #### **1. 电商销量TopN商品(按类目分组)** ```sql SELECT category_id, product_id, sales FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM product_sales ) t WHERE rn <= 5; -- 每个类目取前5 ``` #### **2. 日志分析:访问量Top10页面** ```sql SELECT page_url, visit_count FROM ( SELECT page_url, COUNT(*) AS visit_count, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn FROM web_log GROUP BY page_url ) t WHERE rn <= 10; ``` --- ### **附:核心函数对比** | **函数** | **排名规则** | **典型场景** | |-------------------|---------------------------|-------------------------| | `ROW_NUMBER()` | 唯一连续序号(1,2,3) | 严格取前N名(如抽奖) | | `RANK()` | 允许并列并跳号(1,1,3) | 体育比赛排名 | | `DENSE_RANK()` | 允许并列不跳号(1,1,2) | 奖学金分段排名 | **提示**:在Hive中需注意数据倾斜问题,可结合`DISTRIBUTE BY`优化分区计算。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

象在舞

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值