SQL按权重自动化抽取

§前言§
互联网寒冬已经蔓延，对程序员、数据分析师来说，成为某领域的“专家”这条路比较难走，因为到达某领域的金字塔顶面临两个问题：位置太少，人太多。单纯掌握某一单一领域的知识，可能已经不足以确立自己的不可替代性，不如成为一个“综合性人才”，在具备专业领域技能的同时，也能够在其他领域有所建树。这次来介绍一下数据库SQL，提高大家的竞争力。
数据库技术者，相传在某互联网公司称为“取数技师”，但能把数据库玩6，对于数据分析、机器学习可以起到事半功倍的效果。要知道，对于和数据打交道的人来说，80%的时间用在处理数据上。
§问题分析§
有一组样本，我们从中取出一小部分，进行质检。而抽取的逻辑是多做多抽（按权重），即根据每个个体的量在总量量中的占比，决定抽取多少。
问题的难点在于1.LIMIT、UP 这类函数无法传递参数，只能是固定数字。而对于原数据，个体量、总量每天都在变化，必须要通过传参的方式实现自动化计算、抽取。2.传递的参数类似Python的字典dict，{verifier1：num1，verifier2：num2…verifiern：numn}，而熟悉数据库的小伙伴都知道，SQL无法实现for、while循环。如何另SQL实现循环化，也是需要解决的技术难点。

§SQL代码§

SELECT
	v.object_id,
	v.verifiers,
	v.rate,
	v.risk,
	v.risk_type,
	v.link,
	v.rn,
	t3.choose_num 
FROM
	( SELECT *, 
		row_number ( ) over ( PARTITION BY verifiers ORDER BY verifiers DESC ) AS rn 
	  FROM one 
	  WHERE rate = 12 ) v
JOIN (
	SELECT
		t2.verifiers,
		t2.choose_num 
	FROM
		(
		SELECT
			t1.verifiers,
			t1.rate,
			t1.counter ,
            round( t1.counter / ( sum( counter )over () ) * 100, 0 ) AS choose_num
	        FROM
	    		( 
				SELECT
				verifiers,
				rate,
				COUNT( rate ) AS counter,
				ROUND(
				COUNT( rate ) / ( SELECT COUNT( rate ) FROM one WHERE rate = 12  ) * 100
                 ) AS choose_num 
                FROM
                  		one 
                WHERE
                   	rate = 12 
                GROUP BY
                    verifiers 
                HAVING
                    choose_num >= 5 
                ) t1 
	      ) t2 
  ) t3 ON t3.verifiers = v.verifiers 
WHERE
	v.rn <= t3.choose_num

§解析§
由内向外逐层说明

SELECT
       verifiers,
       rate,
       COUNT( rate ) AS counter,
       ROUND(
               COUNT( rate ) / ( SELECT COUNT( rate ) FROM one WHERE rate = 12  ) * 100
       ) AS choose_num 
FROM
       one 
WHERE
       rate = 12 
GROUP BY
       verifiers 

这段代码主要是将rate=12 的数据按照个人维度进行分组，求出个人量与被抽取数（总量100）。结果如下：
另外比较特殊的一点是，如果抽取数在小于5，则对样本准确性有影响，因此这里用HAVING语句做限制，故我们将少于5的D相关数据去掉，结合上述语句结果如下

HAVING
   choose_num >= 5 

我们可以看到，此时样本已经去掉抽取数小于5的人员，总量为98，不足100。

但是，这样一来抽取样本总量就不足100，我们需要将去除的数据（抽取数小于5）补回来，使总量由98→100。
为实现这一步，我们用以下代码。

round( t1.counter / ( sum( counter ) over ( ) ) * 100, 0 ) AS choose_num

至此，我们可以按照权重科学的将缺少的样本”分配“到对应人员，使样本量重新达到100。重点说一下，sum后需要加over，不然无法历遍所有人员。我们看到，G、H两人抽取数分别增加1个。

接下来，就是把人员对应的抽取数结果(choose_num)，作为参数指标传递到外层。开头说过，LIMIT、UP 只能接受具体数值，无法接受参数。这里我们使用最简单的WHERE解决这一问题，具体如下

WHERE
	v.rn <= t3.choose_num

至此，按权重自动化抽取完成，并获得全部相关信息。

最后，附上一首我用SQL语言写的诗

SELECT
you
FROM
whole word
WHERE
the best one