我有一个表,其中包含类别,日期和价格。对于不同的日期,每个类别可以有不同的汇率,一个类别在给定日期只能有一个汇率。
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我在the forum中找到了一个类似的解决方案,但并没有给出确切的结果
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中做同样的事情?请帮帮我!
发布于 2012-09-05 15:46:39
MySQL不支持分析函数,但是您可以用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上查看。
发布于 2012-09-05 15:18:41
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函数中。
发布于 2016-05-26 20:47:29
我知道我来晚了,但仍然发布了一个对我有效的解决方案。有同样的问题,下面是我是如何得到它的
使用变量找到了一个很好的解决方案
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/
https://stackoverflow.com/questions/12276087
复制相似问题