计算一周内员工每天休假的小时数?

Question

所以我有以下表格:

id  approved    end_date             reason         start_date                  submitted    employee_id
1   true    "2022-02-01 22:00:00"   "sickness"  "2022-02-01 20:01:05"   "2022-05-24 10:44:57.385"   8
2   true    "2022-02-03 22:00:00"   "sickness"  "2022-02-02 20:01:05"   "2022-05-24 10:45:06.548"   8
3   true    "2022-05-31 10:00:00"   "family"    "2022-05-24 08:00:00"   "2022-05-24 16:32:40.257"   8
4   true    "2022-05-17 12:00:00"   "family"    "2022-05-17 10:30:00"   "2022-05-24 16:33:36.213"   8
5   true    "2022-05-20 08:00:00"   "family"    "2022-05-19 08:00:00"   "2022-05-24 16:34:16.09"    8

我想达到的目标是:

我想检索一个雇员在那个星期所取的叶子，并按天过滤它们:

例如，如果我想检索employee_id: 8在[05/23,05/29]周内的叶子(按小时)，我想要的输出将是这样的:

monday | tuesday | wednesday | thursday | friday |saturday |sunday
0           8          8         8         8         0        0      

和下周[05/30,05/05]:

monday | tuesday | wednesday | thursday | friday |saturday |sunday
8          2          0           0         0        0        0 

8因为一天工作= 8小时

我知道这可能看起来像我试图让别人为我做我的工作，但这个操作是如此复杂，我有点卡在我应该如何处理这个问题。我希望你能给我一些指导

Answer 1

创建手动/静态枢轴是我尝试解决这个问题的方法…

https://www.db-fiddle.com/f/5jCTUUaEUoB9HthPrUu4RX/0

基本上，您为给定的行创建一个计算列，在本例中，它是从提交的列中提取星期几，然后计算ID列的值。

select 

count((CASE WHEN extract(isodow from s.submitted) = 1 THEN id END)) AS "Monday",
count((CASE WHEN extract(isodow from s.submitted) = 2 THEN id END)) AS "Tuesday",
count((CASE WHEN extract(isodow from s.submitted) = 3 THEN id END)) AS "Wednesday",
count((CASE WHEN extract(isodow from s.submitted) = 4 THEN id END)) AS "Thursday",
count((CASE WHEN extract(isodow from s.submitted) = 5 THEN id END)) AS "Friday",
count((CASE WHEN extract(isodow from s.submitted) = 6 THEN id END)) AS "Saturday",
count((CASE WHEN extract(isodow from s.submitted) = 7 THEN id END)) AS "Sunday"
from stack s
Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
0       5       0           0           0       0           0