MySQL为给定状态填写两个日期之间的缺少日期 [英] MySQL Fill in missing dates between two dates for a given status

问题描述

我有一个项目的数据集。项目从头到尾更改状态，状态更改的日期记录在表中（表命名为事件 - 不是我的选择）。看起来像这样（简化）：

 日期状态
 2015-06-01开始
 2015- 06-03阶段2 
 2015-06-07阶段3 
  

在任何给定的日期范围（待动态确定）我希望能够看到哪个项目处于哪个状态。但是，对于数据使用BETWEEN或其他查询只能在该期间拉取其状态更改的项目，而不是仍处于给定状态的项目。

我目前在Excel中创建了一个非常笨重的解决方案，它将状态更改日期之间的行复制到新行中，如下所示：

 日期状态
 2015-06-01项目开始
 2015-06-02项目启动（复制）
 2015-06-03阶段2 
 2015-06- 04阶段2（复制）
 2015-06-05阶段2（复制）
 2015-06-06阶段2（复制）
 2015-06-07阶段3 
  

此解决方案允许我查询项目状态，例如2015-06-06，看看它仍然是在第2阶段。

有没有办法我可以使用mySql来提取相同的数据，但作为查询的输出？我听说有一些建议使用日历表，但我不知道这将如何工作。我也看过有人推荐交叉加入，但是再一次，我从描述中看不出如何工作。

提前感谢您的帮助！ / p>

解决方案

计划

• 通过交叉加入数字和date_add在日历时段内创建日历表。


• 将您的数据加入到日期源日期< =日历日期


• 取最大日期< =日历日期


• 加入原始数据源以获取状态

设置

  drop table if exists calendar_t; 
 CREATE TABLE calendar_t（
 id integer primary key auto_increment not null，
`date` date not null，
 day varchar（9）not null，
 month varchar 13）not null，
`year` integer not null 
）; 
 
 drop view if exists digits_v; 
创建视图digits_v 
作为
选择0作为n 
联合全部
选择1 
联合全部
选择2 
联合全部
选择3 
联合全部
选择4 
联合全部
选择5 
联合全部
选择6 
联合全部
选择7 
联合全部
选择8 
联合全部
选择9 
; 
 
 insert into calendar_t 
（`date`，day，month，`year`）
 select 
 date_add（'2015-01-01'，interval 100 * a2.n + 10 * a1.n + a0.n day）as`date`，
 dayname（date_add（'2015-01-01'，interval 100 * a2.n + 10 * a1.n + a0 .n日））当天，
 monthname（date_add（'2015-01-01'，间隔100 * a2.n + 10 * a1.n + a0.n天））作为月份，
年（date_add（'2015-01-01'，interval 100 * a2.n + 10 * a1.n + a0.n day））as $ year $ $ $ $ $ $ $ $ $ $ $ $ cross join digits_v a1 
 cross join digits_v a0 
 order by date_add（'2015-01-01'，interval 100 * a2.n + 10 * a1.n + a0.n day）
 ; 
 
 drop table if exists example; 
创建表示例
（
`date` date not null，
 status varchar（23）not null 
）; 
 
插入示例
（`date`，状态）
值
（'2015-06-01'，'开始'），
 '2015-06-03'，'Stage 2'），
（'2015-06-07'，'Stage 3'）
; 
  

查询

 从
中选择cal_date，mdate，ex2.status 
（
选择cal_date，max（ex_date）as mdate 
 from 
（
 select cal.`date` as cal_date，ex.`date` as ex_date 
 from calendar_t cal 
 inner join example ex 
 on ex.`date`< = cal.`date` 
）maxs 
 group by cal_date 
）m2 
内部连接示例ex2 
 on m2.mdate = ex2.`date` 
 - 选择一个合理的结束日期过滤.. 
其中cal_date< = date（'2015-06-15'）
按cal_date命令
; 
  

