如何使用MySQL分组连续范围

Question

我有一个包含类别、日期和费率的表格。每个类别在不同的日期可能有不同的费率，但在给定日期，每个类别只能有一个费率。

Id        CatId    Date        Rate 
------  ------   ------------   ---------
000001      12   2009-07-07     1
000002      12   2009-07-08     1
000003      12   2009-07-09     1
000004      12   2009-07-10     2
000005      12   2009-07-15     1
000006      12   2009-07-16     1
000007      13   2009-07-08     1
000008      13   2009-07-09     1
000009      14   2009-07-07     2
000010      14   2009-07-08     1
000010      14   2009-07-10     1

唯一索引（catid，日期，利率） 我希望将每个类别分组为所有连续的日期范围，并仅保留范围的开始和结束。 对于上述示例，我们将得到：

CatId    Begin          End            Rate 
------   ------------   ------------   ---------
12        2009-07-07    2009-07-09     1
12        2009-07-10    2009-07-10     2
12        2009-07-15    2009-07-16     1  
13        2009-07-08    2009-07-09     1  
14        2009-07-07    2009-07-07     2
14        2009-07-08    2009-07-08     1
14        2009-07-10    2009-07-10     1

我在论坛中找到了一个类似的解决方案，但并没有得到预期结果。

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY CatId, Rate ORDER BY [Date]) AS rnd,
                ROW_NUMBER() OVER (PARTITION BY CatId ORDER BY [Date]) AS rn
        FROM    my_table
        )
SELECT  CatId AS catidd, MIN([Date]) as beginn, MAX([Date])as endd, Rate
FROM    q
GROUP BY  CatId, rnd - rn, Rate

请参考 SQL FIDDLE。 如何在mysql中实现相同的功能？ 请帮忙！

- Fouzi

---

为什么你的示例中，当(CatId,Rate)=(14,1)时，结果范围显示为2009-07-08到2009-07-10，而底层表中并没有2009-07-09这一日期？与之相比，(CatId,Rate)=(12,1)由于其不连续性产生了两个结果范围。 - eggyal

谢谢eggyal，现在已经修正了。 - Fouzi

Answer 1

MySQL不支持分析函数，但是您可以使用用户定义变量模拟这种行为：

SELECT   CatID, Begin, MAX(Date) AS End, Rate
FROM (
  SELECT   my_table.*,
           @f:=CONVERT(
             IF(@c<=>CatId AND @r<=>Rate AND DATEDIFF(Date, @d)=1, @f, Date), DATE
           ) AS Begin,
           @c:=CatId, @d:=Date, @r:=Rate
  FROM     my_table JOIN (SELECT @c:=NULL) AS init
  ORDER BY CatId, Rate, Date
) AS t
GROUP BY CatID, Begin, Rate

请在 sqlfiddle 上查看。

- eggyal

---

@vanabel：这是MySQL的NULL安全等于运算符。 - eggyal

@eggyal 很好的回答，点赞。您能否详细解释一下您用于设置变量 @f 值的技巧（与 (SELECT @c:=NULL) 连接）？ - Miljen Mikic

在这种情况下，由于CatID是非空的，因此与@c的NULL初始化值不同，因此IF()将始终在第一条记录上采用false分支。因此，在读取之前忽略/覆盖了@f的初始值，因此初始化是不必要的。 - eggyal

这个 SQL 在第一次运行会返回所有11行，之后会返回7行。这在 MySQL 5.6 和 5.7 上都会发生。上述的 sqlfiddle 总是按预期返回7行。有人知道为什么吗？ - bopomofu

Answer 2

SELECT catid,min(ddate),max(ddate),rate
FROM (
    SELECT
        Catid,
        Ddate,  
        rate,
        @rn := CASE WHEN (@prev <> rate 
           or DATEDIFF(ddate, @prev_date)>1) THEN @rn+1 ELSE @rn END AS rn,
        @prev := rate,
        @prev_id := catid ,
        @prev_date :=ddate
    FROM (
        SELECT CatID,Ddate,rate 
        FROM rankdate
        ORDER BY CatID, Ddate ) AS a , 
        (SELECT @prev := -1, @rn := 0, @prev_id:=0 ,@prev_date:=-1) AS vars      

) T1 group by catid,rn

注意：在MySQL Workspace中，(SELECT @prev := -1, @rn := 0, @prev_id:=0 ,@prev_date:=-1) AS vars这行不是必需的，但在PHP的mysql_query函数中是必需的。 SQL FIDDLE HERE

- sel

---

如果我们删除ID ='000004'的记录，您的查询将返回（开始：2009-07-07，结束：2009-07-16，速率：1），这是不正确的，因为存在间隙，它应该返回（开始：2009-07-07，结束：2009-07-09，速率：1）和（开始：2009-07-15，结束：2009-07-16，速率：1）。[SQL FIDDLE HERE]（http://sqlfiddle.com/#!2/513b2/1） - Fouzi

@BoussahelBachir，我已经编辑了答案。在这种情况下，需要包括datediff的条件来满足您提到的情况。 - sel

你似乎没有测试 @prev_id 的任何地方... 如果两个连续的日期具有相同的 Rate 但不同的 CatId，会发生什么？ - eggyal

由于我使用了按 catid 分组，所以它会在某种程度上按 catid 进行分组，然后再按 rn 进行分组。因此，最后我没有使用 @prev_id。最初，我确实考虑将 @prev_id 包含在条件中。 - sel

Answer 3

我知道我来晚了，但还是要发布一个对我有用的解决方案。 遇到了同样的问题，这是我解决的方法

使用变量找到了一个好的解决方案

SELECT  MIN(id) AS id, MIN(date) AS date, MIN(state) AS state, COUNT(*) cnt
FROM    (
    SELECT  @r := @r + (@state != state OR @state IS NULL) AS gn,
            @state := state AS sn,
            s.id, s.date, s.state
    FROM    (
            SELECT  @r := 0,
                    @state := NULL
            ) vars,
            t_range s
    ORDER BY
            date, state
    ) q
GROUP BY gn

更多细节请参见：https://explainextended.com/2009/07/24/mysql-grouping-continuous-ranges/

- MafazR