30.Insert Interval-插入区间(容易题)

本文介绍了一种区间合并算法,该算法能够将一个新区间插入到一个已排序且无重叠的区间列表中,并确保合并后的区间列表仍保持有序且不重叠的状态。文章通过具体的例子演示了插入新区间的过程,并详细解释了三种可能的情况及其处理方式。

插入区间

1.题目

给出一个无重叠的按照区间起始端点排序的区间列表。

在列表中插入一个新的区间,你要确保列表中的区间仍然有序且不重叠(如果有必要的话,可以合并区间)。

2.样例

插入区间[2, 5] 到 [[1,2], [5,9]],我们得到 [[1,9]]。

插入区间[3, 4] 到 [[1,2], [5,9]],我们得到 [[1,2], [3,4], [5,9]]。

3.题解

将一个新的区间插入到已存在的区间列表中无非有三种情况:
1.新区间位于区间列表中某个区间的前面,即newInterval.end < intervals.get(i).start。
2.新区间位于区间列表中某个区间的后面,即newInterval.start > intervals.get(i).end。
3.新区间与区间列表中某个区间或某几个连续区间相交,即将newInterval.end与相交区间的end值相比,取其大者。

/**
 * Definition of Interval:
 * public classs Interval {
 *     int start, end;
 *     Interval(int start, int end) {
 *         this.start = start;
 *         this.end = end;
 *     }
 */

class Solution {
    /**
     * Insert newInterval into intervals.
     * @param intervals: Sorted interval list.
     * @param newInterval: A new interval.
     * @return: A new sorted interval list.
     */
    public ArrayList<Interval> insert(ArrayList<Interval> intervals, Interval newInterval) {
        if (intervals.size() == 0)
        {
            intervals.add(newInterval);
            return intervals;
        }

        for (int i=0;i<intervals.size();i++)
        {
            if (newInterval.end < intervals.get(i).start)
            {
                intervals.add(i,newInterval);
                break;
            }
            else if (newInterval.start > intervals.get(i).end)
            {
                if (i == intervals.size()-1)
                {
                    intervals.add(newInterval);
                }
                continue;
            }
            else
            {
                intervals.get(i).start = Math.min(intervals.get(i).start,newInterval.start);
                intervals.get(i).end = Math.max(intervals.get(i).end,newInterval.end);
                while (i<intervals.size()-1)
                {
                    if (intervals.get(i).end >= intervals.get(i+1).start)
                    {
                        intervals.get(i).end = Math.max(intervals.get(i).end,intervals.get(i+1).end);
                        intervals.remove(i+1);
                    }
                    i++;
                }
            }
        }

        return intervals;
    }
}

Last Update 2016.8.25

