求一个SQL语句,如何划分数据

HuoLarry 2016-03-12 09:07:46
已知表T_OLD

CUS_ID,SVR_NAME
1 张三
2 张三
3 李四
4 李四
5 李四
6 王五
7 王五
8 黄六
9 黄六
10 黄六
11 黄六
12 黄六

T_OLD表数据解释:CUS_ID为不重复的列,有100W行。SVR_NAME是固定的,如T_OLD是4个不重复记录。实际应用中有12个或者30个更多。
T_OLD表业务解释:CUS_ID是表示客户的,有100W个不重复的客户,SVR_NAME是服务经理,如:张三,服务客户1,2

得到的结果,需要满足的条件:
条件1,自己曾经服务过的客户不能再服务
条件2,平均从每个服务经理手中赋予新的客户(如:张三以前服务的客户是1,2,那么新的服务中就不能有1,2.)
条件3,不可把一个服务经理的所有客户赋予另一个服务经理(如:不可以把8,9,10三个客户全部赋予给张三。)



需要达到的结果T_NEW:
CUS_ID,SVR_NEW_NAME
3 张三
6 张三
8 张三
1 李四
11 李四
12 李四
2 王五
9 王五
10 王五
4 黄六
5 黄六
7 黄六

----我目前想到的方法是:
1. 首先使用row_number 做一个开窗。得到下表

CUS_ID SVR_NAME ROW_NUM
1 张三 1
2 张三 2
3 李四 1
4 李四 2
5 李四 3
6 王五 1
7 王五 2
8 黄六 1
9 黄六 2
10 黄六 3
11 黄六 4
12 黄六 5

2。然后再倒入EXCEL手工筛选。

这样做数据若是少还可以,但是若是多就非常麻烦。请各位SQL大神帮忙看一下,如何更加科学的完成此问题。感谢!!!






...全文
331 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
mingqing6364 2016-03-16
  • 打赏
  • 举报
回复
仔细想了下,那个必然的坑应该这样描述:如果一个经理的原客户数大于总客户数-平均数×2,那么必然不满足条件3,这个不是优化算法能解决的问题,如此一想貌似上面的算法就没什么问题了,具体还要多测试。
tiezhongtang123 2016-03-15
  • 打赏
  • 举报
回复
我想你的需求是把所有的客户重新进行分配; 1)统计多少客服经理; 2)统计多少不同客户,得到一个客户经理可以分配多少客户; 3)赋值形成数组,在通过循环来给客户指定客户经理(不好旧客户经理)。
mingqing6364 2016-03-15
  • 打赏
  • 举报
回复
引用 楼主 19850713 的回复:
已知表T_OLD: CUS_ID,SVR_NAME 1 张三 2 张三 3 李四 4 李四 5 李四 6 王五 7 王五 8 黄六 9 黄六 10 黄六 11 黄六 12 黄六 T_OLD表数据解释:CUS_ID为不重复的列,有100W行。SVR_NAME是固定的,如T_OLD是4个不重复记录。实际应用中有12个或者30个更多。 T_OLD表业务解释:CUS_ID是表示客户的,有100W个不重复的客户,SVR_NAME是服务经理,如:张三,服务客户1,2 得到的结果,需要满足的条件: 条件1,自己曾经服务过的客户不能再服务 条件2,平均从每个服务经理手中赋予新的客户(如:张三以前服务的客户是1,2,那么新的服务中就不能有1,2.) 条件3,不可把一个服务经理的所有客户赋予另一个服务经理(如:不可以把8,9,10三个客户全部赋予给张三。) 需要达到的结果T_NEW: CUS_ID,SVR_NEW_NAME 3 张三 6 张三 8 张三 1 李四 11 李四 12 李四 2 王五 9 王五 10 王五 4 黄六 5 黄六 7 黄六 ----我目前想到的方法是: 1. 首先使用row_number 做一个开窗。得到下表 CUS_ID SVR_NAME ROW_NUM 1 张三 1 2 张三 2 3 李四 1 4 李四 2 5 李四 3 6 王五 1 7 王五 2 8 黄六 1 9 黄六 2 10 黄六 3 11 黄六 4 12 黄六 5 2。然后再倒入EXCEL手工筛选。 这样做数据若是少还可以,但是若是多就非常麻烦。请各位SQL大神帮忙看一下,如何更加科学的完成此问题。感谢!!!
这个问题我想了两天了,下面把我的解决方案贴上来 首先声明:这个解决方案没有经过大量测试,不敢保证一定可靠 另外一个必然的坑,如果T_OLD中有某经理的客户数超出了平均数的2倍,那你得修改算法 我没有精力对这个算法细细雕琢,抱歉
CREATE PROCEDURE [dbo].[Get_T_NEW]
AS
BEGIN
/*用编号代替经理*/
	SELECT SVR_NAME,ROW_NUMBER() OVER(ORDER BY MIN(CUS_ID)) AS JL INTO #JL FROM T_OLD GROUP BY SVR_NAME
	SELECT A.CUS_ID AS KH, B.JL, ROW_NUMBER() OVER(PARTITION BY B.JL ORDER BY A.CUS_ID) AS PX INTO #T_OLD FROM T_OLD AS A INNER JOIN #JL AS B ON A.SVR_NAME = B.SVR_NAME
