-- =============================================================================
|
-- /smartor/serviceSubtask/getSfStatistics 的 SQL 版（病区维度 · 全部病区 · 出院随访 serviceType=2）
|
-- =============================================================================
|
-- 对应代码：
|
-- ServiceSubtaskController#getSfStatistics
|
-- → ServiceSubtaskServiceImpl#getSfStatistics （分组 + 逐条累加）
|
-- → ServiceSubtaskMapper.getSfStatistics / getSfStatisticsGroupKey
|
-- → ServiceSubtaskMapper.selectTimelyRateBatch （及时率 rate）
|
-- 数据源：service_subtask（本接口只查这一张表）
|
--
|
-- 入参：只有 hospitalStartTime（出院时间下限）。
|
-- 分组：statisticaltype=1（病区），且不传 leavehospitaldistrictcodes → 全部病区。
|
-- 服务类型：serviceType = 2（出院随访）。
|
-- 说明：接口 Mapper 里出院时间条件是「hospitalStartTime 与 hospitalEndTime 都传才生效」，
|
-- 只传 hospitalStartTime 时接口其实【完全不过滤出院时间】。下面按你要的语义实现：
|
-- 出院时间 >= hospitalStartTime（上限可选）。
|
--
|
-- 已在 MySQL 5.7.34（smartor_sltd）实跑验证：
|
-- hospitalStartTime = '2026-10-01' → 45 个病区 / 3088 条出院记录，
|
-- 抽查病区 40003052：287 条、无需随访 4、应随访 283、首次随访成功 250、随访率 88.34%，与明细逐条核对一致。
|
-- =============================================================================
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 0. 参数（只有第一个是必填）
|
-- -----------------------------------------------------------------------------
|
SET @hospitalStartTime = '2026-10-01'; -- 必填：出院时间下限（当天 0 点起）
|
SET @hospitalEndTime = NULL; -- 可选：出院时间上限，NULL = 不限
|
SET @followUpCountStyle = '1'; -- 系统配置 followUpCountStyle；本库未配置该参数，接口按 '1' 处理
|
SET @orgid = NULL; -- 可选：机构；NULL = 不限（接口里由登录信息注入）
|
SET @campusid = NULL; -- 可选：院区；NULL = 不限
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 1. 主查询：一行 = 一个出院病区，覆盖全部病区
|
-- · 内层 r ：一行 = 一条随访子任务，把「统计口径」翻译成 0/1 标记
|
-- · 中间 t ：按病区把标记累加
|
-- · 最外层 ：把累加值算成接口返回的比率字符串
|
-- 想改统计维度：把 GROUP BY r.ward_code 换成 r.dept_code（科室）或 r.dr_code（医生）即可。
|
-- 想要总计行：把「GROUP BY r.ward_code」改成「GROUP BY r.ward_code WITH ROLLUP」，
|
-- 最后一行 ward_code 为 NULL 的就是全部病区合计。
|
-- -----------------------------------------------------------------------------
|
SELECT
|
t.leavehospitaldistrictcode,
|
t.leavehospitaldistrictname,
|
t.deptcode,
|
t.deptname,
|
t.drcode,
|
t.drname,
|
t.dischargeCount, -- 出院人次
|
t.nonFollowUp, -- 无需随访人次
|
t.filterCount, -- 过滤患者数量
|
t.followUpNeeded, -- 应随访人次
|
t.needFollowUp, -- 首次应随访
|
t.nonFollowUpFirst, -- 首次无需随访
|
t.pendingFollowUp, -- 首次待随访（本接口此路径恒为 0，见 PART 5）
|
t.followUpSuccess, -- 首次随访成功
|
t.followUpFail, -- 首次随访失败（含人工超时）
|
t.manual, -- 首次人工随访
|
t.voice, -- 首次语音随访
|
t.sms, -- 首次短信随访
|
t.weChat, -- 首次微信随访
|
t.manualSuccess, -- 首次人工随访成功
|
t.voiceSuccess, -- 首次语音随访成功
|
t.smsSuccess, -- 首次短信随访成功
|
t.weChatSuccess, -- 首次微信随访成功
|
-- 首次随访率 =（首次随访成功 + 首次随访失败）/ 首次应随访
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
|
t.abnormal, -- 结果异常（首次）
|
t.needFollowUpAgain, -- 二次应随访
|
t.nonFollowUpAgain, -- 二次无需随访
|
t.pendingFollowUpAgain, -- 二次待随访
|
t.followUpSuccessAgain, -- 二次随访成功
|
t.followUpFailAgain, -- 二次随访失败
|
t.manualAgain,
|
t.voiceAgain,
|
t.smsAgain,
|
t.weChatAgain,
|
t.manualAgainSuccess,
|
t.voiceAgainSuccess,
|
t.smsAgainSuccess,
|
t.weChatAgainSuccess,
|
-- 二次随访率
|
IF(t.needFollowUpAgain = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.followUpSuccessAgain + t.followUpFailAgain) / t.needFollowUpAgain, 2), '%')) AS followUpRateAgain,
|
t.abnormalAgain, -- 结果异常（再次）
|
t.taskSituation1, -- 随访情况：正常语音
|
t.taskSituation2, -- 随访情况：患者拒接/拒访
|
t.taskSituation3, -- 随访情况：面访/接诊
|
t.taskSituation4, -- 随访情况：微信随访
|
t.taskSituation5, -- 随访情况：随访电话不正确
|
t.taskSituation6 -- 随访情况：其他（task_situation = 7）
|
FROM (
|
SELECT
|
r.ward_code AS leavehospitaldistrictcode,
|
MAX(r.ward_name) AS leavehospitaldistrictname,
|
MAX(r.dept_code) AS deptcode,
|
MAX(r.dept_name) AS deptname,
|
MAX(r.dr_code) AS drcode,
|
MAX(r.dr_name) AS drname,
|
COUNT(*) AS dischargeCount,
|
SUM(r.f_no) AS nonFollowUp,
|
SUM(r.f_filter) AS filterCount,
|
SUM(r.f_need) AS followUpNeeded,
|
SUM(IF(r.f_first = 1 AND r.f_need = 1, 1, 0)) AS needFollowUp,
|
-- followUpCountStyle='1' 时接口把「首次无需随访」加了两次（代码 L2195-2197 与 L2211-2213），这里 *2 复刻
|
SUM(IF(r.f_first = 1 AND r.f_no = 1, 1, 0)) * IF(@followUpCountStyle = '1', 2, 1) AS nonFollowUpFirst,
|
0 AS pendingFollowUp,
|
SUM(IF(r.f_first = 1 AND r.f_succ_first = 1, 1, 0)) AS followUpSuccess,
|
SUM(IF(r.f_first = 1 AND r.f_fail_first = 1, 1, 0)) AS followUpFail,
|
SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '1', 1, 0)) AS manual,
|
SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '3', 1, 0)) AS voice,
|
SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '4', 1, 0)) AS sms,
|
SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '5', 1, 0)) AS weChat,
|
SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '1', 1, 0)) AS manualSuccess,
|
SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '3', 1, 0)) AS voiceSuccess,
|
SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '4', 1, 0)) AS smsSuccess,
|
SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '5', 1, 0)) AS weChatSuccess,
|
SUM(IF(r.f_first = 1 AND r.f_abnormal = 1, 1, 0)) AS abnormal,
|
SUM(IF(r.f_again = 1 AND r.f_need = 1, 1, 0)) AS needFollowUpAgain,
|
SUM(IF(r.f_again = 1 AND r.f_no = 1, 1, 0)) AS nonFollowUpAgain,
|
SUM(IF(r.f_again = 1 AND r.sendstate = 2, 1, 0)) AS pendingFollowUpAgain,
|
SUM(IF(r.f_again = 1 AND r.f_succ_again = 1, 1, 0)) AS followUpSuccessAgain,
|
SUM(IF(r.f_again = 1 AND r.f_fail_again = 1, 1, 0)) AS followUpFailAgain,
|
SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '1', 1, 0)) AS manualAgain,
|
SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '3', 1, 0)) AS voiceAgain,
|
SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '4', 1, 0)) AS smsAgain,
|
SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '5', 1, 0)) AS weChatAgain,
|
SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '1', 1, 0)) AS manualAgainSuccess,
|
SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '3', 1, 0)) AS voiceAgainSuccess,
|
SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '4', 1, 0)) AS smsAgainSuccess,
|
SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '5', 1, 0)) AS weChatAgainSuccess,
|
SUM(IF(r.f_again = 1 AND r.f_abnormal = 1, 1, 0)) AS abnormalAgain,
|
SUM(IF(r.task_situation = 1, 1, 0)) AS taskSituation1,
|
SUM(IF(r.task_situation = 2, 1, 0)) AS taskSituation2,
|
SUM(IF(r.task_situation = 3, 1, 0)) AS taskSituation3,
|
SUM(IF(r.task_situation = 4, 1, 0)) AS taskSituation4,
|
SUM(IF(r.task_situation = 5, 1, 0)) AS taskSituation5,
|
SUM(IF(r.task_situation = 7, 1, 0)) AS taskSituation6
|
FROM (
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
ss.leavehospitaldistrictname AS ward_name,
|
ss.leaveldeptcode AS dept_code,
|
ss.leaveldeptname AS dept_name,
|
ss.drcode AS dr_code,
|
ss.drname AS dr_name,
|
ss.sendstate AS sendstate,
|
ss.current_preachform AS cp,
|
ss.task_situation AS task_situation,
|
IF(ss.sendstate = 4, 1, 0) AS f_no,
|
IF(ss.sendstate = 4 AND ss.task_situation = 6, 1, 0) AS f_filter,
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
|
IF(ss.visit_count = 1, 1, 0) AS f_first,
|
IF(ss.visit_count > 1, 1, 0) AS f_again,
|
-- 首次随访成功：统计方式 1/3 只算已完成(6)；其它方式算上不执行/已发送(3,4,6)
|
IF(@followUpCountStyle IN ('1', '3'),
|
IF(ss.sendstate = 6, 1, 0),
|
IF(ss.sendstate IN (3, 4, 6), 1, 0)) AS f_succ_first,
|
-- 再次随访成功：统计方式 1/3 只算已完成(6)；其它方式算上待随访失败前的 1 等(1,3,4,6)
|
IF(@followUpCountStyle IN ('1', '3'),
|
IF(ss.sendstate = 6, 1, 0),
|
IF(ss.sendstate IN (1, 3, 4, 6), 1, 0)) AS f_succ_again,
|
IF(ss.sendstate IN (5, 7), 1, 0) AS f_fail_first,
|
IF(ss.sendstate = 5, 1, 0) AS f_fail_again,
|
IF(ss.excep = '1', 1, 0) AS f_abnormal,
|
-- 随访方式命中条件：方式 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' -- serviceType = 2 出院随访
|
-- 接口里 groupKeyList 会带全量病区码，等价于「出院病区为 NULL 的行被丢掉」；
|
-- 若想把空病区也统计进来，注释掉下一行。
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
AND ss.endtime >= DATE(@hospitalStartTime)
|
AND (@hospitalEndTime IS NULL OR ss.endtime < DATE_ADD(DATE(@hospitalEndTime), INTERVAL 1 DAY))
|
AND (@orgid IS NULL OR ss.orgid = @orgid)
|
AND (@campusid IS NULL OR ss.campusid = @campusid)
|
) r
|
GROUP BY r.ward_code
|
) t
|
ORDER BY t.dischargeCount DESC;
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 1B. 只要「首次随访」的数据（等价于接口入参 visitCount = 1 → getStatisticFirst）
|
-- · 只统计 visit_count = 1 的记录：二次随访记录不进入任何一列
|
-- · 列：出院人次 / 应随访拆解 / 随访成功拆解 / 各成功率 / 及时率 / 出院时间范围
|
-- · 口径与 getStatisticFirst 对齐：pendingFollowUp 是 sendstate = 2 的真实数量
|
--
|
-- ★ 对账：接口这几列本身不等于「应随访」，差额是两个真实存在的口径缺项，不是数据错：
|
-- 首次应随访 = 待发送(1) + 待随访(2) + 已发送未完成(3) + 其它状态 + 随访成功(6) + 随访失败(5,7)
|
-- 首次随访成功 = 人工成功 + 语音成功 + 短信成功 + 微信成功 + 无方式成功(sendstate=6 但 current_preachform 为空)
|
-- 接口只统计 2 / 6 / 5+7，所以「待随访 + 成功 + 失败」少于应随访；
|
-- 渠道成功按 current_preachform 分，没有方式的成功记录不落任何渠道。
|
-- 下面把 notSent / sentNotDone / otherStatus / noChannelSuccess 显式列出来，并给出 diffNeed / diffSucc
|
-- 两个自检列（正常恒为 0）。
|
--
|
-- ★ 出院时间：主查询给 endTimeMin/endTimeMax，及时率口径给 timelyEndMin/timelyEndMax。
|
-- ⚠ 及时率子查询里的出院时间条件当前是注释掉的（按你的版本），所以它统计的是该病区「全部历史」，
|
-- 分母 timelyBase 会远大于应随访（实测某病区 643 条应随访 vs 2342 条及时率分母），
|
-- timelyRate 与其它列不再同区间、不可直接比较。要恢复同区间就把那两行放开。
|
--
|
-- 主查询已实跑校验（2026-09-04 ~ 2026-11-03，orgid=20001001，45 个病区）：7378 条 / 无需随访 428 /
|
-- 应随访 6950 = 待发送 116 + 待随访 337 + 已发送未完成 941 + 成功 4188 + 失败 1368；
|
-- 成功 4188 = 人工 2974 + 短信 1197 + 无方式 17（本库 current_preachform 只有 1 和 4，语音/微信为 0）。
|
-- -----------------------------------------------------------------------------
|
SELECT
|
t.leavehospitaldistrictname, -- 出院病区
|
t.endTimeMin, -- 该病区命中的最早出院时间
|
t.endTimeMax, -- 该病区命中的最晚出院时间
|
t.deptname, -- 出院科室
|
t.dischargeCount, -- 出院人次（首次随访记录数）
|
t.nonFollowUp, -- 无需随访人次
|
t.followUpNeeded, -- 应随访人次
|
t.needFollowUp, -- 首次应随访
|
t.notSent, -- 待发送（sendstate = 1）
|
t.pendingFollowUp, -- 首次待随访（sendstate = 2）
|
t.sentNotDone, -- 已发送未完成（sendstate = 3）
|
t.otherStatus, -- 其它状态兜底（正常为 0）
|
t.followUpSuccess, -- 首次随访成功（sendstate = 6）
|
t.followUpFail, -- 首次随访失败（sendstate = 5,7，含人工超时）
|
-- 各随访方式成功数（想同时看总数就放开下面 4 行，成功率分母就是这 4 个）
|
-- t.manual, t.voice, t.sms, t.weChat,
|
t.manualSuccess, -- 首次人工随访成功
|
t.voiceSuccess, -- 首次语音随访成功
|
t.smsSuccess, -- 首次短信随访成功
|
t.weChatSuccess, -- 首次微信随访成功
|
t.noChannelSuccess, -- 成功但没有随访方式（current_preachform 为空或不属于 1/3/4/5）
|
-- 随访率 =（成功 + 失败）/ 应随访（接口 followUpRate 的口径）
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
|
-- 随访成功率 = 成功 / 应随访（只算成功，不含失败）
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
|
-- 各方式成功率 = 该方式成功 / 该方式命中（sendstate<>4 且 current_preachform 为该方式）
|
IF(t.manual = 0, '0.00%', CONCAT(ROUND(100 * t.manualSuccess / t.manual, 2), '%')) AS manualSuccessRate,
|
IF(t.voice = 0, '0.00%', CONCAT(ROUND(100 * t.voiceSuccess / t.voice, 2), '%')) AS voiceSuccessRate,
|
IF(t.sms = 0, '0.00%', CONCAT(ROUND(100 * t.smsSuccess / t.sms, 2), '%')) AS smsSuccessRate,
|
IF(t.weChat = 0, '0.00%', CONCAT(ROUND(100 * t.weChatSuccess / t.weChat, 2), '%')) AS weChatSuccessRate,
|
-- 渠道合计成功率 = 四种方式成功之和 / 四种方式命中之和
|
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,
|
-- 及时率：口径与接口 selectTimelyRateBatch 相同（独立的随访时间窗口 + 排除「无需随访」），
|
-- 及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= 及时天数 + 补偿天数。
|
-- 及时天数阈值见下面 rt 子查询里的 7，按需修改（接口不传 rateDay 时返回 0）。
|
IFNULL(rt.timely_base, 0) AS timelyBase, -- 及时率分母（口径与「应随访」不同）
|
IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
|
CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate,
|
rt.timelyEndMin, -- 及时率口径命中的最早出院时间
|
rt.timelyEndMax, -- 及时率口径命中的最晚出院时间
|
-- 自检：两个差额，正常恒为 0
|
t.needFollowUp - (t.notSent + t.pendingFollowUp + t.sentNotDone + t.otherStatus
|
+ t.followUpSuccess + t.followUpFail) AS diffNeed,
|
t.followUpSuccess - (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess
|
+ t.noChannelSuccess) AS diffSucc
|
-- 另外几列需要时放开：
|
-- , t.abnormal
|
-- , t.taskSituation1, t.taskSituation2, t.taskSituation3, t.taskSituation4, t.taskSituation5, t.taskSituation6
|
FROM (
|
SELECT
|
r.ward_code AS leavehospitaldistrictcode,
|
MAX(r.ward_name) AS leavehospitaldistrictname,
|
MAX(r.dept_name) AS deptname,
|
MIN(r.end_time) AS endTimeMin,
|
MAX(r.end_time) AS endTimeMax,
|
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 = 1, 1, 0)) AS notSent,
|
SUM(IF(r.sendstate = 2, 1, 0)) AS pendingFollowUp,
|
SUM(IF(r.sendstate = 3, 1, 0)) AS sentNotDone,
|
SUM(IF(r.sendstate IS NOT NULL AND r.sendstate <> 4
|
AND r.sendstate NOT IN (1, 2, 3, 5, 6, 7), 1, 0)) AS otherStatus,
|
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,
|
SUM(IF(r.sendstate = 6 AND (r.cp IS NULL OR r.cp NOT IN ('1','3','4','5')), 1, 0)) AS noChannelSuccess
|
FROM (
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
ss.leavehospitaldistrictname AS ward_name,
|
ss.leaveldeptname AS dept_name,
|
ss.endtime AS end_time,
|
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('2026-09-04 00:00:00')
|
AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
) r
|
GROUP BY r.ward_code
|
) t
|
LEFT JOIN (
|
-- 及时率（selectTimelyRateBatch 的等价写法，按病区预聚合一次再关联，避免一对多放大分母）
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
COUNT(*) AS timely_base,
|
SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= 7 + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely_cnt,
|
MIN(ss.endtime) AS timelyEndMin, -- 这一口径实际覆盖到的出院时间范围
|
MAX(ss.endtime) AS timelyEndMax
|
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 时的固定条件
|
-- 及时率要不要跟主查询同区间？放开下面两行即可（放开后 timelyBase 才会等于应随访）
|
-- AND ss.endtime >= DATE('2026-09-04 00:00:00')
|
-- AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
GROUP BY ss.leavehospitaldistrictcode
|
) rt ON rt.ward_code = t.leavehospitaldistrictcode
|
ORDER BY t.dischargeCount DESC;
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 1C. 想看「出院时间」这一列：按 病区 + 出院日期(DATE(endtime)) 分组
|
-- 为什么不能只在 PART 1B 里 select ss.endtime 再 GROUP BY ward_code：
|
-- · ONLY_FULL_GROUP_BY 下会报 1055；
|
-- · 把带秒的 endtime 加进 GROUP BY = 一条记录一组，7378 条被拆成 7133 行，汇总没有意义；
|
-- · 更坑的是及时率子查询仍按病区聚合，整病区的 timelyBase 会被重复挂到每一行
|
-- （实测 timelyBase 合计会变成 9,649,708）。这里 rt 也按「病区 + 出院日期」聚合，
|
-- 所以每行的及时率和该行数据是同一个粒度。
|
-- 实测：1637 行 / dischargeCount 合计 7378 / timelyBase 合计 6950 / diffNeed、diffSucc 全 0。
|
-- -----------------------------------------------------------------------------
|
SELECT
|
t.end_date AS 出院日期, -- 出院时间（按天）
|
t.leavehospitaldistrictname AS 出院病区,
|
t.deptname AS 出院科室,
|
t.dischargeCount, t.nonFollowUp, t.followUpNeeded, t.needFollowUp,
|
t.notSent, t.pendingFollowUp, t.sentNotDone, t.otherStatus,
|
t.followUpSuccess, t.followUpFail,
|
t.manualSuccess, t.voiceSuccess, t.smsSuccess, t.weChatSuccess, t.noChannelSuccess,
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
|
IF(t.manual = 0, '0.00%', CONCAT(ROUND(100 * t.manualSuccess / t.manual, 2), '%')) AS manualSuccessRate,
|
IF(t.voice = 0, '0.00%', CONCAT(ROUND(100 * t.voiceSuccess / t.voice, 2), '%')) AS voiceSuccessRate,
|
IF(t.sms = 0, '0.00%', CONCAT(ROUND(100 * t.smsSuccess / t.sms, 2), '%')) AS smsSuccessRate,
|
IF(t.weChat = 0, '0.00%', CONCAT(ROUND(100 * t.weChatSuccess / t.weChat, 2), '%')) AS weChatSuccessRate,
|
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,
|
IFNULL(rt.timely_base, 0) AS timelyBase,
|
IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
|
CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate,
|
t.needFollowUp - (t.notSent + t.pendingFollowUp + t.sentNotDone + t.otherStatus
|
+ t.followUpSuccess + t.followUpFail) AS diffNeed,
|
t.followUpSuccess - (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess
|
+ t.noChannelSuccess) AS diffSucc
|
FROM (
|
SELECT
|
r.ward_code AS leavehospitaldistrictcode,
|
r.end_date AS end_date,
|
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 = 1, 1, 0)) AS notSent,
|
SUM(IF(r.sendstate = 2, 1, 0)) AS pendingFollowUp,
|
SUM(IF(r.sendstate = 3, 1, 0)) AS sentNotDone,
|
SUM(IF(r.sendstate IS NOT NULL AND r.sendstate <> 4
|
AND r.sendstate NOT IN (1, 2, 3, 5, 6, 7), 1, 0)) AS otherStatus,
|
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,
|
SUM(IF(r.sendstate = 6 AND (r.cp IS NULL OR r.cp NOT IN ('1','3','4','5')), 1, 0)) AS noChannelSuccess
|
FROM (
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
DATE(ss.endtime) AS end_date,
|
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,
|
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('2026-09-04 00:00:00')
|
AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
) r
|
GROUP BY r.ward_code, r.end_date
|
) t
|
LEFT JOIN (
|
-- 及时率：同样按「病区 + 出院日期」聚合，才能和左边每一行对上
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
DATE(ss.endtime) AS end_date,
|
COUNT(*) AS timely_base,
|
SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= 7 + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely_cnt
|
FROM service_subtask ss
|
LEFT JOIN (
|
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())
|
AND ss.orgid = '20001001'
|
GROUP BY ss.leavehospitaldistrictcode, DATE(ss.endtime)
|
) rt ON rt.ward_code = t.leavehospitaldistrictcode AND rt.end_date = t.end_date
|
ORDER BY t.leavehospitaldistrictname, t.end_date;
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 1D. 明细版：一行 = 一条首次随访记录，直接带出院时间（要核对到人/到天就跑这个）
|
-- -----------------------------------------------------------------------------
|
SELECT
|
ss.endtime AS 出院时间,
|
ss.leavehospitaldistrictname AS 出院病区,
|
ss.leaveldeptname AS 出院科室,
|
ss.sendname AS 患者,
|
ss.sendstate AS 状态码,
|
CASE ss.sendstate
|
WHEN 1 THEN '待发送' WHEN 2 THEN '待随访' WHEN 3 THEN '已发送未完成'
|
WHEN 4 THEN '无需随访' WHEN 5 THEN '随访失败' WHEN 6 THEN '随访成功'
|
WHEN 7 THEN '失败/人工超时' ELSE '其它'
|
END AS 随访状态,
|
ss.current_preachform AS 随访方式,
|
ss.visit_count AS 随访次数,
|
ss.visit_time AS 随访时间,
|
ss.finishtime AS 完成时间,
|
IF(ss.sendstate = 4 OR ss.visit_time IS NULL, '-',
|
IF(ss.finishtime IS NOT NULL AND DATEDIFF(ss.finishtime, ss.endtime) <= 7, '及时', '不及时')) AS 是否及时
|
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('2026-09-04 00:00:00')
|
AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
ORDER BY ss.leavehospitaldistrictname, ss.endtime;
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 2. 及时率 rate（接口只在请求里传了 rateDay 时才有值，不传返回 0.0）
|
-- 口径与接口不同：这一块用的是「随访时间(visit_time)」窗口，分母是 visit_time 不为空且
|
-- 不是「无需随访」的记录数；出院时间只作为附加条件。
|
-- rate = 及时数 / 分母，及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= rateDay + 补偿天数
|
-- -----------------------------------------------------------------------------
|
SET @rateDay = 7; -- 几天及时率；不想看就整段不跑
|
|
SELECT
|
ss.leavehospitaldistrictcode AS leavehospitaldistrictcode,
|
COUNT(*) AS base, -- 分母
|
SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= @rateDay + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely, -- 及时数
|
ROUND(SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= @rateDay + IFNULL(cp.compensate_days, 0), 1, 0))
|
/ COUNT(*), 4) AS rate
|
FROM service_subtask ss
|
LEFT JOIN ( -- 补偿时间：先按子任务聚成一行再关联，避免一对多把分母放大
|
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_time IS NOT NULL
|
AND ss.sendstate <> 4
|
AND DATE(ss.visit_time) < DATE(NOW()) -- 接口未传 startTime/endTime 时的固定条件
|
AND ss.endtime >= DATE(@hospitalStartTime) -- 等价于接口的 date_format(endtime,'%y%m%d') >= date_format(...)
|
AND (@orgid IS NULL OR ss.orgid = @orgid)
|
AND (@campusid IS NULL OR ss.campusid = @campusid)
|
GROUP BY ss.leavehospitaldistrictcode
|
ORDER BY base DESC;
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 3. 想固定成「视图」的话用这个（MySQL 的视图不支持传入参数，
|
-- 所以视图按「一条子任务一行」落明细 + 出院日期，参数过滤放在查询外层）
|
-- 注意：视图里把 followUpCountStyle 固定成 '1'（本库未配置该参数，接口实际就走 '1'）。
|
-- 若某机构配的是 '3' 或其它值，改视图里的 f_succ_first / f_succ_again / ch_hit 三处。
|
-- -----------------------------------------------------------------------------
|
CREATE OR REPLACE VIEW v_sf_statistic_flag AS
|
SELECT
|
ss.id,
|
DATE(ss.endtime) AS stat_date, -- 出院日期（hospitalStartTime 过滤用）
|
ss.leavehospitaldistrictcode AS ward_code,
|
ss.leavehospitaldistrictname AS ward_name,
|
ss.leaveldeptcode AS dept_code,
|
ss.leaveldeptname AS dept_name,
|
ss.drcode AS dr_code,
|
ss.drname AS dr_name,
|
ss.endtime AS end_time,
|
ss.visit_time,
|
ss.finishtime,
|
ss.sendstate,
|
ss.visit_count,
|
ss.current_preachform AS cp,
|
ss.task_situation,
|
IF(ss.sendstate = 4, 1, 0) AS f_no,
|
IF(ss.sendstate = 4 AND ss.task_situation = 6, 1, 0) AS f_filter,
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
|
IF(ss.visit_count = 1, 1, 0) AS f_first,
|
IF(ss.visit_count > 1, 1, 0) AS f_again,
|
IF(ss.sendstate = 6, 1, 0) AS f_succ_first, -- style '1'/'3'
|
IF(ss.sendstate = 6, 1, 0) AS f_succ_again, -- style '1'/'3'
|
IF(ss.sendstate IN (5, 7), 1, 0) AS f_fail_first,
|
IF(ss.sendstate = 5, 1, 0) AS f_fail_again,
|
IF(ss.excep = '1', 1, 0) AS f_abnormal,
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS ch_hit -- style '1'
|
FROM service_subtask ss
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2';
|
|
-- 建好视图后，把 PART 1 里内层 "FROM ( ... ) r" 整段换成下面这一行即可（其余不动）：
|
-- FROM v_sf_statistic_flag r
|
-- WHERE r.stat_date >= DATE(@hospitalStartTime)
|
-- AND (@hospitalEndTime IS NULL OR r.end_time < DATE_ADD(DATE(@hospitalEndTime), INTERVAL 1 DAY))
|
-- AND (@orgid IS NULL OR 1 = 1) -- 视图中未带 orgid/campusid，需要的话把它们也加进视图
|
-- 同时把中间层 nonFollowUpFirst 的 "* IF(@followUpCountStyle = '1', 2, 1)" 改成 "* 2"（视图里已是 style '1'）。
|
|
|
-- -----------------------------------------------------------------------------
|
-- PART 4. 口径说明 / 与接口的差异
|
-- -----------------------------------------------------------------------------
|
-- 1) 接口返回里恒为 0、本次未输出的字段（代码这条路径没赋值）：
|
-- serviceCount、bzxCount、ysfCount、yfsCount、dfsCount、fssbCount、ywcCount、
|
-- dxfscs、dxsfwccs、dhfscs、dhsfwccs、yczs、hfycs、finishRate、joyCount、joyAllCount、joyTotal。
|
-- pendingFollowUp（首次待随访）同理：getStatistic 的“不分次数”分支里没有累加，恒为 0。
|
-- 2) nonFollowUpFirst 在 followUpCountStyle='1' 时接口会重复累加一次（两个 if 都命中 sendstate=4），
|
-- SQL 用 *2 复刻。若按“一条记录算一次”应为 1 倍，把 IF(@followUpCountStyle='1',2,1) 改成 1。
|
-- 3) 比率格式：接口用 DecimalFormat("##.##%")（四舍六入、末尾 0 省略，如 85.5%），
|
-- SQL 用 ROUND(...,2)（四舍五入、固定两位，如 85.50%）；分母为 0 时接口给 "0.00%"。
|
-- 4) deptcode/deptname/drcode/drname：接口取「分组内第一条记录」的值（HashMap 顺序，无业务含义），
|
-- SQL 用 MAX() 代替；按病区统计时这四列只作参考。
|
-- 5) 随访情况 taskSituation1~6：task_situation = 1~5 分别对应 1~5，其他(=7) 对应 6。
|
-- 本库 service_subtask.task_situation 全为 NULL，所以这几列目前必然都是 0。
|
-- 6) 只看首次/再次：接口传 visitCount=1 只看首次、>1 只看再次；本 SQL 两类同时给，均为 0 说明该窗口没有对应数据。
|
-- 7) 换维度：GROUP BY r.ward_code（病区，本 SQL）→ r.dept_code（科室）/ r.dr_code（医生）。
|
-- 统计方式、及时率天数都通过 PART 0 的变量控制，不需要改结构。
|
-- =============================================================================
|
