我会记录每个人开车,还车时间,(一次出车一条数据)
然后我要统计每个人在一定时间段内的开车数据(另外有一个表记录,每次出车时候的油耗,里程等分项统计数据,一次出车有多条数据)
并根据每个分项数据的加权值,来计算出一个得分, 然后进行排名.
我现在的做法是循环这个记录开车还车时间的表, 然后 根据每个时间段,去取这个时间段内这辆车的统计数据,累计后得到这个人在这个时间段内的统计数据.并计算得分list. 然后对这个list 进行排序. 然后保存这个排序.
这样计算在车辆比较少的情况下 还能胜任. 现在车辆一多 就会卡很久, 请教下 有没有更优的算法?
51 个解决方案
#1
先不提算法，车和司机在多能有多少。。。
#2
公司要求能支持10w以上的司机和车...............
#3
什么数据库？尽量在数据库存储过程中完成计算，不要在C#里面循环计算。
减少查询次数，多用临时表。
减少查询次数，多用临时表。
#4
怎么分解这个算法? 那些放到数据库, 那些在C#中?
数据库 目前是 sqlserver2005
#5
我的意思是用存储过程来完成这个事情，C#只负责调用存储过程获得结果。
存储过程的效率本身就比较高，先动手试一试。
#6
这样计算在车辆比较少的情况下 还能胜任. 现在车辆一多 就会卡很久,
->谁知道你的代码如何写的。
->谁知道你的代码如何写的。
#7
我已经说了我现在的算法流程了啊
#8
试过 , 用了 游标, 还是比较慢
#9
我没有让你用游标，用临时表...
#10
这个临时表怎么生成?
我想不到
#11
sqlserver临时表就是临时创建的表，可以存储你在数据处理过程中需要临时存放一下的数据。
http://www.cnblogs.com/chongzi/archive/2011/01/19/1939106.html
http://www.cnblogs.com/chongzi/archive/2011/01/19/1939106.html
#12
不好意思我表达错了 , 我不是说我不知道创建临时表的命令, 我的意思是如何生成这个临时表的数据, 怎样组织数据结构,怎样提取数据
#13
先暂时不谈数据库，先说说设计方法，设计要根据实际业务走，你现在的问题集中在一个时间跨度上，时间跨度的*框选才是你的设计的问题，但需要这样吗？是不需要的，你可以固定按周、月、季度、半年、年来分别框选时间段，这个定下来了，就可以在每周进行一次加权统计，将统计结果记入数据库，然后框选时间最小单位就是周，后面月、季度等等，你只需从数据库中的周统计结果进行计算，这样效率就大为提高了。
#14
不行啊 ,产品给的要求就是可以*的框选时间段
我们以前就是按你说的这个做法做的, 每月找一天时间,专门来算这个结果
#15
如果换成是我，我的做法就是定时执行job（比如每天晚上）
job的内容就是当天各司机的加权得分，最后塞到一张得分表中
你以后统计只需要sum即可。
如果你统计的时间段间隔很小的话，我没啥好办法，只能考虑优化SQL了吧。
job的内容就是当天各司机的加权得分，最后塞到一张得分表中
你以后统计只需要sum即可。
如果你统计的时间段间隔很小的话，我没啥好办法，只能考虑优化SQL了吧。
#16
可以啊，他要这样没问题的，但要效率肯定需要建立在已有计算结果的基础上才能提高效率，否则你只能提高你计算机的计算性能，用数据库算也没用，比程序算能快多少？你还是记录周计算结果，如果他们框选了2015-8-1至2015-8-19，那你可以拆开先取整周的,2015-8-2到2015-8-15是两整周，这个可以从原来计算结果中直接取得，2015-8-1跟2015-8-6~2015-8-19再算一下就行了，这样计算记录数就大大减少，速度提升就非常明显了。
#17
你有 2 个表，考虑到加权还应增加一张表
对这三张表做一个连接查询即可
正式使用时可将查询转为视图（表间关系将由数据库自己维护，性能优于独立的查询），直接访问视图
对这三张表做一个连接查询即可
正式使用时可将查询转为视图（表间关系将由数据库自己维护，性能优于独立的查询），直接访问视图
#18
我有点理解你的意思了 , 你的意思是每天定时执行一个job , 算出当天或者前一天的每个人的数据集, 然后在用户选择的时候,在这个结果集的上面在进行分析统计, 我的理解没有错吧?
但是这样也有个问题, 就像上面那位兄弟说的, 如果时间跨度很小, 或者说跨度精确掉小时,分钟, 这样就没用了
#19
你每天能算一次那更好，不管他哪怕框选到分钟，就像我上面说的，先从已有的统计单位中取，有周统计单位的，有天统计单位的，那么他们提交了一个时间段，你就对这个时间段进行细分，从你的最大统计单位（周）中先取，然后剩下（天），再取，剩下的小时就直接算，核心思想就是降低计算记录数。你最好数据库也做一下月、季度、半年、季统计结果、你原先的那种算法不管如何优化代码，如果对方跨度了两年，那还是卡死，其实要知道，他们只要一个排名而已。
#20
做一个专门的计算服务器，通过webservice调用。
计算服务器也可以不只一台，通过负载均衡服务器分配计算到各个计算服务器。
计算服务器也可以不只一台，通过负载均衡服务器分配计算到各个计算服务器。
#21
突然想到一个问题, 我们现在都是假设在每天统计时候 , 已经知道了分配记录, 但是还有一种情况, 分配记录是补录的话 要怎么弄呢
#22
补录是肯定存在的，你每天的统计结果不能计算成加权值，统计成总数：公里总数、油耗总数，这样补、改、删对原有的统计结果只需变更总数就行了，最终统计的时候，将数据汇总后进行加权，这样更合理一些，其实你之前之所以卡，95%的时间是在数据库查询上面耗费掉了，你需要解决的就是减少查询的记录数，只有小时部分才从历史数据中查找，这样范围就一下缩小，大的范围你可以从天、周、月的原有统计总数中取得，这个查询非常快。你想一个司机每个月会有数百个记录，你做好了小的统计元素就会大大减少查询时间。
#23
还一种情况, 有些车出车时间比较长 ,在记录中只记录了出车时间, 到统计时候后可能还没有还车,这样的记录 , 是需要作为特例, 在出排名 时候, 再临时去取数据吗?
#24
你每天不要统计当天的，每天统计前一天或者更前一天的。
#25
我大致明白了 , 我刚刚试着做了下了 , 但是有一小问题把我绕住了 , 出车记录的时间段 是精确的奥分钟的, 但是跨度有可能是1天 有可能是N天, 我是要要把这个时间段拆成实际跨越的天数吗?如果是拆的话, 要怎么拆? 我一时想不通了
#26
我的想法是，你的统计单位有天、周、月，考虑到有数万或十多万的司机，你可以也加上季度、半年统计，这些都是针对单个司机的统计。
查询使用人设定一个查询时间跨度，那么就先判断是否有最大统计单位，如果跨了整个季度，就从季度统计中取，接下来取月、周、天，层层缩小，最后到几点几分的部分直接从原始记录调取，最后合并总数，进行加权。
查询使用人设定一个查询时间跨度，那么就先判断是否有最大统计单位，如果跨了整个季度，就从季度统计中取，接下来取月、周、天，层层缩小，最后到几点几分的部分直接从原始记录调取，最后合并总数，进行加权。
#27
对了，忘记说了，如果第数据库不是太熟悉的话，建议看看怎样科学的建立索引，查询效率会大大提高。
#28
这思路确实不错 考虑到补录情况 我先暂时只考虑已经录入的吧
下面是我写的sql 语句,请指教
DECLARE drecordcursor CURSOR for
select [DR_ID], [C_ID],[D_ID],[StartTime],[EndTime] from [DriverRecord_tbl] where [IsEnd]=1 and [IsStatistics]=0 and [DR_ID]=4
open drecordcursor
declare @id int
declare @c_id int
declare @d_id int
declare @startime datetime
declare @endtime datetime
fetch next from drecordcursor into @id,@c_id,@d_id,@startime,@endtime
while @@FETCH_STATUS =0
begin
declare @sql nvarchar(max)
set @sql=''
declare @K_DriverTravelTime [decimal](18, 2)
declare @K_Oil [decimal](18, 2)
declare @K_Mileage [decimal](18, 2)
declare @K_Action_20 [decimal](18, 2)
declare @K_Action_21 [decimal](18, 2)
declare @K_Action_0 int
declare @K_Action_1 int
declare @K_Action_2 int
declare @K_Action_3 int
declare @K_Action_6 int
declare @K_Action_7 int
declare @K_Action_9 int
declare @K_Alarm_13 int
declare @K_Alarm_14 int
declare @K_Alarm_15 int
declare @K_Alarm_16 int
declare @datatime datetime
--分割datetime
declare @t1 datetime
declare @t2 datetime
set @t1 =@startime
set @t2 =convert(datetime, convert(varchar(10),dateadd(day,1,@startime) ,120) )
while @t2<@endtime
begin
--行程
select @K_DriverTravelTime=sum([Totaltraveltime]), @K_Mileage=sum([TotalMileage]),@K_Oil=sum([CumulativeOil])
,@K_Action_20=sum([HotCarLength]), @K_Action_21=sum([SuspendedIdling]) from [CarTrackRecording_tbl]
where C_ID =@c_id and [EndDatetime] between @t1 and @t2
--报警
select @K_Alarm_13=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =13 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_14=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =14 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_15=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =15 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_16=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =16 and createtime between @t1 and @t2 group by C_id, asr_typeid
--动作
select @K_Action_0=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =0 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_1=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =1 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_2=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =2 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_3=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =3 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_6=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =6 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_7=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =7 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_9=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =9 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
set @datatime=convert(datetime, convert(varchar(10),@t1, 120) )
--创建sql 语句
set @sql = @sql + ' if exists (select 1 from DriverKpiData_tbl where D_ID='+@d_id + ' K_DateTime = '''+ @datatime +''') '
+ ' begin '
+ ' update DriverKpiData_tbl '
+ ' set K_DriverTravelTime = K_DriverTravelTime +' + @K_DriverTravelTime
+ ' set K_Oil=K_Oil+' +@K_Oil
+ ' set K_Mileage=K_Mileage+' +@K_Mileage
+ ' set K_Action_0=K_Action_0+' +@K_Action_0
+ ' set K_Action_1=K_Action_1+' +@K_Action_1
+ ' set K_Action_2=K_Action_2+' +@K_Action_2
+ ' set K_Action_3=K_Action_3+' +@K_Action_3
+ ' set K_Action_6=K_Action_6+' +@K_Action_6
+ ' set K_Action_7=K_Action_7+' +@K_Action_7
+ ' set K_Action_9=K_Action_9+' +@K_Action_9
+ ' set K_Alarm_13=K_Alarm_13+' +@K_Alarm_13
+ ' set K_Alarm_14=K_Alarm_14+' +@K_Alarm_14
+ ' set K_Alarm_15=K_Alarm_15+' +@K_Alarm_15
+ ' set K_Alarm_16=K_Alarm_16+' +@K_Alarm_16
+ ' where D_ID='+@d_id + ' and K_DateTime='''+ @datatime + ''''
+ ' end '
+ ' else '
+ ' begin '
+ ' insert into DriverKpiData_tbl(C_ID,K_DriverTravelTime,K_Oil,K_Mileage,K_Action_0,K_Action_1,K_Action_2,K_Action_3,K_Action_6,K_Action_7,K_Action_9,K_Alarm_13,K_Alarm_14,K_Alarm_15,K_Alarm_16,K_DateTime) '
+ ' VALUES('+@c_id+','+@K_DriverTravelTime+','+@K_Oil+','+@K_Mileage+','+@K_Action_0+','+@K_Action_1+','+@K_Action_2+','+@K_Action_3+','+@K_Action_6+','+@K_Action_7+','+@K_Action_9+','+@K_Alarm_13+','+@K_Alarm_14+','+@K_Alarm_15+','+@K_Alarm_16+','''+@datatime+''')'
+ ' end '
print @K_DriverTravelTime
print @K_Mileage
print @K_Oil
print @t2
set @t1 = @t2
set @t2 = dateadd(day,1,@t1)
end
--补充末尾的
declare @t datetime
set @t= convert(datetime, convert(varchar(10),@endtime ,120) )
--行程
select @K_DriverTravelTime=sum([Totaltraveltime]), @K_Mileage=sum([TotalMileage]),@K_Oil=sum([CumulativeOil])
,@K_Action_20=sum([HotCarLength]), @K_Action_21=sum([SuspendedIdling]) from [CarTrackRecording_tbl]
where C_ID =@c_id and [EndDatetime] between @t and @endtime
--报警
select @K_Alarm_13=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =13 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_14=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =14 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_15=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =15 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_16=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =16 and createtime between @t and @endtime group by C_id, asr_typeid
--动作
select @K_Action_0=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =0 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_1=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =1 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_2=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =2 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_3=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =3 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_6=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =6 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_7=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =7 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_9=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =9 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
set @datatime=@t
--创建sql 语句
set @sql = @sql + ' if exists (select 1 from DriverKpiData_tbl where D_ID='+@d_id + ' K_DateTime = '''+ @datatime +''') '
+ ' begin '
+ ' update DriverKpiData_tbl '
+ ' set K_DriverTravelTime = K_DriverTravelTime +' + @K_DriverTravelTime
+ ' set K_Oil=K_Oil+' +@K_Oil
+ ' set K_Mileage=K_Mileage+' +@K_Mileage
+ ' set K_Action_0=K_Action_0+' +@K_Action_0
+ ' set K_Action_1=K_Action_1+' +@K_Action_1
+ ' set K_Action_2=K_Action_2+' +@K_Action_2
+ ' set K_Action_3=K_Action_3+' +@K_Action_3
+ ' set K_Action_6=K_Action_6+' +@K_Action_6
+ ' set K_Action_7=K_Action_7+' +@K_Action_7
+ ' set K_Action_9=K_Action_9+' +@K_Action_9
+ ' set K_Alarm_13=K_Alarm_13+' +@K_Alarm_13
+ ' set K_Alarm_14=K_Alarm_14+' +@K_Alarm_14
+ ' set K_Alarm_15=K_Alarm_15+' +@K_Alarm_15
+ ' set K_Alarm_16=K_Alarm_16+' +@K_Alarm_16
+ ' where D_ID='+@d_id + ' and K_DateTime='''+ @datatime + ''''
+ ' end '
+ ' else '
+ ' begin '
+ ' insert into DriverKpiData_tbl(C_ID,K_DriverTravelTime,K_Oil,K_Mileage,K_Action_0,K_Action_1,K_Action_2,K_Action_3,K_Action_6,K_Action_7,K_Action_9,K_Alarm_13,K_Alarm_14,K_Alarm_15,K_Alarm_16,K_DateTime) '
+ ' VALUES('+@c_id+','+@K_DriverTravelTime+','+@K_Oil+','+@K_Mileage+','+@K_Action_0+','+@K_Action_1+','+@K_Action_2+','+@K_Action_3+','+@K_Action_6+','+@K_Action_7+','+@K_Action_9+','+@K_Alarm_13+','+@K_Alarm_14+','+@K_Alarm_15+','+@K_Alarm_16+','''+@datatime+''')'
+ ' end '
--事务添加
BEGIN TRY
BEGIN TRANSACTION
--/插入需要事务执行的代码
exec @sql
update [DriverRecord_tbl] set IsStatistics=1 where DR_ID=@ID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
--SET @error=ERROR_PROCEDURE()
--RAISERROR(@error,16,1)
ROLLBACK TRANSACTION
END CATCH
print '----------------------------'
fetch next from drecordcursor into @id,@c_id,@d_id,@startime,@endtime
print @id
end
close drecordcursor
DEALLOCATE drecordcursor
#29
先不提10w辆车的问题
就1000辆,循环1000个人去数据库里匹配车辆,这至少就要对数据库进行查询1000次
你首先必须改变这个思路才行
如果你的服务器性能足够好,可以考虑先把人和车都提取出来,在内存里循环匹配,当然这涉及到大数据通信,可能还是会卡,但是至少比查询1000次要快
更好的办法当然是写个存储过程之类的,只是将指令发给数据库,数据库自己计算好了,返回给你结果
这样性能全部都集中在数据服务器的计算能力上,就跟通信无关了
要知道,只要是内存操作,总是比IO通信要快1000倍以上的.所以能在内存里解决的问题,就不要数据丢来丢去的
就1000辆,循环1000个人去数据库里匹配车辆,这至少就要对数据库进行查询1000次
你首先必须改变这个思路才行
如果你的服务器性能足够好,可以考虑先把人和车都提取出来,在内存里循环匹配,当然这涉及到大数据通信,可能还是会卡,但是至少比查询1000次要快
更好的办法当然是写个存储过程之类的,只是将指令发给数据库,数据库自己计算好了,返回给你结果
这样性能全部都集中在数据服务器的计算能力上,就跟通信无关了
要知道,只要是内存操作,总是比IO通信要快1000倍以上的.所以能在内存里解决的问题,就不要数据丢来丢去的
#30
这样的东西在数据库里面搞，本身就是无解。
需要自己写存储算法。多分一些字典啥的。
需要自己写存储算法。多分一些字典啥的。
#31
有没有具体的方案啊?
#32
大概看了，一些变量你也没注释，看不下去了，单纯谈sql的话，也有很多优化的空间，如果要这样干的话，你其实完全可以将C_id=@c_id and createtime between @t and @endtime这部分一步查询存入临时表，然后在临时表中循环，免得每次查询整个表。
不过，你没必要这样干，你完全可以在插入一条新记录的时候，同时在该司机的天、周、月这些记录上递增一下就完成了，你说呢？
不过，你没必要这样干，你完全可以在插入一条新记录的时候，同时在该司机的天、周、月这些记录上递增一下就完成了，你说呢？
#33
你应该先跟公司确认，你首先只能给出“按天统计”的结果。
#34
补录当然就要请用户耐心地等待着一天天重算了。所以那些不懂编程的用户自己会选择其他办法来加快计算速度的。
当然是以还车时间为核算日期啦。
#35
不用拆。所有的涉及排名的事情，都记在还车时间那一个瞬间。
#36
哎 你的做法确实是会减少很多编程工作, 但是公司不同意啊
#37
你需要先给出不同纬度的索引表
比如 按人，按月索引。现有索引了，查询量瞬间就少了。自己另外一个问题你的问对方一个问题“你们想按权责统计，还是想按收付统计”
权责统计“按借车时间统计”
收付“按还车时间统计”
当然如果对方不乐意，你就只能加个夜核了，先都默认出借1天，如果当天没还，系统就自动续租1天，然后系统每天按时夜核也可以
比如 按人，按月索引。现有索引了，查询量瞬间就少了。自己另外一个问题你的问对方一个问题“你们想按权责统计，还是想按收付统计”
权责统计“按借车时间统计”
收付“按还车时间统计”
当然如果对方不乐意，你就只能加个夜核了，先都默认出借1天，如果当天没还，系统就自动续租1天，然后系统每天按时夜核也可以
#38
夜合不需要这样弄吧
没有还车日期的记录,按查询日期作为还车日期来统计呗
#39
我是一个新手，我正在学hibernate框架，如果是我的话，如果工程与数据库连接我会首先考虑这个框架，毕竟这个框架经得起了数数人的验证，如果公司不让，那就用楼上所说的临时表和存储过程吧，我个人觉得代码自己能少写就少写，多用一些框架对程序的效率而言肯定大有益处，希望对楼主有帮助
#40
刚刚又仔细看了一下您的问题，我有一个想法不知道可行不可行：我认为你的程序效率低的原因可能是没有采用合适的查找方法，我觉得，楼主可以考虑下半分法查找。
#41
这里有个问题 , 我还没有想好 ,对于没有还车日期的记录, 是不是要增加一个字段表示已经统计到哪一天了
#42
不是你说的这些问题
#43
用程序配合数据库来计算，按之前写考勤时的算法，感觉就算是10W个司机和车，应该用不了多长时间吧。
按时间段select出出车记录和开车数据，开车数据按车group by，再sum求和，乘加权值。
按时间段select出出车记录和开车数据，开车数据按车group by，再sum求和，乘加权值。
#44
框架对于这种业务逻辑问题并不起什么卵用。
#45
去sql论坛问问，说不定一句sql就把你搞定了。
#46
我觉得你该把表结构贴出来
#47
hibernate 在所有框架里面，也算得上是拖拉机的效率了
#48
事实是很慢 , 我跑批都要跑好久
#49
我的想法是，另加一张表，专门记录哪个人，在哪个时间段，获得了多少的权值。每次还车时记录一次。一万辆车，一万个司机，这数量级根本不大。
#50
该回复于2015-09-30 23:53:15被版主删除
#1
先不提算法，车和司机在多能有多少。。。
#2
公司要求能支持10w以上的司机和车...............
#3
什么数据库？尽量在数据库存储过程中完成计算，不要在C#里面循环计算。
减少查询次数，多用临时表。
减少查询次数，多用临时表。
#4
怎么分解这个算法? 那些放到数据库, 那些在C#中?
数据库 目前是 sqlserver2005
#5
我的意思是用存储过程来完成这个事情，C#只负责调用存储过程获得结果。
存储过程的效率本身就比较高，先动手试一试。
#6
这样计算在车辆比较少的情况下 还能胜任. 现在车辆一多 就会卡很久,
->谁知道你的代码如何写的。
->谁知道你的代码如何写的。
#7
我已经说了我现在的算法流程了啊
#8
试过 , 用了 游标, 还是比较慢
#9
我没有让你用游标，用临时表...
#10
这个临时表怎么生成?
我想不到
#11
sqlserver临时表就是临时创建的表，可以存储你在数据处理过程中需要临时存放一下的数据。
http://www.cnblogs.com/chongzi/archive/2011/01/19/1939106.html
http://www.cnblogs.com/chongzi/archive/2011/01/19/1939106.html
#12
不好意思我表达错了 , 我不是说我不知道创建临时表的命令, 我的意思是如何生成这个临时表的数据, 怎样组织数据结构,怎样提取数据
#13
先暂时不谈数据库，先说说设计方法，设计要根据实际业务走，你现在的问题集中在一个时间跨度上，时间跨度的*框选才是你的设计的问题，但需要这样吗？是不需要的，你可以固定按周、月、季度、半年、年来分别框选时间段，这个定下来了，就可以在每周进行一次加权统计，将统计结果记入数据库，然后框选时间最小单位就是周，后面月、季度等等，你只需从数据库中的周统计结果进行计算，这样效率就大为提高了。
#14
不行啊 ,产品给的要求就是可以*的框选时间段
我们以前就是按你说的这个做法做的, 每月找一天时间,专门来算这个结果
#15
如果换成是我，我的做法就是定时执行job（比如每天晚上）
job的内容就是当天各司机的加权得分，最后塞到一张得分表中
你以后统计只需要sum即可。
如果你统计的时间段间隔很小的话，我没啥好办法，只能考虑优化SQL了吧。
job的内容就是当天各司机的加权得分，最后塞到一张得分表中
你以后统计只需要sum即可。
如果你统计的时间段间隔很小的话，我没啥好办法，只能考虑优化SQL了吧。
#16
可以啊，他要这样没问题的，但要效率肯定需要建立在已有计算结果的基础上才能提高效率，否则你只能提高你计算机的计算性能，用数据库算也没用，比程序算能快多少？你还是记录周计算结果，如果他们框选了2015-8-1至2015-8-19，那你可以拆开先取整周的,2015-8-2到2015-8-15是两整周，这个可以从原来计算结果中直接取得，2015-8-1跟2015-8-6~2015-8-19再算一下就行了，这样计算记录数就大大减少，速度提升就非常明显了。
#17
你有 2 个表，考虑到加权还应增加一张表
对这三张表做一个连接查询即可
正式使用时可将查询转为视图（表间关系将由数据库自己维护，性能优于独立的查询），直接访问视图
对这三张表做一个连接查询即可
正式使用时可将查询转为视图（表间关系将由数据库自己维护，性能优于独立的查询），直接访问视图
#18
我有点理解你的意思了 , 你的意思是每天定时执行一个job , 算出当天或者前一天的每个人的数据集, 然后在用户选择的时候,在这个结果集的上面在进行分析统计, 我的理解没有错吧?
但是这样也有个问题, 就像上面那位兄弟说的, 如果时间跨度很小, 或者说跨度精确掉小时,分钟, 这样就没用了
#19
你每天能算一次那更好，不管他哪怕框选到分钟，就像我上面说的，先从已有的统计单位中取，有周统计单位的，有天统计单位的，那么他们提交了一个时间段，你就对这个时间段进行细分，从你的最大统计单位（周）中先取，然后剩下（天），再取，剩下的小时就直接算，核心思想就是降低计算记录数。你最好数据库也做一下月、季度、半年、季统计结果、你原先的那种算法不管如何优化代码，如果对方跨度了两年，那还是卡死，其实要知道，他们只要一个排名而已。
#20
做一个专门的计算服务器，通过webservice调用。
计算服务器也可以不只一台，通过负载均衡服务器分配计算到各个计算服务器。
计算服务器也可以不只一台，通过负载均衡服务器分配计算到各个计算服务器。
#21
突然想到一个问题, 我们现在都是假设在每天统计时候 , 已经知道了分配记录, 但是还有一种情况, 分配记录是补录的话 要怎么弄呢
#22
补录是肯定存在的，你每天的统计结果不能计算成加权值，统计成总数：公里总数、油耗总数，这样补、改、删对原有的统计结果只需变更总数就行了，最终统计的时候，将数据汇总后进行加权，这样更合理一些，其实你之前之所以卡，95%的时间是在数据库查询上面耗费掉了，你需要解决的就是减少查询的记录数，只有小时部分才从历史数据中查找，这样范围就一下缩小，大的范围你可以从天、周、月的原有统计总数中取得，这个查询非常快。你想一个司机每个月会有数百个记录，你做好了小的统计元素就会大大减少查询时间。
#23
还一种情况, 有些车出车时间比较长 ,在记录中只记录了出车时间, 到统计时候后可能还没有还车,这样的记录 , 是需要作为特例, 在出排名 时候, 再临时去取数据吗?
#24
你每天不要统计当天的，每天统计前一天或者更前一天的。
#25
我大致明白了 , 我刚刚试着做了下了 , 但是有一小问题把我绕住了 , 出车记录的时间段 是精确的奥分钟的, 但是跨度有可能是1天 有可能是N天, 我是要要把这个时间段拆成实际跨越的天数吗?如果是拆的话, 要怎么拆? 我一时想不通了
#26
我的想法是，你的统计单位有天、周、月，考虑到有数万或十多万的司机，你可以也加上季度、半年统计，这些都是针对单个司机的统计。
查询使用人设定一个查询时间跨度，那么就先判断是否有最大统计单位，如果跨了整个季度，就从季度统计中取，接下来取月、周、天，层层缩小，最后到几点几分的部分直接从原始记录调取，最后合并总数，进行加权。
查询使用人设定一个查询时间跨度，那么就先判断是否有最大统计单位，如果跨了整个季度，就从季度统计中取，接下来取月、周、天，层层缩小，最后到几点几分的部分直接从原始记录调取，最后合并总数，进行加权。
#27
对了，忘记说了，如果第数据库不是太熟悉的话，建议看看怎样科学的建立索引，查询效率会大大提高。
#28
这思路确实不错 考虑到补录情况 我先暂时只考虑已经录入的吧
下面是我写的sql 语句,请指教
DECLARE drecordcursor CURSOR for
select [DR_ID], [C_ID],[D_ID],[StartTime],[EndTime] from [DriverRecord_tbl] where [IsEnd]=1 and [IsStatistics]=0 and [DR_ID]=4
open drecordcursor
declare @id int
declare @c_id int
declare @d_id int
declare @startime datetime
declare @endtime datetime
fetch next from drecordcursor into @id,@c_id,@d_id,@startime,@endtime
while @@FETCH_STATUS =0
begin
declare @sql nvarchar(max)
set @sql=''
declare @K_DriverTravelTime [decimal](18, 2)
declare @K_Oil [decimal](18, 2)
declare @K_Mileage [decimal](18, 2)
declare @K_Action_20 [decimal](18, 2)
declare @K_Action_21 [decimal](18, 2)
declare @K_Action_0 int
declare @K_Action_1 int
declare @K_Action_2 int
declare @K_Action_3 int
declare @K_Action_6 int
declare @K_Action_7 int
declare @K_Action_9 int
declare @K_Alarm_13 int
declare @K_Alarm_14 int
declare @K_Alarm_15 int
declare @K_Alarm_16 int
declare @datatime datetime
--分割datetime
declare @t1 datetime
declare @t2 datetime
set @t1 =@startime
set @t2 =convert(datetime, convert(varchar(10),dateadd(day,1,@startime) ,120) )
while @t2<@endtime
begin
--行程
select @K_DriverTravelTime=sum([Totaltraveltime]), @K_Mileage=sum([TotalMileage]),@K_Oil=sum([CumulativeOil])
,@K_Action_20=sum([HotCarLength]), @K_Action_21=sum([SuspendedIdling]) from [CarTrackRecording_tbl]
where C_ID =@c_id and [EndDatetime] between @t1 and @t2
--报警
select @K_Alarm_13=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =13 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_14=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =14 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_15=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =15 and createtime between @t1 and @t2 group by C_id, asr_typeid
select @K_Alarm_16=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =16 and createtime between @t1 and @t2 group by C_id, asr_typeid
--动作
select @K_Action_0=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =0 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_1=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =1 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_2=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =2 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_3=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =3 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_6=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =6 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_7=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =7 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
select @K_Action_9=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =9 and [DHR_Datetim] between @t1 and @t2 group by c_id ,[DA_ID]
set @datatime=convert(datetime, convert(varchar(10),@t1, 120) )
--创建sql 语句
set @sql = @sql + ' if exists (select 1 from DriverKpiData_tbl where D_ID='+@d_id + ' K_DateTime = '''+ @datatime +''') '
+ ' begin '
+ ' update DriverKpiData_tbl '
+ ' set K_DriverTravelTime = K_DriverTravelTime +' + @K_DriverTravelTime
+ ' set K_Oil=K_Oil+' +@K_Oil
+ ' set K_Mileage=K_Mileage+' +@K_Mileage
+ ' set K_Action_0=K_Action_0+' +@K_Action_0
+ ' set K_Action_1=K_Action_1+' +@K_Action_1
+ ' set K_Action_2=K_Action_2+' +@K_Action_2
+ ' set K_Action_3=K_Action_3+' +@K_Action_3
+ ' set K_Action_6=K_Action_6+' +@K_Action_6
+ ' set K_Action_7=K_Action_7+' +@K_Action_7
+ ' set K_Action_9=K_Action_9+' +@K_Action_9
+ ' set K_Alarm_13=K_Alarm_13+' +@K_Alarm_13
+ ' set K_Alarm_14=K_Alarm_14+' +@K_Alarm_14
+ ' set K_Alarm_15=K_Alarm_15+' +@K_Alarm_15
+ ' set K_Alarm_16=K_Alarm_16+' +@K_Alarm_16
+ ' where D_ID='+@d_id + ' and K_DateTime='''+ @datatime + ''''
+ ' end '
+ ' else '
+ ' begin '
+ ' insert into DriverKpiData_tbl(C_ID,K_DriverTravelTime,K_Oil,K_Mileage,K_Action_0,K_Action_1,K_Action_2,K_Action_3,K_Action_6,K_Action_7,K_Action_9,K_Alarm_13,K_Alarm_14,K_Alarm_15,K_Alarm_16,K_DateTime) '
+ ' VALUES('+@c_id+','+@K_DriverTravelTime+','+@K_Oil+','+@K_Mileage+','+@K_Action_0+','+@K_Action_1+','+@K_Action_2+','+@K_Action_3+','+@K_Action_6+','+@K_Action_7+','+@K_Action_9+','+@K_Alarm_13+','+@K_Alarm_14+','+@K_Alarm_15+','+@K_Alarm_16+','''+@datatime+''')'
+ ' end '
print @K_DriverTravelTime
print @K_Mileage
print @K_Oil
print @t2
set @t1 = @t2
set @t2 = dateadd(day,1,@t1)
end
--补充末尾的
declare @t datetime
set @t= convert(datetime, convert(varchar(10),@endtime ,120) )
--行程
select @K_DriverTravelTime=sum([Totaltraveltime]), @K_Mileage=sum([TotalMileage]),@K_Oil=sum([CumulativeOil])
,@K_Action_20=sum([HotCarLength]), @K_Action_21=sum([SuspendedIdling]) from [CarTrackRecording_tbl]
where C_ID =@c_id and [EndDatetime] between @t and @endtime
--报警
select @K_Alarm_13=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =13 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_14=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =14 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_15=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =15 and createtime between @t and @endtime group by C_id, asr_typeid
select @K_Alarm_16=count(1) from AlarmSpecialRecords_tbl where C_id=@c_id and asr_typeid =16 and createtime between @t and @endtime group by C_id, asr_typeid
--动作
select @K_Action_0=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =0 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_1=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =1 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_2=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =2 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_3=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =3 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_6=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =6 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_7=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =7 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
select @K_Action_9=count(1) from [DrivingHabitsRecords_tbl] where C_id=@c_id and [DA_ID] =9 and [DHR_Datetim] between @t and @endtime group by c_id ,[DA_ID]
set @datatime=@t
--创建sql 语句
set @sql = @sql + ' if exists (select 1 from DriverKpiData_tbl where D_ID='+@d_id + ' K_DateTime = '''+ @datatime +''') '
+ ' begin '
+ ' update DriverKpiData_tbl '
+ ' set K_DriverTravelTime = K_DriverTravelTime +' + @K_DriverTravelTime
+ ' set K_Oil=K_Oil+' +@K_Oil
+ ' set K_Mileage=K_Mileage+' +@K_Mileage
+ ' set K_Action_0=K_Action_0+' +@K_Action_0
+ ' set K_Action_1=K_Action_1+' +@K_Action_1
+ ' set K_Action_2=K_Action_2+' +@K_Action_2
+ ' set K_Action_3=K_Action_3+' +@K_Action_3
+ ' set K_Action_6=K_Action_6+' +@K_Action_6
+ ' set K_Action_7=K_Action_7+' +@K_Action_7
+ ' set K_Action_9=K_Action_9+' +@K_Action_9
+ ' set K_Alarm_13=K_Alarm_13+' +@K_Alarm_13
+ ' set K_Alarm_14=K_Alarm_14+' +@K_Alarm_14
+ ' set K_Alarm_15=K_Alarm_15+' +@K_Alarm_15
+ ' set K_Alarm_16=K_Alarm_16+' +@K_Alarm_16
+ ' where D_ID='+@d_id + ' and K_DateTime='''+ @datatime + ''''
+ ' end '
+ ' else '
+ ' begin '
+ ' insert into DriverKpiData_tbl(C_ID,K_DriverTravelTime,K_Oil,K_Mileage,K_Action_0,K_Action_1,K_Action_2,K_Action_3,K_Action_6,K_Action_7,K_Action_9,K_Alarm_13,K_Alarm_14,K_Alarm_15,K_Alarm_16,K_DateTime) '
+ ' VALUES('+@c_id+','+@K_DriverTravelTime+','+@K_Oil+','+@K_Mileage+','+@K_Action_0+','+@K_Action_1+','+@K_Action_2+','+@K_Action_3+','+@K_Action_6+','+@K_Action_7+','+@K_Action_9+','+@K_Alarm_13+','+@K_Alarm_14+','+@K_Alarm_15+','+@K_Alarm_16+','''+@datatime+''')'
+ ' end '
--事务添加
BEGIN TRY
BEGIN TRANSACTION
--/插入需要事务执行的代码
exec @sql
update [DriverRecord_tbl] set IsStatistics=1 where DR_ID=@ID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
--SET @error=ERROR_PROCEDURE()
--RAISERROR(@error,16,1)
ROLLBACK TRANSACTION
END CATCH
print '----------------------------'
fetch next from drecordcursor into @id,@c_id,@d_id,@startime,@endtime
print @id
end
close drecordcursor
DEALLOCATE drecordcursor
#29
先不提10w辆车的问题
就1000辆,循环1000个人去数据库里匹配车辆,这至少就要对数据库进行查询1000次
你首先必须改变这个思路才行
如果你的服务器性能足够好,可以考虑先把人和车都提取出来,在内存里循环匹配,当然这涉及到大数据通信,可能还是会卡,但是至少比查询1000次要快
更好的办法当然是写个存储过程之类的,只是将指令发给数据库,数据库自己计算好了,返回给你结果
这样性能全部都集中在数据服务器的计算能力上,就跟通信无关了
要知道,只要是内存操作,总是比IO通信要快1000倍以上的.所以能在内存里解决的问题,就不要数据丢来丢去的
就1000辆,循环1000个人去数据库里匹配车辆,这至少就要对数据库进行查询1000次
你首先必须改变这个思路才行
如果你的服务器性能足够好,可以考虑先把人和车都提取出来,在内存里循环匹配,当然这涉及到大数据通信,可能还是会卡,但是至少比查询1000次要快
更好的办法当然是写个存储过程之类的,只是将指令发给数据库,数据库自己计算好了,返回给你结果
这样性能全部都集中在数据服务器的计算能力上,就跟通信无关了
要知道,只要是内存操作,总是比IO通信要快1000倍以上的.所以能在内存里解决的问题,就不要数据丢来丢去的
#30
这样的东西在数据库里面搞，本身就是无解。
需要自己写存储算法。多分一些字典啥的。
需要自己写存储算法。多分一些字典啥的。
#31
有没有具体的方案啊?
#32
大概看了，一些变量你也没注释，看不下去了，单纯谈sql的话，也有很多优化的空间，如果要这样干的话，你其实完全可以将C_id=@c_id and createtime between @t and @endtime这部分一步查询存入临时表，然后在临时表中循环，免得每次查询整个表。
不过，你没必要这样干，你完全可以在插入一条新记录的时候，同时在该司机的天、周、月这些记录上递增一下就完成了，你说呢？
不过，你没必要这样干，你完全可以在插入一条新记录的时候，同时在该司机的天、周、月这些记录上递增一下就完成了，你说呢？
#33
你应该先跟公司确认，你首先只能给出“按天统计”的结果。
#34
补录当然就要请用户耐心地等待着一天天重算了。所以那些不懂编程的用户自己会选择其他办法来加快计算速度的。
当然是以还车时间为核算日期啦。
#35
不用拆。所有的涉及排名的事情，都记在还车时间那一个瞬间。
#36
哎 你的做法确实是会减少很多编程工作, 但是公司不同意啊
#37
你需要先给出不同纬度的索引表
比如 按人，按月索引。现有索引了，查询量瞬间就少了。自己另外一个问题你的问对方一个问题“你们想按权责统计，还是想按收付统计”
权责统计“按借车时间统计”
收付“按还车时间统计”
当然如果对方不乐意，你就只能加个夜核了，先都默认出借1天，如果当天没还，系统就自动续租1天，然后系统每天按时夜核也可以
比如 按人，按月索引。现有索引了，查询量瞬间就少了。自己另外一个问题你的问对方一个问题“你们想按权责统计，还是想按收付统计”
权责统计“按借车时间统计”
收付“按还车时间统计”
当然如果对方不乐意，你就只能加个夜核了，先都默认出借1天，如果当天没还，系统就自动续租1天，然后系统每天按时夜核也可以
#38
夜合不需要这样弄吧
没有还车日期的记录,按查询日期作为还车日期来统计呗
#39
我是一个新手，我正在学hibernate框架，如果是我的话，如果工程与数据库连接我会首先考虑这个框架，毕竟这个框架经得起了数数人的验证，如果公司不让，那就用楼上所说的临时表和存储过程吧，我个人觉得代码自己能少写就少写，多用一些框架对程序的效率而言肯定大有益处，希望对楼主有帮助
#40
刚刚又仔细看了一下您的问题，我有一个想法不知道可行不可行：我认为你的程序效率低的原因可能是没有采用合适的查找方法，我觉得，楼主可以考虑下半分法查找。
#41
这里有个问题 , 我还没有想好 ,对于没有还车日期的记录, 是不是要增加一个字段表示已经统计到哪一天了
#42
不是你说的这些问题
#43
用程序配合数据库来计算，按之前写考勤时的算法，感觉就算是10W个司机和车，应该用不了多长时间吧。
按时间段select出出车记录和开车数据，开车数据按车group by，再sum求和，乘加权值。
按时间段select出出车记录和开车数据，开车数据按车group by，再sum求和，乘加权值。
#44
框架对于这种业务逻辑问题并不起什么卵用。
#45
去sql论坛问问，说不定一句sql就把你搞定了。
#46
我觉得你该把表结构贴出来
#47
hibernate 在所有框架里面，也算得上是拖拉机的效率了
#48
事实是很慢 , 我跑批都要跑好久
#49
我的想法是，另加一张表，专门记录哪个人，在哪个时间段，获得了多少的权值。每次还车时记录一次。一万辆车，一万个司机，这数量级根本不大。
#50
该回复于2015-09-30 23:53:15被版主删除