You need to enable JavaScript to run this app. MySQL动态SQL实现上季度各月患者数据查询优化问询
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.05.15 03:49:55