以下为数据模型sql，此sql基本算是一个通用的公式，往里面套用即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
drop table if exists eventLog_dimission;

drop table if exists WORKFLOW_NODENAME_DIMISSION;

drop table if exists eventDetail_dimission;

drop table if exists eventDetail_dimission_OPERATEDATETIME;

-- drop table if exists caseTable_depart;

create table eventLog_dimission (
        caseID          int
    ,   eventID         float
    ,   caseType        string
    ,   activity        string 
    ,   receiveTime       TIMESTAMP
    ,   operateTime       TIMESTAMP
    ,   viewTime       TIMESTAMP
    ,   firstviewTime       TIMESTAMP
    ,   user            int
    ,   sorting         int
);

create table WORKFLOW_NODENAME_DIMISSION (
        NODEID          int
    ,   NODENAME         string
    ,   sorting         int
);

-- 这里我把WORKFLOWID = 1187026的所有WORKFLOW_CURRENTOPERATOR放到eventDetail_dimission中，WORKFLOW_CURRENTOPERATOR数据过于庞大，不要直接用

create table eventDetail_dimission
select *
from WORKFLOW_CURRENTOPERATOR where WORKFLOWID in (1187026);

-- OPERATEDATETIME 操作时间

-- RECEIVEDATETIME 接收时间

-- select OPERATEDATETIME,RECEIVEDATETIME from eventDetail_dimission where NODEID = 1216645; 

-- select OPERATEDATETIME,OPERATEDATE,OPERATETIME from eventDetail_dimission where NODEID = 1216645; 

-- select RECEIVEDATETIME,RECEIVETIME,RECEIVEDATE from eventDetail_dimission where NODEID = 1216645;

insert into WORKFLOW_NODENAME_DIMISSION
values
(1216645, '流程管理工程师', 100),
(1216651, '国内营销部GPO', 100),
(1216647, '总经理意见', 100),
(1216653, '评审代表/会签人', 100),
(1217583, '知会知会人', 100),
(1216652, '上级流程Owner', 100),
(1216655, 'IT工程师认领/应用工程师', 100),
(1216650, '归档', 110),
(1216644, '申请人', 1),
(1216646, '部门负责人意见', 100),
(1216648, 'IT工程师维护', 100),
(1216654, '一级流程Owner/项目经理', 100),
(1216649, 'IT测试人员/流程管理工程师', 100);

insert into eventLog_dimission
SELECT DISTINCT
        T1.REQUESTID           as  caseID
    ,   T1.ID           as  eventID
    ,   NULL         as  caseType
    ,   T3.NODENAME        as  activity      
    ,   TIMESTAMP(CONCAT(RECEIVEDATE, ' ', RECEIVETIME))     as  receiveTime  
    ,   TIMESTAMP(CONCAT(OPERATEDATE, ' ', OPERATETIME))     as  operateTime  
    ,   TIMESTAMP(CONCAT(VIEWDATE, ' ', VIEWTIME))     as  viewTime  
    ,   TIMESTAMP(CONCAT(FIRSTVIEWDATE, ' ', FIRSTVIEWTIME))     as  firstviewTime 
    ,   T1.processuser     as  user 
    ,   T3.sorting                  as  sorting
FROM WORKFLOW_CURRENTOPERATOR T1 
JOIN WORKFLOW_NODEBASE T2 ON T1.NODEID = T2.ID
JOIN WORKFLOW_NODENAME_DIMISSION T3 ON T1.NODEID = T3.NODEID
WHERE T1.WORKFLOWID = 1187026;

-- select REQUESTID,OPERATEDATETIME from eventDetail_dimission where NODEID = 1216652 AND REQUESTID = 2429509;

-- select REQUESTID,OPERATEDATETIME from eventDetail_dimission where NODEID = 1216654 AND REQUESTID = 2429509;

-- 此处为方便时间计算创建，一般不用

create table eventDetail_dimission_OPERATEDATETIME
select 
    T1.ID as ID,
    T1.REQUESTID AS REQUESTID,
    T1.OPERATEDATETIME as OPERATEDATETIME_1216652,
    T2.OPERATEDATETIME as OPERATEDATETIME_1216654
from eventDetail_dimission T1 JOIN eventDetail_dimission T2
on T1.REQUESTID = T2.REQUESTID
where T1.NODEID = 1216652 and T2.NODEID = 1216654;

以下为PQL示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 内容审核效率
ROUND(
	AVG(
		TIMESTAMPDIFF(
			SECOND,
			( CASE WHEN `eventDetail_dimission`.`NODEID` = 1216645 THEN `eventDetail_dimission`.`RECEIVEDATETIME` END ),
			( CASE WHEN `eventDetail_dimission`.`NODEID` = 1216645 THEN `eventDetail_dimission`.`OPERATEDATETIME` END ) 
		) 
	) / 3600,
2 
)


-- IT配置时长
ROUND(
	AVG(
		TIMESTAMPDIFF(
			SECOND,
			( CASE WHEN `eventDetail_dimission`.`NODEID` = 1216648 THEN `eventDetail_dimission`.`RECEIVEDATETIME` END ),
			( CASE WHEN `eventDetail_dimission`.`NODEID` = 1216648 THEN `eventDetail_dimission`.`OPERATEDATETIME` END ) 
		) 
	) / 3600,
2 
)


-- 内容审核通过率
ROUND(
    (
        COUNT(DISTINCT CASE WHEN `eventDetail_dimission`.`NODEID` = 1216644 AND `eventDetail_dimission`.`WORKFLOWID` = 1187026 AND `eventDetail_dimission`.`ISBEREJECT` = 1 THEN `eventDetail_dimission`.`REQUESTID` END)
        / COUNT(DISTINCT `eventDetail_dimission`.`REQUESTID`)
    ) * 100, 2
)



-- IT配置返工率
ROUND(
    (
        COUNT(DISTINCT CASE WHEN `eventDetail_dimission`.`NODEID` = 1216648 AND `eventDetail_dimission`.`WORKFLOWID` = 1187026 AND `eventDetail_dimission`.`ISBEREJECT` = 1 THEN `eventDetail_dimission`.`REQUESTID` END)
        / COUNT(DISTINCT `eventDetail_dimission`.`REQUESTID`)
    ) * 100, 2
)