Best Way to Query over time window in Postgresql-CodePudding

I have a table with records of sales from 2021-01-01 to 2021-09-27, like this:

Order_Date     Order_number
2021-09-27        8989
.
.
.
2021-01-01        56565

I want to query the count of orders in the last 1day, 2days, 5 days, 30days etc...

I know how to use the where date(Order_date) >= (cast(current_date as date) - interval '1' day

but can I query over these time intervals in the same query?

CodePudding user response：

Use FILTER.

SELECT 
    COUNT(*) FILTER (WHERE date(Order_date) >= (cast(current_date as date) - interval '1' day)) as day1,
    COUNT(*) FILTER (WHERE date(Order_date) >= (cast(current_date as date) - interval '2' day)) as day2,
    COUNT(*) FILTER (WHERE date(Order_date) >= (cast(current_date as date) - interval '5' day)) as day5
FROM whatever