通过CTE SQL查询进行数据分析，了解乳制品运输业务的趋势。什么样的包装，最常搬动的物品，最繁忙的区域

Question

我能够使用此查询获取所有记录，并希望使用 CTE 将其分解以进行简单的 DA，以了解我们每月使用的包装量，哪些 SKU 每月移动最多， 以及按地点划分的数量。如果我在 2021 年执行此操作，我会因超过 1500 万条记录而超时 30 分钟。我想学习如何使用 SQL

SELECT
SKU,
,boxsize
,month
,CASE WHEN aisle IN ('700','781') THEN 'Dairy' ELSE 'FrozenDairy' END AS Location

FROM production_table 

JOIN date_table t2
    ON to_date(create_tstamp_local) = t2.date
    
JOIN aisle_info t3
    ON sku= t3.sku
    
JOIN orders_table t4
  ON delivery_number =t4.delivery_num
  
WHERE order_status = 'shipped'
AND filling_rsc = 'IL01'
AND t3.aisle_num IN ('780','781','682','683','684','685','686','687','688','689','690','692')
AND t2.year = 2021
AND t4.shipped = 'F'

结果回答这些问题：

SKU	boxsize	Month	Location
1045678	12x12x12	1	55
1045678	6x6x6	2	53
5796678	12x12x12	1	55
1045678	12x12x12	1	55

预期表结果：

表 1：包装每月按 SKU 分析不同盒子尺寸计数

SKU	盒子尺寸	BoxSizeCount	每月	位置
1045678	12x12x12	1,000,000	1	55
1045678	6x6x6	1,000,000	1	55
1045678	12x12x12	200,000	2	55
1045678	6x6x6	200,000	2	55
5796678	12x12x12	100,000	3	53
1045678	12x12x12	100,000	3	55

表 2：按每月 SKU 划分的不同盒子尺寸计数的 SKU 分析

SKU	SKUCount	月	位置
1045678	1,500,000	1	55
9045672	1,000,000	1	55
1032618	220,000	1	55
1045673	245,000	1	55
5796678	175,000	2	53
1031045678	100,039	2	53

我可以导出这两个表以用于第三次位置分析。

Answer 1

SKU,
,boxsize
,month
,CASE WHEN aisle IN ('700','781') THEN 'Dairy' ELSE 'FrozenDairy' END AS Location
**,COUNT(1) AS record_count**

FROM production_table 

JOIN date_table t2
    ON to_date(create_tstamp_local) = t2.date
    
JOIN aisle_info t3
    ON sku= t3.sku
    
JOIN orders_table t4
  ON delivery_number =t4.delivery_num
  
WHERE order_status = 'shipped'
AND filling_rsc = 'IL01'
AND t3.aisle_num IN ('780','781','682','683','684','685','686','687','688','689','690','692')
AND t2.year = 2021
AND t4.shipped = 'F'

**GROUP BY 1,2,3,4,5,6,7**`