如何按门店统计买家平均购买量？SQL分组无效标识符报错排查

错误原因 #

你的SQL报错「invalid identifier」的核心问题有两个：

1. 子查询中没有选择Store字段，外层查询无法引用t.store；
2. 子查询仅按Buyer_ID分组，但买家的购买行为是和具体门店绑定的，必须同时按Buyer_ID和Store分组，才能准确统计每个买家在对应门店的购买次数。

修正后的SQL #

以下是符合需求的SQL，既不会暴露Buyer_ID，又能正确统计各门店的买家平均购买次数：

SELECT 
    store,
    AVG(purchase_count) AS average_purchases_per_store
FROM (
    SELECT 
        store,
        buyer_id,
        COUNT(DISTINCT purchase_id) AS purchase_count
    FROM your_table_name
    GROUP BY store, buyer_id
) AS buyer_store_purchases
GROUP BY store;

逻辑说明 #

1. 内层子查询：按Store和Buyer_ID分组，统计每个买家在对应门店的有效购买次数（用DISTINCT purchase_id避免重复订单统计）；
2. 外层查询：按Store分组，对每个门店内所有买家的购买次数求平均值，得到目标结果。

百万级数据的性能优化建议 #

针对百万级数据集，建议做以下优化提升查询效率：

• 给Store、Buyer_ID、Purchase_ID建立联合索引：CREATE INDEX idx_store_buyer_purchase ON your_table_name(store, buyer_id, purchase_id);，这样分组和统计时可以直接利用索引，避免全表扫描；
• 如果Purchase_ID本身是唯一的（无重复订单记录），可以去掉DISTINCT，直接用COUNT(purchase_id)，减少计算开销。

验证结果 #

用你提供的样本数据测试，上述SQL会输出：

完全符合你的理想输出要求。

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