MySQL 连续日期序列的实现

Question

我需要一个 MySQL 实现来回答这个问题：

连续的日期

我有完全相同的问题 - 但（据我了解）MySQL 不支持 DENSE_RANK() 或 ROW_NUMBER()

如果有人能指出我的正确方向方向，那就太好了。我尝试使用此处找到的基于月份的查询版本： forums.mysql.com：按连续日期分组 和 此处，但是查询陷入了 6,000 个“用户”的 300,000 条不太连续的记录，

非常感谢

Answer 1

好的。因此，除了“自上次范围结束以来的月份”字段之外，这个答案应该为您提供所需的一切。也许您可以在此基础上自行解决；-)。我已将 #data 表（在连续的日期序列帖子中提到）重命名为 payment：

CREATE TABLE payment
(
Contact_reference VARCHAR(55),
Date_payment DATETIME,
Payment_value double
);

INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2003-06-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2004-06-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2004-12-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-04-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-05-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-06-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-07-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-08-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-09-08',12.82);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-10-10',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-11-10',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2005-12-10',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-01-10',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-02-10',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-02-28',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-04-12',12.8205);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2006-05-10',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-06-11',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-07-10',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-08-09',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-09-10',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-10-09',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-11-09',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2007-12-10',19.2308);
INSERT INTO payment VALUES ('18EC3CD2-3065-4FF4-BE40-000004228590','2008-01-10',19.2308);

select Contact_reference,
consecStartDate as Range_start,
max(Date_payment) as Range_end,
count(*) as Payments,
sum(Payment_value) as value
from
(
select Contact_reference,
case when year(Date_payment)*12 + month(Date_payment) - @curMonthNum  <= 1 and @curRef = Contact_reference then 'Y' else 'N' end as consec,
case when year(Date_payment)*12 + month(Date_payment) - @curMonthNum  <= 1 and @curRef = Contact_reference then @consecStartDate := @consecStartDate else @consecStartDate := Date_payment  end as consecStartDate,
Date_payment,Payment_value,
@curMonthNum := year(Date_payment)*12 + month(Date_payment),
@curRef := Contact_reference
from payment
inner join (SELECT @consecStartDate := null,@curMonthNum := null,@consecStart := null,@curRef := null) as t
order by Contact_reference desc,Date_payment asc
) n
group by Contact_reference,consecStartDate;

就像我说的 - 它不会给你从最后一个范围结束，但我认为答案的一部分总比没有好！