Oracle实现按产品经理分组的近6个月业务统计数据透视表构建
近7个月份维度业务统计透视实现方案
原查询框架的核心逻辑缺口
- 内层子查询仅关联了月份表与订单行表,未补全原业务查询的表连接链路(订单主表、商品卡表、用户表),导致产品经理维度、统计指标无有效计算数据源
- Pivot聚合块为占位写法,仅定义了单值
SUM(CNT),未匹配原查询的三个核心统计项:有效订单数、未交付订单数、未交付占比 - 外层SELECT误将未交付占比设为全局独立列,实际上占比是分月份粒度计算的指标,需要与对应月份的其他统计值绑定
- 未将原查询的过滤规则、产品经理名称拼接逻辑纳入分组维度,导致分组粒度与业务逻辑不匹配
可直接运行的完整SQL
WITH MONTHS AS ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-LEVEL+1) AS MONTH, DECODE(LEVEL, 1, 'Akt_mesiac', 'minuly_mesiac'||(LEVEL-1)) AS MONTH_NAME FROM DUAL CONNECT BY LEVEL <=7 ) SELECT partner, prod_man, -- 当月指标 NVL(Akt_mesiac_obj_pocet, 0) AS 当月有效订单数, NVL(Akt_mesiac_nedod_pocet, 0) AS 当月未交付订单数, NVL(Akt_mesiac_pct, 0) AS 当月未交付占比, -- 近6个月指标 NVL(minuly_mesiac1_obj_pocet, 0) AS 前1月有效订单数, NVL(minuly_mesiac1_nedod_pocet, 0) AS 前1月未交付订单数, NVL(minuly_mesiac1_pct, 0) AS 前1月未交付占比, NVL(minuly_mesiac2_obj_pocet, 0) AS 前2月有效订单数, NVL(minuly_mesiac2_nedod_pocet, 0) AS 前2月未交付订单数, NVL(minuly_mesiac2_pct, 0) AS 前2月未交付占比, NVL(minuly_mesiac3_obj_pocet, 0) AS 前3月有效订单数, NVL(minuly_mesiac3_nedod_pocet, 0) AS 前3月未交付订单数, NVL(minuly_mesiac3_pct, 0) AS 前3月未交付占比, NVL(minuly_mesiac4_obj_pocet, 0) AS 前4月有效订单数, NVL(minuly_mesiac4_nedod_pocet, 0) AS 前4月未交付订单数, NVL(minuly_mesiac4_pct, 0) AS 前4月未交付占比, NVL(minuly_mesiac5_obj_pocet, 0) AS 前5月有效订单数, NVL(minuly_mesiac5_nedod_pocet, 0) AS 前5月未交付订单数, NVL(minuly_mesiac5_pct, 0) AS 前5月未交付占比, NVL(minuly_mesiac6_obj_pocet, 0) AS 前6月有效订单数, NVL(minuly_mesiac6_nedod_pocet, 0) AS 前6月未交付订单数, NVL(minuly_mesiac6_pct, 0) AS 前6月未交付占比 FROM ( SELECT l.partner, bu.meno||' '||DECODE(substr(bu.priezvisko, 1, 2), 'Sz', substr(bu.priezvisko, 1, 2), 'Gy', substr(bu.priezvisko, 1, 2), 'Ny', substr(bu.priezvisko, 1, 2), 'Zs', substr(bu.priezvisko, 1, 2), 'Cs', substr(bu.priezvisko, 1, 2), substr(bu.priezvisko, 1, 1)) ||'.' AS prod_man, m.MONTH_NAME, -- 有效订单数统计 SUM(CASE WHEN o.pocet!=0 THEN 1 ELSE 0 END) AS obj_pocet, -- 未交付订单数统计 SUM(CASE WHEN o.pocet=0 OR o.p_del+o.p_del_dod >= o.pocet THEN 1 ELSE 0 END) AS nedod_pocet, -- 未交付占比统计 ROUND( SUM(CASE WHEN o.pocet=0 OR o.p_del+o.p_del_dod >= o.pocet THEN 1 ELSE 0 END)/COUNT(*), 3 ) * 100 AS pct FROM MONTHS M JOIN obj_odb_l l ON M.MONTH = TRUNC(l.datum_p,'MONTH') -- 补全原业务查询的所有表连接 JOIN obj_odb_o o ON o.rid_o = l.rid JOIN sklad_karta sk ON sk.id = o.kod_id JOIN bartex_users bu ON bu.id = sk.id.prod_man -- 保留原查询的合作伙伴过滤规则 WHERE l.partner IN (325, 326) GROUP BY l.partner, bu.meno||' '||DECODE(substr(bu.priezvisko, 1, 2), 'Sz', substr(bu.priezvisko, 1, 2), 'Gy', substr(bu.priezvisko, 1, 2), 'Ny', substr(bu.priezvisko, 1, 2), 'Zs', substr(bu.priezvisko, 1, 2), 'Cs', substr(bu.priezvisko, 1, 2), substr(bu.priezvisko, 1, 1)) ||'.', m.MONTH_NAME ) PIVOT ( SUM(obj_pocet) AS obj_pocet, SUM(nedod_pocet) AS nedod_pocet, SUM(pct) AS pct FOR MONTH_NAME IN ( 'Akt_mesiac' AS Akt_mesiac, 'minuly_mesiac1' AS minuly_mesiac1, 'minuly_mesiac2' AS minuly_mesiac2, 'minuly_mesiac3' AS minuly_mesiac3, 'minuly_mesiac4' AS minuly_mesiac4, 'minuly_mesiac5' AS minuly_mesiac5, 'minuly_mesiac6' AS minuly_mesiac6 ) );
逻辑说明
- 月份CTE自动生成当前月向前追溯6个自然月的时间列表,通过
TRUNC(l.datum_p,'MONTH')实现月份粒度匹配,无需额外写日期范围过滤条件 - 内层查询完全复用原基础查询的计算规则、过滤逻辑、表连接关系,仅增加了月份维度的分组
- Pivot块同时对三个统计指标做聚合,避免单值占位的逻辑漏洞
- 最外层通过
NVL将无数据月份的空值转为0,保证输出格式统一
内容的提问来源于stack exchange,提问作者Know-nothing
相关产品推荐
相关产品推荐

