按营收取TOP3产品分类并转为单行逗号分隔列表的SQL技术问询

现有如下SQL查询：

select date_trunc('day', ordertimestamp) as days 
, product
, revenue
from table

查询结果如下：

days       |   product    | revenue

2022-07-10 |   shoes      |  $ 430
2022-07-10 |   chocolate  |  $ 10
2022-07-10 |   shirts     |  $ 3.25
2022-07-10 |   phones     |  $ 500
2022-01-02 |   vests      |  $ 200
2022-02-03 |   computers  |  $ 700

需要针对2022-07-10当天的数据，按营收降序取TOP3产品分类，合并为单行逗号分隔的格式，预期输出：

2022-07-10 |  phones, shoes, chocolate

尝试了以下SQL，但不确定是否准确：

SELECT
array(select products from dataset order by revenue desc limit 3) as top_three_product_categories_by_revenue
FROM table 

你尝试的SQL存在几处问题：未指定目标日期、引用了不存在的dataset表、字段名错误（原表字段是product而非products），且直接返回数组无法得到逗号分隔的字符串格式。以下是主流数据库的针对性实现：

PostgreSQL 实现 #

利用STRING_AGG聚合函数结合窗口函数筛选TOP3：

SELECT 
  days,
  STRING_AGG(product, ', ' ORDER BY revenue DESC) AS top_products
FROM (
  SELECT 
    days,
    product,
    revenue,
    -- 按日期分组，给营收降序排名
    ROW_NUMBER() OVER (PARTITION BY days ORDER BY revenue DESC) AS rn
  FROM (
    SELECT 
      date_trunc('day', ordertimestamp) AS days,
      product,
      -- 去除$符号并转成数值，确保排序逻辑准确
      CAST(REPLACE(revenue, '$ ', '') AS NUMERIC) AS revenue
    FROM "table"
  ) t
  WHERE days = '2022-07-10'::DATE
) ranked
WHERE rn <= 3
GROUP BY days;

MySQL 实现 #

使用GROUP_CONCAT结合变量实现排名筛选：

SELECT 
  days,
  GROUP_CONCAT(product ORDER BY revenue DESC SEPARATOR ', ') AS top_products
FROM (
  SELECT 
    DATE(ordertimestamp) AS days,
    product,
    CAST(REPLACE(revenue, '$ ', '') AS DECIMAL) AS revenue,
    @rn := IF(@current_day = DATE(ordertimestamp), @rn + 1, 1) AS rn,
    @current_day := DATE(ordertimestamp)
  FROM "table", (SELECT @rn := 0, @current_day := '') vars
  WHERE DATE(ordertimestamp) = '2022-07-10'
  ORDER BY days, revenue DESC
) ranked
WHERE rn <=3
GROUP BY days;

核心注意点 #

• 必须处理revenue字段：原数据中的$ 430是字符串类型，直接排序会出现逻辑错误，需先去除$ 前缀并转换为数值类型。
• 精准筛选目标日期2022-07-10，避免无关数据干扰。
• 通过排名逻辑筛选TOP3产品后，再用字符串聚合函数合并为逗号分隔的结果。

内容的提问来源于stack exchange，提问作者Joshua Smart-Olufemi