sql - Redshift - 按周对数据进行排序

Question

我正在尝试按周对 Redshift 中的一些数据进行排序。

select CONCAT(CONCAT('Week', ' '), 
EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)) as date,count(*) as total 
from sales 
where sale_date between ('2019-03-22' - 30) and '2019-03-22' 
group by EXTRACT(WEEK FROM  sale_date::date + '1 day'::interval)
order by 1 asc;

上面的查询工作正常，但输出如下：

Week 10, 10
Week 11, 20
Week 12, 3
Week 7, 12
Week 8, 3
Week 9, 5

我如何修改上述查询，使输出如下：

Week 7, 12
Week 8, 3
Week 9, 5
Week 10, 10
Week 11, 20
Week 12, 3

标签： sqlsql-order-byamazon-redshift

Answer 1

您需要将提取的周值转换为numeric typefororder by语句。所以转换

order by 1 asc 至

order by cast(EXTRACT(WEEK FROM sale_date::date + '1 day'::interval) as integer)