求最客户经理名下客户资产最大值，谢谢大家回帖！

2010-09-27 04:40:49 167 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

客户流失预警模型评估实战：从混淆矩阵到ROC/KS曲线在机器学习与数据科学领域，模型评估是确保预测结果可靠、指导业务决策的核心环节。其原理在于通过量化指标衡量模型性能，避免过拟合与欠拟合，从而提升模型泛化能力。对于二分类问题，如客户流失预测，评估的技术价值尤为突出，它直接关系到营销资源的分配效率与业务成本控制。评估体系通常从混淆矩阵出发，衍生出精确率、召回率等核心指标，进而通过ROC曲线与AUC值全局评估模型排序能力，并利用KS曲线寻找最佳决策阈值。这些方法广泛应用于金融风控、精准营销、客户关系管理等场景，帮助数据科学家与业务分析师在模型性能与业务需求间取得平阅读详情

brokerid fundid zc date
001 56001 475.00 20100401
001 56002 500.00 20100401
002 56005 475.00 20100401
002 56006 500.00 20100401
003 56007 600.00 20100401
004 56008 700.00 20100401
001 56009 200.00 20100402
001 560010 500.00 20100402
002 560011 475.00 20100402
002 560012 500.00 20100402
003 560013 600.00 20100402
004 560014 700.00 20100402

。。。。

求客户经理（brokerid）当月最大资产，以及资产日期。
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢？
还有更好的解决方法吗，我用的是sql2000，谢谢各位了！

...全文

【Cadence 17.4实战】----- 基于MySQL与ODBC的企业级CIS数据库配置全攻略本文详细介绍了如何在Cadence 17.4中配置基于MySQL与ODBC的企业级CIS数据库，提升硬件设计团队的元器件管理效率。通过实战案例和避坑指南，帮助读者掌握数据库部署、ODBC驱动配置及CIS参数映射等关键技巧，实现设计数据的实时同步与高效管理。阅读详情

guosenwh

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

ai_li7758521 2010-09-27

打赏
举报

SELECT brokerid,[SUM]=sum(zc),[date]

INTO ##

FROM #2

GROUP BY brokerid,[date]







SELECT 客户经理=brokerid,最大资产=[SUM],时间=[date]

FROM ## T

WHERE NOT EXISTS

	(

		SELECT 1 

		FROM ## X 

		WHERE X.brokerid=T.brokerid 

		  and X.[SUM]>T.[SUM]

	)



客户经理       最大资产                                    时间

---------- --------------------------------------- -----------------------

001        975.00                                  2010-04-01 00:00:00.000

002        975.00                                  2010-04-01 00:00:00.000

002        975.00                                  2010-04-02 00:00:00.000

003        600.00                                  2010-04-01 00:00:00.000

003        600.00                                  2010-04-02 00:00:00.000

004        700.00                                  2010-04-01 00:00:00.000

004        700.00                                  2010-04-02 00:00:00.000



(7 行受影响)



drop table ##

ai_li7758521 2010-09-27

打赏
举报

CREATE TABLE #2

(

	brokerid varchar(10),

	fundid varchar(10),

	zc decimal(18,2),

	date datetime

)



INSERT #2

SELECT '001', '56001' , 475.00 ,'20100401'union all 

SELECT '001', '56002' , 500.00 ,'20100401'union all 

SELECT '002', '56005' , 475.00 ,'20100401'union all 

SELECT '002', '56006' , 500.00 ,'20100401'union all 

SELECT '003', '56007' , 600.00 ,'20100401'union all 

SELECT '004', '56008' , 700.00 ,'20100401'union all 

SELECT '001', '56009' , 200.00 ,'20100402'union all 

SELECT '001', '560010', 500.00 ,'20100402'union all 

SELECT '002', '560011', 475.00 ,'20100402'union all 

SELECT '002', '560012', 500.00 ,'20100402'union all 

SELECT '003', '560013', 600.00 ,'20100402'union all 

