时间戳

首页 > 解决方案 > 用于计算月份列中时间百分比的 SQL 查询

sql - 用于计算月份列中时间百分比的 SQL 查询

问题描述

我正在尝试计算一个人每月在团队中的时间百分比。

您如何计算 1 月 19 日、2 月 19 日、3 月 19 日等的列，以根据他们的开始和结束日期显示每个人在团队中的时间百分比？

现在我有这些数据：

Team Name   Res_Name         Res_Start_Date   Res_End_Date
Swoosh      Bob Jones         1/1/2019  
Swoosh      Mary Johnson      5/5/2019  
Marvel      Bill Bobberson    10/29/2019      12/31/2019
Marvel      David James       4/4/2019        4/28/2019
Marvel      John Smith        5/1/2019        10/1/2019

我希望新列的结构如下： 列格式

Team Name   Res_Name         Res_Start_Date   Res_End_Date   Jan-19     Feb-19      Mar-19  etc..

Swoosh      Bob Jones         1/1/2019  
Swoosh      Mary Johnson      5/5/2019  
Marvel      Bill Bobberson    10/29/2019      12/31/2019
Marvel      David James       4/4/2019        4/28/2019
Marvel      John Smith        5/1/2019        10/1/2019

并期望该数据的计算列的输出为： 预期输出

标签： sqloraclepivot

解决方案

这是一种接近您期望的方法。

不同的是年份不在列名中。

这样，明年就不必修改相同的查询。

这个怎么运作？

首先在 CTE 中生成月份的开始和结束日期。

然后将它们连接到范围上的表中。
计算每个资源每月的百分比。

然后该结果被旋转。

CREATE TABLE YourTable 
(
  Team_Name varchar2(30),
  Res_Name varchar2(30),
  Res_Start_Date date,
  Res_End_Date date
);

INSERT INTO YourTable (Team_Name, Res_Name, Res_Start_Date, Res_End_Date)
      SELECT 'Swoosh', 'Bob Jones', '1-JAN-2019', null FROM DUAL
UNION SELECT 'Swoosh', 'Mary Johnson', '5-MAY-2019', null FROM DUAL
UNION SELECT 'Marvel', 'Bill Bobberson', '29-OCT-2019', '31-DEC-2019' FROM DUAL
UNION SELECT 'Marvel', 'David James', '4-APR-2019', '28-APR-2019' FROM DUAL
UNION SELECT 'Marvel', 'John Smith', '1-MAY-2019', '1-OCT-2019' FROM DUAL

WITH MONTHS AS
(
  SELECT
  ADD_MONTHS(CAST('01-JAN-'||(EXTRACT(year FROM SYSDATE)-1) AS DATE), ROWNUM-1) AS month_start,
  LAST_DAY(ADD_MONTHS(CAST('01-JAN-'||(EXTRACT(year FROM SYSDATE)-1) AS DATE), ROWNUM-1)) AS month_end
  FROM DUAL
  CONNECT BY ROWNUM <= 12
)
, PERCENTS AS
(
 SELECT t.*
 , EXTRACT(year from m.month_start) as "year"
 , TO_CHAR(m.month_start,'MON') as "month"
 , ROUND(100*((case when t.Res_End_Date < m.month_end then t.Res_End_Date else m.month_end end)
  - (case when t.Res_Start_Date > m.month_start then t.Res_Start_Date else m.month_start end)
  + 1)/(m.month_end-m.month_start+1)) as perc
 FROM MONTHS m
 JOIN YourTable t
   ON t.Res_Start_Date <= m.month_end
  AND (t.Res_End_Date IS NULL OR t.Res_End_Date >= m.month_start)
)
SELECT *
FROM PERCENTS
PIVOT (SUM(perc) FOR "month" IN ('JAN' JAN,'FEB' FEB,'MAR' MAR,'APR' APR,'MAY' MAY,'JUN' JUN,'JUL' JUL,'AUG' AUG,'SEP' SEP,'OCT' OCT,'NOV' NOV,'DEC' DEC)) pvt
ORDER BY Team_Name DESC, Res_Name ASC

TEAM_NAME | RES_NAME | RES_START_DATE | RES_END_DATE | 年份 | 一月 | 二月 | 三月 | 四月 | 五月 | 六月 | 七月 | 八月 | 九月 | 华侨城 | 十一月 | 十二月
:-------- | :------------- | :------------- | :----------- | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---:
旋风 | 鲍勃·琼斯 | 19 年 1 月 1 日 | 空         | 2019 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
旋风 | 玛丽·约翰逊 | 19 年 5 月 5 日 | 空         | 2019 | 空| 空| 空| 空| 87 | 100 | 100 | 100 | 100 | 100 | 100 | 100
漫威 | 比尔博伯森 | 19 年 10 月 29 日 | 19 年 12 月 31 日 | 2019 | 空| 空| 空| 空| 空| 空| 空| 空| 空| 10 | 100 | 100
漫威 | 大卫詹姆斯 | 19 年 4 月 4 日 | 19 年 4 月 28 日 | 2019 | 空| 空| 空| 83 | 空| 空| 空| 空| 空| 空| 空| null
奇迹 | 约翰·史密斯 | 19 年 5 月 1 日 | 19 年 10 月 1 日 | 2019 | 空| 空| 空| 空| 100 | 100 | 100 | 100 | 100 | 3 | 空| 无效的

db<>在这里摆弄

推荐阅读