如何用函数简化PostgreSQL中重复的CTE代码？

一、创建带参数的自定义函数（完美匹配“封装重复逻辑”需求） #

根据你的CTE结构，可分为4类重复逻辑，分别创建函数，原查询仅需替换CTE定义部分，核心关联逻辑无需改动：

1. 旧表（2018-2019）销售统计函数 #

对应sales_2018、sales_2019，封装查询gh_consolidated.bestellungen_artikel的逻辑：

CREATE OR REPLACE FUNCTION get_sales_old(p_year integer)
RETURNS TABLE(article_id integer, article_number text, netto numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        ba.article_id,
        ba.article_number,
        SUM(ba.position_total)::numeric AS netto,
        SUM(ba.quantity)::integer AS quantity
    FROM gh_consolidated.bestellungen_artikel ba
    LEFT JOIN gh_consolidated.vt_myzeit t ON t.ansi = ba.ordertime::date
    WHERE t.year_reporting::double precision = p_year
      AND ba.order_status <> 'Storniert / Abgelehnt'
    GROUP BY ba.article_id, ba.article_number;
END;
$$;

调用替换：原CTE的sales_2018 as (...)改为：

, sales_2018 AS (SELECT * FROM get_sales_old(2018))

2. 新表（2020+）销售统计函数 #

对应sales_2020、sales_2021、sales_2022，封装查询02_business_layer.orderposition的逻辑：

CREATE OR REPLACE FUNCTION get_sales_new(p_year integer)
RETURNS TABLE(article_number text, sales numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        op.article_number,
        SUM(op.sales_price_net_eur_inkl_order_pos_discount)::numeric AS sales,
        SUM(op.quantity)::integer AS quantity
    FROM "02_business_layer".orderposition op
    WHERE op.orderyear = p_year::text
      AND op.auftragsstatus_stf1 NOT IN ('Abgesagt')
    GROUP BY op.article_number;
END;
$$;

调用替换：原CTE的sales_2020 as (...)改为：

, sales_2020 AS (SELECT * FROM get_sales_new(2020))

3. 新表销售YTD（年初至今）统计函数 #

对应sales_2021_ytd、sales_2022_ytd、sales_2023_ytd：

CREATE OR REPLACE FUNCTION get_sales_ytd(p_year integer)
RETURNS TABLE(article_number text, sales numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        op.article_number,
        SUM(op.sales_price_net_eur_inkl_order_pos_discount)::numeric AS sales,
        SUM(op.quantity)::integer AS quantity
    FROM "02_business_layer".orderposition op
    WHERE op.orderyear = p_year::text
      AND op.auftragsstatus_stf1 NOT IN ('Abgesagt')
      AND op.order_creationdate <= NOW() - INTERVAL '1 year' * (EXTRACT(YEAR FROM NOW()) - p_year)
    GROUP BY op.article_number;
END;
$$;

调用替换：原CTE的sales_2021_ytd as (...)改为：

, sales_2021_ytd AS (SELECT * FROM get_sales_ytd(2021))

4. 营收统计函数（含常规与YTD） #

4.1 旧表（2018-2019）营收函数

对应revenue_2018、revenue_2019：

CREATE OR REPLACE FUNCTION get_revenue_old(p_year integer)
RETURNS TABLE(article_id integer, article_number text, revenue numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        ba.article_id,
        ba.article_number,
        SUM(ba.position_total)::numeric AS revenue,
        SUM(ba.quantity)::integer AS quantity
    FROM gh_consolidated.bestellungen_artikel ba
    LEFT JOIN gh_consolidated.vt_myzeit t ON t.ansi = ba.delivered_earliest::date
    WHERE ba.delivered_earliest IS NOT NULL
      AND ba.order_status <> 'Storniert / Abgelehnt'
      AND t.year_reporting::integer = p_year
    GROUP BY ba.article_id, ba.article_number;
END;
$$;

4.2 新表（2020+）营收函数

对应revenue_2020、revenue_2021、revenue_2022：

CREATE OR REPLACE FUNCTION get_revenue_new(p_year integer)
RETURNS TABLE(article_number text, revenue numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        op.article_number,
        SUM(op.sales_price_net_eur_inkl_order_pos_discount)::numeric AS revenue,
        SUM(op.quantity)::integer AS quantity
    FROM "02_business_layer".orderposition op
    WHERE DATE_PART('year', op.delivered_earliest_date_new) = p_year
      AND op.auftragsstatus_stf1 NOT IN ('Abgesagt')
    GROUP BY op.article_number;
END;
$$;

4.3 新表营收YTD函数

对应revenue_2021_ytd、revenue_2022_ytd、revenue_2023_ytd：

CREATE OR REPLACE FUNCTION get_revenue_ytd(p_year integer)
RETURNS TABLE(article_number text, revenue numeric, quantity integer)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        op.article_number,
        SUM(op.sales_price_net_eur_inkl_order_pos_discount)::numeric AS revenue,
        SUM(op.quantity)::integer AS quantity
    FROM "02_business_layer".orderposition op
    WHERE DATE_PART('year', op.delivered_earliest_date_new) = p_year
      AND op.auftragsstatus_stf1 NOT IN ('Abgesagt')
      AND op.delivered_earliest_date_new <= NOW() - INTERVAL '1 year' * (EXTRACT(YEAR FROM NOW()) - p_year)
    GROUP BY op.article_number;
END;
$$;

二、参数化CTE（无需创建数据库对象，临时优化） #

如果不想创建函数，可先用通用CTE聚合全量数据，再通过过滤得到对应年份结果，适合临时调整：

-- 先聚合所有新表销售数据
, all_sales_new AS (
    SELECT
        op.article_number,
        op.orderyear::integer AS year,
        SUM(op.sales_price_net_eur_inkl_order_pos_discount) AS sales,
        SUM(op.quantity) AS quantity
    FROM "02_business_layer".orderposition op
    WHERE op.auftragsstatus_stf1 NOT IN ('Abgesagt')
    GROUP BY op.article_number, op.orderyear
)
-- 原CTE替换为过滤逻辑
, sales_2020 AS (SELECT article_number, sales, quantity FROM all_sales_new WHERE year = 2020)
, sales_2021 AS (SELECT article_number, sales, quantity FROM all_sales_new WHERE year = 2021)

此方式无需创建函数，但需先聚合全量数据，适合数据量较小的场景。

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