Generate data for range of dates including those that are not present in data
Here is my table structure and data:
CREATE TABLE event (
EventID INT(11) NOT NULL AUTO_INCREMENT,
EventDate DATETIME DEFAULT NULL,
Description VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (EventID)
);
INSERT INTO event (EventID, EventDate, Description) VALUES
(1, '2011-01-01 00:00:00', 'Event 1'),
(2, '2011-03-01 00:00:00', 'Event 2'),
(3, '2011-06-01 00:00:00', 'Event 3'),
(4, '2011-09-01 00:00:00', 'Event 4');
And this query and output:
SELECT *
FROM EVENT
WHERE EventDate BETWEEN '2011-02-01' AND '2011-03-31'
+---------+---------------------+-------------+
| EventID | EventDate | Description |
+---------+---------------------+-------------+
| 2 | 2011-03-01 00:00:00 | Event 2 |
+---------+---------------------+-------------+
1 row in set (0.00 sec)
I want inject empty dates in the result like so:
+---------+---------------------+-------------+
| EventID | EventDate 开发者_Go百科 | Description |
+---------+---------------------+-------------+
| NULL | 2011-02-01 00:00:00 | NULL |
| NULL | 2011-02-02 00:00:00 | NULL |
| NULL | 2011-02-03 00:00:00 | NULL |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL | 2011-02-28 00:00:00 | NULL |
| 2 | 2011-03-01 00:00:00 | Event 2 |
| NULL | 2011-03-02 00:00:00 | NULL |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL | 2011-03-31 00:00:00 | NULL |
+---------+---------------------+-------------+
The output should contain 59 rows: 28 for February and 31 for March.
I'll want to avoid any hard coding; instead, I need a very flexible solution that adapts itself to the specified dates.
An auxiliary calendar table will work well. The simplest possible calendar table is a single column of dates.
create table calendar (
cal_date date primary key
);
You can use a spreadsheet or SQL to populate it. An outer join on it will bring in the dates that don't exist in your data. Limit permissions with GRANT and REVOKE, and use whatever means necessary to make sure that the dates you expect to be in there are actually in there. I run a daily report on my server that makes sure there are 'n' rows, and verifies the earliest and latest dates.
On some platforms, you can generate a series of dates on the fly, and either use it directly or in a CTE. PostgreSQL has functions for that; I don't know whether MySQL does. They're not hard to write, though, if you want to roll your own.
maybe you need an pivot table, Take a look at that
pivot table schema
name: pivot columns: {i : datatype int}
Populate
create foo table
schema foo
name: foo column: value datatype varchar
insert into foo
values('0'),
values('1'),
values('2'),
values('3'),
values('4'),
values('5'),
values('6'),
values('7'),
values('8'),
values('9');
- insert 100 values
insert into pivot
select concat(a.value, a.value)
from foo a, foo b
- insert 1000 values
insert into pivot
select concat(a.value, b.value, c.value)
from foo a, foo b, foo c
your query
SELECT
Event.EventId,
case when EventDate is null then DATE_ADD(periods.periodstart, INTERVAL auxtable.i DAY)
else EventDate end,
Description
FROM
(
select id, min(EventDate ) periodstart, max(EventDate) periodend,
DATEDIFF(max(EventDate),min(EventDate )) as days
FROM EVEN
WHERE EventDate BETWEEN '2011-02-01' AND '2011-03-31'
) periods
inner join
(
select *
from pivot
where i >= 0 and i < 31 //max one month change with your needs
)auxtable
on auxtable.i < periods.days
left join Event
on Event.EventDate = DATE_ADD(periods.periodstart, INTERVAL auxtable.i DAY)
I'm fairly sure this is impossible in pure SQL, so your choices are:
- use a stored procedure/function
- do this in the app code, which seems pretty straight forward
精彩评论