用PL/SQL替代Python三重循环计算商品渗透率：效率及转换方法

我有一个查询，输出字段包括：

• location number: lct_nbr
• fiscal week end date: fsc_wk_end_dt
• item number: itm_nbr
• product number: pg_nbr
• SUM(quantity): qty
• localtimestamp: refresh_date

我需要计算商品渗透率（即weight），当前用Python三重循环实现，但耗时极久。想知道：

1. 能否用PL/SQL实现该逻辑？
2. 是否比Python更快？
3. 如何进行转换？

当前Python代码：

item_penetration_by_units = pd.read_sql_query(query, trino_conn)

item_penetration_by_units['total'] = math.nan
item_penetration_by_units['weight'] = math.nan 

LCT_NBRs = item_penetration_by_units['lct_nbr'] 
LCT_NBRs = LCT_NBRs.drop_duplicates()
LCT_NBRs = LCT_NBRs.reset_index(drop=True)  
LCT_NBRs = np.array(LCT_NBRs)  

i = 0 
for i in range(len(LCT_NBRs)):
    x = item_penetration_by_units.loc[item_penetration_by_units['lct_nbr'] == LCT_NBRs[i]]
    print("i: " + str(i) + " and LCT_NBR: " + str(LCT_NBRs[i]))
    PG_NBRs = x['pg_nbr'] 
    PG_NBRs = PG_NBRs.drop_duplicates()
    PG_NBRs = PG_NBRs.reset_index(drop=True)  
    PG_NBRs = np.array(PG_NBRs)  
    n = len(PG_NBRs)
    j = 0 
    for j in range(n):
        y = x.loc[x['pg_nbr'] == PG_NBRs[j]]
        print("j: " + str(j) + " and PG_NBR: " + str(PG_NBRs[j]))
        WK_End_DTs = y['fsc_wk_end_dt'] 
        WK_End_DTs = WK_End_DTs.drop_duplicates()
        WK_End_DTs = WK_End_DTs.reset_index(drop=True)  
        WK_End_DTs = np.array(WK_End_DTs)  
        m = len(WK_End_DTs)
        k = 0 
        for k in range(m):
            z = y.loc[y['fsc_wk_end_dt'] == WK_End_DTs[k]]
            index = z.index
            indx = index.values
            z = z.reset_index(drop=True)
            total_k = float(z.qty.sum())
            l = 0 
            for l in range(len(z)):
                item_penetration_by_units.loc[indx[l], 'total'] = total_k
                if (float(z.loc[l, 'qty']) == 0 and total_k == 0):
                    item_penetration_by_units.loc[indx[l], 'weight'] = 0.0
                else:
                    item_penetration_by_units.loc[indx[l], 'weight'] = float(z.loc[l, 'qty']) / total_k 

注：每个itm_nbr对应唯一的qty、lct_nbr、pg_nbr和fsc_wk_end_dt。weight的计算规则为：单个itm_nbr的qty除以同一lct_nbr、pg_nbr、fsc_wk_end_dt下的总qty；当两者均为0时，weight为0.0。

1. 能否用PL/SQL实现？ #

完全可以，而且根本不需要复杂的循环——直接用SQL的窗口函数就能实现核心逻辑，PL/SQL可用来封装该逻辑（比如做成存储过程或函数），但甚至直接在原查询里集成窗口函数就能搞定，比Python的三重循环简洁太多。

2. 是否比Python更快？ #

肯定更快，核心原因：

• Python三重循环是逐行迭代的行级运算，数据量较大时效率极低；
• 数据库窗口函数是批量运算，由引擎优化执行（比如利用索引、并行计算），处理效率远高于客户端循环；
• 避免了大量数据从数据库拉到Python的IO开销，直接在数据库端完成计算，减少传输耗时。

3. 转换方法 #

方法一：直接用SQL（推荐，无需PL/SQL）

在原查询基础上添加窗口函数，一步完成计算：

SELECT
    lct_nbr,
    fsc_wk_end_dt,
    itm_nbr,
    pg_nbr,
    qty,
    LOCALTIMESTAMP AS refresh_date,
    -- 计算同一lct_nbr、pg_nbr、fsc_wk_end_dt下的总qty
    SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt) AS total,
    -- 计算weight，处理0/0的特殊情况
    CASE
        WHEN qty = 0 AND SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt) = 0 THEN 0.0
        ELSE qty / NULLIF(SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt), 0)
    END AS weight
FROM
    -- 替换为你的原查询语句
    (你的原查询) AS base_data

说明：

• PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt 按需求的三个维度分组，计算每组总qty；
• NULLIF函数避免除数为0的报错，CASE语句处理了qty和总qty都为0的特殊情况；
• 直接在数据库端完成所有计算，无需拉取数据到Python处理，效率提升显著。

方法二：PL/SQL封装（适合复用逻辑）

如果需要把逻辑封装为可复用的存储过程，可参考以下代码：

CREATE OR REPLACE PROCEDURE calculate_item_penetration
IS
BEGIN
    -- 将结果插入目标表，可根据需求改为返回结果集
    INSERT INTO target_table (lct_nbr, fsc_wk_end_dt, itm_nbr, pg_nbr, qty, refresh_date, total, weight)
    SELECT
        lct_nbr,
        fsc_wk_end_dt,
        itm_nbr,
        pg_nbr,
        qty,
        LOCALTIMESTAMP AS refresh_date,
        SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt) AS total,
        CASE
            WHEN qty = 0 AND SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt) = 0 THEN 0.0
            ELSE qty / NULLIF(SUM(qty) OVER (PARTITION BY lct_nbr, pg_nbr, fsc_wk_end_dt), 0)
        END AS weight
    FROM
        (你的原查询) AS base_data;
    COMMIT;
END;
/

调用存储过程即可执行计算：

EXEC calculate_item_penetration;

关键说明 #

你的Python代码本质是按lct_nbr、pg_nbr、fsc_wk_end_dt分组计算总qty，再逐个计算item占比。用SQL窗口函数完全可以替代该逻辑，数据库会自动优化执行计划，无需手动写循环，性能提升可达数个数量级。

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