-- =============================================================================
|
-- 病区出院随访统计 · 可传参视图
|
-- =============================================================================
|
-- 为什么是「参数表 + 视图」而不是 @变量：
|
-- MySQL 视图定义里禁止引用 @用户变量 / 系统变量 / 存储过程参数，
|
-- 否则创建时报 ERROR 1351 (HY000): View's SELECT contains a variable or parameter。
|
-- 官方原文：「The SELECT statement cannot refer to system variables or
|
-- user-defined variables.」
|
-- https://dev.mysql.com/doc/refman/8.0/en/create-view.html
|
--
|
-- 所以用单行参数表 report_param 承载参数：
|
-- 1) REPLACE INTO report_param ... 改第 1 行（起止时间 / 口径 / 及时天数）
|
-- 2) SELECT * FROM v_ward_followup_stats;
|
--
|
-- 并发提醒：参数表是共享状态，两个会话同时改会互相串。多用户并发请改用
|
-- 同目录的 p_ward_followup_stats.sql（存储过程，真正按调用传参）。
|
-- =============================================================================
|
|
-- ---------------------------------------------------------------------------
|
-- 1. 参数表（永远只有 id = 1 这一行）
|
-- ---------------------------------------------------------------------------
|
CREATE TABLE IF NOT EXISTS report_param (
|
id TINYINT NOT NULL COMMENT '固定为 1',
|
start_time DATETIME NOT NULL COMMENT '出院时间起（含），传 yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss 均可',
|
end_time DATETIME NOT NULL COMMENT '出院时间止（含当天 23:59:59），传 yyyy-MM-dd 即可',
|
count_style VARCHAR(2) NOT NULL DEFAULT '1' COMMENT '随访方式命中口径：1=应随访 3=仅成功 其它=全部',
|
timely_days INT NOT NULL DEFAULT 7 COMMENT '及时率的天数基数（补偿天数另加）',
|
PRIMARY KEY (id)
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '报表参数（单行，id 恒为 1）';
|
|
-- ---------------------------------------------------------------------------
|
-- 2. 视图
|
-- ---------------------------------------------------------------------------
|
CREATE OR REPLACE VIEW v_ward_followup_stats AS
|
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(p.count_style = '1',
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0),
|
IF(p.count_style = '3',
|
IF(ss.sendstate = 6, 1, 0),
|
IF(ss.sendstate IS NOT NULL, 1, 0))) AS ch_hit
|
FROM service_subtask ss
|
JOIN report_param p ON p.id = 1
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2'
|
AND ss.visit_count = 1 -- 只要首次随访
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
-- >>> 可传参的时间范围：左闭右开，end_time 传日期即可含当天全天 <<<
|
AND ss.endtime >= DATE(p.start_time)
|
AND ss.endtime < DATE_ADD(DATE(p.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) <= p.timely_days + IFNULL(cp.compensate_days, 0),
|
1, 0)) AS timely_cnt
|
FROM service_subtask ss
|
JOIN report_param p ON p.id = 1
|
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(p.start_time)
|
AND ss.endtime < DATE_ADD(DATE(p.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;
|
|
-- ---------------------------------------------------------------------------
|
-- 3. 用法
|
-- ---------------------------------------------------------------------------
|
-- 改参数（只改这一行，两个日期就是原来的 endtime 起止）
|
REPLACE INTO report_param (id, start_time, end_time, count_style, timely_days)
|
VALUES (1, '2026-10-04', '2026-11-03', '1', 7);
|
|
-- 查数
|
SELECT *
|
FROM v_ward_followup_stats
|
-- WHERE leavehospitaldistrictname LIKE '%骨科%' -- 视图外照样能再加条件
|
;
|