输出

  + ------------------------ + ------------- ----------- + --------- + 
 | cal_date | mdate |状态| 
 + ------------------------ + -------------------- ---- + --------- + 
 | 2015年6月01日00:00:00 | 2015年6月01日00:00:00 |开始| 
 | 2015年6月02日00:00:00 | 2015年6月01日00:00:00 |开始| 
 | 2015年6月3日00:00:00 | 2015年6月3日00:00:00 |第2阶段| 
 | 2015年6月04日00:00:00 | 2015年6月3日00:00:00 |阶段2 | 
 | 2015年6月05日00:00:00 | 2015年6月3日00:00:00 |阶段2 | 
 | 2015年6月6日00:00:00 | 2015年6月3日00:00:00 |阶段2 | 
 | 2015年6月7日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月08日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月09日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月10日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月11日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月12日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月13日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月14日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 | 2015年6月15日00:00:00 | 2015年6月7日00:00:00 |阶段3 | 
 + ------------------------ + -------------------- ---- + --------- + 
  

sqlfiddle

---

参考

• SO mysql序列生成


• sql cross join

I have a data set of projects. The projects change status from beginning to end, and the date of the status change is logged in a table (table is named "events" - not my choice). Would look like this (simplified):

Date        Status
2015-06-01  Start
2015-06-03  Stage 2
2015-06-07  Stage 3

In any given date range (to be determined dynamically) I want to be able to see which projects are at which status. However, using BETWEEN or other query against the data will only pull those projects whose status changed during that period, not the ones that are still at a given status.

I've currently created a very clunky solution in Excel which copies rows into new rows between status change dates, like so:

Date          Status  
2015-06-01    Project start
2015-06-02    Project start (copied)
2015-06-03    Stage 2 
2015-06-04    Stage 2 (copied)
2015-06-05    Stage 2 (copied)
2015-06-06    Stage 2 (copied)
2015-06-07    Stage 3

This solution allows me to query the status for the project on, say, 2015-06-06 and see that it is still at Stage 2.

Is there some way I can use mySql to pull this same data, but as output to a query? I've heard some suggest to use a Calendar table, but I'm not sure how that would work. I've also seen someone recommend a Cross Join, but again, I couldn't understand from the description how that would work.

Thanks in advance for your help!

解决方案

plan

• create calendar table by cross joining digits and date_add over calendar period..
• join your data to calendar source with date <= calendar date
• take max of date <= calendar date
• join back to original data source to get status

setup

drop table if exists calendar_t;
CREATE TABLE calendar_t (
  id integer primary key auto_increment not null,
  `date` date not null,
  day varchar(9) not null,
  month varchar(13) not null,
  `year` integer not null
);

drop view if exists digits_v;
create view digits_v
as
select 0 as n
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
;

insert into calendar_t
( `date`, day, month, `year` )
select 
date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day) as `date`,
dayname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as day,
monthname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as month,
year(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) as `year`
from
digits_v a2
cross join digits_v a1
cross join digits_v a0
order by date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)
;

drop table if exists example;
create table example
(
  `date` date not null,
  status varchar(23) not null
);

insert into example
( `date`, status )
values
( '2015-06-01',  'Start'   ),
( '2015-06-03',  'Stage 2' ),
( '2015-06-07',  'Stage 3' )
;

query

select cal_date, mdate, ex2.status
from
(
select cal_date, max(ex_date) as mdate
from
(
select cal.`date` as cal_date, ex.`date` as ex_date
from calendar_t cal
inner join example ex
on ex.`date` <= cal.`date`
) maxs
group by cal_date
) m2
inner join example ex2
on m2.mdate = ex2.`date`
-- pick a reasonable end date for filtering..
where cal_date <= date('2015-06-15')
order by cal_date
;

output

+------------------------+------------------------+---------+
|        cal_date        |         mdate          | status  |
+------------------------+------------------------+---------+
| June, 01 2015 00:00:00 | June, 01 2015 00:00:00 | Start   |
| June, 02 2015 00:00:00 | June, 01 2015 00:00:00 | Start   |
| June, 03 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 |
| June, 04 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 |
| June, 05 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 |
| June, 06 2015 00:00:00 | June, 03 2015 00:00:00 | Stage 2 |
| June, 07 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 08 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 09 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 10 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 11 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 12 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 13 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 14 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
| June, 15 2015 00:00:00 | June, 07 2015 00:00:00 | Stage 3 |
+------------------------+------------------------+---------+

sqlfiddle

---

reference

• SO mysql sequence generation
• sql cross join

这篇关于MySQL为给定状态填写两个日期之间的缺少日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！