全SQL数据库处理效率分析

01拣货数据处理

declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--分拣记录数据初始化，补充开始时间和计算真实用时
update A set A.开始时间 = b.操作时间,完整时长 = DATEDIFF (S,A.操作时间,b.操作时间),a.配送日期 =@当前日期,a.作业环节 = '拣货', a.站点 = (case 
        when a.容器编码 = b.容器编码 then '小件'   
        when a.容器编码 <> b.容器编码 and a.变更数量 > 300 then '大件' 
        else '小件'
      end),时长 = 
(case 
        when a.容器编码 <> b.容器编码 and a.变更数量 > 300 and DATEDIFF (S,A.操作时间,b.操作时间) >600 then 600   
        when a.容器编码 <> b.容器编码 and a.变更数量 > 300 and DATEDIFF (S,A.操作时间,b.操作时间) <=600 then DATEDIFF (S,A.操作时间,b.操作时间)
        when a.容器编码 = b.容器编码 and DATEDIFF (S,A.操作时间,b.操作时间) >300 then 300
        when a.容器编码 = b.容器编码 and DATEDIFF (S,A.操作时间,b.操作时间) <300 then DATEDIFF (S,A.操作时间,b.操作时间)
				when a.容器编码 <> b.容器编码 and a.变更数量 < 300 and DATEDIFF (S,A.操作时间,b.操作时间) <=300 then DATEDIFF (S,A.操作时间,b.操作时间)
				when a.容器编码 <> b.容器编码 and a.变更数量 < 300 and DATEDIFF (S,A.操作时间,b.操作时间) >300 then 300
      end)
 from 分拣明细表 A,分拣明细表 B
 where A.操作人 = b.操作人 and A.操作时间 < b.操作时间 and A.配送日期 is null and a.作业环节 is null and b.配送日期 is null and b.作业环节 is null
 ----收集作业时长数据，但不包括异常数据
insert into SKU效率表(SKU,时长,中心网格,变化量,配送日期,完整时长) select SKu,时长,站点,sum(变更数量) as 变更数量,配送日期,完整时长 from 分拣明细表 where 时长 >0 and 时长 <> 600 and 时长 <> 300  and 配送日期 =@当前日期 and 作业环节 = '拣货'
 GROUP BY 操作时间,操作人,SKu,商品名称,时长,站点,配送日期,完整时长
  --删除无时长的数据
 DELETE FROM 分拣明细表 where 完整时长<=0 and 配送日期 =@当前日期 and 作业环节 = '拣货'  or 配送日期 is null
 

02分拣数据处理

declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--分拣记录数据初始化，补充开始时间和计算真实用时
update A set A.开始时间 = b.操作时间,完整时长 = DATEDIFF (S,A.操作时间,b.操作时间),时长 = 
(case 
        when A.生产波次 = '福州中心仓直配' and DATEDIFF (S,A.操作时间,b.操作时间) >120 then 30   
        when A.生产波次 = '福州中心仓直配' and DATEDIFF (S,A.操作时间,b.操作时间) <=120 then DATEDIFF (S,A.操作时间,b.操作时间)
        when A.生产波次 = '福州网格站' and DATEDIFF (S,A.操作时间,b.操作时间) >600 then 300
        else DATEDIFF (S,A.操作时间,b.操作时间)
      end),A.站点= 
(case 
        when A.站点 is null then '中心'
        else '网格'
      end)
 from 分拣明细表 A,分拣明细表 B
 where A.操作人 = b.操作人 and A.操作时间 < b.操作时间 and A.配送日期 =@当前日期 and a.作业环节 ='分拣' and b.配送日期 =@当前日期 and b.作业环节 ='分拣'
--收集作业时长数据，但不包括异常数据
insert into SKU效率表(SKU,商品名称,品类,时长,中心网格,变化量,配送日期,完整时长) select SKu,商品名称,分拣区,时长,站点,sum(变化量)  as 变化量,配送日期,完整时长 from 分拣明细表 where 时长 >0 and 时长 <> 30 and 时长 <> 300  and 配送日期 =@当前日期 and 作业环节 ='分拣'GROUP BY 操作时间,操作人,SKu,商品名称,分拣区,时长,站点,配送日期,完整时长
 --删除无时长的数据
 DELETE FROM 分拣明细表 where 完整时长<=0 and 配送日期 =@当前日期  and 作业环节 <>'拣货' and 作业环节<>'分拣' or  完整时长<=0 and 作业环节<>'分拣' and 配送日期 =@当前日期 and 作业环节 <>'拣货'
  
 --收集产出数据存放历史表
 insert into 分拣明细表_历史(操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分) select  top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 from (
SELECT  top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,sum(变化量) as 变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 FROM(
SELECT  top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,分拣明细表.SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,
                      (CASE WHEN dbo.分拣明细表.作业环节 = '拣货' THEN dbo.平均时效.时间 * 0.00555555555555555 WHEN dbo.分拣明细表.作业环节 = '分拣' THEN (CASE WHEN 分拣区 = 'DP' AND SUM(变化量) 
                      > 10 AND SUM(变化量) / 10 < 10 THEN SUM(变化量) / 10 WHEN 分拣区 = 'DP' AND SUM(变化量) > 10 AND SUM(变化量) / 10 > 10 THEN 10 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND 
                      SUM(变化量) / 20 < 10 THEN SUM(变化量) / 20 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND SUM(变化量) / 20 > 10 THEN 10 ELSE 1 END) * 平均时效.时间 * 0.00555555555555555 END) AS 得分, SUM(dbo.分拣明细表.变化量) AS 变化量
FROM         dbo.分拣明细表 INNER JOIN
                      dbo.平均时效 ON dbo.分拣明细表.站点 = dbo.平均时效.中心网格 AND dbo.分拣明细表.SKU = dbo.平均时效.SKU
WHERE     (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '拣货') OR
                      (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '分拣')
GROUP BY 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,分拣明细表.SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,时间) aa
GROUP BY 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 ORDER BY 作业环节,操作时间 DESC) bb




