SQL优化之使用数学的方式优化SQL编码01

前言

笔者最近工作大多写sql为主。所以在此记录一下工作中遇到的问题和解决方案。

今天先写上一篇，之后也会将这类似的方法总结下来，发表在这里。

在特定场景下如何利用数学优化SQL编码

直接拿最近的一个需求举例，楼主习惯先写自己原先的笨方法，也是大多数人一下子就能想到的方法，然后在去写改进的方法。

好了，说下具体需求：

轻度汇总后的数据涉及到了标签打分。根据某列(数值型)的范围进行划分并打上相应的标签，并用该标签与标签评分表关联拿到评分。

看完需求后，简单啊，开搞。

为了方便，以下数据都用一个标签来讲。

• 标签评分表一个标签对应了多个等级，（label， grade）唯一确定一条数据。如下：

  注意在我们的场景中当汇总表的total_countw 为0时，score分值也为0，并没有包含在前20%的区间中

label(标签)	grade(等级)	score(分值)	desc( (左闭右开)
L1	A	1000	前20%
L1	B	800	20%—40%
L1	C	600	40%—60%
L1	D	400	60%—80%
L1	E	200	后20%
L1	F	0	值为0

• 轻度汇总表table_agg：

id	label(标签)	total_count(该标签出现的次数)
001	L1	390
002	L1	700
003	L1	675
004	L1	5065

1. 首先需要将数据根据百分比将数据的等级标识出来

SELECT  id
       ,label
       ,total_count 
       ,rk
       ,cnt
       ,rk / cnt as percentage 
       ,CASE WHEN total_count = 0 THEN 'F' 
             WHEN rk / cnt > 0 AND rk / cnt < 0.2 THEN 'A' 
             WHEN rk / cnt >= 0.2 AND rk / cnt < 0.4 THEN 'B' 
             WHEN rk / cnt >= 0.4 AND rk / cnt < 0.6 THEN 'C' 
             WHEN rk / cnt >= 0.6 AND rk / cnt < 0.8 THEN 'D' 
             WHEN rk / cnt >= 0.8 THEN 'E' 
        END AS grade
FROM 
(
    SELECT  id
           ,label
           ,total_count
           ,row_number() OVER (ORDER BY total_count DESC) AS rk 
           ,COUNT(*) OVER ()                              AS cnt
    FROM table_agg 
) t;

结果如下：

id	label	total_count	rk	cnt	_c5	grade
004	L1	5065	1	8	0.125	A
007	L1	4078	2	8	0.25	B
002	L1	700	3	8	0.375	B
001	L1	390	4	8	0.5	C
008	L1	335	5	8	0.625	D
006	L1	201	6	8	0.75	D
005	L1	99	7	8	0.875	E
003	L1	0	8	8	1	F

然后根据（label， grade） 去关联标签评分表即可拿到对应的分值。

BUT

如果在0到1区县化分不只为5个区间呢？有时候为了让评分更加细粒度，我们可能会在0到1之前划分为100，500，甚至1000个区间！！

如果按照上面写case when的方式。。。emmm也挺好，一天啥也不用干了，泡杯茶CV大法。

但是这么做写出来的SQL别人看了会骂娘，而且也不保证区间的开闭都正确。

最重要的一点那就是：如果我的区间动态调整了，也就是说我的标签评分表更新了怎么办？人工维护吗?？？

非也！！下面介绍一种从数学上的方法，让你的代码由几十行，几百行都变成短短的一行。

改进

首先，在标签评分表中，如果我们的区间分为了n个，n > 50 …甚至1000，用大写字母ABC来表示已经很难了。

所以我们换种思路，将grade使用自然升序数字：1，2，3 … n 来表示，即：

label(标签)	grade(等级)	score(分值)	desc( (左闭右开)
L1	1	1000	前20%
L1	2	800	20%—40%
L1	3	600	40%—60%
L1	4	400	60%—80%
L1	5	200	后20%
L1	6	0	值为0

然后重点来了，先把改完的sql列出来：

SELECT  id
       ,label
       ,total_count 
       ,rk
       ,cnt
       ,rk / cnt as percentage 
       ,CASE WHEN total_count = 0 THEN '0' ELSE ceil((rk / cnt) * 5) END AS grade
FROM 
(
    SELECT  id
           ,label
           ,total_count
           ,row_number() OVER (ORDER BY total_count DESC) AS rk 
           ,COUNT(*) OVER ()                              AS cnt
    FROM table_agg 
) t;

得到的结果为：

id	label	total_count	rk	cnt	_c5	grade
004	L1	5065	1	8	0.125	1
007	L1	4078	2	8	0.25	2
002	L1	700	3	8	0.375	2
001	L1	390	4	8	0.5	3
008	L1	335	5	8	0.625	4
006	L1	201	6	8	0.75	4
005	L1	99	7	8	0.875	5
003	L1	0	8	8	1	6

然后根据（label， grade） 去关联标签评分表即可拿到对应的分值。

大家看到了，没错！重点那就是

CASE WHEN total_count = 0 THEN '0' ELSE ceil((rk / cnt) * 5) END AS grade

这里使用了ceil这个向上取整的函数，而5则是代表了划分的区间。

至此，用数学方法解决了此类场景下根据百分数多个特定区间划分的问题。

总结

真是妙蛙种子吃着妙脆角妙进了米奇妙妙屋，妙到家了

by 俩只猴

2021.01.04