更新时间：2026-05-28 GMT+08:00

数据开发

DWS层：数据清洗SQL脚本开发

本小节将过滤商品数量小于2的小票信息，并将清洗后的数据写入DWD层数据表中。

进入数据治理中心 DataArts Studio控制台-->进入实例控制台-->数据开发-->脚本开发，新建DWS SQL脚本，粘贴以下SQL命令，单击”运行”，页面下方可查看SQL执行结果，单击”保存”定义该脚本名称为“DWD层过滤商品数量小于2的小票”，编辑完成之后单击“提交”按钮即可在作业开发中使用该脚本。

WITH flow_item_counts AS (
    -- 计算每个flow_id的商品数量
    SELECT 
        flow_id,
        COUNT(DISTINCT product_code) AS item_cnt
    FROM 
        ods_sales_detail
    GROUP BY 
        flow_id
    HAVING 
        COUNT(DISTINCT product_code) > 1
)
INSERT INTO dwd_retail_sales_detail_clean (
    id,
    flow_id,
    org_code,
    product_code,
    price,
    quantity,
    amount,
    sale_time,
    item_cnt
)
SELECT 
    s.id,
    s.flow_id,
    s.org_code,
    s.product_code,
    s.price,
    s.quantity,
    s.amount,
    s.sale_time,
    f.item_cnt
FROM 
    ods_sales_detail s
JOIN 
    flow_item_counts f ON s.flow_id = f.flow_id;

图1 SQL脚本开发页面功能
点击放大

DWD层：数据指标SQL脚本开发

本小节以DWD层数据表为基础，按门店维度对数据进行统计分析，从共现次数、支持度、置信度统计出每个门店最畅销的两种商品组合的前3名。

表1 字段含义说明
字段	含义
共现次数	两种商品同时出现的次数
支持度	「同时买了商品 A 和商品 B 的订单数」占「总订单数」的百分比。
置信度A->B	「同时买 A 和 B 的订单数」占「所有买了 A 的订单数」的百分比。
置信度B->A	「同时买 A 和 B 的订单数」占「所有买了 B 的订单数」的百分比。

进入数据治理中心 DataArts Studio控制台-->进入实例控制台-->数据开发-->脚本开发，新建DWS SQL脚本，粘贴以下SQL命令，单击”运行”，页面下方可查看SQL执行结果，单击“执行结果”页面可查看查询结果，单击”保存”定义该脚本名称为“DWS层门店维度分析商品组合”，编辑完成之后单击“提交”按钮即可在作业开发中使用该脚本。

WITH
-- 使用全量数据
all_data AS (
    SELECT
        flow_id,
        org_code,
        product_code
    FROM dwd_retail_sales_detail_clean
),
-- 计算每个门店的总订单数
total_orders AS (
    SELECT
        org_code,
        COUNT(DISTINCT flow_id) AS total_order_count
    FROM all_data
    GROUP BY org_code
),
-- 计算每个门店每个商品的出现次数
product_counts AS (
    SELECT
        org_code,
        product_code,
        COUNT(DISTINCT flow_id) AS product_order_count
    FROM all_data
    GROUP BY org_code, product_code
),
-- 找出每个门店的商品组合及其出现次数
product_combinations AS (
    SELECT
        a.org_code,
        a.product_code AS product1,
        b.product_code AS product2,
        COUNT(DISTINCT a.flow_id) AS combination_count,
        ROW_NUMBER() OVER (PARTITION BY a.org_code ORDER BY COUNT(DISTINCT a.flow_id) DESC) AS rn
    FROM
        all_data a
    JOIN
        all_data b ON a.flow_id = b.flow_id AND a.org_code = b.org_code AND a.product_code < b.product_code
    GROUP BY
        a.org_code, a.product_code, b.product_code
)
-- 插入分析结果
INSERT INTO dws_product_combination_analysis (
    org_code,
    product1,
    product2,
    combination_count,
    support_percentage,
    confidence_product1_to_product2,
    confidence_product2_to_product1,
    analysis_date
)
SELECT
    pc.org_code,
    pc.product1,
    pc.product2,
    pc.combination_count,
    -- 支持度：组合出现的次数 / 该门店总订单数
    ROUND((pc.combination_count::numeric / to_cnt.total_order_count) * 100, 4) AS support_percentage,
    -- 置信度：组合出现的次数 / 商品1在该门店出现的次数
    ROUND((pc.combination_count::numeric / p1.product_order_count) * 100, 4) AS confidence_product1_to_product2,
    -- 置信度：组合出现的次数 / 商品2在该门店出现的次数
    ROUND((pc.combination_count::numeric / p2.product_order_count) * 100, 4) AS confidence_product2_to_product1,
    CURRENT_DATE AS analysis_date
FROM
    product_combinations pc
JOIN
    total_orders to_cnt ON pc.org_code = to_cnt.org_code
JOIN
    product_counts p1 ON pc.org_code = p1.org_code AND pc.product1 = p1.product_code
JOIN
    product_counts p2 ON pc.org_code = p2.org_code AND pc.product2 = p2.product_code
WHERE
    pc.rn <= 3
ORDER BY
    pc.org_code, pc.combination_count DESC;


SELECT
	p.org_code AS "店铺编码",
	p.product1 AS "商品A",
	p.product2 AS "商品B",
	p.combination_count AS "共现次数",
	p.support_percentage AS "支持度%",
	p.confidence_product1_to_product2 AS "置信度A->B",
	p.confidence_product2_to_product1 AS "置信度B->A"
FROM
	dws_product_combination_analysis p
ORDER BY
	combination_count DESC;

图2 结果查询