SQL Sever 中随机抽样，分层抽样和平衡抽样

3.3 对表做抽样

3.3.1 随机抽样

--获取10%的随机样本数据
SELECT t.*
FROM <t> t
WHERE RAND() < 0.1

但是不能用在SQL Sever中，要用 NEWID()，可是有个问题是这样必须为表汇总的所有记录排序,
过于耗费资源，解决办法是使用NEWID()

SELECT TOP PERCENT t.*
FROM <T> t
ORDER BY NEWID()
返回数据的10%

SELECT t.*
FROM <t> t
WHERE RAND(CHECKSUM(NEWID())) < 0.1
这个查询为随机数生成器提供种子，每次调用时的种子都不同，以满足每次调用时对不同值的需求。

3.3.2 可重复的随机样本
使用ID作为随机数生成器的种子：

SELECT t.*
FROM <t> t
WHERE RAND(id) < 0.1

每次输入某个ID时，都会生成同样的随机数，为了获取不同的样本，可以修改种子：
WHERE RAND(id + 1) < 0.1
另一个方法是基于 ROW_NUMBER()的伪随机数生成器。
WITH t as (
			 SELECT t.*, ROW_NUMBER() OVER (ORDER BY col) as seqnum
			 FROM <t> t
	)
SELECT t.*
FROM t
WHERE (t * 17 + 57) % 101 <= 10;

3.3.3 分层比例抽样
分层抽样能够保证列值的分布和整体分布相近，subscribers表有47.18%的活跃的订阅者。
WITH a as (
				SELECT s.*, ROW_NUMBER() OVER (ORDER BY isactive) as seqnum
				FROM subscribers s
					)
SELECT AVG(1.0 * isactive)
FROM a
WHERE seqnum % 100 = 1;

CTE依次为非活跃者和活跃者分配连续的数字，然后每隔99个数据抽取第100个数据，这个程序从每100个
非活跃者中抽取一条数据，从100个活跃者中抽取一条数据。返回值47.18，与原始数据相同

分层抽样的优势在于可以应用于不止一个变量，下面例子，包含以市场和活跃度划分的与原始数据相同比例的样本

WITH s as (
		SELECT s.*, ROW_NUMBER() OVER (ORDER BY market, isactive) as sequm
		FROM subscribers s
)
SELECT AVG(isactive)
FROM s
WHERE sequm % 100 = 1;

3.3.4 平衡的样本

WITH o as (
			SELECT o.*,
			ROW_NUMBER() OVER (PARTITION BY isae ORDER BY NEWID()) as seqnum
			FROM (SELECT o.*,
									 (CASE WHEN paymenttype = 'AE' THEN 1 ELSE 0 END) as isae
						FROM orders o
						WHERE totalprice <= 200
					) o
			)
SELECT orderdate,
			(CASE WHEN isae = 1 THEN totalprice END) as ae,
			(CASE WHEN isae = 0 THEN totalprice END) as notae
FROM o
WHERE seqnum <= 100;