【mysql】统计排名

背景

关于排名最近的项目中有很多应用场景，例如：年度账单中注册时间打败了xx%的用户，投资金额打败了xx%的用户，以及标签中营销敏感度排名在xx%--xx%之间等等，都涉及到对某个字段数据进行排名。下面就让我们看一下排名在mysql中的实现吧。

技术与实现

• 用户自定义变量
  用户可以自己定义一个变量，然后将想要的值存储在变量中，可以在后面的语句中引用该变量。用户的自定义变量具有会话专一性，即一个客户端创建的自定义变量不能被其他客户端所使用，当客户端退出时，该客户端创建的所有自定义变量会自动释放。
  用户自定义变量的形式：@variableName
  设置自定义变量;set @a=0,@b:=0; select @c:=0;

• ：=与=

  • =
    只有在set、update时才跟:=一样是赋值作用，其他都是等于作用。

  • :=赋值作用，不止在set、update时是赋值作用，在select 时也是赋值作用。

• 实现
  表结构

  CREATE TABLE `annual_bill_result` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长id',
    `year` int(11) NOT NULL DEFAULT '0' COMMENT '年份',
    `bill_type` enum('ANNUAL','SEMI_ANNUAL') NOT NULL DEFAULT 'ANNUAL' COMMENT '账单类型： \n''ANNUAL''：年度账单,\n''SEMI_ANNUAL''：半年度账单',
    `user_id` int(11) NOT NULL COMMENT '用户id',
    `first_invest_money` decimal(24,2) DEFAULT '0.00' COMMENT '首投金额'
  ) ENGINE=InnoDB AUTO_INCREMENT=2188973 DEFAULT CHARSET=utf8 COMMENT='年度账单结果表'
  

  • 方法1

  -- 定义自定义变量rownum，并初始化为0
  set @rownum:=0;
  -- 按照首投金额的降序和用户id的升序，实现排名
  SELECT @rownum:=@rownum+1 AS rownum, user_id,first_invest_money from  big_data.annual_bill_result
  WHERE year = 2018
  ORDER BY first_invest_money DESC,user_id ASC;
  

  • 方法2

  SELECT @rownum:=@rownum+1 AS rownum, user_id,first_invest_money from  (SELECT @rownum:=0) r,annual_bill_result
  WHERE year = 2018
  ORDER BY first_invest_money DESC,user_id ASC;;
  

  • 结果

    
    
    image.png