Oracle下基于店铺营业时间计算投诉有效处理时长

表结构定义（示例） #

先明确两张核心表的结构，方便后续逻辑实现：

-- 店铺营业时间表：存储各店铺每周每日的营业起止时间
CREATE TABLE SHOP_BUSINESS_HOURS (
    SHOP_ID VARCHAR2(20) PRIMARY KEY,
    DAY_OF_WEEK NUMBER(1) CHECK (DAY_OF_WEEK BETWEEN 1 AND 7), -- 1=周六，2=周日，3=周一...7=周五
    OPEN_TIME VARCHAR2(5), -- 格式HH24:MI，如'11:00'
    CLOSE_TIME VARCHAR2(5) -- 格式HH24:MI，如'22:00'
);

-- 投诉表：存储投诉的提交、解决时间及关联店铺
CREATE TABLE COMPLAINTS (
    COMPLAINT_ID VARCHAR2(20) PRIMARY KEY,
    SHOP_ID VARCHAR2(20) REFERENCES SHOP_BUSINESS_HOURS(SHOP_ID),
    SUBMIT_TIME TIMESTAMP,
    RESOLVE_TIME TIMESTAMP
);

核心思路 #

将投诉的时间范围拆分为提交当天、中间完整天数、解决当天三个部分，分别计算每个日期内的有效营业时段重叠时长，最后求和得到总有效处理时长。

步骤1：编写单天有效时长计算函数 #

自定义函数用于计算单个日期内，投诉时间与店铺营业时段的重叠分钟数：

CREATE OR REPLACE FUNCTION CALC_DAY_EFF_MINUTES(
    p_shop_id VARCHAR2,
    p_date DATE,
    p_start_time TIMESTAMP,
    p_end_time TIMESTAMP
) RETURN NUMBER IS
    v_day_of_week NUMBER;
    v_open_time DATE;
    v_close_time DATE;
    v_day_start DATE;
    v_day_end DATE;
    v_overlap_start DATE;
    v_overlap_end DATE;
BEGIN
    -- 转换Oracle默认星期格式到业务定义（1=周六）
    SELECT CASE TO_CHAR(p_date, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')
               WHEN '1' THEN 2 -- 周日映射为业务定义的2
               WHEN '2' THEN 3 -- 周一映射为3
               WHEN '3' THEN 4 -- 周二映射为4
               WHEN '4' THEN 5 -- 周三映射为5
               WHEN '5' THEN 6 -- 周四映射为6
               WHEN '6' THEN 7 -- 周五映射为7
               WHEN '7' THEN 1 -- 周六映射为1
           END INTO v_day_of_week
    FROM DUAL;

    -- 获取当前日期对应的店铺营业起止时间
    SELECT TO_DATE(OPEN_TIME, 'HH24:MI'), TO_DATE(CLOSE_TIME, 'HH24:MI')
    INTO v_open_time, v_close_time
    FROM SHOP_BUSINESS_HOURS
    WHERE SHOP_ID = p_shop_id AND DAY_OF_WEEK = v_day_of_week;

    -- 定义当天的时间边界（0点到次日0点）
    v_day_start := TRUNC(p_date);
    v_day_end := TRUNC(p_date) + 1;

    -- 计算投诉时间与营业时段的重叠区间
    v_overlap_start := GREATEST(
        p_start_time,
        v_day_start + (v_open_time - TRUNC(v_open_time)) -- 营业开始时间转当天日期
    );
    v_overlap_end := LEAST(
        p_end_time,
        v_day_start + (v_close_time - TRUNC(v_close_time)) -- 营业结束时间转当天日期
    );

    -- 无重叠则返回0，否则计算重叠分钟数
    IF v_overlap_end <= v_overlap_start THEN
        RETURN 0;
    ELSE
        RETURN ROUND((v_overlap_end - v_overlap_start) * 24 * 60);
    END IF;
EXCEPTION
    -- 店铺当天无营业记录，返回0
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
/

步骤2：跨多天场景的总时长计算 #

通过CONNECT BY生成投诉时间范围内的所有日期，逐个调用上述函数求和：

SELECT
    c.COMPLAINT_ID,
    c.SHOP_ID,
    c.SUBMIT_TIME,
    c.RESOLVE_TIME,
    SUM(CALC_DAY_EFF_MINUTES(c.SHOP_ID, dt.dt, c.SUBMIT_TIME, c.RESOLVE_TIME)) AS EFF_PROCESS_MINUTES
FROM COMPLAINTS c
-- 生成提交日期到解决日期的所有日期序列
JOIN (
    SELECT TRUNC(c.SUBMIT_TIME) + LEVEL - 1 AS dt
    FROM COMPLAINTS c
    CONNECT BY LEVEL <= TRUNC(c.RESOLVE_TIME) - TRUNC(c.SUBMIT_TIME) + 1
) dt ON dt.dt BETWEEN TRUNC(c.SUBMIT_TIME) AND TRUNC(c.RESOLVE_TIME)
WHERE c.RESOLVE_TIME > c.SUBMIT_TIME -- 过滤异常的时间顺序
GROUP BY c.COMPLAINT_ID, c.SHOP_ID, c.SUBMIT_TIME, c.RESOLVE_TIME
ORDER BY c.COMPLAINT_ID;

关键注意事项 #

• 星期映射兼容性：使用NLS_DATE_LANGUAGE=AMERICAN避免系统语言对星期计算的影响
• 非营业日处理：函数通过NO_DATA_FOUND异常处理店铺无营业记录的情况，直接返回0
• 时间精度：用ROUND处理分钟数，避免因时间戳精度产生的小数误差

内容的提问来源于stack exchange，提问作者deepankar srigyan