SELECT '004', '560014', 700.00 ,'20100402'



SELECT 客户经理=brokerid,最大资产=[SUM],时间=[date]

FROM

(

	SELECT brokerid,[SUM]=sum(zc),[date]

	FROM #2

	GROUP BY brokerid,[date]

) T

WHERE NOT EXISTS

	(SELECT 1 

		FROM

		 (

			SELECT brokerid,[SUM]=sum(zc),[date]

			FROM #2

			GROUP BY brokerid,[date]

		) X 

		WHERE X.brokerid=T.brokerid and X.[SUM]>T.[SUM]

	)





客户经理       最大资产                                    时间

---------- --------------------------------------- -----------------------

001        975.00                                  2010-04-01 00:00:00.000

002        975.00                                  2010-04-01 00:00:00.000

002        975.00                                  2010-04-02 00:00:00.000

003        600.00                                  2010-04-01 00:00:00.000

003        600.00                                  2010-04-02 00:00:00.000

004        700.00                                  2010-04-01 00:00:00.000

004        700.00                                  2010-04-02 00:00:00.000



(7 行受影响)



drop table #2

dawugui 2010-09-27

打赏
举报

[Quote=引用 13 楼 guosenwh 的回复:]
继续等待，
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢？这个是难点！
[/Quote]
我5楼不是已经把结果都弄出来了？还有什么地方不对？

guosenwh 2010-09-27

打赏
举报

继续等待，
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢？这个是难点！

ai_li7758521 2010-09-27

打赏
举报

CREATE TABLE #2

(

	brokerid varchar(10),

	fundid varchar(10),

	zc decimal(18,2),

	date datetime

)



INSERT #2

SELECT '001', '56001' , 475.00 ,'20100401'union all 

SELECT '001', '56002' , 500.00 ,'20100401'union all 

SELECT '002', '56005' , 475.00 ,'20100401'union all 

SELECT '002', '56006' , 500.00 ,'20100401'union all 

SELECT '003', '56007' , 600.00 ,'20100401'union all 

SELECT '004', '56008' , 700.00 ,'20100401'union all 

SELECT '001', '56009' , 200.00 ,'20100402'union all 

SELECT '001', '560010', 500.00 ,'20100402'union all 

SELECT '002', '560011', 475.00 ,'20100402'union all 

SELECT '002', '560012', 500.00 ,'20100402'union all 

SELECT '003', '560013', 600.00 ,'20100402'union all 

SELECT '004', '560014', 700.00 ,'20100402'



SELECT 客户经理=brokerid,最大资产=MAX([SUM]),时间=CONVERT(varchar(6),[date],112) 

FROM

(

	SELECT brokerid,SUM=sum(zc),[date]

	FROM #2

	GROUP BY brokerid,[date]

) T

GROUP BY brokerid,CONVERT(varchar(6),[date],112)





客户经理       最大资产                                    时间

---------- --------------------------------------- ------

001        975.00                                  201004

002        975.00                                  201004

003        600.00                                  201004

004        700.00                                  201004



(4 行受影响)

guosenwh 2010-09-27

打赏
举报

没有对客户经理资产求和呢，先要求和再取最大值。

guosenwh 2010-09-27

打赏
举报

我把这个语句试了下，在我的表上是不对的。

ws_hgo 2010-09-27

打赏
举报

[Quote=引用 7 楼 guosenwh 的回复:]

还是不对，应该有用到max吧
[/Quote]

6楼不是你要的结果吗？

只要到达目的就行啦

什么方法一样

dawugui 2010-09-27

打赏
举报

[Quote=引用 7 楼 guosenwh 的回复:]
还是不对，应该有用到max吧
[/Quote]我那个也不对？如果你需要多个条件，自己加进去即可。

guosenwh 2010-09-27

打赏
举报

还是不对，应该有用到max吧

ws_hgo 2010-09-27

打赏
举报

if object_id('tb') is not null drop table tb

go

create table tb

