显示每日订单总额T-SQL

Question

我有一个Orders表，其中包含此信息

id | phone_number | Order_Date_Time
_________________________________________
1    [phone]    2020-09-01 12:30:45
_________________________________________
2    [phone]    2020-09-02 12:31:44
__________________________________________
3    [phone]    2020-09-03 10:10:15
__________________________________________
4    [phone]    2020-09-03 11:10:15

我如何才能有一个查询来返回上周或自定义间隔的每日订单计数。例如，订单计数从2020-09-01到2020-09-03

_____________________________________________________
Phone_number | 2020-09-01 | 2020-09-02 | 2020-09-03
_____________________________________________________
[phone]       1            NULL          2
___________________________________________________
[phone]      NULL           1           NULL

Answer 1

对于固定的日期列表，可以进行条件聚合：

select phone_number,
sum(case when date(order_date_time) = '20200901' then 1 else 0 end) cnt_20200901,
sum(case when date(order_date_time) = '20200902' then 1 else 0 end) cnt_20200902,
sum(case when date(order_date_time) = '20200903' then 1 else 0 end) cnt_20200903
from orders
where order_date_time >= '20200901' and order_date_time < '20200904'
group by phone_number