MySQL动态SQL实现上季度各月患者数据查询优化问询
好问题!要避免重复写UNION拼接查询,我们可以通过动态生成上季度的月份范围,再将原查询与这个范围关联来实现。下面分步骤给你优化方案:
优化方案:动态获取上季度每月数据
1. 先优化原查询的日期计算(减少重复函数调用)
原查询中多次调用STR_TO_DATE(CONCAT(...)),不仅冗余还影响性能。我们可以先通过CTE把用户的日期计算一次,复用结果:
WITH user_exp_dates AS ( SELECT m.user_id, -- 一次性计算患者的日期,同时过滤无效日期 STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') AS exp_date, IFNULL(um5.meta_value, 0) AS vID FROM wp_usermeta m INNER JOIN wp_usermeta um1 ON m.user_id = um1.user_id AND um1.meta_key = 'year' INNER JOIN wp_usermeta um2 ON m.user_id = um2.user_id AND um2.meta_key = 'month' INNER JOIN wp_usermeta um3 ON m.user_id = um3.user_id AND um3.meta_key = 'day' LEFT JOIN wp_usermeta um5 ON m.user_id = um5.user_id AND um5.meta_key = 'vID' WHERE STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') IS NOT NULL )
2. 动态生成上季度的三个月份范围
我们用CTE生成上季度每个月的起始和结束日期,不用硬编码日期。这里用变量简化重复的日期计算:
SET @prev_quarter_start = CASE WHEN QUARTER(CURDATE()) = 1 THEN DATE_FORMAT(CURDATE() - INTERVAL 1 YEAR, '%Y-10-01') WHEN QUARTER(CURDATE()) = 2 THEN DATE_FORMAT(CURDATE(), '%Y-01-01') WHEN QUARTER(CURDATE()) = 3 THEN DATE_FORMAT(CURDATE(), '%Y-04-01') WHEN QUARTER(CURDATE()) = 4 THEN DATE_FORMAT(CURDATE(), '%Y-07-01') END; WITH quarter_months AS ( SELECT @prev_quarter_start AS month_start, LAST_DAY(@prev_quarter_start) AS month_end UNION ALL -- 生成第二个月 SELECT DATE_ADD(month_start, INTERVAL 1 MONTH), LAST_DAY(DATE_ADD(month_start, INTERVAL 1 MONTH)) FROM quarter_months -- 生成第三个月后停止递归 WHERE month_start < DATE_ADD(@prev_quarter_start, INTERVAL 2 MONTH) )
3. 关联两个CTE,获取最终结果
把用户数据和上季度月份范围关联,就能一次性拿到每个月的结果:
-- 完整可执行查询 SET @prev_quarter_start = CASE WHEN QUARTER(CURDATE()) = 1 THEN DATE_FORMAT(CURDATE() - INTERVAL 1 YEAR, '%Y-10-01') WHEN QUARTER(CURDATE()) = 2 THEN DATE_FORMAT(CURDATE(), '%Y-01-01') WHEN QUARTER(CURDATE()) = 3 THEN DATE_FORMAT(CURDATE(), '%Y-04-01') WHEN QUARTER(CURDATE()) = 4 THEN DATE_FORMAT(CURDATE(), '%Y-07-01') END; WITH user_exp_dates AS ( SELECT m.user_id, STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') AS exp_date, IFNULL(um5.meta_value, 0) AS vID FROM wp_usermeta m INNER JOIN wp_usermeta um1 ON m.user_id = um1.user_id AND um1.meta_key = 'year' INNER JOIN wp_usermeta um2 ON m.user_id = um2.user_id AND um2.meta_key = 'month' INNER JOIN wp_usermeta um3 ON m.user_id = um3.user_id AND um3.meta_key = 'day' LEFT JOIN wp_usermeta um5 ON m.user_id = um5.user_id AND um5.meta_key = 'vID' WHERE STR_TO_DATE(CONCAT(um1.meta_value, '-', um2.meta_value, '-', um3.meta_value), '%Y-%m-%d') IS NOT NULL ), quarter_months AS ( SELECT @prev_quarter_start AS month_start, LAST_DAY(@prev_quarter_start) AS month_end UNION ALL SELECT DATE_ADD(month_start, INTERVAL 1 MONTH), LAST_DAY(DATE_ADD(month_start, INTERVAL 1 MONTH)) FROM quarter_months WHERE month_start < DATE_ADD(@prev_quarter_start, INTERVAL 2 MONTH) ) SELECT u.user_id, DATE(u.exp_date) AS DateEXP, u.vID, -- 可选:标记该数据属于上季度的哪个月 DATE_FORMAT(q.month_start, '%Y-%m') AS quarter_month FROM user_exp_dates u INNER JOIN quarter_months q ON u.exp_date BETWEEN q.month_start AND q.month_end -- 如果同一个用户当月有多条记录,按用户+月份分组 GROUP BY u.user_id, q.month_start;
额外优化建议
- 如果
wp_usermeta数据量很大,建议给user_id和meta_key建立联合索引:CREATE INDEX idx_usermeta_user_key ON wp_usermeta(user_id, meta_key);,能大幅提升JOIN的性能。 - 长远来看,建议把用户的目标日期存储为DATE类型的字段,而不是拆分到year/month/day三个meta字段,这样后续查询会更简单高效。
内容的提问来源于stack exchange,提问作者choloboy
相关产品推荐
相关产品推荐

