首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何使用MySQL对连续范围进行分组

问如何使用MySQL对连续范围进行分组
EN

Stack Overflow用户
提问于 2012-09-05 14:59:53
回答 3查看 3.1K关注 0票数 7

我有一个表,其中包含类别,日期和价格。对于不同的日期,每个类别可以有不同的汇率,一个类别在给定日期只能有一个汇率。

代码语言:javascript
复制
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,日期,速率)我想为每个类别,以分组所有连续的日期范围,只保留开始和结束的范围。对于上一个示例,我们将具有:

代码语言:javascript
复制
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

我在the forum中找到了一个类似的解决方案,但并没有给出确切的结果

代码语言:javascript
复制
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中做同样的事情?请帮帮我!

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2012-09-05 15:46:39

MySQL不支持分析函数,但是您可以用user-defined variables模拟这种行为

代码语言:javascript
复制
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上查看。

票数 6
EN

Stack Overflow用户

发布于 2012-09-05 15:18:41

代码语言:javascript
复制
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 PHP0,@prev_id:=0,@prev_date := -1) AS vars这一行不是必需的,但它在PHP mysql_query函数中。

SQL FIDDLE HERE

票数 4
EN

Stack Overflow用户

发布于 2016-05-26 20:47:29

我知道我来晚了,但仍然发布了一个对我有效的解决方案。有同样的问题,下面是我是如何得到它的

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

代码语言:javascript
复制
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/

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/12276087

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档