提取按月份postresql分组的日期范围的天数

Question

我的OrderHistory表中有一个pickupDate和returnDate。我想提取所有OrderHistory条目的租赁天数总和，按月份分组/排序。cte似乎是解决方案，但我不知道如何在我的查询中实现它，因为我看到的cte是指它们自己，它说"；来自cte"；。

我试过这样的东西：

SELECT
SUM((EXTRACT (DAY FROM("OrderHistory"."returnDate")-("OrderHistory"."pickupDate")))) as traveltime
, to_char("OrderHistory"."pickupDate"::date, 'YYYY-MM') as M  

FROM
"OrderHistory" 
GROUP BY 
M

ORDER BY 
M

但结果并没有将预订分成两个月(例如，pickupDate=2022年3月27日，returnDate=2022年4月03日(，而是将整个7天分配给3月份，因为返回日期在其中。它应该显示3月的4天和4月的3天。

抱歉问了这个可能很愚蠢的问题，但我是个初学者。(我的代码是用postgresql-btw编写的(

Answer 1

PostgreSQL命名约定

PostgreSQL列名区分大小写吗？

只使用合法的小写名称，因此不会使用双引号需要。

db fiddle 中的最终结果

添加日期范围列。alter table order_history add column date_ranges daterange;更新订单_历史

with a(m_begin,  m_end, pickup_date) as
(select date_trunc('month', pickup_date)::date,
(date_trunc('month', pickup_date) + interval '1 month - 1 day')::date,
pickup_date from order_history)
update order_history set date_ranges =
daterange(a.m_begin, a.m_end,'[]') from a 
where a.pickup_date = order_history.pickup_date;

然后最后查询：

WITH A AS(
select
pickup_date,
return_date,
return_date - pickup_date as  total,
case when return_date <@ date_ranges then (return_date  - pickup_date)
else ( date_trunc('month', pickup_date) + interval '1 month - 1 day')::date - pickup_date
end partial_mth
from order_history),
b as (SELECT *, a.total - partial_mth parital_not_mth FROM a)
select *,
case when to_char(pickup_date,'YYYY-MM') = to_char(return_date,'YYYY-MM')
then
sum(partial_mth) over(partition by to_char(pickup_date,'YYYY-MM')) +
sum(parital_not_mth) over (partition by to_char(return_date,'YYYY-MM'))
else sum(partial_mth) over(partition by to_char(pickup_date,'YYYY-MM'))
end
from b;

Answer 2

在尝试了不同的方法后，我认为我找到了我的问题的最佳答案，我想与社区分享：

WITH hier as (
SELECT
"OrderHistory"."pickupDate" as start_date
, "OrderHistory"."returnDate" as end_date                 
, to_char("OrderHistory"."pickupDate"::date, 'YYYY-MM') as M


FROM
"OrderHistory" 

GROUP BY 
1, 2, 3


ORDER BY 
3
), calendar as (
select date '2022-01-01' + (n || ' days')::interval calendar_date
from generate_series(0, 365) n
)

select
to_char(calendar_date::date, 'YYYY-MM')
, count(*) as tage_gebucht

from calendar
inner join hier on calendar.calendar_date between start_date and end_date
where calendar_date between '2022-01-01' and '2022-12-31'

group by 1
order by 1;

我认为这是我想出的最简单的解决方案。