请求实现SQL逻辑：关联指定时间内销售数据更新Dept交易字段

核心逻辑 #

针对txn_type = 'dept'的交易，关联同一casemgr_id + casemgr_clnt_id组合下、交易日期前后90-180天的sales类型交易，按指定规则聚合产品分类、总销售额和总数量，最终更新对应dept交易行。

结果验证查询（先确认数据匹配正确性） #

WITH dept_txn AS (
    SELECT 
        txn_id,
        casemgr_id,
        casemgr_clnt_id,
        txn_date
    FROM txn_table
    WHERE txn_type = 'dept'
),
sales_matched AS (
    SELECT 
        d.txn_id,
        -- 按M、MP、A、MS的固定顺序拼接产品分类
        STRING_AGG(s.prod_cat, ',' ORDER BY 
            CASE s.prod_cat 
                WHEN 'M' THEN 1
                WHEN 'MP' THEN 2
                WHEN 'A' THEN 3
                WHEN 'MS' THEN 4
                ELSE 5
            END) AS agg_prod_cat,
        SUM(s.sales_amt) AS total_sales,
        SUM(s.qty) AS total_qty
    FROM dept_txn d
    JOIN txn_table s
        ON s.casemgr_id = d.casemgr_id
        AND s.casemgr_clnt_id = d.casemgr_clnt_id
        AND s.txn_type = 'sales'
        -- 筛选交易日期前后90至180天的范围
        AND ABS(DATEDIFF(day, s.txn_date, d.txn_date)) BETWEEN 90 AND 180
    GROUP BY d.txn_id
)
-- 关联原表查看待更新的dept行数据
SELECT 
    t.txn_id,
    t.txn_type,
    t.casemgr_id,
    t.casemgr_clnt_id,
    t.txn_date,
    sm.agg_prod_cat AS updated_prod_cat,
    sm.total_sales AS updated_sales_amt,
    sm.total_qty AS updated_qty
FROM txn_table t
LEFT JOIN sales_matched sm ON t.txn_id = sm.txn_id
WHERE t.txn_type = 'dept';

最终更新语句 #

WITH dept_txn AS (
    SELECT 
        txn_id,
        casemgr_id,
        casemgr_clnt_id,
        txn_date
    FROM txn_table
    WHERE txn_type = 'dept'
),
sales_matched AS (
    SELECT 
        d.txn_id,
        STRING_AGG(s.prod_cat, ',' ORDER BY 
            CASE s.prod_cat 
                WHEN 'M' THEN 1
                WHEN 'MP' THEN 2
                WHEN 'A' THEN 3
                WHEN 'MS' THEN 4
                ELSE 5
            END) AS agg_prod_cat,
        SUM(s.sales_amt) AS total_sales,
        SUM(s.qty) AS total_qty
    FROM dept_txn d
    JOIN txn_table s
        ON s.casemgr_id = d.casemgr_id
        AND s.casemgr_clnt_id = d.casemgr_clnt_id
        AND s.txn_type = 'sales'
        AND ABS(DATEDIFF(day, s.txn_date, d.txn_date)) BETWEEN 90 AND 180
    GROUP BY d.txn_id
)
UPDATE txn_table t
SET 
    prod_cat = sm.agg_prod_cat,
    sales_amt = sm.total_sales,
    qty = sm.total_qty
FROM sales_matched sm
WHERE t.txn_id = sm.txn_id
AND t.txn_type = 'dept';

数据库兼容调整说明 #

不同数据库的函数语法有差异，需根据实际环境修改：

• MySQL：将STRING_AGG替换为GROUP_CONCAT，排序规则嵌入函数内：

  GROUP_CONCAT(s.prod_cat ORDER BY 
      CASE s.prod_cat 
          WHEN 'M' THEN 1
          WHEN 'MP' THEN 2
          WHEN 'A' THEN 3
          WHEN 'MS' THEN 4
          ELSE 5
      END SEPARATOR ',') AS agg_prod_cat
  

• PostgreSQL：日期差计算可使用DATE_PART('day', ABS(s.txn_date - d.txn_date))，STRING_AGG语法一致。

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