SQL聚合函数执行顺序问题：SQLite统计用户首尾购买日销量错误原因

错误根因 #

你写的SQL逻辑不符合SQL执行顺序规则，核心问题有两个：

• 聚合计算的执行优先级高于HAVING筛选：sum(units_sold)会先把对应客户所有购买记录的销量全部累加，你的样例数据里就是1+1+3=5，HAVING子句仅用来判断「当前客户分组要不要返回」，不会过滤掉首次、末次购买日之外的记录，也不会仅对符合条件的行做求和。
• GROUP BY customer_id之后，每个客户仅对应一行结果，你在HAVING里直接引用非分组、非聚合的purchase_date字段，属于SQLite的特殊兼容逻辑，取到的是分组内任意一行的purchase_date值，这个判断逻辑本身就不可靠。

正确实现方式 #

需要先筛选出每个客户首次、末次购买日对应的行，再对这些行做聚合求和，两种常用写法如下：

子查询关联写法（兼容所有SQLite版本） #

SELECT 
  s.customer_id,
  SUM(s.units_sold) AS total_units_sold
FROM sales s
INNER JOIN (
  SELECT 
    customer_id,
    MIN(purchase_date) AS first_purchase,
    MAX(purchase_date) AS last_purchase
  FROM sales
  GROUP BY customer_id
) t ON s.customer_id = t.customer_id
WHERE s.purchase_date = t.first_purchase OR s.purchase_date = t.last_purchase
GROUP BY s.customer_id;

窗口函数写法（SQLite 3.25及以上版本支持） #

WITH sales_with_flag AS (
  SELECT 
    customer_id,
    units_sold,
    purchase_date = MIN(purchase_date) OVER(PARTITION BY customer_id) AS is_first_purchase,
    purchase_date = MAX(purchase_date) OVER(PARTITION BY customer_id) AS is_last_purchase
  FROM sales
)
SELECT 
  customer_id,
  SUM(units_sold) AS total_units_sold
FROM sales_with_flag
WHERE is_first_purchase = 1 OR is_last_purchase = 1
GROUP BY customer_id;

以上两种写法返回的total_units_sold都是你预期的4。

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