交叉统计一年(大概 几十万行数据)销售数据,交叉统计后得出的统计表有 三万多行,如下代码 耗时 667 秒 这效率实在是快奔溃了 请问如何才能 优化 或 提高统计效率?
此主题相关图片如下:qq图片20220105030405.png
'代码如下:--------------------------------------------------------------------------------------------------------------------------------------
Dim st As Date = Date.Now
Dim jb As New SQLJoinTableBuilder("临时测试表1","测试表A")
jb.ConnectionName = _sjymc
jb.AddExp("年份","year(日期)",True)
jb.AddExp("月份","month(日期)",True)
jb.AddExp("客户id","btypeid",True)
jb.AddExp("产品id","PtypeId",True)
'--------------------------------------------------------------------------------------------------------------------------------------
jb.AddExp("金额","sum(金额)")
jb.AddExp("数量","sum(数量)")
jb.AddExp("毛利","sum(金额) - sum(成本)")
'--------------------------------------------------------------------------------------------------------------------------------------
jb.Filter = "日期 >= '2021-01-01' and 日期 <= '2021-12-31' and 编号 in ('11','26','45') and 类型 = '2'"
'--------------------------------------------------------------------------------------------------------------------------------------
Dim cb As new CrossTableBuilder("临时测试表",jb.BuildSQL,_sjymc)
cb.HGroups.AddDef("年份")
cb.HGroups.AddDef("月份")
cb.HGroups.AddDef("产品id")
cb.VGroups.AddDef("客户id")
cb.Totals.Adddef("数量")
cb.Totals.AddDef("实销")
cb.Totals.AddDef("毛利")
cb.Totals.AddDef("毛利率")
cb.Build()
'--------------------------------------------------------------------------------------------------------------------------------------
output.show("耗时: " & (Date.Now - st).TotalSeconds & "秒")
'--------------------------------------------------------------------------------------------------------------------------------------
MainTable = Tables("临时测试表")
[此贴子已经被作者于2022/1/5 3:07:16编辑过]