PostgreSQL视图如何实现跨分类差值引用与规则化分组转换
实现思路与可运行代码
核心计算前置值
所有规则涉及的跨行依赖值是固定唯一的,先统一提取避免重复计算:
- Converse无排除条件行的差值:
Converse + exclusion IS NULL行的include_spec - exclude_spec,对应示例值为184 - 89 = 95,为Nike行的Medium、Large列提供叠加值 - Converse无排除条件行的基准值:同上一行的
include_spec,示例值184,为Converse行的Small、Medium、Large列提供计算基准 - Converse带Dior排除条件行的include值:
Converse + exclusion = 'Dior'行的include_spec,示例值550,为Converse行的Large列提供叠加值
之前的初步代码存在的问题
- 字段笔误:CTE里写的
inc_specialty和实际表字段include_spec不一致 - 未做多行聚合:Converse对应2条原始数据,直接逐行查询会输出重复的Class行,不符合最终每个Class单行展示的要求
- 漏了Large列的计算逻辑,也未提取Converse专属的Dior行参考值
对应PostgreSQL的可实现代码
WITH calc_public_values AS ( -- 提取Converse空排除行的差值与基准值 SELECT include_spec AS converse_empty_include, (include_spec - exclude_spec) AS converse_empty_diff FROM class WHERE class = 'Converse' AND exclusion IS NULL -- 提取Converse带Dior排除行的参考值 UNION ALL SELECT include_spec AS converse_dior_include, NULL FROM class WHERE class = 'Converse' AND exclusion = 'Dior' ), class_base_agg AS ( -- 按Class维度聚合,拿到每个Class的基础计算值(空排除行的include_spec) SELECT class, MAX(CASE WHEN exclusion IS NULL THEN include_spec END) AS self_base_value FROM class GROUP BY class ) SELECT cba.class, -- Small列计算 CASE cba.class WHEN 'Nike' THEN cba.self_base_value WHEN 'Converse' THEN cba.self_base_value END AS Small, -- Medium列计算 CASE cba.class WHEN 'Nike' THEN cba.self_base_value + (SELECT converse_empty_diff FROM calc_public_values) WHEN 'Converse' THEN cba.self_base_value END AS Medium, -- Large列计算 CASE cba.class WHEN 'Nike' THEN cba.self_base_value + (SELECT converse_empty_diff FROM calc_public_values) WHEN 'Converse' THEN cba.self_base_value + (SELECT converse_dior_include FROM calc_public_values) END AS Large FROM class_base_agg cba;
示例数据运行结果
| Class | Small | Medium | Large |
|---|---|---|---|
| Nike | 2008 | 2103 | 2103 |
| Converse | 184 | 184 | 734 |
完全匹配给出的转换规则。
后续扩展注意点
- 上述写法适合参考值全局唯一的场景,因为都是单值,直接用标量子查询取值不会出现关联膨胀问题,性能也比多次join好
- 后续如果新增其他Class的计算规则,直接在三个CASE判断里补对应分支即可,不用改动前置的公共值提取逻辑
内容的提问来源于stack exchange,提问作者Noshame00
相关产品推荐
相关产品推荐

