如何在SQL中使用通过group by聚合的日期“create table as”?

给定一个如下所示的数据,其中日期为字符串格式 YYYYMMDD :

目标是

• 每行最多选择N行 sales_date 具体时间范围
• 使用 group by 在“项目”列中,

e、 g.在“20211002”和“20211004”之间,每天最多3行:

SELECT *
FROM 
    (SELECT item, 
            max(vietnamese) as vietnamese,
            sum(cost) as total_cost,
            array_agg(cost) as costs,
            array_agg(unique_id) as unique_ids,
            row_number() over (partition by max(sales_date) order by rand()) as row
     FROM mytable
     where sales_date between '20211002' and '20211004'
  GROUP BY item)
where row <= 3
limit 9

注: vietnamese 每列 item 是一对一的映射,因此 max(vietnamese)

上述结果应类似于:

所需的输出被保存为 parquet 格式:

目的是把它保存到 s3://somes3path/ ,目录中有一些结构:

s3://somes3path/
     item=fruits/
        sales_date=20211002
        sales_date=20211003
     item=vege/
        sales_date=20211002
        sales_date=20211003
        sales_date=20211004

如何在abvoe所列的目录结构中实现预期的输出?

我尝试过这个方法,但没有像我预期的那样将其保存在正确的目录结构中:

CREATE TABLE somedb.mytable
WITH ( format = 'PARQUET', external_location = 's3://somes3path/', 
       partitioned_by = ARRAY['item'], 
       bucketed_by = ARRAY['sales_date'], bucket_count = 30) AS 
SELECT *
FROM 
    (SELECT item, 
            max(vietnamese) as vietnamese,
            sum(cost) as total_cost,
            array_agg(cost) as costs,
            array_agg(unique_id) as unique_ids,
            first(sales_date) as sales_date,
            row_number() over (partition by max(sales_date) order by rand()) as row
     FROM mytable
     where sales_date between '20211002' and '20211004'
  GROUP BY item)
where row <= 3
limit 9