(

  brokerid varchar(10),

fundid int,

zc decimal(19,2),

date datetime

)

insert into tb select '001',56001,475.00,'20100401'

union all select '001',56002,500.00,'20100401'

union all select '002',56005,475.00,'20100401'

union all select '002',56006,500.00,'20100401'

union all select '003',56007,600.00,'20100401'

union all select '004',56008,700.00,'20100401'

union all select '001',56009,200.00,'20100402'

union all select '001',560010,500.00,'20100402'

union all select '002',560011,475.00,'20100402'

union all select '002',560012,500.00,'20100402'

union all select '003',560013,600.00,'20100402'

union all select '004',560014,500.00,'20100402'

go



select brokerid,sum(zc) zc,date from tb

where date in

(

select distinct t.date

from

(

select brokerid,sum(zc) zc,date from tb group by brokerid,date

) t

join

(

 select brokerid,sum(zc) zc,date from tb group by brokerid,date

) t1

on t.brokerid=t1.brokerid and t.zc>t1.zc 

)

 group by brokerid,date 



brokerid   zc                                      date

---------- --------------------------------------- -----------------------

001        975.00                                  2010-04-01 00:00:00.000

002        975.00                                  2010-04-01 00:00:00.000

003        600.00                                  2010-04-01 00:00:00.000

004        700.00                                  2010-04-01 00:00:00.000



(4 行受影响)

dawugui 2010-09-27

打赏
举报

create table tb(brokerid varchar(10),fundid varchar(10),zc decimal(18,2),date datetime)

insert into tb values('001', '56001' , 475.00 ,'20100401')

insert into tb values('001', '56002' , 500.00 ,'20100401')

insert into tb values('002', '56005' , 475.00 ,'20100401')

insert into tb values('002', '56006' , 500.00 ,'20100401')

insert into tb values('003', '56007' , 600.00 ,'20100401')

insert into tb values('004', '56008' , 700.00 ,'20100401')

insert into tb values('001', '56009' , 200.00 ,'20100402')

insert into tb values('001', '560010', 500.00 ,'20100402')

insert into tb values('002', '560011', 475.00 ,'20100402')

insert into tb values('002', '560012', 500.00 ,'20100402')

insert into tb values('003', '560013', 600.00 ,'20100402')

insert into tb values('004', '560014', 700.00 ,'20100402')

go



select m.* from

(

  select brokerid ,sum(zc) zc,date from tb group by brokerid,date

) m where not exists (select 1 from

(

  select brokerid ,sum(zc) zc,date from tb group by brokerid,date

) n where n.brokerid = m.brokerid and (n.zc > m.zc or (n.zc = m.zc and n.date < m.date))

)

order by m.brokerid





drop table tb



/*

brokerid   zc                                       date                                                   

---------- ---------------------------------------- ------------------------------------------------------ 

001        975.00                                   2010-04-01 00:00:00.000

002        975.00                                   2010-04-01 00:00:00.000

003        600.00                                   2010-04-01 00:00:00.000

004        700.00                                   2010-04-01 00:00:00.000



（所影响的行数为 4 行）

*/

guosenwh 2010-09-27

打赏
举报

结果,4月客户经理资产峰值及峰值日期：
brokerid zc date
001 975 20100401
002 975 20100401
003 600 20100401
004 700 20100401

不好意思，这个数据可能没有设计好，不知大家是否明白我的意图。

dawugui 2010-09-27

打赏
举报

select t.* from tb t where zc = (select max(zc) from tb where brokerid = t.brokerid and datediff(mm,date,t.date) = 0)



select t.* from tb t where not exists (select 1 from tb where brokerid = t.brokerid and datediff(mm,date,t.date) = 0 and zc > t.zc)

ws_hgo 2010-09-27

打赏
举报

结果是什么啊？？

dawugui 2010-09-27

打赏
举报

就你的数据，结果是什么？

求最客户经理名下客户资产最大值，谢谢大家回帖！

相关推荐