--查询当日总产出得分
SELECT 配送日期,操作人,sum(总得分) as 分数 FROM(
SELECT     dbo.分拣明细表.配送日期, dbo.分拣明细表.操作时间, dbo.分拣明细表.时长, dbo.分拣明细表.操作人, dbo.平均时效.时间, 
                      (CASE WHEN dbo.分拣明细表.作业环节 = '拣货' THEN dbo.平均时效.时间 * 0.00555555555555555 WHEN dbo.分拣明细表.作业环节 = '分拣' THEN (CASE WHEN 分拣区 = 'DP' AND SUM(变化量) 
                      > 10 AND SUM(变化量) / 10 < 10 THEN SUM(变化量) / 10 WHEN 分拣区 = 'DP' AND SUM(变化量) > 10 AND SUM(变化量) / 10 > 10 THEN 10 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND 
                      SUM(变化量) / 20 < 10 THEN SUM(变化量) / 20 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND SUM(变化量) / 20 > 10 THEN 10 ELSE 1 END) * 平均时效.时间 * 0.00555555555555555 END) 
                      AS 总得分, SUM(dbo.分拣明细表.变化量) AS 数量
FROM         dbo.分拣明细表 INNER JOIN
                      dbo.平均时效 ON dbo.分拣明细表.站点 = dbo.平均时效.中心网格 AND dbo.分拣明细表.SKU = dbo.平均时效.SKU
WHERE     (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '拣货') OR
                      (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '分拣')
GROUP BY dbo.分拣明细表.配送日期, dbo.分拣明细表.作业环节, dbo.分拣明细表.操作时间, dbo.分拣明细表.分拣区, dbo.分拣明细表.完整时长, dbo.分拣明细表.时长, dbo.分拣明细表.操作人, dbo.分拣明细表.SKU, dbo.平均时效.时间) aa
GROUP BY 配送日期,操作人 ORDER BY 分数 DESC

03分时段汇总

declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--查询分时总产出
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) order by 时间段

--查询所有人分时产出
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段',操作人, sum(得分) as '得分'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY 操作人,(case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) order by 操作人,时间段
--查询整体分时产出
SELECT 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',sum(得分)/COUNT(操作人) as '平均得分' FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a 
GROUP BY 时间段 order by 时间段
--查询个人分时产出

SELECT 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',sum(得分)/COUNT(操作人) as '平均得分' FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 and 操作人=@姓名 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a 
GROUP BY 时间段 order by 时间段
--按部门分时统计
SELECT    dbo.部门信息表.部门,  dbo.分时统计得分.时间段, SUM(dbo.分时统计得分.得分) / SUM(dbo.分时统计得分.人数) AS 平均分
FROM         dbo.分时统计得分 INNER JOIN
                      dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, dbo.分时统计得分.时间段
--按部门+人分时统计
SELECT    dbo.部门信息表.部门, 操作人, dbo.分时统计得分.时间段, sum(dbo.分时统计得分.得分)as 得分 ,SUM(dbo.分时统计得分.件数) AS 件数
FROM         dbo.分时统计得分 INNER JOIN
                      dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, 操作人, dbo.分时统计得分.时间段 order by 部门,时间段

部门表

分拣明细表

历史数据表

SKU效率表

视图表
04按部门分时统计

SELECT    dbo.部门信息表.部门,  dbo.分时统计得分.时间段, SUM(dbo.分时统计得分.得分) / SUM(dbo.分时统计得分.人数) AS 平均分
FROM         dbo.分时统计得分 INNER JOIN
                      dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, dbo.分时统计得分.时间段

分拣待更新记录表

SELECT TOP (100000)  系数, 操作时间, 配送批次, 配送日期, 生产波次, 站点, 分拣区, 集货位, SKU, 商品名称, 时长, 变化量, 操作人, 容器编号, 作业环节, 开始时间, 完整时长, 容器编码, 变更数量
FROM         dbo.分拣明细表
WHERE     (配送日期 IS NULL) AND (作业环节 IS NULL)
ORDER BY 操作人, 操作时间 DESC

分时统计得分

SELECT top 100000 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',操作人 FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =convert(varchar(100),getdate(),23) GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a 
GROUP BY 时间段, 操作人 order by 时间段

平均时效

SELECT     中心网格, SKU, AVG(时长) AS 时间
FROM         dbo.SKU效率表
GROUP BY 中心网格, SKU