如何用单SQL查询获取2018-2019年销售指标？子查询报错求助

需求描述 #

需要通过SQL查询分别获取2018年和2019年的以下指标：

• 总销售额
• 总活跃客户数（当年有交易的客户数量）
• 客户平均消费额（当年每个客户的平均总消费）
• 单笔交易平均消费额（每笔交易的平均消费）

原查询语句存在语法错误，无法正常运行：

select year(transdate) as year,truncate(sum(price * quantity),2) AS totalsales,COUNT(DISTINCT(b.custid)) as activecustomers,

SELECT truncate(AVG(total),2) AS AVERAGE 
FROM (SELECT custid, sum(price*quantity) AS total 
      FROM transaction GROUP BY custid) A
    ;

from transaction 
where transdate >= '2018-01-01' and transdate <= '2019-12-31'
group by YEAR(transdate)

问题分析 #

原查询的核心问题：

1. 语法错误：在SELECT子句中直接嵌套完整SELECT语句不符合SQL语法规范，且未正确关联年份维度
2. 逻辑错误：子查询未按年份分组，会计算所有年份的客户平均消费，无法实现按年统计的需求
3. 别名错误：引用了未定义的表别名b

正确查询语句 #

以下是实现需求的SQL语句（以MySQL为例）：

SELECT
    YEAR(transdate) AS year,
    TRUNCATE(SUM(price * quantity), 2) AS total_sales,
    COUNT(DISTINCT custid) AS active_customers,
    TRUNCATE(AVG(cust_year_total), 2) AS avg_customer_spending,
    TRUNCATE(AVG(price * quantity), 2) AS avg_transaction_spending
FROM (
    SELECT
        transdate,
        custid,
        price,
        quantity,
        -- 计算每个客户当年的总消费
        SUM(price * quantity) OVER (PARTITION BY YEAR(transdate), custid) AS cust_year_total
    FROM transaction
    WHERE transdate BETWEEN '2018-01-01' AND '2019-12-31'
) AS sub_query
GROUP BY YEAR(transdate);

指标说明 #

• total_sales：当年所有交易的总销售额，通过SUM(price * quantity)汇总后保留两位小数
• active_customers：当年有交易的唯一客户数量，使用COUNT(DISTINCT custid)统计
• avg_customer_spending：当年每个客户的平均总消费，先通过窗口函数计算每个客户当年的消费总额，再取平均值
• avg_transaction_spending：单笔交易的平均消费，直接计算每笔交易消费额price * quantity的平均值

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