row_number()函数按组和顺序为每行分配数字。
rn<3，每个日期仅获得3个顶级产品。

with cte as (
  select days, product, row_number() over (partition by days order by revenue ASC) as rn
  from mytable
)
select days, string_agg(product, ', ' ORDER BY rn DESC) as top_products
from cte
where rn <= 3
group by days

Demo here

这段代码可以在DuckDB上运行，但也可以很容易地移植到其他数据库。

with cte_sequence as (
    select * from generate_series(1,100) as id
),
cte_base_data as (
    select
        id,
        timezone('utc', now()) - floor(random()*id*10000)::int) as order_datetime,
        'product' || floor(3*random())::int::text as product,
        random()*100 as revenue
    from
        cte_sequence
),
cte_top_products as (
    select
        date_trunc('day', order_datetime) as order_date,
        product,
        revenue,
        row_number() over(partition by order_date order by revenue desc) as row_id
    from
        cte_base_data
)
select
    order_date,
    string_agg(distinct product, ', ') as top_products_by_revenue
from
    cte_top_products
where
    row_id <= 3
group by 1
order by 1

正如其他人在此期间发布的那样：
ROW_NUMBER()，按日期列划分ORDER BY revenue DESC，然后按结果行数小于4进行过滤，按日期和product的STRING_AGG()进行分组。

marco ~/1/Vertica/supp $ psql -af top3list.sql
WITH
indata(dt,product,revenue) AS (
          SELECT DATE '2022-07-10','shoes'    ,430
UNION ALL SELECT DATE '2022-07-10','chocolate',10
UNION ALL SELECT DATE '2022-07-10','shirts'   ,3.25
UNION ALL SELECT DATE '2022-07-10','phones'   ,500
UNION ALL SELECT DATE '2022-01-02','vests'    ,200
UNION ALL SELECT DATE '2022-02-03','computers',700
)
,
w_rn AS (
  SELECT
    *
  , ROW_NUMBER() OVER w AS rn
  FROM indata
  WINDOW w AS (PARTITION BY dt ORDER BY revenue DESC)
)
SELECT
  dt
, STRING_AGG(product,',' ORDER BY revenue DESC) AS top3
FROM w_rn
WHERE rn < 4
GROUP BY dt
;
     dt     |          top3          
------------+------------------------
 2022-01-02 | vests
 2022-02-03 | computers
 2022-07-10 | phones,shoes,chocolate