我正在使用查询之类的查询从mysql表构建快速csv：
1
| select DATE(date),count(date) from table group by DATE(date) order by date asc; |
，然后通过以下命令将它们转储到perl中的文件中：
1
2
3
4
| while(my($date,$sum) = $sth->fetchrow) {
print CSV"$date,$sum\
"
} |
数据中有日期间隔，但：
1
2
| | 2008-08-05 | 4 |
| 2008-08-07 | 23 | |
我想用零计数条目填充数据以填写缺失的日子，最后以：
1
2
3
| | 2008-08-05 | 4 |
| 2008-08-06 | 0 |
| 2008-08-07 | 23 | |
我拍了一个非常尴尬的解决方案(几乎可以肯定是越野车)，每个月都有几天，还提供了一些数学运算，但是在mysql或perl方面都必须有一些更简单的方法。
任何天才的想法/对我为何如此愚蠢都打耳光？
由于一些原因，我最终使用了存储过程，该存储过程针对所讨论的日期范围生成了一个临时表：
-
我知道每次都会寻找的日期范围
-
不幸的是，有问题的服务器不是我可以在atm上安装perl模块的服务器，并且它的状态已严重不足，无法远程安装Date ::-y
perl Date / DateTime迭代答案也非常好，我希望我可以选择多个答案！
当在服务器端需要类似的内容时，通常会创建一个表，其中包含两个时间点之间的所有可能的日期，然后将该表与查询结果连接起来。像这样的东西：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| create procedure sp1(d1 date, d2 date)
declare d datetime;
create temporary table foo (d date not null);
set d = d1
while d <= d2 do
insert into foo (d) values (d)
set d = date_add(d, interval 1 day)
end while
select foo.d, count(date)
from foo left join table on foo.d = table.date
group by foo.d order by foo.d asc;
drop temporary table foo;
end procedure |
在这种特殊情况下，最好在客户端进行一点检查，如果当前日期不是previos 1，则放置一些附加字符串。
当我不得不处理这个问题时，为了填写缺少的日期，我实际上创建了一个引用表，该表仅包含我感兴趣的所有日期，并在日期字段中加入了数据表。这很粗糙，但是行得通。
1
2
3
4
5
| SELECT DATE(r.date),count(d.date)
FROM dates AS r
LEFT JOIN table AS d ON d.date = r.date
GROUP BY DATE(r.date)
ORDER BY r.date ASC; |
对于输出，我只使用SELECT INTO OUTFILE而不是手工生成CSV。也使我们不必担心转义特殊字符。
您可以使用DateTime对象：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| use DateTime;
my $dt;
while ( my ($date, $sum) = $sth->fetchrow ) {
if (defined $dt) {
print CSV $dt->ymd .",0\
" while $dt->add(days => 1)->ymd lt $date;
}
else {
my ($y, $m, $d) = split /-/, $date;
$dt = DateTime->new(year => $y, month => $m, day => $d);
}
print CSV,"$date,$sum\
";
} |
以上代码的作用是将上次打印的日期保存在
DateTime对象$dt，并且当前日期超过一天
将来，它会将$dt增加一天(并将其打印到
CSV)，直到与当前日期相同为止。
这样，您不需要额外的表，也不需要获取所有的表
提前行。
不傻，这不是MySQL要做的事情，它插入了空的日期值。我在Perl中分两个步骤进行操作。首先，将查询中的所有数据加载到按日期组织的哈希中。然后，我创建一个Date :: EzDate对象并将其按天递增，所以...
1
2
3
4
5
6
7
| my $current_date = Date::EzDate->new();
$current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}';
while ($current_date <= $final_date)
{
print"$current_date\\t|\\t%hash_o_data{$current_date}"; # EzDate provides for automatic stringification in the format specfied in 'default'
$current_date++;
} |
其中最终日期是另一个EzDate对象或包含日期范围末尾的字符串。
EzDate目前不在CPAN上，但是您可能会找到另一个perl mod，它将进行日期比较并提供日期增量器。
我希望你能弄清楚其余部分。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| select * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
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) n1,
(select 0 as num
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) n2,
(select 0 as num
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) n3,
(select 0 as num
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) n4,
(select 0 as num
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) n5
) a
where date >'2011-01-02 00:00:00.000' and date < NOW()
order by date |
使用
1
| select n3.num*100+n2.num*10+n1.num as date |
您将获得一列数字，范围从0到max(n3)* 100 max(n2)* 10 max(n1)
因为这里我们的最大n3为3，所以SELECT将返回399，再加上0-> 400条记录(日历中的日期)。
您可以通过限制动态日历来调整动态日历，例如，从必须的min(date)到now()。
我认为最简单的解决方案是创建一个Ordinal表，该表具有所需的最大行数(在您的情况下为31 * 3 = 93)。
1
2
3
4
5
| CREATE TABLE IF NOT EXISTS `Ordinal` (
`n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`)
);
INSERT INTO `Ordinal` (`n`)
VALUES (NULL), (NULL), (NULL); #etc |
接下来，对数据执行Ordinal中的LEFT JOIN。这是一个简单的案例，它是上周的每一天：
1
2
3
| SELECT CURDATE() - INTERVAL `n` DAY AS `day`
FROM `Ordinal` WHERE `n` <= 7
ORDER BY `n` ASC |
您需要对此进行更改的两件事是起点和间隔。为了清楚起见，我使用了SET @var = 'value'语法。
1
2
3
4
5
6
7
| SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY;
SET @begin = @end - INTERVAL 3 MONTH;
SET @period = DATEDIFF(@end, @begin);
SELECT @begin + INTERVAL (`n` + 1) DAY AS `date`
FROM `Ordinal` WHERE `n` < @period
ORDER BY `n` ASC; |
因此，如果您要获取过去三个月中每天的邮件数量，则最终代码将类似于以下内容：
1
2
3
4
5
6
7
8
9
| SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM (
SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date`
FROM `Ordinal`
WHERE `n` < (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH)))
ORDER BY `n` ASC
) AS `ord`
LEFT JOIN `Message` AS `msg`
ON `ord`.`date` = `msg`.`date`
GROUP BY `ord`.`date` |
提示和评论：
-
查询中最困难的部分可能是确定限制Ordinal时要使用的天数。相比之下，将整数序列转换为日期很容易。
-
您可以使用Ordinal满足所有不间断序列的需求。只需确保它包含的行数超过最长序列的行数即可。
-
您可以在Ordinal上对多个序列使用多个查询，例如，在过去七(1-7)周的每个工作日(1-5)列出。
-
您可以通过将日期存储在Ordinal表中来加快速度，但是灵活性较差。这样，无论使用多少次，您都只需要一个Ordinal表。尽管如此，如果速度值得，请尝试使用INSERT INTO ... SELECT语法。
由于您不知道差距在哪里，但是您想要从列表中的第一个日期到最后一个日期的所有值(大概)，请执行以下操作：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| use DateTime;
use DateTime::Format::Strptime;
my @row = $sth->fetchrow;
my $countdate = strptime("%Y-%m-%d", $firstrow[0]);
my $thisdate = strptime("%Y-%m-%d", $firstrow[0]);
while ($countdate) {
# keep looping countdate until it hits the next db row date
if(DateTime->compare($countdate, $thisdate) == -1) {
# counter not reached next date yet
print CSV $countdate->ymd .",0\
";
$countdate = $countdate->add( days => 1 );
$next;
}
# countdate is equal to next row's date, so print that instead
print CSV $thisdate->ymd .",$row[1]\
";
# increase both
@row = $sth->fetchrow;
$thisdate = strptime("%Y-%m-%d", $firstrow[0]);
$countdate = $countdate->add( days => 1 );
} |
嗯，事实证明这比我想的要复杂。.我希望这是有道理的！
使用一些Perl模块进行日期计算，例如推荐的DateTime或Time :: Piece(5.10版的核心)。仅增加日期和打印日期，直到日期与当前日期匹配为止为0。
我不知道这是否行得通，但是如果您创建一个包含所有可能日期的新表又怎么样(如果日期范围发生不可预测的变化，这可能是这个想法的问题。) 。)，然后在两个表上进行左联接？我猜这是一个疯狂的解决方案，如果存在大量可能的日期，或者无法预测第一个和最后一个日期，但是如果日期范围是固定的或易于计算，那么这可能会起作用。
