时间戳

首页 > 解决方案 > 获取客户年初至今的支出

sql - 获取客户年初至今的支出

问题描述

我正在尝试获取所有客户的列表以及他们从 1 月到 10 月每个月花了多少钱。

我有一个用户表和一个订单表。Orders 表有一个 user_id 字段，该字段链接到 Users 表的 id，因此一个用户可以有很多订单，也可以没有。

我有这个查询，它将给我订单数量和用户在一月份花了多少钱。我怎样才能改变这个，所以我可以获得每个月的订单数量和总支出的列？此外，对于这个查询，我没有得到订单数为 0 的用户的任何结果。

select users.id, users.first_name, users.last_name, count(orders.id) as num_of_orders, sum(orders.total) as total_spent
from users
left join orders on users.id = orders.user_id
where orders.created_at between '2020-01-01 00:00:00' and '2020-01-31 23:59:00'
group by users.id
order by num_of_orders asc

标签： sqlpostgresql

解决方案

在您的查询之上构建 - 构建一组月份（monthsCTE），orders按订单的月份和usersID 将其加入。按用户和期间分组。我假设这users.id是一个主键，所以你不需要按其他users属性分组。对于没有订单的月份/用户，您将获得零结果。

with month_periods(m) as
(
 select date_trunc('month', s)::date 
 from generate_series('2020-01-01'::timestamp, '2020-10-01', interval '1 month') s
)
select month_periods.m month_period, users.id, users.first_name, users.last_name, 
       count(orders.id) as num_of_orders, sum(orders.total) as total_spent 
from month_periods
left join orders on date_trunc('month', orders.created_at)::date = month_periods.m 
left join users on users.id = orders.user_id
group by users.id, month_periods.m
order by num_of_orders;

推荐阅读