我的 OrderHistory 表中有一個pickupDate 和 returnDate。我想提取所有 OrderHistory 條目的租賃天數總和，按月分組/排序。cte 似乎是解決方案，但我不知道如何在我的查詢中實作它，因為 cte′si 看到的是在它說“FROM 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 月 3 日），而是將整個 7 天分配到 3 月，因為回傳日期在其中。它應該顯示 3 月的 4 天和 4 月的 3 天。
抱歉這個可能非常愚蠢的問題，但我是初學者。（我的代碼是用 postgresql 寫的）
uj5u.com熱心網友回復：
PostgreSQL 命名約定
PostgreSQL 列名是否區分大小寫？
僅使用合法的小寫名稱，因此不需要雙引號。
db fiddle中的最終結果
添加日期范圍列。
alter table order_history add column date_ranges daterange;
更新 order_history
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;
uj5u.com熱心網友回復：
在嘗試了不同的事情之后，我想我找到了我想與社區分享的問題的最佳答案：
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;
我認為這是我想出的最簡單的解決方案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/456818.html
標籤：PostgreSQL 日期 时间 提炼 日期范围