SQL中CONCAT生成别名后统计distinct去重计数的问题及实现

问题背景 #

我正在练习SQL编写，想做一个拓展案例：使用CONCAT生成字段别名后，统计该别名的去重计数，但一直报错找不到正确写法，初始代码如下：

SELECT 
concat(product_code, product_color) as new_product_code
count (distinct new_product_code)
 FROM `hands-on-project-automobile.customer_data.customer_purchase` 
 where 
 product = 'couch'
 order by new_product_code desc

第一次更新 #

经过调整后我写出了第一个可运行的子查询版本，但缺少每个new_product_code对应的统计数量列，无法输出类似「灰色沙发2个、白色沙发3个」的结果，修改后代码如下：

SELECT 
   distinct new_product_code
   
FROM 
    (SELECT 
         concat(product_code, product_color) as new_product_code
        FROM `hands-on-project-automobile.customer_data.customer_purchase` 
        where 
        product = 'couch'
        order by new_product_code desc
    )

第二次更新 #

最终调试完成的可运行代码如下：

SELECT 
   distinct new_product_code,
   count(*) 
   
FROM 
    (SELECT 
         concat(product_code, product_color) as new_product_code
        FROM `hands-on-project-automobile.customer_data.customer_purchase` 
        where 
        product = 'couch'
        order by new_product_code desc
    )
    group by new_product_code 

问题解析与优化方案 #

1. 初始代码报错原因：
   • 两个查询字段之间缺少逗号分隔，属于基础语法错误
   • SQL执行逻辑中，SELECT子句中定义的别名无法在同层级的SELECT中直接引用，别名的生效时机晚于同层字段计算
2. 代码优化方案：
   不需要嵌套子查询即可实现需求，GROUP BY可以直接引用SELECT中定义的别名，且分组后自动去重，无需额外加DISTINCT关键字，简化后代码如下：

SELECT
  CONCAT(product_code, product_color) AS new_product_code,
  COUNT(*) AS product_count
FROM `hands-on-project-automobile.customer_data.customer_purchase`
WHERE product = 'couch'
GROUP BY new_product_code
ORDER BY new_product_code DESC

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