获取过去十二个月有价值的数据从数据库
我试图用的失业人数从劳动统计数据的部门(可在ftp://ftp.bls.gov/pub/time.series/la/)获取过去十二个月有价值的数据从数据库
数据库工作,我需要得到最后的12因为它们并不总是有最近几个月的数据(现在,上个月的数据价值是2010年11月),所以选择上一年的所有数据会更棘手。
我知道哪个记录是最新的,并且日期字段我在数据库中有与工作有:
PERIOD_NAME(月份名称) 年 期(M01,M02等为一月,二月)
我现在的SQL,它从一堆连接的表中提取数据,方法是:
USE unemploymentdata;
SELECT DISTINCT series.series_id, period_name, year, value, series.area_code,
footnote_codes, period_name, measure_text, area_text, area_type_text
FROM state_overview
LEFT JOIN series ON state_overview.series_id=series.series_id
LEFT JOIN footnote ON state_overview.footnote_codes = footnote.footnote_code
LEFT JOIN period ON state_overview.period = period.period
LEFT JOIN measure ON series.measure_code = measure.measure_code
LEFT JOIN area ON series.area_code=area.area_code
LEFT JOIN area_type ON area.area_type_code=area_type.area_type_code
WHERE area_text = 'State Name' AND year > 2009
ORDER BY state_overview.period, measure_text;
任何想法?
既然你有文本值与月和年工作,你需要将它们转换到MySQL格式DATE
值,然后可以让MySQL的计算,去年的间隔像这样:
SELECT ... WHERE STR_TO_DATE(CONCAT(period_name,' 1 ',year),'%M %d %Y') >= DATE_SUB(STR_TO_DATE(CONCAT(most_recent_period_name,' 1 ',most_recent_year),'%M %d %Y'), INTERVAL 1 YEAR) ...;
CONCAT()
函数只是建立一个字符串,如“Month 1 YYYY”,而STR_TO_DATE()
函数正在接受该字符串和一个格式化字符串,以告诉它如何解析它,并将其转换为DATE
。
注意:此查询可能会吸引索引,但它应该工作。 :)
我认为WHERE子句应该做一些改动,但为了提高效率/简单性,您还应该将MAX(year)
添加到SELECT部分。
SELECT ...... MAX(year) as max_year .....
WHERE area_text = 'State Name'
AND year >= max_year - 1
AND period >= (SELECT MAX(period) WHERE year = max_year)
ORDER BY state_overview.period, measure_text;
即使您没有日期信息,也可以将年份和月份存储为日期。只需使用每个月的第一个。
{2009, 'M1'} => 2009-01-01
{2009, 'M2'} => 2009-02-01
{2009, 'M3'} => 2009-03-01
这使得日期算法比处理(可能脏的)数据的子字符串要容易得多。 Plus(这很大),你可以更有效地索引数据。作为奖励,您现在可以使用DATE_FORMAT提取很多额外的好东西,例如月份名称,月份中的天数等等。
是否所有州都具有所有月份的数据,并且是同时更新的数据?这个问题的答案决定了你应该使用什么查询策略。
最好的方法是在1年前采取正确的时间($ a),然后从数据库中获取数值,然后在每个结果中找到日期的strtotime($ b)。现在
if($b < $a){
continue;
}
else {
//do something.
}
这将工作,即使我只有月和年的价值?我可以预期这种方法在几千条记录上运行有多慢? – MarathonStudios 2011-01-09 07:42:20