SQLite环境下获取2019年总交易量的SQL查询报列名歧义错误及结果异常问题排查

Let's walk through the issues in your query and fix them to get the expected results:

Key Issues in Your Original Query #

1. Ambiguous month Column: The month column exists in all three tables, so when you use GROUP BY month or ORDER BY month, SQLite can't tell which table's column you mean.
2. Mismatched Parentheses: Your SUM() function had unclosed parentheses, which would throw a syntax error even if the column ambiguity was fixed.
3. Unprocessed Percentage Values: The perc_active_cust field is a string with a % sign (e.g., 72%). You can't multiply strings directly—you need to convert this to a decimal value first.
4. Poorly Structured JOIN Conditions: You lumped all JOIN conditions into a single ON clause, which makes the query hard to read and maintain. Also, the 2019 filter belongs in a WHERE clause, not an ON clause.

Corrected SQL Query #

SELECT 
    a.month AS Activity_Month,
    SUM(
        -- Convert Charge_volume to integer by removing commas
        CAST(REPLACE(a.Charge_volume, ',', '') AS INTEGER) 
        -- Convert new_cust_onboarded to integer by removing commas
        * CAST(REPLACE(c.new_cust_onboarded, ',', '') AS INTEGER) 
        -- Convert percentage to decimal (remove % sign, divide by 100)
        * (CAST(REPLACE(b.perc_active_cust, '%', '') AS INTEGER) / 100.0)
    ) AS Total_transactions_2019
FROM Charge_volume a
-- Join Active_Customers using all shared fields as per your rule
JOIN Active_Customers b 
    ON a.cust_sign_month = b.cust_sign_month
    AND a.country = b.country
    AND a.month = b.month
    AND a.months_since_cust = b.months_since_cust
-- Join New_customers using cust_sign_month and country as per your rule
JOIN New_customers c
    ON b.cust_sign_month = c.month
    AND b.country = c.country
-- Filter for only 2019 records
WHERE SUBSTRING(a.month, 1, 4) = '2019'
-- Group by the aliased Activity_Month to avoid ambiguity
GROUP BY Activity_Month
-- Sort using the aliased column for clarity
ORDER BY Activity_Month;

What Changed? #

• Resolved Column Ambiguity: We use the Activity_Month alias in GROUP BY and ORDER BY to explicitly reference the column we want to group/sort by.
• Fixed Syntax: The SUM() function now properly wraps the entire calculation with matching parentheses.
• Processed Percentages: We remove the % from perc_active_cust, convert it to an integer, then divide by 100.0 to get a decimal percentage (e.g., 72% becomes 0.72).
• Cleaner JOIN Structure: Each JOIN has its own ON clause that defines the relationship between tables, making the query easier to debug and maintain.
• Moved Filter to WHERE: The 2019 year filter is now in the WHERE clause, which is the correct place for row-level filtering after table joins.

This query should return the exact format and values you're expecting for 2019's total transactions.

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