我一直相信, 人是能预测未来的, 这应该是前几年看弗洛伊德, 荣格的一些心理学书, 给我的一些感受, 有个片段是关于做梦的, 一个人梦见子弹穿过他自己的头颅, 结果不久, 他就去世了. 这个片段当时给了我很多奇思妙想, 关于人类的潜意识, 也许未来就在潜意识中, 但生活中可能无法察觉到这一点.
开头有点偏了, 也是为了引出一个今天的话题, 我上午有一个面谈, 我冥冥中就感觉会被问到 sql 排序问题, 之前也写过, 做了笔记, 但临场还是给忘了, 然后是一些 GBDT, XGBOOST, 决策树, 随机森林这些话题, 我也是准备没那么充分, 虽说都这些的数学原理我都是推导过的.. 哎...最后也没能再争取一下, 有点难受, 即便如此, 还是给推荐了我的笔记, 希望能遇见伯乐吧, 笔记是真诚的.
排名这块, 我虽然近几个月写了几千行 SQL 了, rank 其实没有写过, Oracle 有这种 rank () 之类的窗口函数, 但 Mysql 是没有的, 要自己来实现一把.
测试数据, 还是用之前联系的 cj.score 表来展示, 数据如下:
mysql> select * from cj.score;
+------+------+-------+
| s_id | c_id | score |
+------+------+-------+
| 0001 | 0001 | 80 |
| 0001 | 0002 | 90 |
| 0001 | 0003 | 99 |
| 0002 | 0002 | 60 |
| 0002 | 0003 | 80 |
| 0003 | 0001 | 80 |
| 0003 | 0002 | 80 |
| 0003 | 0003 | 80 |
+------+------+-------+
8 rows in set (0.00 sec)
首先, 定义两个变量, 就叫 @rank, 和 @pre
给自定义变量赋值有2种方法，一种是用set，另一种使用select ; 而且赋值推荐使用 := 这种方式.
select @rank := 0, @pre := null
然后, 对每条数据进行判断.
@rank := if (@pre=score, @rank, @rank+1) as rank,
@pre := score
算法:
大前提: 先要对数据集中, 该排序字段进行降序
for 遍历第一条记录的时候, @rank 值为0, @pre 值为 null:
if @pre = score:
# 排名不变
@rank + 0
else:
#
@rank + 1
当遍历到第二条记录, 此时 @rank = 1, @pre 为上条记录的score值.
if 当前的 score 值 = 上一条的 score 值, 就排名不变嘛, 还是 @rank;
不等于就 @rank + 1
... 这样就保证相同分数的排名相等, 不同排名会使 rank 增加, 且不会出现间隔
-- step1: 定义变量, 并对数据集按 score 降序
-- 相当于把主表, 添加两个字段
select
a.* ,
b.*
from cj.score as a, (select @rank:=0, @pre:=null) as b
order by a.score desc
+------+------+-------+----------+------------+
| s_id | c_id | score | @rank:=0 | @pre:=null |
+------+------+-------+----------+------------+
| 0001 | 0003 | 99 | 0 | NULL |
| 0001 | 0002 | 90 | 0 | NULL |
| 0001 | 0001 | 80 | 0 | NULL |
| 0002 | 0003 | 80 | 0 | NULL |
| 0003 | 0001 | 80 | 0 | NULL |
| 0003 | 0002 | 80 | 0 | NULL |
| 0003 | 0003 | 80 | 0 | NULL |
| 0002 | 0002 | 60 | 0 | NULL |
+------+------+-------+----------+------------+
8 rows in set (0.00 sec)
select
-- 动态来计算 rank 值
a.score ,
@rank:= if(@pre=a.score, @rank+0, @rank+1) as my_rank ,
@pre:=score
-- 0: 给主表添加上两个字段
from cj.score as a, (select @rank:=0, @pre:=null) as b
order by a.score desc
+-------+---------+-------------+
| score | my_rank | @pre:=score |
+-------+---------+-------------+
| 99 | 1 | 99 |
| 90 | 2 | 90 |
| 80 | 3 | 80 |
| 80 | 3 | 80 |
| 80 | 3 | 80 |
| 80 | 3 | 80 |
| 80 | 3 | 80 |
| 60 | 4 | 60 |
+-------+---------+-------------+
8 rows in set (0.00 sec)
搞定, 这样就已经排序出来了. 最后还是来一个完整版的, 把其他字段补全, 不需要显示的 @pre 字段给干掉.
select
c.s_id,
c.c_id,
c.score,
c.my_rank as score_rank
from (
select
a.s_id,
a.c_id,
a.score,
@rank := if(@pre=score, @rank + 0, @rank + 1) as my_rank,
@pre := score
from cj.score as a, (select @rank:=0, @pre=null) as b
order by a.score desc
) as c
+------+------+-------+------------+
| s_id | c_id | score | score_rank |
+------+------+-------+------------+
| 0001 | 0003 | 99 | 1 |
| 0001 | 0002 | 90 | 2 |
| 0001 | 0001 | 80 | 3 |
| 0002 | 0003 | 80 | 3 |
| 0003 | 0001 | 80 | 3 |
| 0003 | 0002 | 80 | 3 |
| 0003 | 0003 | 80 | 3 |
| 0002 | 0002 | 60 | 4 |
+------+------+-------+------------+
8 rows in set (0.00 sec)
我是前段时间, 无意中发现的, 在通常的认知中, "等于" 和 "不等于" 二者应该是 矛盾关系, 是对立统一的, 但无意间发现 Null 的时候, 并非如此.
先来正常的. 我用一个常用的 超市数据集做演示, 有一个字段 category 产品的分类, 先分别统计它的分类值数量:
select
category,
count(category) as cnt
from cj.super_market
group by category
+--------------+------+
| category | cnt |
+--------------+------+
| 办公用品 | 5687 |
| 家具 | 2244 |
| 技术 | 2028 |
+--------------+------+
3 rows in set (0.01 sec)
mysql> select 5687 + 2244 + 2028;
+--------------------+
| 5687 + 2244 + 2028 |
+--------------------+
| 9959 |
+--------------------+
1 row in set (0.00 sec)
可以看到总数: 办公用品 + 家具 + 技术 = 5687 + 2244 + 2028 = 9959
先进行过滤, 将家具排除掉, 理论上, 总数应该是 : 5687 + 2028 = 7715
select count(id) from cj.super_market where category != ‘家具‘;
7715
哦.. . 是对的, 我好像之前自己写 SB 了...呀, 心态崩了, 之前以为的 bug 是把 2028 写成了2208 导致错误..
想说的是, 当值有 null 的时候, 要单独来考虑...
我傻了, 弄混淆了 count(*) 是会计算所有的行数, 包括 null 的, 而 count(col) 会忽略 null 的行, 导致总数对不上, 此刻我对自己有些无语...
嗯不举例了, 我现在需要冷静一下, 好好回溯下这个问题.
原文：https://www.cnblogs.com/chenjieyouge/p/13290832.html