/*求平均数,为条件2做准备,随便求下经理数*/
	DECLARE @PJS INT,@JLS INT
	SELECT @PJS = COUNT(DISTINCT KH) / COUNT(DISTINCT JL), @JLS = COUNT(DISTINCT JL) FROM #T_OLD
/*创建#T_NEW表,结构和#T_OLD一致*/
	CREATE TABLE #T_NEW (KH INT, JL INT);
/*将原本大于等于平均数的经理名下的客户分配出去,
这样分配能保证其他经理至少能分配到某经理名下的一名客户
满足了条件3
PS:这个备注到下个备注之间,用游标写应该会更好理解,不过我不会游标^_^*/
	WITH T1 AS
	(
		SELECT
			*,
			(ROW_NUMBER() OVER(PARTITION BY JL ORDER BY KH) - 1) % (@JLS - 1) + 1 AS JL_KH_ID
		FROM
			#T_OLD
		WHERE
			JL IN (SELECT JL FROM #T_OLD GROUP BY JL HAVING COUNT(KH) >= @PJS)
	)
	INSERT INTO #T_NEW
	SELECT KH, JL_KH_ID + CASE WHEN JL_KH_ID >= JL THEN 1 ELSE 0 END AS NEW_JL FROM T1
/*这些客户已经分配出去了,所以删除这些旧的数据,剩下的就是未分配的了*/
	DELETE FROM #T_OLD WHERE KH IN (SELECT KH FROM #T_NEW)
/*接下来求出按这个初步分配方案,还有多少位经理缺少客户*/
	DECLARE @WMJLS/*未满经理数*/ INT
	SELECT @WMJLS = COUNT(DISTINCT JL) FROM #T_OLD
/*循环分配,每一次循环分配掉一个经理名下的客户,直到所有客户分配完毕*/
	DECLARE @DFPJL/*待分配经理*/ INT
	WHILE @WMJLS <> 0
		BEGIN
/*首先拿出一个未分配完的经理*/
			SELECT @DFPJL = MIN(JL) FROM #T_OLD
/*然后求出还能分配客户的经理,并对他们按顺序编号,最大编号为@WMJLS,未满经理ID*/
			SELECT JL,ROW_NUMBER() OVER(ORDER BY JL) AS WMJLID INTO #WMJL FROM #T_NEW WHERE JL <> @DFPJL GROUP BY JL HAVING @PJS > COUNT(KH)
/*再按顺序依次分配*/
			INSERT INTO #T_NEW
				SELECT
					#T_OLD.KH,#WMJL.JL
				FROM
					#T_OLD INNER JOIN #WMJL ON (#T_OLD.PX - 1) % @WMJLS + 1 = #WMJL.WMJLID
				WHERE
					#T_OLD.JL = @DFPJL
/*删除已分配的服务关系*/
			DELETE FROM #T_OLD WHERE JL = @DFPJL
/*删除#WMJL,方便下次创建*/
			DROP TABLE #WMJL
/*最后再次计算未分配满的经理数*/
			SELECT @WMJLS = COUNT(DISTINCT JL) FROM #T_OLD
		END
/*最后的最后,把经理ID替换会经理姓名*/
	SELECT #T_NEW.KH AS CUS_ID,#JL.SVR_NAME FROM #T_NEW INNER JOIN #JL ON #T_NEW.JL = #JL.JL ORDER BY CUS_ID
	DROP TABLE #T_NEW
END
道素 2016-03-13
  • 打赏
  • 举报
回复
你是照在客户经理的角度,找客户 我上面想的是站在客户的角度分配客户经理,不会考虑一个经理管多少个客户 另外我上面写是SQL还满足不了的需求 一是排序的顺序,确定可能每次都选中同一个人 也没有考虑你说的条件三
道素 2016-03-13
  • 打赏
  • 举报
回复
按我的理解你是想给所有的客户分配客户经理吗? 我的想法是: 1 先拿到需要重新分配客户经理的客户名单,也就是那个T_OLD 2.列出重来没有服务过这个客户的所有待选人选 3.选择一个作为新的客户经理 下面的逻辑我没测试,大概意思就是由从来没有担任过该客户的经理中选择第一个 如果你有单独的客户经理可以不用下面的写法

 SELECT *,aa.SVR_NAME AS SVR_NEW_NAME FROM T_OLD AS a1
 CROSS APPLY (SELECT  DISTINCT TOP 1 a2.SVR_NAME FROM T_OLD AS a2 WHERE NOT EXISTS (SELECT 0 FROM T_OLD AS a3 WHERE a3.CUS_ID=a1.CUS_ID AND a3.SVR_NAME=a2.SVR_NAME)) aa

道玄希言 2016-03-12
  • 打赏
  • 举报
回复
想到一方法, 得用游标来处理 1. 查出有多少个服务经理(不重复的SVR_NAME) 2. 查出有多少客户(不重复的CUS_ID) 3. 求出平均每个服务经理可以分配多少个客户 4. 通过开窗函数查询出每个服务经理已经有了多少客户 5. 游标, 将大于平均值的客户经理,更新成游标小于平均值的客户经理

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