MySQL错误#1111 - 分组函数误用排查及集合条件消费者查询语句优化求助

Hey there, let's work through this SQL issue step by step! First, let's tackle the error you're seeing, then we'll refine your set logic into a working query.

Why You're Getting the #1111 Error #

The Invalid use of group function error happens because you tried using SUM() directly in the WHERE clause. Here's the key difference to remember:

• WHERE filters individual rows before any grouping or aggregation happens. It can't access calculated aggregate values like sums or averages.
• HAVING filters grouped results after aggregation is done—this is where you need to put your SUM() check.

Breaking Down Your Set Conditions #

First, let's map each set to valid SQL logic:

• Set A: Customers who didn't shop on Monday/Friday → t.the_day NOT IN ('Monday', 'Friday') (row-level filter, can go in WHERE or HAVING since it's tied to each purchase group)
• Set B: Customers who bought 'Non-Consumable' products → pc.product_family = 'Non-Consumable' (row-level filter)
• Set C: Customers with a single purchase (per time_id) with total units >10 → SUM(s.unit_sales) > 10 (aggregate, must go in HAVING; ¬C is SUM(s.unit_sales) ≤ 10)
• Set D: Female customers from Canada → c.country = 'Canada' AND c.gender = 'F' (customer-level attribute)

Simplifying Your Set Expression #

Your original expression A ∪ (B ∩ ¬C ∩ ¬(A ∩ ¬(B ∪ D))) can be simplified using basic logical rules (De Morgan's laws) to make your SQL cleaner:

1. ¬(A ∩ ¬(B ∪ D)) simplifies to ¬A ∨ (B ∪ D)
2. Substituting back and simplifying further, the entire expression reduces to A ∪ (B ∩ ¬C)

This means you're looking for customers who either:

• Never shopped on Monday/Friday (Set A), OR
• Bought 'Non-Consumable' products and never had a single purchase with over 10 units (Set B ∩ ¬C)

Corrected SQL Query (Simplified Logic) #

This version uses the simplified logic and fixes the aggregate error:

SELECT DISTINCT c.fname, c.lname
FROM customer AS c
INNER JOIN sales_fact_1997 AS s ON c.customer_id = s.customer_id
INNER JOIN time_by_day AS t ON s.time_id = t.time_id
INNER JOIN product AS p ON s.product_id = p.product_id
INNER JOIN product_class AS pc ON p.product_class_id = pc.product_class_id
GROUP BY c.customer_id, c.fname, c.lname, s.time_id, t.the_day, pc.product_family
HAVING 
  -- Condition A: Not shopping on Monday or Friday
  t.the_day NOT IN ('Monday', 'Friday')
  OR
  -- Condition B ∩ ¬C: Purchased Non-Consumable, and total units per purchase ≤10
  (
    pc.product_family = 'Non-Consumable'
    AND SUM(s.unit_sales) <= 10
  )

Strictly Adhering to Original Set Expression #

If you need to use the exact original expression without simplification, here's the full query:

SELECT DISTINCT c.fname, c.lname
FROM customer AS c
INNER JOIN sales_fact_1997 AS s ON c.customer_id = s.customer_id
INNER JOIN time_by_day AS t ON s.time_id = t.time_id
INNER JOIN product AS p ON s.product_id = p.product_id
INNER JOIN product_class AS pc ON p.product_class_id = pc.product_class_id
GROUP BY c.customer_id, c.fname, c.lname, s.time_id, t.the_day, pc.product_family, c.country, c.gender
HAVING 
  -- Condition A
  t.the_day NOT IN ('Monday', 'Friday')
  OR
  -- Condition B ∩ ¬C ∩ ¬(A ∩ ¬(B∪D))
  (
    pc.product_family = 'Non-Consumable'
    AND SUM(s.unit_sales) <= 10
    AND NOT (
      t.the_day IN ('Monday', 'Friday')
      AND NOT (
        pc.product_family = 'Non-Consumable'
        OR (c.country = 'Canada' AND c.gender = 'F')
      )
    )
  )

Key Notes on the Query #

• GROUP BY includes all non-aggregate columns to avoid ambiguous column errors (required in standard SQL mode)
• DISTINCT ensures each customer only appears once in results, even if they have multiple qualifying purchases
• Aggregate checks like SUM(s.unit_sales) are now correctly placed in the HAVING clause

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