You need to enable JavaScript to run this app. Oracle实现按产品经理分组的近6个月业务统计数据透视表构建
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.17 22:57:01