PostgreSQL合并两个查询结果并生成指定格式汇总的技术需求

原始查询及结果 #

查询1（计划数据） #

SELECT cpl.product_id
FROM crm_planning_line cpl
    LEFT JOIN crm_planning cp ON cpl.crm_planning_id = cp.id 
WHERE EXTRACT(MONTH FROM cp.planning_date + interval '7' HOUR)=9
    AND EXTRACT(YEAR FROM cp.planning_date + interval '7' HOUR)=2022
    AND cp.salesman_id = 27
    AND cp.customer_id = 2977

结果1（注：推测原查询实际包含1 AS view字段）：

product_id | view
7359         1
8192         1
7359         1
8192         1
7357         1

查询2（采购数据） #

SELECT line.product_id
FROM account_invoice_line line
    LEFT JOIN account_invoice inv on inv.id=line.invoice_id
WHERE EXTRACT(MONTH FROM inv.date_invoice)=9
    AND EXTRACT(YEAR FROM inv.date_invoice)=2022
    AND inv.user_id=27
    AND inv.partner_id=2977

结果2（注：推测原查询实际包含line.buy AS buy字段）：

product_id | buy
7359         100
4970         200
4970         50

需求说明 #

需要合并上述两个查询的结果，生成两种汇总格式：

• 预期结果1：仅展示存在采购或计划数据的产品，缺失数据项省略
• 预期结果2：所有涉及产品均展示，缺失数据项补0

实现代码 #

1. 生成预期结果1的SQL #

WITH plan_data AS (
    SELECT 
        product_id,
        COUNT(*) AS view_count
    FROM crm_planning_line cpl
    LEFT JOIN crm_planning cp ON cpl.crm_planning_id = cp.id 
    WHERE EXTRACT(MONTH FROM cp.planning_date + interval '7' HOUR)=9
        AND EXTRACT(YEAR FROM cp.planning_date + interval '7' HOUR)=2022
        AND cp.salesman_id = 27
        AND cp.customer_id = 2977
    GROUP BY product_id
),
buy_data AS (
    SELECT 
        product_id,
        SUM(buy) AS buy_total
    FROM account_invoice_line line
    LEFT JOIN account_invoice inv on inv.id=line.invoice_id
    WHERE EXTRACT(MONTH FROM inv.date_invoice)=9
        AND EXTRACT(YEAR FROM inv.date_invoice)=2022
        AND inv.user_id=27
        AND inv.partner_id=2977
    GROUP BY product_id
)
SELECT 
    COALESCE(p.product_id, b.product_id) AS product_id,
    CONCAT_WS(',', 
        CASE WHEN b.buy_total IS NOT NULL THEN b.buy_total END,
        CASE WHEN p.view_count IS NOT NULL THEN p.view_count END
    ) AS summery
FROM plan_data p
FULL JOIN buy_data b ON p.product_id = b.product_id
ORDER BY product_id;

2. 生成预期结果2的SQL #

WITH plan_data AS (
    SELECT 
        product_id,
        COUNT(*) AS view_count
    FROM crm_planning_line cpl
    LEFT JOIN crm_planning cp ON cpl.crm_planning_id = cp.id 
    WHERE EXTRACT(MONTH FROM cp.planning_date + interval '7' HOUR)=9
        AND EXTRACT(YEAR FROM cp.planning_date + interval '7' HOUR)=2022
        AND cp.salesman_id = 27
        AND cp.customer_id = 2977
    GROUP BY product_id
),
buy_data AS (
    SELECT 
        product_id,
        SUM(buy) AS buy_total
    FROM account_invoice_line line
    LEFT JOIN account_invoice inv on inv.id=line.invoice_id
    WHERE EXTRACT(MONTH FROM inv.date_invoice)=9
        AND EXTRACT(YEAR FROM inv.date_invoice)=2022
        AND inv.user_id=27
        AND inv.partner_id=2977
    GROUP BY product_id
)
SELECT 
    COALESCE(p.product_id, b.product_id) AS product_id,
    CONCAT(COALESCE(b.buy_total, 0), ',', COALESCE(p.view_count, 0)) AS summery
FROM plan_data p
FULL JOIN buy_data b ON p.product_id = b.product_id
ORDER BY product_id;

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