SQL优化需求：基于分区Max Over计算套餐子项数量

输入数据示例 #

StartDate  EndDate    PlanSystemId  Package_Id  Package_Type    month   mode    rnumber 
2024-03-19 2024-04-14 93167253       null        Package       2024-04  null      1          
2024-03-19 2024-04-14 93170470       93167253    Child         2024-04  MENSUEL   2 
2024-03-19 2024-04-14 93170471       93167253    Child         2024-04  MENSUEL   3 
2024-03-19 2024-04-14 93167253       null        Package       2024-03  null      1          
2024-03-19 2024-04-14 93170470       93167253    Child         2024-03  MENSUEL   2 

统计需求 #

• 2024-04月，PlanSystemId为93167253的套餐有2个子项
• 2024-03月，PlanSystemId为93167253的套餐有1个子项

期望输出 #

PlanSystemId    Package_Id  Package_Type    month    mode   rnumber number_childs
93167253         null        Package       2024-04   null         1          2
93167253         null        Package       2024-03   null         1          1

用户原查询语句 #

WITH CTE_parent_child AS (
SELECT 
  Startdate, EndDate,PlanSystemId,Placement_code,
  Package_Id,Package_Type,month,mode,
  ROW_NUMBER() OVER (PARTITION BY month, IFNULL(Package_Id,PlanSystemId ) ORDER BY PlanSystemId ) AS rnumber

FROM table
WHERE (package_type = 'Package' or package_type='Child') 
  AND (PlanSystemId = 93167253  or Package_Id = 93167253) 
)
SELECT 
  Startdate,EndDate, PlanSystemId, Package_Id,
  Package_Type,month, mode,rnumber,
  MAX(rnumber) over(partition by month, Package_Id ORDER BY PlanSystemId ) as number_childs
  ,COUNT(CASE WHEN Package_Type = 'Child' THEN 1 END) OVER (PARTITION BY month, Package_Id) AS number_childs

FROM CTE_parent_child
ORDER BY month,plansystemID,IFNULL(Package_Id,PlanSystemId ) desc;

优化方案 #

原查询存在重复计算、逻辑冗余的问题，以下是更简洁高效的优化方案：

方案1：关联统计法（通用SQL兼容）

SELECT
  p.StartDate,
  p.EndDate,
  p.PlanSystemId,
  p.Package_Id,
  p.Package_Type,
  p.month,
  p.mode,
  p.rnumber,
  COUNT(c.PlanSystemId) AS number_childs
FROM table p
LEFT JOIN table c
  ON c.Package_Id = p.PlanSystemId
  AND c.Package_Type = 'Child'
  AND c.month = p.month
WHERE p.Package_Type = 'Package'
  AND p.PlanSystemId = 93167253
GROUP BY
  p.StartDate,
  p.EndDate,
  p.PlanSystemId,
  p.Package_Id,
  p.Package_Type,
  p.month,
  p.mode,
  p.rnumber
ORDER BY p.month DESC, p.PlanSystemId;

优化说明：

• 直接以套餐（Package）行作为主表，左关联同月份下属于该套餐的子项（Child）行，通过COUNT直接统计子项数量，逻辑直观
• 过滤条件直接限定只取套餐行，避免处理无关数据
• 去掉不必要的CTE和冗余窗口函数，降低计算开销

方案2：窗口函数+QUALIFY过滤（适用于BigQuery、Snowflake等支持QUALIFY的数据库）

SELECT
  StartDate,
  EndDate,
  PlanSystemId,
  Package_Id,
  Package_Type,
  month,
  mode,
  rnumber,
  COUNT(CASE WHEN Package_Type = 'Child' THEN 1 END) OVER (PARTITION BY month, PlanSystemId) AS number_childs
FROM table
WHERE (Package_Type = 'Package' AND PlanSystemId = 93167253)
   OR (Package_Type = 'Child' AND Package_Id = 93167253)
QUALIFY Package_Type = 'Package'
ORDER BY month DESC, PlanSystemId;

优化说明：

• 用窗口函数一次性统计同月份同套餐下的子项数量
• 通过QUALIFY直接过滤出套餐行，无需额外子查询，代码更简洁

内容的提问来源于stack exchange，提问作者Alejandro Ruiz