-- =============================================================================
|
-- 病区出院随访统计 · 纯查询版（会话变量传参，适合 Navicat/DataGrip 临时取数）
|
-- =============================================================================
|
-- @start_time / @end_time 就是原来写死的 endtime 起止，改这两行即可。
|
-- 注意：这不是视图，变量属于当前连接，换连接要重新 SET。
|
-- =============================================================================
|
|
SET @start_time = '2026-10-04'; -- 出院时间起（含）
|
SET @end_time = '2026-11-03'; -- 出院时间止（含当天）
|
SET @followUpCountStyle = '1'; -- 1=应随访 3=仅成功 其它=全部
|
SET @timelyDays = 7; -- 及时天数基数
|
|
SELECT
|
t.leavehospitaldistrictname, -- 出院病区
|
t.deptname, -- 出院科室
|
t.dischargeCount, -- 出院人次（首次随访记录数）
|
t.nonFollowUp, -- 无需随访人次
|
t.followUpNeeded, -- 应随访人次
|
t.needFollowUp, -- 首次应随访（= 应随访人次）
|
t.pendingFollowUp, -- 首次待随访
|
t.followUpSuccess, -- 首次随访成功
|
t.followUpFail, -- 首次随访失败（含人工超时）
|
t.manualSuccess, -- 首次人工随访成功
|
t.voiceSuccess, -- 首次语音随访成功
|
t.smsSuccess, -- 首次短信随访成功
|
t.weChatSuccess, -- 首次微信随访成功
|
-- 随访成功率 = 成功 / 应随访（只算成功，不含失败）
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
|
-- 渠道合计成功率 = 四种方式成功之和 / 四种方式命中之和
|
IF(t.manual + t.voice + t.sms + t.weChat = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess)
|
/ (t.manual + t.voice + t.sms + t.weChat), 2), '%')) AS channelSuccessRate,
|
-- 及时率：及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= 及时天数 + 补偿天数
|
IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
|
CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate
|
FROM (
|
SELECT
|
r.ward_code AS ward_code,
|
MAX(r.ward_name) AS leavehospitaldistrictname,
|
MAX(r.dept_name) AS deptname,
|
COUNT(*) AS dischargeCount,
|
SUM(r.f_no) AS nonFollowUp,
|
SUM(r.f_need) AS followUpNeeded,
|
SUM(r.f_need) AS needFollowUp,
|
SUM(IF(r.sendstate = 2, 1, 0)) AS pendingFollowUp,
|
SUM(r.f_succ) AS followUpSuccess,
|
SUM(r.f_fail) AS followUpFail,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '1', 1, 0)) AS manual,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '3', 1, 0)) AS voice,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '4', 1, 0)) AS sms,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '5', 1, 0)) AS weChat,
|
SUM(IF(r.sendstate = 6 AND r.cp = '1', 1, 0)) AS manualSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '3', 1, 0)) AS voiceSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '4', 1, 0)) AS smsSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '5', 1, 0)) AS weChatSuccess
|
FROM (
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
ss.leavehospitaldistrictname AS ward_name,
|
ss.leaveldeptname AS dept_name,
|
ss.sendstate AS sendstate,
|
ss.current_preachform AS cp,
|
IF(ss.sendstate = 4, 1, 0) AS f_no,
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
|
IF(ss.sendstate = 6, 1, 0) AS f_succ,
|
IF(ss.sendstate IN (5, 7), 1, 0) AS f_fail,
|
-- 随访方式命中条件：方式 1 排除「无需随访」；方式 3 只算已完成；其它方式不设限
|
IF(@followUpCountStyle = '1',
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0),
|
IF(@followUpCountStyle = '3',
|
IF(ss.sendstate = 6, 1, 0),
|
IF(ss.sendstate IS NOT NULL, 1, 0))) AS ch_hit
|
FROM service_subtask ss
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2'
|
AND ss.visit_count = 1 -- 只要首次随访
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
AND ss.endtime >= DATE(@start_time)
|
AND ss.endtime < DATE_ADD(DATE(@end_time), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
) r
|
GROUP BY r.ward_code
|
) t
|
LEFT JOIN (
|
-- 及时率：按病区预聚合一次再关联，避免一对多放大分母
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
COUNT(*) AS timely_base,
|
SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= @timelyDays + IFNULL(cp.compensate_days, 0),
|
1, 0)) AS timely_cnt
|
FROM service_subtask ss
|
LEFT JOIN (
|
-- 补偿时间：varchar 且可能是空串，先按子任务聚合成一行
|
SELECT subid,
|
SUM(CAST(COALESCE(NULLIF(TRIM(compensate_time), ''), '0') AS SIGNED)) AS compensate_days
|
FROM service_subtask_preachform
|
WHERE del_flag = 0
|
GROUP BY subid
|
) cp ON cp.subid = ss.id
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2'
|
AND ss.visit_count = 1
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
AND ss.visit_time IS NOT NULL
|
AND ss.sendstate <> 4
|
AND DATE(ss.visit_time) < DATE(NOW()) -- 接口未传 startTime/endTime 时的固定条件
|
AND ss.endtime >= DATE(@start_time)
|
AND ss.endtime < DATE_ADD(DATE(@end_time), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
GROUP BY ss.leavehospitaldistrictcode
|
) rt ON rt.ward_code = t.ward_code
|
ORDER BY t.dischargeCount DESC;
|
