一、需求
订单销量数据统计，按商品分类（二级，三级）或商品统计，平均采购单价，毛利单价，毛利金额，毛利率等
销量 = 商品销售数量
平均采购单价 = 采购订单总金额/采购订单总数量
毛利单价 = 销售单价-采购单价
毛利金额 = 毛利单价*销售数量
毛利率 = 毛利单价/销售单价
二、实现
组织架构说明：总运营中心-子运营中心-服务仓（服务仓下订单，进行采买）
涉及到两个主表的查询，订单表和流水表（商品入库类型）的查询
第一步：查询时间范围内订单，并以时间和商品code分组
第二步：查询时间范围内流水表商品平均入库价格（一个服务仓下的最近一次入库（时间可选）商品价格，以商品分组得到平均入库价格）
第三步：以左边表订单商品左查找右边表商品入库价格
第一轮查询sql，数据量大了，效率很低
SELECT
dateTime,
basePdtCode,
baseUnit,
reportWeight,
reportAmount,
avgReportPrice,
avgLastStockPrice,
profitPrice,
profitPrice * reportWeight profitAmount,
profitPrice / avgReportPrice profitRate,
parentName,
parentCode,
typeName,
typeCode,
pdtName basePdtName
FROM
(
SELECT
( tt1.reportAmount / tt1.reportWeight ) avgReportPrice,
tt2.avgLastStockPrice,
(( tt1.reportAmount / tt1.reportWeight ) - tt2.avgLastStockPrice ) profitPrice,
tt1.*
FROM
(
SELECT
DATE_FORMAT( t1.arrvedTime, '%Y-%m-%d' ) dateTime,
t3.baseUnit,
t3.basePdtCode,
sum(
IF
( t2.settleType = 'SALEUNIT', t2.reportWeight, t2.baseReportWeight )) reportWeight,
sum(
IF
( t2.settleType = 'SALEUNIT', t2.reportAmount, t2.baseReportAmount )) reportAmount,
t4.parentCode parentCode,
( SELECT typeName FROM tct_pdt_type WHERE typeCode = t4.parentCode ) parentName,
t4.typeCode,
t4.typeName,
t5.pdtName
FROM
tod_order t1
JOIN tod_order_detail t2 ON t1.guid = t2.orderGuid
JOIN tct_pdt t3 ON t2.pdtCode = t3.pdtCode
JOIN tct_base_pdt t5 ON t5.basePdtCode = t3.basePdtCode
JOIN tct_pdt_type t4 ON t3.typeCode = t4.typeCode
WHERE
1 = 1
AND t1.houseGuid IN ( '19673B35C4124D7998AA59AFECEB5303', '515D09F6EB4D431DB5EC7EF2242E96DE', '803B2F646E2B49C09225B01C9D099870', '8340DA9CC2394F6CB0668070A8A04DC6', 'D8DC02B133BC48FFB9B961F3BDDB7769', '3E97F61C60CE4E078DE5DAA93537B27D', '7559DBFC882C4398A77BB5416637663E' )
AND t1.arrvstTime BETWEEN '2022-05-02 00:00:00 '
AND '2022-06-01 23:59:59 999 '
AND t1.STATUS IN ( 'FILLED', 'PAID' )
AND t4.parentCode IN ( '01001', '01002', '01003' )
AND t2.baseUnit = '斤'
GROUP BY
dateTime,
t3.basePdtCode
) tt1
LEFT JOIN (
SELECT
avg( t10.lastStockPrice ) avgLastStockPrice,
t10.basePdtCode
FROM
(
SELECT
( SELECT t1.stockPrice FROM tims_stock_flow t4 WHERE t4.whouseGuid = t1.whouseGuid AND t4.basePdtCode = t1.basePdtCode AND t1.flowTime < '2022-06-01 23:59:59 999 '
ORDER BY t4.serialNo DESC LIMIT 1 ) AS lastStockPrice,
t1.basePdtCode,
t1.whouseGuid
FROM
tims_stock_flow t1
JOIN twh_warehouse t2 ON t1.whouseGuid = t2.guid
JOIN tct_base_pdt t5 ON t5.basePdtCode = t1.basePdtCode
JOIN tct_pdt_type t6 ON t6.typeCode = t5.typeCode
WHERE
1 = 1
AND t1.flowTime < '2022-06-01 23:59:59 999 '
AND t1.whouseGuid IN ( '19673B35C4124D7998AA59AFECEB5303', '515D09F6EB4D431DB5EC7EF2242E96DE', '803B2F646E2B49C09225B01C9D099870', '8340DA9CC2394F6CB0668070A8A04DC6', 'D8DC02B133BC48FFB9B961F3BDDB7769', '3E97F61C60CE4E078DE5DAA93537B27D', '7559DBFC882C4398A77BB5416637663E' )
AND t6.parentCode IN ( '01001', '01002', '01003', '01004', '01005', '01006', '01007', '01008', '01009', '01010', '01011', '01012', '01013' )
AND t1.baseUnit = '斤'
-- AND t1.flowSubType = 'PURCHASEIN'
GROUP BY
t1.whouseGuid,
t1.basePdtCode
) t10
GROUP BY
t10.basePdtCode
) tt2 ON tt1.basePdtCode = tt2.basePdtCode
) ttt1;
优化后的查询sql
SELECT
dateTime,
basePdtCode,
baseUnit,
sum( reportWeight ) reportWeight,
sum( reportAmount ) reportAmount,
AVG( avgReportPrice ) avgReportPrice,
IFNULL( AVG( avgLastStockPrice ), 0 ) avgLastStockPrice,
IFNULL( AVG( profitPrice ), 0 ) profitPrice,
IFNULL( AVG( profitPrice ) * sum( reportWeight ), 0 ) profitAmount,
IFNULL( AVG( profitPrice ) / AVG( avgReportPrice ), 0 ) profitRate,
parentName,
parentCode,
typeName,
typeCode
FROM
(
SELECT
( tt1.reportAmount / tt1.reportWeight ) avgReportPrice,
tt2.avgLastStockPrice,
(( tt1.reportAmount / tt1.reportWeight ) - tt2.avgLastStockPrice ) profitPrice,
tt1.*
FROM
(
SELECT
DATE_FORMAT( t1.arrvedTime, '%Y-%m-%d' ) dateTime,
t3.baseUnit,
t3.basePdtCode,
sum(
IF
( t2.settleType = 'SALEUNIT', t2.reportWeight, t2.baseReportWeight )) reportWeight,
sum(
IF
( t2.settleType = 'SALEUNIT', t2.reportAmount, t2.baseReportAmount )) reportAmount,
t4.parentCode parentCode,
( SELECT typeName FROM tct_pdt_type WHERE typeCode = t4.parentCode ) parentName,
t4.typeCode,
t4.typeName,
t5.pdtName
FROM
tod_order t1
JOIN tod_order_detail t2 ON t1.guid = t2.orderGuid
JOIN tct_pdt t3 ON t2.pdtCode = t3.pdtCode
JOIN tct_base_pdt t5 ON t5.basePdtCode = t3.basePdtCode
JOIN tct_pdt_type t4 ON t3.typeCode = t4.typeCode
WHERE
1 = 1
AND t1.houseGuid IN ( '19673B35C4124D7998AA59AFECEB5303', '515D09F6EB4D431DB5EC7EF2242E96DE', '803B2F646E2B49C09225B01C9D099870', '8340DA9CC2394F6CB0668070A8A04DC6', 'D8DC02B133BC48FFB9B961F3BDDB7769', '3E97F61C60CE4E078DE5DAA93537B27D', '7559DBFC882C4398A77BB5416637663E' )
AND t1.arrvstTime BETWEEN '2022-05-02 00:00:00 '
AND '2022-06-01 23:59:59 999 '
AND t1.STATUS IN ( 'FILLED', 'PAID' )
AND t4.parentCode IN ( '01001', '01002', '01003', '01004', '01005', '01006', '01007', '01008', '01009', '01010', '01011', '01012', '01013' )
AND t2.baseUnit = '斤'
GROUP BY
dateTime,
t3.basePdtCode
) tt1
LEFT JOIN (
SELECT
avg( t10.lastStockPrice ) avgLastStockPrice,
t10.basePdtCode
FROM
(
SELECT
SUBSTRING_INDEX( GROUP_CONCAT( t1.stockPrice ORDER BY t1.serialNo DESC ), ',', 1 ) lastStockPrice,
t1.basePdtCode,
t1.whouseGuid
FROM
tims_stock_flow t1
JOIN twh_warehouse t2 ON t1.whouseGuid = t2.guid
JOIN tct_base_pdt t5 ON t5.basePdtCode = t1.basePdtCode
JOIN tct_pdt_type t6 ON t6.typeCode = t5.typeCode
WHERE
1 = 1
AND t1.flowTime < '2022-06-01 23:59:59 999 '
AND t1.whouseGuid IN ( '19673B35C4124D7998AA59AFECEB5303', '515D09F6EB4D431DB5EC7EF2242E96DE', '803B2F646E2B49C09225B01C9D099870', '8340DA9CC2394F6CB0668070A8A04DC6', 'D8DC02B133BC48FFB9B961F3BDDB7769', '3E97F61C60CE4E078DE5DAA93537B27D', '7559DBFC882C4398A77BB5416637663E' )
AND t6.parentCode IN ( '01001', '01002', '01003', '01004', '01005', '01006', '01007', '01008', '01009', '01010', '01011', '01012', '01013' )
AND t1.baseUnit = '斤'
AND t1.flowSubType = 'PURCHASEIN'
GROUP BY
t1.whouseGuid,
t1.basePdtCode
) t10
GROUP BY
t10.basePdtCode
) tt2 ON tt1.basePdtCode = tt2.basePdtCode
) ttt1
GROUP BY
dateTime,
parentCode;
优化点：如何取服务仓的商品最近一次流水记录sql效率更高
( SELECT t1.stockPrice FROM tims_stock_flow t4 WHERE t4.whouseGuid = t1.whouseGuid AND t4.basePdtCode = t1.basePdtCode AND t1.flowTime < '2022-06-01 23:59:59 999 '
ORDER BY t4.serialNo DESC LIMIT 1 ) AS lastStockPrice,
以 whouseGuid, basePdtCode分组结果desc排序后GROUP_CONCAT stockPrice 字段取第一条stockPrice
SELECT SUBSTRING_INDEX( GROUP_CONCAT( t1.stockPrice ORDER BY t1.serialNo DESC ), ',', 1 ) lastStockPrice,
因为流水记录表数据量非常大，嵌套查询流水表，效率很低。以whouseGuid，basePdtCode 分组后利用函数GROUP_CONCAT 并排序，可以让想要的数据在第一条并截取，避免了嵌套查询。
效果图：
订单数据分析01.png
网友评论