WITH RECURSIVE current_week_monday AS (
-- 获取当前周的周一（用于构建周范围）
SELECT
DATE_SUB(CURRENT_DATE, INTERVAL WEEKDAY(CURRENT_DATE) DAY) AS current_monday
),
-- 生成最近14周的日期范围（基于周一）
week_dates AS (
SELECT
DATE_SUB(cm.current_monday, INTERVAL (seq * 7) DAY) AS week_start
FROM
(SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
UNION ALL SELECT 12 UNION ALL SELECT 13) AS seq
CROSS JOIN current_week_monday cm
ORDER BY week_start DESC
),
-- 获取 ISO 周信息
iso_week_info AS (
SELECT
week_start,
DATE_ADD(week_start, INTERVAL 6 DAY) AS week_end,
DATE_FORMAT(week_start, '%Y-%U') AS iso_week_num,
DATE_FORMAT(DATE_ADD(week_start, INTERVAL -1 YEAR), '%Y-%U') AS last_year_iso_week_num,
ROW_NUMBER() OVER (ORDER BY week_start DESC) AS week_seq
FROM
week_dates
),
-- 当前年份的科室+手术室数据
current_year_dept AS (
SELECT
i.week_start,
i.week_end,
i.iso_week_num,
i.week_seq,
d.KsDm,
d.KsMc,
o.OperAddrDm,
o.OperAddrMc,
COALESCE(SUM(o.YzSl), 0) AS current_year_count,
COALESCE(SUM(o.AllOperSl), 0) AS current_year_AllOperSl,
COALESCE(SUM(o.SjSl), 0) AS current_year_SjSl
FROM
iso_week_info i
CROSS JOIN (SELECT DISTINCT KsDm, KsMc FROM dw3bqksoper) AS d
LEFT JOIN dw3bqksoper o
ON o.KsDm = d.KsDm
AND o.OperDate BETWEEN i.week_start AND i.week_end
GROUP BY
i.week_start, i.week_end, i.iso_week_num, i.week_seq, d.KsDm, d.KsMc, o.OperAddrDm, o.OperAddrMc
),
-- 去年同期数据
last_year_dept AS (
SELECT
DATE_ADD(i.week_start, INTERVAL -1 YEAR) AS last_year_week_start,
DATE_ADD(DATE_ADD(i.week_start, INTERVAL -1 YEAR),INTERVAL 6 DAY)AS last_year_week_end,
DATE_FORMAT(DATE_ADD(i.week_start, INTERVAL -1 YEAR), '%Y-%U') AS last_year_iso_week_num,
d.KsDm,
d.KsMc,
o.OperAddrDm,
o.OperAddrMc,
COALESCE(SUM(o.YzSl), 0) AS last_year_count,
COALESCE(SUM(o.AllOperSl), 0) AS last_year_AllOperSl,
COALESCE(SUM(o.SjSl), 0) AS last_year_SjSl
FROM
iso_week_info i
CROSS JOIN (SELECT DISTINCT KsDm, KsMc FROM dw3bqksoper) AS d
LEFT JOIN dw3bqksoper o
ON o.KsDm = d.KsDm
AND o.OperDate BETWEEN DATE_ADD(i.week_start, INTERVAL -1 YEAR)
AND DATE_ADD(DATE_ADD(i.week_start, INTERVAL -1 YEAR), INTERVAL 6 DAY)
GROUP BY
DATE_ADD(i.week_start, INTERVAL -1 YEAR),
d.KsDm, d.KsMc, o.OperAddrDm, o.OperAddrMc
)
-- 最终结果集
SELECT
i.iso_week_num AS current_iso_week,
l.last_year_iso_week_num AS last_year_iso_week,
i.week_start,
i.week_end,
i.week_seq,
c.KsDm,
c.KsMc,
c.OperAddrDm,
c.OperAddrMc,
c.current_year_count,
l.last_year_count,
c.current_year_SjSl,
l.last_year_SjSl,
(c.current_year_count - l.last_year_count) AS yoy_change,
CASE
WHEN l.last_year_count > 0 THEN
ROUND((c.current_year_count - l.last_year_count) / l.last_year_count * 100, 2)
ELSE NULL
END AS yoy_change_percent,
c.current_year_AllOperSl,
l.last_year_AllOperSl
FROM
current_year_dept c
LEFT JOIN
last_year_dept l
ON c.week_start = DATE_ADD(l.last_year_week_start, INTERVAL 1 YEAR)
AND c.KsDm = l.KsDm
AND c.OperAddrDm = l.OperAddrDm
INNER JOIN
iso_week_info i
ON c.week_start = i.week_start
WHERE
c.current_year_count >= 0
ORDER BY
i.week_seq,
c.KsDm,
c.OperAddrDm;
请你根据我的代码给我建表sql语句
扫一扫
2068
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
