泛微流程效率相关报表与操作统计
为什么要统计操作次数？
操作次数是衡量流程顺畅度更敏感的指标。当统计【产生过特定操作的流程数量】
是为了衡量问题的影响广度。这个维度可以反映 “有多少流程出现了异常？” ，识别出流程体系中共性问题。 例如，如果发现超过30%的流程都存在“退回”操作，说明流程填写规范或制度培训可能存在问题。
当统计【特定操作行为的发生总次数】
是为了衡量问题的严重深度与发生频率。这个维度可以反映 “流程异常发生的频率有多高？问题的复杂程度如何？” 例如：同一流程多次退回，可能是不同环节或不同性质的问题，如第一次退回是“信息填写不全”，第二次是“附件缺失”。
表结构解析
- 操作记录是记录在workflow_requestoperatelog【流程操作记录日志主表】。
- 流程删除后，会同时在实例表workflow_requestbase【工作流请求基本信息表】和workflow_requestlog【工作流请求签字日志表】删除对应requestid的记录。而workflow_requestoperatelog【流程操作记录日志主表】里的不会被删除。因此统计次数时使用workflow_requestoperatelog会更加精准。
- 因此针对先对审批流产生操作（退回、干预等）再删除的场景，导出流程退回/转发等操作清单时，行数量是可能少于报表统计的操作记录数的。
一、流程操作统计
从宏观层面了解各类流程的操作情况。以下SQL统计了按流程分类和流程类型分组的所有关键操作次数，包括提交、退回、干预等，提供整体视角。
此查询展示各类操作的发生频次，可以协助分析人员快速识别操作密集的流程类型。
mssql写法，未测试
--统计OA流程在固定日期内的操作记录次数（近一年）
WITH
active_workflow AS (
SELECT
id AS workflowid,
COALESCE(activeVersionID, id) AS activeVersionID
FROM workflow_base
),
operation_stats AS (
SELECT
COALESCE(NULLIF(d.typename, ''), '已删除的流程') as typename,
COALESCE(NULLIF(c.workflowname, ''), '已删除的流程') as workflowname,
COUNT(DISTINCT a.requestid) as '涉及流程数',
SUM(CASE WHEN a.operatetype = 'submit' THEN 1 ELSE 0 END) as '提交次数',
SUM(CASE WHEN a.operatetype = 'reject' THEN 1 ELSE 0 END) as '退回次数',
SUM(CASE WHEN a.operatetype = 'intervenor' THEN 1 ELSE 0 END) as '干预次数',
SUM(CASE WHEN a.operatetype = 'forceover' THEN 1 ELSE 0 END) as '强制归档次数',
SUM(CASE WHEN a.operatetype = 'forward' THEN 1 ELSE 0 END) as '转发次数',
SUM(CASE WHEN a.operatetype = 'trans' THEN 1 ELSE 0 END) as '转办次数',
SUM(CASE WHEN a.operatetype = 'take' THEN 1 ELSE 0 END) as '意见征询次数'
FROM workflow_requestoperatelog a
LEFT JOIN workflow_requestbase b ON a.requestid = b.requestid
LEFT JOIN active_workflow aw ON b.workflowid = aw.workflowid
LEFT JOIN workflow_base c ON aw.activeVersionID = c.id
LEFT JOIN workflow_type d ON c.workflowtype = d.id
WHERE a.operatedate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY
COALESCE(NULLIF(d.typename, ''), '已删除的流程'),
COALESCE(NULLIF(c.workflowname, ''), '已删除的流程')
)SELECT
CAST(YEAR(DATEADD(YEAR, -1, GETDATE())) AS VARCHAR) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS 年份,
CASE
WHEN os.typename = '已删除的流程' THEN '已删除的流程'
ELSE COALESCE(NULLIF(dbo.convToCN(os.typename), ''), os.typename) END as '流程分类',
CASE
WHEN os.workflowname = '已删除的流程' THEN '已删除的流程'
ELSE COALESCE(NULLIF(dbo.convToCN(os.workflowname), ''), os.workflowname)
END as '流程类型名称',
os.涉及流程数,
os.提交次数,
os.退回次数,
os.干预次数,
os.强制归档次数,
os.转发次数,
os.转办次数,
os.意见征询次数
FROM operation_stats os
ORDER BY typename asc, workflowname asc
oracle 11g 测试完成的写法
WITH active_workflow AS (
SELECT
id AS workflowid,
NVL(activeVersionID, id) AS activeVersionID
FROM workflow_base
),
operate_stat AS(
SELECT
COALESCE(nullif(d.TYPENAME, ''), '已删除的流程') as TYPENAME,
COALESCE(NULLIF(c.WORKFLOWNAME, ''), '已删除的流程') as workflowname,
COUNT(DISTINCT a.requestid) AS 涉及流程数,
SUM(CASE WHEN a.OPERATETYPE = 'submit' THEN 1 ELSE 0 END) as 提交次数,
SUM(CASE WHEN a.operatetype = 'reject' THEN 1 ELSE 0 END) AS 退回次数,
SUM(CASE WHEN a.operatetype = 'intervenor' THEN 1 ELSE 0 END) AS 干预次数,
SUM(CASE WHEN a.operatetype = 'forceover' THEN 1 ELSE 0 END) AS 强制归档次数,
SUM(CASE WHEN a.operatetype = 'forward' THEN 1 ELSE 0 END) AS 转发次数,
SUM(CASE WHEN a.operatetype = 'trans' THEN 1 ELSE 0 END) AS 转办次数,
SUM(CASE WHEN a.operatetype = 'take' THEN 1 ELSE 0 END) AS 意见征询次数
FROM workflow_requestoperatelog a
LEFT JOIN workflow_requestbase b ON a.requestid = b.requestid
left join active_workflow aw on b.WORKFLOWID = aw.WORKFLOWID
left join workflow_base c on aw.ACTIVEVERSIONID = c.id
left join workflow_type d on c.WORKFLOWTYPE = d.id
WHERE a.operatedate >= '2025-01-01'
-- AND (OPERATORID=54 or OPERATORID=561) //添加操作人
GROUP BY
COALESCE(nullif(d.TYPENAME, ''), '已删除的流程'),
COALESCE(NULLIF(c.WORKFLOWNAME, ''), '已删除的流程')
)
SELECT
-- TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) || '-' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) AS 年份,
CASE
WHEN os.typename = '已删除的流程' THEN '已删除的流程' ELSE NVL(NULLIF(convToCN(os.typename), ''), os.typename) END AS 流程分类,
CASE
WHEN os.workflowname = '已删除的流程' THEN '已删除的流程' ELSE NVL(NULLIF(convToCN(os.workflowname), ''), os.workflowname) END AS 流程类型名称,
os.涉及流程数,
os.提交次数,
os.退回次数,
os.干预次数,
os.强制归档次数,
os.转发次数,
os.转办次数,
os.意见征询次数
from operate_stat os
ORDER BY os.TYPENAME asc, os.WORKFLOWNAME asc
二、操作明细分析
MSSQL的写法，未验证
--查询产生过退回操作的流程清单及次数
SELECT
COALESCE(NULLIF(b.requestname,''),'已删除的流程') as '审批流名称',
b.requestid '流程id',
COUNT(a.requestid) as '退回次数',
COALESCE(NULLIF(d.typename, ''), '已删除的流程') as '所属流程分类',
COALESCE(NULLIF(c.workflowname, ''), '已删除的流程') as '所属流程类型名称'
FROM workflow_requestoperatelog a
LEFT JOIN workflow_requestbase b ON a.requestid = b.requestid
LEFT JOIN workflow_base c ON b.workflowid = c.id
LEFT JOIN workflow_type d ON c.workflowtype = d.id
WHERE a.operatetype = 'reject'
AND a.operatedate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY d.typename, c.workflowname, b.requestname, b.requestid,c.workflowtype,c.id
ORDER BY c.workflowtype ASC,c.id ASC,b.requestid ASC
Oracle 11g的写法，已验证
SELECT
COALESCE(NULLIF(b.REQUESTNAME, ''), '已删除的流程') as 审批洲名称,
b.REQUESTID 流程id,
COUNT(a.REQUESTID) as 退回次数,
COALESCE(NULLIF(d.TYPENAME, ''), '已删除的流程') as 所属流程分类,
COALESCE(NULLIF(c.WORKFLOWNAME, ''), '已删除的流程') as 所属流程类型名称
FROM WORKFLOW_REQUESTOPERATELOG a
LEFT JOIN workflow_requestbase b on a.requestid = b.requestid
LEFT JOIN WORKFLOW_BASE c on b.workflowid = c.id
LEFT JOIN WORKFLOW_TYPE d on c.WORKFLOWTYPE = d.id
WHERE a.OPERATETYPE = 'reject'
AND EXTRACT(YEAR FROM TO_DATE(a.OPERATEDATE, 'YYYY-MM-DD')) = 2025
GROUP BY d.TYPENAME, c.WORKFLOWNAME, b.REQUESTNAME, b.REQUESTID, c.WORKFLOWTYPE, c.id
ORDER BY c.WORKFLOWTYPE asc, c.id asc, b.requestid asc
其中，您可以替换【WHERE a.operatetype = 'reject'】中的reject来查询其它操作的清单。
提交 submit
退回 reject
干预 intervenor
转办 trans
意见征询 tak
转发 forward
强制归档 forceover
三、节点级退回分析
识别出高频退回的流程后，我们需要进一步定位具体在哪个环节容易出现问题。以下查询展示每条流程的退回操作发生在哪些审批节点，协助精准定位流程瓶颈：
SELECT
c.typename, a.workflowid, b.workflowname, d.requestid, d.requestname,
d.createdate, e.nodename, h.lastname, a.remark
FROM
workflow_requestlog a, workflow_base b, workflow_type c,
workflow_requestbase d, workflow_nodebase e, hrmresource h
WHERE
a.logtype = '3'
AND a.workflowid = b.id
AND a.requestid = d.requestid
AND a.nodeid = e.id
AND a.operator = h.id
AND b.workflowtype = c.id
AND EXTRACT(YEAR FROM TO_DATE(d.createdate, 'YYYY-MM-DD')) = 2025
扩展扩展一： 对工作流名称、节点名称、节点ID以及退回记录数据进行分类汇总
SELECT
b.workflowname as 工作流名称,
e.nodename as 节点名称,
e.id as 节点ID,
COUNT(*) as 记录数量
FROM workflow_requestlog a
INNER JOIN workflow_base b ON a.workflowid = b.id
INNER JOIN workflow_type c ON b.workflowtype = c.id
INNER JOIN workflow_requestbase d ON a.requestid = d.requestid
INNER JOIN workflow_nodebase e ON a.nodeid = e.id
INNER JOIN hrmresource h ON a.operator = h.id
WHERE a.logtype = '3'
AND EXTRACT(YEAR FROM TO_DATE(d.createdate, 'YYYY-MM-DD')) = 2025
GROUP BY b.workflowname, e.nodename, e.id
ORDER BY 记录数量 DESC
扩展二： 以操作者为条件，排序退回操作数量
SELECT
b.workflowname as 流程名称, h.lastname as 操作人, count(*) as 退回数量
FROM
workflow_requestlog a, workflow_base b, workflow_type c,
workflow_requestbase d, workflow_nodebase e, hrmresource h
WHERE
a.logtype = '3'
AND a.workflowid = b.id
AND a.requestid = d.requestid
AND a.nodeid = e.id
AND a.operator = h.id
AND b.workflowtype = c.id
AND EXTRACT(YEAR FROM TO_DATE(d.createdate, 'YYYY-MM-DD')) = 2025
GROUP BY h.lastname, b.workflowname
ORDER BY 退回数量 desc, 操作人 desc
其中，您可以替换【WHERE a.logtype = '3' 】中的reject来查询其它操作的清单。
0：批准
1：保存
2：提交
3：退回
7：转发
9：批注
a：意见征询
e：强制归档
h：转办
i：干预
t：抄送
四、以个人为单位分析
1、 参与过的流程
以操作人operator的操作类型，一个人如果既有批准也有抄送的，则会有多条记录。
SELECT ROW_NUMBER() OVER (ORDER BY wrlog.requestid ASC) AS id,
wrlog.REQUESTID,
wrlog.workflowid,
base.WORKFLOWNAME,
wrlog.logtype 操作类型,
wrlog.remark 意见
FROM workflow_requestlog wrlog
LEFT JOIN WORKFLOW_BASE base on wrlog.WORKFLOWID=base.id
WHERE wrlog.operator=49
AND wrlog.OPERATEDATE >= '2025-10-01' and wrlog.OPERATEDATE <= '2025-10-31'
2、 参与过的流程，以requestid去重
SELECT
ROW_NUMBER() OVER (ORDER BY t.REQUESTID ASC) AS id,
t.REQUESTID, t.workflowid, t.WORKFLOWNAME, t.操作类型, t.意见
FROM (
SELECT
wrlog.REQUESTID,
wrlog.workflowid,
base.WORKFLOWNAME,
wrlog.logtype AS 操作类型,
wrlog.remark AS 意见,
ROW_NUMBER() OVER (PARTITION BY wrlog.REQUESTID ORDER BY wrlog.OPERATEDATE ASC) as rn
FROM workflow_requestlog wrlog
LEFT JOIN WORKFLOW_BASE base ON wrlog.WORKFLOWID=base.id
WHERE wrlog.operator=49
AND wrlog.OPERATEDATE >= '2025-10-01'
AND wrlog.OPERATEDATE <= '2025-10-31'
) t
WHERE t.rn = 1
3、参与过的流程类型，以WORKFLOWID（流程类型）去重的
SELECT
ROW_NUMBER() OVER (ORDER BY t.REQUESTID ASC) AS id,
t.REQUESTID, t.workflowid, t.WORKFLOWNAME
FROM (
SELECT
wrlog.REQUESTID,
wrlog.workflowid,
base.WORKFLOWNAME,
ROW_NUMBER() OVER (PARTITION BY wrlog.WORKFLOWID ORDER BY wrlog.OPERATEDATE DESC) as rn
FROM workflow_requestlog wrlog
LEFT JOIN WORKFLOW_BASE base ON wrlog.WORKFLOWID = base.id
WHERE wrlog.operator = 49
AND wrlog.OPERATEDATE >= '2025-10-01'
AND wrlog.OPERATEDATE <= '2025-10-31'
) t
WHERE t.rn = 1
4、 全年参与过的流程，按月汇总数（存在流程跨月仅保留一次操作）
SELECT TO_CHAR(TO_DATE(z.OPERATEDATE, 'YYYY-MM-DD'), 'YYYY-MM') as month, count(z.REQUESTID) as total
from (
SELECT
ROW_NUMBER() OVER (ORDER BY t.REQUESTID ASC) AS id,
t.REQUESTID, t.OPERATEDATE, t.workflowid, t.WORKFLOWNAME, t.操作类型, t.意见
FROM (
SELECT
wrlog.REQUESTID,
wrlog.OPERATEDATE,
wrlog.workflowid,
base.WORKFLOWNAME,
wrlog.logtype AS 操作类型,
wrlog.remark AS 意见,
ROW_NUMBER() OVER (PARTITION BY wrlog.REQUESTID ORDER BY wrlog.OPERATEDATE ASC) as rn
FROM workflow_requestlog wrlog
LEFT JOIN WORKFLOW_BASE base ON wrlog.WORKFLOWID=base.id
WHERE wrlog.operator= 49
AND wrlog.OPERATEDATE >= '2025-01-01'
AND wrlog.OPERATEDATE <= '2025-10-31'
AND wrlog.logtype in ('0','3')
-- AND wrlog.logtype in ('3')
) t
WHERE t.rn = 1
) z
group BY TO_CHAR(TO_DATE(z.OPERATEDATE, 'YYYY-MM-DD'), 'YYYY-MM')
ORDER BY month
5、 存在虚拟账号时，使用人名查询
SELECT
ROW_NUMBER() OVER (ORDER BY t.REQUESTID ASC) AS id,
t.REQUESTID, t.流程标题, t.WORKFLOWNAME, t.操作类型, t.签字意见, t.操作时间
FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY wrlog.REQUESTID ORDER BY wrlog.OPERATEDATE ASC) as rn,
wrlog.REQUESTID,
e.REQUESTNAMENEW 流程标题,
base.WORKFLOWNAME,
wn.NODENAME 操作节点,
wrlog.logtype AS 操作类型,
wrlog.RECEIVEDPERSONS 接收人,
wrlog.OPERATEDATE 操作时间,
wrlog.remark 签字意见
FROM workflow_requestlog wrlog
LEFT JOIN workflow_requestbase e ON wrlog.requestid = e.requestid
LEFT JOIN WORKFLOW_BASE base ON wrlog.WORKFLOWID=base.id
left JOIN hrmresource hrm on wrlog.OPERATOR = hrm.id
left join workflow_nodebase wn on wn.id = wrlog.nodeid
where wrlog.logtype in ('0', '3')
and hrm.lastname = '钟燕燕'
and wrlog.OPERATEDATE >= '2025-10-01' AND wrlog.OPERATEDATE <= '2025-10-31'
) t
WHERE t.rn = 1