在postgresql中对日期列执行某些操作
我有一个包含以下列的表：
不带时区的personnel_id INT，date DATE，time TIME。
每个人每天都有一次打卡和打卡。也可以有多个打卡和打卡退出。例如，一个人可能8:00来，13:00离开，16:36回来，19:20打卡，最后离开。
因此，对于每个日期，我需要计算一个人在工作中的总小时数，并由此计算该人每个月的工作小时数。因此，我需要一个select，它得到一个personnel_id，并返回该人在每个月的工作时间。对于ex：
ID 1 2 3 4 5 6 7 8 9 10 11 12
3 173.24 134 147.26 180 50.47 138 196.36 47 93.56 .56 78 139回答 1
Stack Overflow用户
发布于 2019-01-09 16:12:36
您首先需要计算每个打卡/打卡组合的持续时间。
假设对于每个personnel_id的每个日期，您总是有偶数个条目，您可以使用以下内容来计算每对日期的持续时间：
select personnel_id,
"date",
case
when row_number() over w % 2 = 0 then "time" - lag("time") over w
end as duration
from person_work
window w as (partition by personnel_id, "date" order by "time")row_number()是一个为每一行分配一个编号的window function。lag()是另一个窗口函数，它从前一行获取列的值。因为这两个函数共享相同的“窗口定义”，所以我只在最后用window子句声明了一次。CASE表达式每隔一行计算time列的差值。打卡行的行号为奇数，出卡行的行号为偶数。% 2检查偶数行号。
在下一步中，我们需要将这些对聚合到每月的持续时间中。这可以通过在前一个查询的基础上进行构建来完成。我使用common table expression重用前面的查询：
with hours as (
select personnel_id,
"date",
case
when row_number() over w % 2 = 0 then
-- this converts the interval into a decimal value
extract(epoch from "time" - lag("time") over w)/3600
end as hours
from person_work
window w as (partition by personnel_id, "date" order by "time")
), hours_per_month as (
select personnel_id,
extract(year from "date")::int as work_year,
extract(month from "date")::int as work_month,
sum(hours) work_hours
from hours
where hours is not null
group by personnel_id, work_year, work_month
)
select *
from hours_per_month;extract(year from ...)以十进制值的形式返回date列的年份。::int是一个简单地将其转换为整数的type cast。严格地说，这并不是真正必要的。
extract(epoch from ..)以秒为单位返回interval的持续时间。将结果除以3600将返回以小时为单位的间隔。
这将返回类似如下的内容：
personnel_id | work_year | work_month | work_hours
-------------+-----------+------------+-----------
1 | 2018 | 1 | 25.33
1 | 2018 | 2 | 17.08
1 | 2018 | 3 | 8.25然后在最后一步中，我们需要将行转换为列。这可以通过使用filter子句的条件聚合来完成：
with hours as (
select personnel_id,
"date",
case
when row_number() over w % 2 = 0 then extract(epoch from "time" - lag("time") over w)/3600
end as hours
from person_work
window w as (partition by personnel_id, "date" order by "time")
), hours_per_month as (
select personnel_id,
extract(year from "date")::int as work_year,
extract(month from "date")::int as work_month,
sum(hours) hours
from hours
where hours is not null
group by personnel_id, work_year, work_month
)
select personnel_id,
work_year,
sum(hours) filter (where work_month = 1) as hours_jan,
sum(hours) filter (where work_month = 2) as hours_feb,
sum(hours) filter (where work_month = 3) as hours_mar,
sum(hours) filter (where work_month = 4) as hours_apr,
sum(hours) filter (where work_month = 5) as hours_may,
sum(hours) filter (where work_month = 6) as hours_jun,
sum(hours) filter (where work_month = 7) as hours_Jul,
sum(hours) filter (where work_month = 8) as hours_aug,
sum(hours) filter (where work_month = 9) as hours_sep,
sum(hours) filter (where work_month = 10) as hours_oct,
sum(hours) filter (where work_month = 11) as hours_nov,
sum(hours) filter (where work_month = 12) as hours_dec
from hours_per_month
group by personnel_id, work_year;这将返回如下内容：
personnel_id | work_year | hours_jan | hours_feb | hours_mar | hours_apr | hours_may | hours_jun | hours_jul | hours_aug | hours_sep | hours_oct | hours_nov | hours_dec
-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------
1 | 2018 | 25.33 | 17.08 | 8.25 | ... | ... | ... | ... | ... | .... | .... | ... | .... 如果只需要单个年份的报告，则可以在最终选择中使用where work_year = ...，并从选择列表和group by中删除该列
https://stackoverflow.com/questions/54104883
复制相似问题
腾讯云开发者
