You need to enable JavaScript to run this app. 合并3个MySQL查询 实现用户服务历史及offer时长统计
优惠活动
大模型
产品
解决方案
定价
更多

合并3个MySQL查询 实现用户服务历史及offer时长统计

重复与统计翻倍的核心原因
  • 基础查询本身是一对多的行展开逻辑:1个用户的N条购买记录、N条服务请求、M条服务操作关联时会生成多行结果,此时如果直接将两个全局sum的聚合值关联到每一行,每匹配一行就会重复挂载一次聚合值,最终统计结果会随匹配行数成倍放大,这就是重复、数值翻倍的根因
  • 原查询中c.codContrato in (select MAX(codContrato) from contratan where usuario = 17)的过滤条件,会把用户历史购买的所有其他时长包全部排除,无法展示全周期的offer数据
  • 全局sum的统计逻辑没有匹配业务的扣减顺序,无法计算每一次服务操作执行时的实时剩余时长
正确的实现思路

拆分独立统计维度,避免行展开带来的重复计算

  • 独立计算用户维度的累计总购买时长,生成单行的用户级统计值,不直接挂接在多行的服务操作行上
  • 对所有服务操作按执行时间排序,用窗口函数计算截至每一次操作点的累计已消耗时长,匹配业务按操作先后扣减时长的规则

调整关联逻辑

  • 移除原RIGHT JOIN的写法,明确关联层级:用户 -> 时长包购买记录(contratan关联bonos) -> 服务请求(asistencia) -> 服务操作(actuacion)
  • 所有关联条件严格匹配表外键,避免产生跨表笛卡尔积
  • 移除过滤单条最新合同的错误条件,保留用户全部历史关联数据
修正后的参考查询(以用户ID=17为例)
WITH
-- 计算用户全周期累计可使用的总时长,仅生成单行用户级统计
user_total_purchase AS (
    SELECT
        c.usuario,
        SUM(b.minutos) AS total_available_minutes
    FROM contratan c
    INNER JOIN bonos b ON c.bono = b.codBono
    WHERE c.usuario = 17
    GROUP BY c.usuario
),
-- 计算每一条服务操作对应的累计消耗时长,按执行时间排序
actuacion_record_with_cumulative_usage AS (
    SELECT
        ac.codActuacion,
        ac.tiempoEmpleado,
        ac.fechaEjecucion,
        a.codAsistencia,
        a.fechaSolicitud,
        a.usuario,
        -- 按操作执行时间累加已消耗时长,得到截至当前操作的累计值
        SUM(ac.tiempoEmpleado) OVER (
            ORDER BY ac.fechaEjecucion
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_used_minutes
    FROM actuacion ac
    INNER JOIN asistencia a ON ac.asistencia = a.codAsistencia
    WHERE a.usuario = 17
)
-- 关联所有维度,输出需要的字段
SELECT
    b.tipo AS offer_type,
    b.minutos AS offer_total_minutes,
    c.fechaContratacion AS offer_purchase_date,
    ar.fechaSolicitud AS service_request_date,
    ar.fechaEjecucion AS service_operation_date,
    ar.tiempoEmpleado AS consumed_duration_of_current_operation,
    utp.total_available_minutes - ar.cumulative_used_minutes AS remaining_total_minutes
FROM contratan c
INNER JOIN bonos b ON c.bono = b.codBono
CROSS JOIN user_total_purchase utp
LEFT JOIN actuacion_record_with_cumulative_usage ar ON ar.usuario = c.usuario
ORDER BY ar.fechaEjecucion;
避坑要点
  • 不要将全局聚合子查询直接嵌入JOIN关联,否则在一对多的行展开过程中,聚合值会被重复挂载,导致统计翻倍
  • 计算历史剩余时长时,必须按操作执行时间做窗口累加,不能直接使用全局总消耗值,否则无法匹配“每次操作发生后展示当时剩余时长”的历史查询需求
  • 移除原查询中仅保留最新一条合同的过滤逻辑,否则无法展示用户历史上购买的所有时长包信息

内容的提问来源于stack exchange,提问作者daviserraalonso

相关产品推荐
方舟 Agent Plan

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

最近更新时间:2026.11.15 14:06:03