我有一些關于專案時間表的記錄，并且想計算每個專案的一個專案階段結束與另一個專案開始之間的差異。我曾嘗試使用 LEAD 函式，但找不到將其用于多個列的方法。
以專案A為例：求phase2和phase 1（phase1的end_date和phase 2的開始日期）和phase3和phase2（phase2的結束日期和phase 3的start_date）之間的差異（延遲）
| 專案名 | 專案階段 | 開始日期 | 結束日期 | 延遲 |
|---|---|---|---|---|
| 專案_A | 階段1 | 20-01-2021 | 23-01-2021 | |
| 專案_A | 階段2 | 27-01-2021 | 2021 年 10 月 2 日 | |
| 專案_A | 第三階段 | 17-02-2021 | 26-02-2021 | |
| 專案_B | 階段1 | 2021 年 2 月 2 日 | 05-03-2021 | |
| 專案_B | 階段2 | 2021 年 12 月 3 日 | 15-04-2021 | |
| 專案_B | 第三階段 | 21-04-2021 | 26-05-2021 |
我試過什么：
select project_name, project_phase,start_date, end_date ,
lead(end_date - start_date) OVER (PARTITION BY project_phase ORDER BY start_date ) as delay
from projects
這給我的是同一project_phase的開始日期和結束日期之間的日期差異。我想要的是階段1的結束日期和階段2的開始日期之間的差異
uj5u.com熱心網友回復：
提供end_date并且start_date將timestamp delay是一個interval.
select project_name, project_phase, start_date, end_date,
end_date - lead(start_date) OVER (PARTITION BY project_name ORDER BY start_date ) as delay
from projects
uj5u.com熱心網友回復：
您可以創建帶有延遲的視圖，并將專案資料與視圖左連接。
create table "project log"
(
"project name" text,
"project phase" text,
"start date" date,
"end date" date
);
insert into "project log"
(
"project name",
"project phase",
"start date",
"end date"
)
values
('project_A', 'phase 1', '2021-01-20', '2021-01-23'),
('project_A', 'phase 2', '2021-01-27', '2021-02-10'),
('project_A', 'phase 3', '2021-02-17', '2021-02-26'),
('project_B', 'phase 1', '2021-02-02', '2021-03-05'),
('project_B', 'phase 2', '2021-03-12', '2021-04-15'),
('project_B', 'phase 3', '2021-04-21', '2021-05-26');
create view "project delay" as
select
"project name",
"project phase",
"start date" -
(select "end date"
from "project log"
where
"project name" = p."project name" and
"project phase" = 'phase 1') "delay"
from
"project log" p
where
"project phase" = 'phase 2'
union
select
"project name",
"project phase",
"start date" -
(select "end date"
from "project log"
where
"project name" = p."project name" and
"project phase" = 'phase 2') "delay"
from
"project log" p
where
"project phase" = 'phase 3';
select *
from
"project log" left join
"project delay" using ("project name", "project phase");
https://www.db-fiddle.com/f/kzowBhxgay6W7H5VafyHvz/0
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415516.html
標籤：
上一篇：如何檢查用戶名是否已存在于表中？
下一篇：交叉表函式結果中出現意外重復