Geek Answers Handbook

How to group continuous ranges using MySQL

I have a table that contains categories, dates and bids. Each category can have different bids for different dates, one category can have only one bid for a certain 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 each category to group all ranges of continuous dates and keep only the beginning and end of the range. In 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 that definitely didn’t give a 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

CM. SQL FIDDLE How can I do the same in mysql? Please, help!

+7
source share
3 answers

MySQL does not support analytic functions, but you can emulate this behavior 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

Take a look at sqlfiddle .

+6
source 
 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

Note: line (SELECT @prev: = -1, @rn: = 0, @prev_id: = 0, @prev_date: = - 1) AS vars is not required in Mysql Workspace, but it is in the mysql_query PHP function.

SQL FIDDLE HERE

+3
source

I know I'm late, still posting a solution that worked for me. Had the same problem, this is how I got it

Found a good solution using variables

 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

More details: https://explainextended.com/2009/07/24/mysql-grouping-continuous-ranges/

0
source

More articles:


All Articles