深圳智汇创想科技有限公司

计算基础数据及kogan,待验证数据

# 建产品相关信息临时表
CREATE TABLE `p_product_newbak20231110` (
  `Id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `CustomerId` int NOT NULL COMMENT '商户号',
  `Sku` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '系统SKU',
    `PackageWeight1` decimal(18,2) NOT NULL COMMENT '包裹重',
    `PackageVolume1` decimal(18,2) NOT NULL COMMENT '体积',    
    `Length1` decimal(18,2) NOT NULL COMMENT '长',
  `Width1` decimal(18,2) NOT NULL COMMENT '宽',
  `Height1` decimal(18,2) NOT NULL COMMENT '高',
  `PackageWeight` decimal(18,2) NOT NULL COMMENT '包裹重',
  `GrossWeight` decimal(18,2) NOT NULL COMMENT '毛重',
  `PackageLength` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '包裹长',
  `PackageWidth` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '包裹宽',
  `PackageHeight` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '包裹高',
     `Length` decimal(18,2) NOT NULL COMMENT '长',
  `Width` decimal(18,2) NOT NULL COMMENT '宽',
  `Height` decimal(18,2) NOT NULL COMMENT '高',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `uidx_customerid_sku` (`Sku`,`CustomerId`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='产品临时表';

# 计算产品信息
INSERT INTO p_product_newbak20231110 (CustomerId,sku,PackageWeight1,PackageVolume1,Length1,Width1,Height1,PackageWeight ,GrossWeight,PackageLength,PackageWidth,PackageHeight,
Length,Width,Height)
select CustomerId,sku,
case when PackageWeight >0 then PackageWeight else GrossWeight end as PackageWeight1,
case when (PackageLength*PackageWidth*PackageHeight)>0 then (PackageLength*PackageWidth*PackageHeight) else (Length*Width*Height) end as PackageVolume1,
case when (PackageLength*PackageWidth*PackageHeight) >0 then PackageLength else Length end as Length1,
case when (PackageLength*PackageWidth*PackageHeight) >0 then PackageWidth else Width end as Width1,
case when (PackageLength*PackageWidth*PackageHeight) >0 then PackageHeight else Height end as Height1,
PackageWeight ,GrossWeight,
PackageLength,PackageWidth,PackageHeight,
Length,Width,Height
from p_product;


# kogan平台汇总
select 'kogan' as marketname,a.OrderSourceId,c.OrderSourceName,a.ProductId,a.sku,a.SellerSKU,
case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end as '抛率' ,d.sales as '销量', (PackageVolume1/6000) as '体积重',b.Length1 as '长',b.Width1 as '宽',b.Height1 as '高',
(b.PackageWeight1/1000) as '包裹重',b.Length1 as '单边长',(b.Length1+b.Width1+b.Height1) as '三边和'
from m_kogan_listing a  
inner join p_product_newbak20231110 b on a.CustomerId=b.CustomerId and a.sku=b.sku
inner join sys_ordersource c on a.CustomerId=c.CustomerId and a.OrderSourceId=c.OrderSourceId
left join ( select CustomerId,OrderSourceId,SellerSku,Sku,SUM(sales) as sales from m_kogan_sale_year_summary GROUP BY CustomerId,OrderSourceId,SellerSku,Sku) d on a.CustomerId=d.CustomerId and a.OrderSourceId=d.OrderSourceId and a.SellerSku=d.SellerSku
where a.CustomerId=1 and a.OnlineStatus=1

其它平台统计SQL如下:

# shopee平台汇总
select a.OrderSourceId,c.OrderSourceName,a.ProductId,a.SubProductId,a.sku,a.SellerSKU,
case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end as '抛率' ,d.sales as '销量',
(PackageVolume1/6000) as '体积重',b.Length1 as '长',b.Width1 as '宽',b.Height1 as '高',
(b.PackageWeight1/1000) as '包裹重',b.Length1 as '单边长',(b.Length1+b.Width1+b.Height1) as '三边和'
from m_shopeev2_listing_sub a  
inner join p_product_newbak20231110 b on a.CustomerId=b.CustomerId and a.sku=b.sku
inner join sys_ordersource c on a.CustomerId=c.CustomerId and a.OrderSourceId=c.OrderSourceId
inner join ( select CustomerId,OrderSourceId,SellerSku,Sku,SUM(sales) as sales from m_shopeev2_sale_year_summary where Date=2023 GROUP BY CustomerId,OrderSourceId,SellerSku,Sku) d on a.CustomerId=d.CustomerId and a.OrderSourceId=d.OrderSourceId and a.SellerSku=d.SellerSku
where a.CustomerId=1 and a.EditStatus=1 and (b.PackageVolume1>0 or PackageWeight1>0)
and (case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end)>2 and d.sales>0


# lazada平台汇总
select a.OrderSourceId,c.OrderSourceName,a.ProductId,a.SubProductId,a.sku,a.SellerSKU,
case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end as '抛率' ,d.sales as '销量',
(PackageVolume1/6000) as '体积重',b.Length1 as '长',b.Width1 as '宽',b.Height1 as '高',
(b.PackageWeight1/1000) as '包裹重',b.Length1 as '单边长',(b.Length1+b.Width1+b.Height1) as '三边和'
from m_lazada_listing_sub a  
inner join p_product_newbak20231110 b on a.CustomerId=b.CustomerId and a.sku=b.sku
inner join sys_ordersource c on a.CustomerId=c.CustomerId and a.OrderSourceId=c.OrderSourceId
left join ( select CustomerId,OrderSourceId,SellerSku,Sku,SUM(sales) as sales from m_lazada_sale_year_summary where Date=2023 GROUP BY CustomerId,OrderSourceId,SellerSku,Sku) d on a.CustomerId=d.CustomerId and a.OrderSourceId=d.OrderSourceId and a.SellerSku=d.SellerSku
where a.CustomerId=1 and a.EditStatus=1 and (b.PackageVolume1>0 or PackageWeight1>0)
and (case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end)>2;


# Allegro平台汇总
select a.OrderSourceId,c.OrderSourceName,a.ProductId,a.sku,a.SellerSKU,
case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end as '抛率' ,d.sales as '销量',
(PackageVolume1/8000) as '体积重',b.Length1 as '长',b.Width1 as '宽',b.Height1 as '高',
(b.PackageWeight1/1000) as '包裹重',b.Length1 as '单边长',(b.Length1+b.Width1+b.Height1) as '三边和'
from m_allegro_listing a  
inner join p_product_newbak20231110 b on a.CustomerId=b.CustomerId and a.sku=b.sku
inner join sys_ordersource c on a.CustomerId=c.CustomerId and a.OrderSourceId=c.OrderSourceId
inner join ( select CustomerId,OrderSourceId,SellerSku,Sku,SUM(sales) as sales from m_allegro_sale_year_summary where Date=2023 GROUP BY CustomerId,OrderSourceId,SellerSku,Sku) d on a.CustomerId=d.CustomerId and a.OrderSourceId=d.OrderSourceId and a.SellerSku=d.SellerSku
where a.CustomerId=1 and a.OnlineStatus=1 and (b.PackageVolume1>0 or PackageWeight1>0)
and (case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*8)) else 0 end)>2 and d.sales>0;


# aliexpress平台汇总
select a.OrderSourceId,c.OrderSourceName,a.ProductId,a.SubProductId,a.sku,a.SellerSKU,
case when b.PackageWeight1> 0 then (PackageVolume1/(b.PackageWeight1*6)) else 0 end as '抛率' ,d.sales as '销量',
(PackageVolume1/6000) as '体积重',b.Length1 as '长',b.Width1 as '宽',b.Height1 as '高',
(b.PackageWeight1/1000) as '包裹重',b.Length1 as '单边长',(b.Length1+b.Width1+b.Height1) as '三边和'
from m_aliexpress_listing_sub a  
inner join p_product_newbak20231110 b on a.CustomerId=b.CustomerId and a.sku=b.sku
inner join sys_ordersource c on a.CustomerId=c.CustomerId and a.OrderSourceId=c.OrderSourceId
left join ( select CustomerId,OrderSourceId,SellerSku,Sku,SUM(sales) as sales from m_aliexpress_sale_year_summary where Date=2023 GROUP BY CustomerId,OrderSourceId,SellerSku,Sku) d on a.CustomerId=d.CustomerId and a.OrderSourceId=d.OrderSourceId and a.SellerSku=d.SellerSku
where a.CustomerId=1 and a.EditStatus=1 and (b.PackageVolume1>0 or PackageWeight1>0)
and (b.Length1>60 or (b.Length1+b.Width1+b.Height1)>90);

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

各平台导出记录数据如下：
kogan : 22万+
shopee ： 880万+ （有销量的：15461）
lazada: 21万+
aliexpress: 1866
allegro: 384万+ （有销量的：19784）