明白了!你希望新增的字段 `WeekLabel` 不仅标识“本周”、“过去第1周”等语义标签,还要**显示对应的完整周区间**,例如: - **本周**:2025-07-28 至 2025-08-03 - **过去第1周**:2025-07-21 至 2025-07-27 - **过去第2周**:2025-07-14 至 2025-07-20 - ... - **过去第13周**:2025-04-28 至 2025-05-04 --- ## ✅ 一、更新建表语句(新增字段) 我们将在 `dm2yj_kdyy_week_tj` 表中新增两个字段,用于记录周的起止日期和带日期区间的标签: ```sql ALTER TABLE dm2yj_kdyy_week_tj ADD COLUMN WeekStartDate DATE COMMENT &#39;本周起始日期(周一)&#39;, ADD COLUMN WeekEndDate DATE COMMENT &#39;本周结束日期(周日)&#39;, ADD COLUMN WeekLabelWithDate VARCHAR(50) COMMENT &#39;带日期的周标签,如:本周(2025-07-28 - 2025-08-03)&#39;; ``` --- ## ✅ 二、更新抽取逻辑(计算带日期的 WeekLabel) 以下是完整的插入语句,包含周起止日期和带日期格式的 `WeekLabelWithDate` 字段: ```sql INSERT INTO dm2yj_kdyy_week_tj ( GzWeek, YjFlDm, YjFl, YjKs, ExamType, CheckFl, Kd_Cnt, Kd_Jf_Cnt, Yy_Dt_Cnt, Yy_Gt_Cnt, Yy_Cnt1, Yy_Cnt2, Yy_Cnt3, Yy_Cnt4, ZdYy_Xm_Cnt, Kd_Wc_Cnt, Kd_Wc_Cnt1, Time1_Wc_Rate, Time2_Wc_Rate, Time3_Wc_Rate, Time4_Wc_Rate, Time5_Wc_Rate, Time6_Wc_Rate, Dj_Wjc_Cnt, Jc_Wbg_Cnt, Yy_Wc_Cnt, Yy_Wc_Rate, OpenSl_Cnt, KcSl_Cnt, KcSl2_Cnt, Time1_Wc_Num, Time2_Wc_Num, Time3_Wc_Num, Time4_Wc_Num, Time5_Wc_Num, Time6_Wc_Num, Time_Wc_Den, Yy_Wc_Num, Yy_Wc_Den, WeekStartDate, WeekEndDate, WeekLabelWithDate ) SELECT CONCAT(YEAR(a.GzRq), &#39;-W&#39;, LPAD(WEEK(a.GzRq, 1), 2, &#39;0&#39;)) AS GzWeek, a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.CheckFl, SUM(a.Kd_Cnt), SUM(a.Kd_Jf_Cnt), SUM(a.Yy_Dt_Cnt), SUM(a.Yy_Gt_Cnt), SUM(a.Yy_Cnt1), SUM(a.Yy_Cnt2), SUM(a.Yy_Cnt3), SUM(a.Yy_Cnt4), SUM(a.ZdYy_Xm_Cnt), SUM(a.Kd_Wc_Cnt), SUM(a.Kd_Wc_Cnt1), ROUND(SUM(a.Time1_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), ROUND(SUM(a.Time2_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), ROUND(SUM(a.Time3_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), ROUND(SUM(a.Time4_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), ROUND(SUM(a.Time5_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), ROUND(SUM(a.Time6_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4), SUM(a.Dj_Wjc_Cnt), SUM(a.Jc_Wbg_Cnt), SUM(a.Yy_Wc_Cnt), ROUND(SUM(a.Yy_Wc_Num) / NULLIF(SUM(a.Yy_Wc_Den), 0), 4), IFNULL(SUM(c.OpenSl_Cnt), 0), IF(IFNULL(SUM(c.OpenSl_Cnt), 0) - IFNULL(SUM(b.Yy_cnt), 0) < 0, 0, IFNULL(SUM(c.OpenSl_Cnt), 0) - IFNULL(SUM(b.Yy_cnt), 0)), IF(IFNULL(SUM(e.OpenSl_Cnt), 0) - IFNULL(SUM(d.Yy_cnt), 0) < 0, 0, IFNULL(SUM(e.OpenSl_Cnt), 0) - IFNULL(SUM(d.Yy_cnt), 0)), SUM(a.Time1_Wc_Num), SUM(a.Time2_Wc_Num), SUM(a.Time3_Wc_Num), SUM(a.Time4_Wc_Num), SUM(a.Time5_Wc_Num), SUM(a.Time6_Wc_Num), SUM(a.Time_Wc_Den), SUM(a.Yy_Wc_Num), SUM(a.Yy_Wc_Den), DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY AS WeekStartDate, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY AS WeekEndDate, CASE WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 0 AND 6 THEN CONCAT(&#39;本周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 7 AND 13 THEN CONCAT(&#39;过去第1周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 14 AND 20 THEN CONCAT(&#39;过去第2周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 21 AND 27 THEN CONCAT(&#39;过去第3周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 28 AND 34 THEN CONCAT(&#39;过去第4周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 35 AND 41 THEN CONCAT(&#39;过去第5周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 42 AND 48 THEN CONCAT(&#39;过去第6周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 49 AND 55 THEN CONCAT(&#39;过去第7周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 56 AND 62 THEN CONCAT(&#39;过去第8周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 63 AND 69 THEN CONCAT(&#39;过去第9周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 70 AND 76 THEN CONCAT(&#39;过去第10周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 77 AND 83 THEN CONCAT(&#39;过去第11周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 84 AND 90 THEN CONCAT(&#39;过去第12周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) WHEN DATEDIFF(CURRENT_DATE, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY) BETWEEN 91 AND 97 THEN CONCAT(&#39;过去第13周(&#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, &#39;%Y-%m-%d&#39;), &#39; - &#39;, DATE_FORMAT(DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, &#39;%Y-%m-%d&#39;), &#39;)&#39;) ELSE &#39;历史周&#39; END AS WeekLabelWithDate FROM dw2yj_kdyy_tj a -- 此处保留原 JOIN 逻辑 -- 省略部分 JOIN 子句,保持与你原始逻辑一致 WHERE a.GzRq >= CURRENT_DATE - INTERVAL 14 WEEK GROUP BY CONCAT(YEAR(a.GzRq), &#39;-W&#39;, LPAD(WEEK(a.GzRq, 1), 2, &#39;0&#39;)), a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.CheckFl, DATE_FORMAT(a.GzRq, &#39;%Y-%m-%d&#39;) - INTERVAL WEEKDAY(a.GzRq) DAY, WeekLabelWithDate ORDER BY WeekStartDate DESC; ``` --- ## ✅ 三、关键说明 - `WeekStartDate`:本周周一日期。 - `WeekEndDate`:本周周日日期。 - `WeekLabelWithDate`:格式如 `本周(2025-07-28 - 2025-08-03)`,便于在 BI 报表中直接使用。 - 使用 `WEEKDAY()` 函数获取周一日期,确保符合 ISO 周标准。 - 所有字段都支持动态生成,无需硬编码。 --- ## ✅ 四、相关问
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值