前言
- 在日常数分工作中,可能会遇到这样一种场景:在一定时间段内,各个操作对象存在多个进程同时开始和结束,且每个进程具有明确的起止时间(结束时间始终晚于开始时间)。针对同一操作对象,多个进程可能同时开始(即开始时间相同),但结束时间各不相同;反之亦然,不同的进程可能在不同时间开始,却恰好在同一时刻结束。然而,请注意,同一操作对象任何两个进程都不会出现既同时开始又同时结束的现象。在同一时间段内,不同操作对象间、同一对象的不同进程间以及各进程自身均不存在相互影响或有关联。尽管对于每个操作对象,其进程中可能存在未知数量的进程数,但可以确定的是进程总数与操作对象总数均有限。
- 现在,目标是计算出每个独立操作对象在其所有有限进程中的实际占用时间段。下面分别是采用Python和Oracle两种方式来解决这个问题,并给出具体的数据结构和算法实现。
使用Python方式实现
- 首先,以Python实现方案为例,参照下图所示的数据源结构,该数据源自本地文件中某个操作对象的部分进程记录。值得注意的是,每一条进程记录均确保具备唯一且对应的开始与结束时间。
- 在代码实现中,数据处理流程如下:首先从本地文件中读取所需的数据,经过程序的计算与处理后,最终得到的结果数据也将被保存,一方面以.xlsx格式文件的形式存储回本地文件系统,另一方面也确保了本地文件中存有这一经由程序完整运行而产生的新数据集。在代码中,此本地文件的路径被指定为file_path变量,接下来呈现具体的实现代码如下。
import openpyxl;
import pandas as pd;
from datetime import datetime;
file_path = r"C:\Users\Desktop\test_time.xlsx";
workbook = openpyxl.load_workbook(file_path);
sheet = workbook.active;
time_data = [];
for row in sheet.iter_rows(values_only=True):
time_data.append([row[0], str(row[1]), str(row[2])]);
all_time_ranges = [];
for name, start_str, end_str in time_data:
start_time = datetime.strptime(start_str, "%Y-%m-%d %H:%M:%S");
end_time = datetime.strptime(end_str, "%Y-%m-%d %H:%M:%S");
all_time_ranges.append((start_time, end_time));
all=sorted(all_time_ranges);
merged_time_ranges = [];
for start, end in all:
if not merged_time_ranges or start > merged_time_ranges[-1][1]:
merged_time_ranges.append((start, end));
else:
merged_time_ranges[-1] = (merged_time_ranges[-1][0], max(merged_time_ranges[-1][1], end));
for start, end in merged_time_ranges:
print("begin_time:",start.strftime('%Y-%m-%d %H:%M:%S'));
print("over_time:",end.strftime('%Y-%m-%d %H:%M:%S'));
print(f"begin_time:{start.strftime('%Y-%m-%d %H:%M:%S')}, over_time:{end.strftime('%Y-%m-%d %H:%M:%S')}");
data = {
"begin_time": [start.strftime('%Y-%m-%d %H:%M:%S') for start, _ in merged_time_ranges],
"over_time": [end.strftime('%Y-%m-%d %H:%M:%S') for _, end in merged_time_ranges]
};
df = pd.DataFrame(data);
df.to_excel(r"C:\Users\Desktop\result_time.xlsx", index=False);
print("Successful!");
使用Oracle方式实现
- 当数据源位于底层Oracle数据库中,无法便捷地导出本地处理,或者需要实时监测并即时分析时,采用Oracle方式实现尤为适宜。具体而言,此方案适用于直接访问数仓中存储的特定数据集,在此处仅展示逻辑层面的实现方法,以便在实际需求下能够实时查询和计算每个操作对象在其所有进程中的实际占用时间段。
with test_time as
(
select serial_id, agent_id, trunc(to_date(initiate_time, 'yyyy-mm-dd hh24:mi:ss')) as start_t,
to_date(initiate_time, 'yyyy-mm-dd hh24:mi:ss') as start_dt,
to_date(terminate_time, 'yyyy-mm-dd hh24:mi:ss') as over_dt
from work_test.cc_time_table
where trunc(to_date(initiate_time, 'yyyy-mm-dd hh24:mi:ss')) >= trunc(sysdate - 31)
and trunc(to_date(initiate_time, 'yyyy-mm-dd hh24:mi:ss')) <= trunc(sysdate - 1)
),
test_initiate_time as
(
select c.*, row_number() over(partition by c.start_t, c.agent_id order by c.start_dt) as rank
from (
select distinct a.agent_id, a,start_t, a.start_dt
from test_time a
left join test_time b
on a.agent_id = b.agent_id and a.start_t = b.start_t and a.start_dt > b.start_dt
and a.start_dt <= b.over_dt
where b.serial_id is null
) c
),
test_terminate_time as
(
select z.*, row_number() over(partition by z.start_t, z.agent_id order by z.over_dt) as rank
from (
select distinct x.agent_id, x.start_t, x.over_dt
from test_time x
left join test_time y
on x.agent_id = y.agent_id and x.start_t = y.start_t and x.over_dt >= y.start_dt
and x.over_dt < y.over_dt
where y.serial_id is null
) z
),
test_new_time as
(
select init.start_t, init.agent_id, as agent_acct, init.start_dt, term.over_dt,
ceil((to_char(init.start_dt, 'hh24')*3600 + to_char(init.start_dt, 'mi')*60 + to_char(init.start_dt, 'ss'))/900 )
as initiate_time_number,
ceil((to_char(term.over_dt, 'hh24')*3600 + to_char(term.over_dt, 'mi')*60 + to_char(term.over_dt, 'ss'))/900 )
as terminate_time_number
from test_inititate_time init
left join test_terminate_time term
on init.agent_id = term.agent_id and init,start_dt = term.start_dt and init.rank = term.rank
)
select * from test_new_time