如何通过LEFT JOIN合并两表并实现无重复聚合？SQL促销效果分析问询

Hey there! Let's walk through your two SQL questions with practical examples—these are super common scenarios when analyzing marketing data, so I’ve got you covered.

First, let's map out the key metrics you’ll want to track to measure campaign performance: unique visitors, total sales, average order value, conversion rate, and paying customer count. Since you have a web traffic table and a promotion campaign table (let’s assume it’s named promo_campaigns with fields like campaign_name, start_date, and end_date), we can join these to isolate and analyze promo-specific traffic.

Example 1: Performance Metrics per Campaign #

This query calculates core metrics for each promotion, so you can see which campaigns drive the best results:

SELECT
    c.campaign_name,
    COUNT(DISTINCT w.visitor_id) AS unique_visitors,
    SUM(w.purchase_value) AS total_sales,
    ROUND(AVG(w.purchase_value), 2) AS avg_order_value,
    COUNT(CASE WHEN w.purchase_value > 0 THEN w.visitor_id END) AS paying_customers,
    ROUND(
        COUNT(CASE WHEN w.purchase_value > 0 THEN w.visitor_id END) * 100.0 / 
        COUNT(DISTINCT w.visitor_id), 
        2
    ) AS conversion_rate
FROM web w
LEFT JOIN promo_campaigns c 
    ON w.campaign_name = c.campaign_name
WHERE c.campaign_name IS NOT NULL -- Filter to only promotion traffic
GROUP BY c.campaign_name
ORDER BY total_sales DESC;

• unique_visitors: Tracks how many distinct people engaged with the campaign
• total_sales: Measures overall revenue generated by the campaign
• avg_order_value: Shows the average amount spent per paying customer
• conversion_rate: Percentage of visitors who made a purchase (critical for measuring campaign efficiency)

Example 2: Promo vs. Non-Promo Traffic Comparison #

To put your promo performance in context, compare it to non-promotion traffic:

SELECT
    CASE 
        WHEN c.campaign_name IS NOT NULL THEN 'Promotion' 
        ELSE 'Non-Promotion' 
    END AS traffic_segment,
    COUNT(DISTINCT w.visitor_id) AS unique_visitors,
    SUM(w.purchase_value) AS total_sales,
    ROUND(
        COUNT(CASE WHEN w.purchase_value > 0 THEN w.visitor_id END) * 100.0 / 
        COUNT(DISTINCT w.visitor_id), 
        2
    ) AS conversion_rate
FROM web w
LEFT JOIN promo_campaigns c 
    ON w.campaign_name = c.campaign_name
GROUP BY traffic_segment;

This will tell you if promotions actually drive better conversion or sales than regular traffic.

Duplicate counts/sums usually happen when your promotion table has multiple entries for the same campaign_name (e.g., a campaign split into multiple phases, or accidental duplicate rows). When you LEFT JOIN, each row in the web table gets matched to every corresponding row in the promo table—leading to inflated numbers when you aggregate.

Here are two reliable fixes:

Fix 1: Deduplicate the Promo Table First #

Use a CTE (Common Table Expression) to remove duplicate entries from the promo table before joining:

WITH distinct_promos AS (
    SELECT DISTINCT 
        campaign_name, 
        start_date, 
        end_date -- Include only the fields you need for analysis
    FROM promo_campaigns
)
SELECT
    w.campaign_name,
    COUNT(DISTINCT w.visitor_id) AS unique_visitors,
    SUM(w.purchase_value) AS total_sales
FROM web w
LEFT JOIN distinct_promos dp 
    ON w.campaign_name = dp.campaign_name
GROUP BY w.campaign_name;

By deduping first, each web row only matches one promo row, eliminating duplicate aggregation.

Fix 2: Aggregate Web Data Before Joining #

Another approach is to calculate your web metrics first, then join the promo table. This way, even if the promo table has duplicates, your aggregated numbers won’t be affected:

WITH web_aggregated AS (
    SELECT
        campaign_name,
        COUNT(DISTINCT visitor_id) AS unique_visitors,
        SUM(purchase_value) AS total_sales
    FROM web
    GROUP BY campaign_name
)
SELECT
    wa.campaign_name,
    wa.unique_visitors,
    wa.total_sales,
    pc.start_date,
    pc.end_date
FROM web_aggregated wa
LEFT JOIN promo_campaigns pc 
    ON wa.campaign_name = pc.campaign_name
GROUP BY 
    wa.campaign_name, 
    wa.unique_visitors, 
    wa.total_sales, 
    pc.start_date, 
    pc.end_date; -- Final GROUP BY removes any remaining promo duplicates

This method is especially useful if you need to keep promo details (like start/end dates) but don’t want them to skew your traffic/sales metrics.

内容的提问来源于stack exchange，提问作者Pak Hang Leung