通过SQL进行数据分发

业务背景：工作中遇到一个需求，有一张门店表、还有一张用户表。需要将用户通过门店的销售额占比分发给各个门店。所以就产生了这篇文章

思路：根据每个店的销售额及所有店的总额，求出每个店的销售占比。根据销售占比、将一万人的客户分发给各个店铺

示例数据如下：

## 门店表
CREATE TABLE `store_mapping` (
  `store_id` varchar(255) NOT NULL COMMENT '店铺编码',
  `amt_rate` varchar(255) DEFAULT NULL COMMENT '销售占比',
  PRIMARY KEY (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
## 用户表
CREATE TABLE `consumer` (
  `user_id` varchar(255) NOT NULL COMMENT '用户编码',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
## 最终数据存储表
CREATE TABLE `distribute_over` (
  `user_id` int NOT NULL COMMENT '用户编码',
  `store_id` int DEFAULT NULL COMMENT '门店编码',
  `rn` varchar(255) DEFAULT NULL COMMENT 'rank值',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into store_mapping values ("1001","0.05");
insert into store_mapping values ("1002","0.35");
insert into store_mapping values ("1003","0.2");
insert into store_mapping values ("1004","0.07");
insert into store_mapping values ("1005","0.33");
insert into consumer 
SELECT
	1000000+rn1 
FROM
	( SELECT row_number () over () rn1 FROM car ##这张表是随便取的，你可以取一张大于10000条数据的表
	) a 
	LIMIT 10000;

follow me、follow me

1.每个门店应分发的人数

SELECT
	store_id,
	round( amt_rate * user_cnt, 0 ) AS "每个门店应分发的人数" 
FROM
	store_mapping,
	( SELECT count( user_id ) AS user_cnt FROM consumer ) u

2. 按照每个门店应分发的人数累计计算(求rank值)

SELECT
	sdd_2.store_id,
	sum( sdd_1.store_distribute_people )+ 1 
FROM
	(
	SELECT
		store_id,
		round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
	FROM
		store_mapping,
		( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
	) sdd_1,##store_distribute_detail_1,
	(
	SELECT
		store_id,
		round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
	FROM
		store_mapping,
		( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
	) sdd_2 ##store_distribute_detail_2,
WHERE
	sdd_1.store_id <= sdd_2.store_id 
GROUP BY
	sdd_2.store_id 

3.数据错位

SELECT
	store_id,
CASE
		
		WHEN lag ( rn1, 1 ) over () IS NULL THEN
		1 ELSE lag ( rn1, 1 ) over () 
	END AS rn2,
	rn1 
FROM
	(
	SELECT
		sdd_2.store_id,
		sum( sdd_1.store_distribute_people )+ 1 AS rn1 
	FROM
		(
		SELECT
			store_id,
			round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
		FROM
			store_mapping,
			( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
		) sdd_1,##store_distribute_detail_1,
		(
		SELECT
			store_id,
			round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
		FROM
			store_mapping,
			( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
		) sdd_2 ##store_distribute_detail_2,
		
	WHERE
		sdd_1.store_id <= sdd_2.store_id 
	GROUP BY
		sdd_2.store_id 
	) a 

4. 开始分发

若你的SQL支持ISNULL/NVL函数，可以替代下面的CASE WHEN。

-- insert into distribute_over --确认数据没有问题、落入最终表
SELECT
	user_id,store_id,rn
FROM
	( SELECT user_id, row_number () over ( ORDER BY user_id ) rn FROM consumer ) user_info
	LEFT JOIN (
	SELECT
		store_id,
	CASE
			WHEN lag ( rn1, 1 ) over () IS NULL THEN
			1 ELSE lag ( rn1, 1 ) over () 
		END AS rn2, -- 若你的sql支持ISNULL/NVL那么就不需要case when了
		rn1 
	FROM
		(
		SELECT
			sdd_2.store_id,
			sum( sdd_1.store_distribute_people )+ 1 AS rn1 
		FROM
			(
			SELECT
				store_id,
				round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
			FROM
				store_mapping,
				( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
			) sdd_1,##store_distribute_detail_1,
			(
			SELECT
				store_id,
				round( amt_rate * user_cnt, 0 ) AS store_distribute_people 
			FROM
				store_mapping,
				( SELECT count( user_id ) AS user_cnt FROM consumer ) u 
			) sdd_2 ##store_distribute_detail_2,
			
		WHERE
		sdd_1.store_id <= sdd_2.store_id GROUP BY sdd_2.store_id ) a ) dis ON user_info.rn >= dis.rn2 
	AND user_info.rn < dis.rn1

5.数据验证-和理想效果一致。

SELECT
	count( DISTINCT user_id ) 
FROM
	distribute_over 
GROUP BY
	store_id;

相关内容

kettle组件【维度查询/更新】的用法
https://blog.csdn.net/weixin_43932609/article/details/124734608?spm=1001.2014.3001.5501
kettle组件HTTP client的使用方法
https://blog.csdn.net/weixin_43932609/article/details/123984884?spm=1001.2014.3001.5502
Kettle循环导出整库数据
https://blog.csdn.net/weixin_43932609/article/details/119610480?spm=1001.2014.3001.5502
ETL工具kettle的计算方式
https://blog.csdn.net/weixin_43932609/article/details/110371110
Kettle工具中Rest client组件的用法！！
https://blog.csdn.net/weixin_43932609/article/details/109988783

=========================================================

人生得意须尽欢，莫使金樽空对月！
__一个热爱说唱的程序员。
今日份推荐音乐：盛宇DamnShine / KEY.L刘聪 / ICE《隆里电丝 (LIVE版)》

=========================================================