如何使用MySQL对连续范围进行分组 [英] How to group continuous ranges using MySQL

问题描述

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

I have a table that contains categories, dates and rates. Each category can have different rates for different dates, one category can have only one rate at a given date.

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

唯一索引(类别，日期，费率) 我想为每个类别将所有连续的日期范围分组，并仅保留范围的开始和结束. 对于前面的示例，我们将:

Unique index (catid, Date, Rate) I would like for each category to group all continuous dates ranges and keep only the begin and the end of the range. For the previous example, we would have:

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

我在论坛中找到了类似的解决方案，但没有找到准确给出结果

I found a similar solution in the forum which did not exactly give the result

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中做同样的事情? 请帮忙！

SEE SQL FIDDLE How can I do the same thing in mysql? Please help!

推荐答案

MySQL不支持解析功能，但是您可以使用

MySQL doesn't support analytic functions, but you can emulate such behaviour with user-defined variables:

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 上查看.

这篇关于如何使用MySQL对连续范围进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！