SQL技术问询：跨模糊列名表计算周度销售额增减率（重发）

Hey there! Let's work through this together—since you're new to SQL, I'll break it down nice and clear so you can follow along. The key issue here is that you need to associate the "current week" and "previous week" sales data for the same category (盘子), which is why UNION alone didn't work (UNION stacks rows, but we need to link columns side-by-side to compare values).

Step 1: First, alias those fuzzy column names #

Since your tables have unclear column names, start by giving them meaningful aliases in subqueries. This will make your main query way easier to read and debug. Let's assume:

• Both tables have a column for category (let's say it's fuzzy_col_2), sales amount (fuzzy_col_1), and week identifier (like week number or date range, fuzzy_col_3).

Step 2: Join the two datasets to compare periods #

We'll use a JOIN to link the current week's sales data with the previous week's data for the "盘子" category. Here's a concrete example:

SELECT
    curr.category,
    curr.week_id AS current_week,
    prev.week_id AS previous_week,
    curr.current_sales,
    prev.prev_sales,
    -- Calculate growth rate, handle division by zero to avoid errors
    CASE
        WHEN prev.prev_sales = 0 THEN NULL  -- Avoid division by zero
        ELSE ROUND((curr.current_sales / prev.prev_sales - 1) * 100, 2)
    END AS sales_growth_rate_percent
FROM
    -- Subquery for current week's plate sales
    (
        SELECT
            fuzzy_col_2 AS category,
            fuzzy_col_3 AS week_id,
            fuzzy_col_1 AS current_sales
        FROM your_current_week_table
        WHERE fuzzy_col_2 = '盘子'
    ) curr
JOIN
    -- Subquery for previous week's plate sales
    (
        SELECT
            fuzzy_col_2 AS category,
            fuzzy_col_3 AS week_id,
            fuzzy_col_1 AS prev_sales
        FROM your_previous_week_table
        WHERE fuzzy_col_2 = '盘子'
    ) prev
ON
    curr.category = prev.category
    -- Adjust this condition based on how your week is identified:
    -- If week is a number (e.g., 2024-W25), use curr.week_id = prev.week_id + 1
    -- If week is a date range, use DATEADD to get the prior week (e.g., DATEADD(week, -1, curr.week_start_date) = prev.week_start_date)
    AND curr.week_id = prev.week_id + 1;

Why your earlier attempts might have failed #

• UNION: This combines rows from two tables, but we need to compare columns (current vs previous sales) side-by-side, not stack them.
• Column aliases without subqueries: If you tried aliasing columns directly in a single query without isolating each period first, the SQL engine might have confused which table's column you were referring to.

Bonus: If all sales data is in one table (not two separate tables) #

If your data lives in a single table with all weeks' records, use a self-join instead:

SELECT
    curr.category,
    curr.week_id AS current_week,
    prev.week_id AS previous_week,
    curr.sales AS current_sales,
    prev.sales AS prev_sales,
    CASE
        WHEN prev.sales = 0 THEN NULL
        ELSE ROUND((curr.sales / prev.sales - 1) * 100, 2)
    END AS sales_growth_rate_percent
FROM
    (
        SELECT
            fuzzy_col_2 AS category,
            fuzzy_col_3 AS week_id,
            fuzzy_col_1 AS sales
        FROM all_sales_table
        WHERE fuzzy_col_2 = '盘子' AND fuzzy_col_3 = 25  -- Replace with your current week ID
    ) curr
JOIN
    (
        SELECT
            fuzzy_col_2 AS category,
            fuzzy_col_3 AS week_id,
            fuzzy_col_1 AS sales
        FROM all_sales_table
        WHERE fuzzy_col_2 = '盘子' AND fuzzy_col_3 = 24  -- Replace with your previous week ID
    ) prev
ON curr.category = prev.category;

Quick tips for SQL newbies #

• Always test subqueries first (run just the inner SELECT statements) to make sure they return the right data.
• If your sales column is stored as a string, convert it to a numeric type first using CAST(fuzzy_col_1 AS DECIMAL(10,2)) to avoid calculation errors.
• Adjust the week comparison logic to match how your table stores week data (numbers, date ranges, etc.).

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