2017-02-28
一、要求:
要求实现下面效果:
二、实现步骤:
1.创建中间过程表,包含字段如下:
2.查询资源表resource_storage_info数据依据时间放入中间表storage_usaged对应列中,代码如下:
更新今天数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,r.STORAGE_SIZE SIZ,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now()) group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_USED = d.SZE where s.USER_ID = d.UID,s.STORAGE_ALL = d.SIZ;
更新昨天数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now())-1 group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_USED = d.SZE where s.USER_ID = d.UID;
更新前天数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now())-2 group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_USED = d.SZE where s.USER_ID = d.UID;
更新上周数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY))
group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_LASTWEEK = d.SZE
where s.USER_ID = d.UID;
更新前2周数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 8 DAY))
group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_LASTWEEK = d.SZE
where s.USER_ID = d.UID;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
如果前两个月都是在本年,用下面的SQL语句:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
更新上月数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now()) day),interval 0 month)) group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_LASTMONTH = d.SZE
where s.USER_ID = d.UID;
更新上2个月数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select CONCAT(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01')) group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_BEFORE_LASTMONTH = d.SZE
where s.USER_ID = d.UID
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
若现在是1月,上个月是去年12月,上2个月是去年11月,使用下面SQL语句:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
更新去年11月数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-11-30'),'%Y-%m-%d') group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_BEFORE_LASTMONTH = d.SZE
where s.USER_ID = d.UID;
更新去年12月数据:
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-12-31'),'%Y-%m-%d') group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_LASTMONTH = d.SZE
where s.USER_ID = d.UID
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
若现在是2月,上2个月是去年12月,使用下面SQL语句:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
update storage_usaged s inner join (select r.STORAGE_USAGE SZE,r.STORAGE_SIZE SIZ,c.USER_ID UID from resource_storage_info r,cfg_user_filepath c
where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-12-31'),'%Y-%m-%d') group by r.CUST_INFO) d on s.USER_ID = d.UID
set s.STORAGE_BEFORE_LASTMONTH = d.SZE
where s.USER_ID = d.UID
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
3.更新展示表storage_rate数据:
update storage_rate r inner join (select user_id id,
round((storage_used/1024)/1024,2) used, --此字段是KB,保留两位小数
CONCAT(round((storage_used*100/storage_all),1),'%') used_rate,
CONCAT(ABS(round(((storage_yesterday-storage_before_yesterday)*100/storage_before_yesterday),1)),'%') day_rate,
CONCAT(ABS(round(((storage_lastweek-storage_before_lastweek)*100/storage_before_lastweek),1)),'%') week_rate,
CONCAT(ABS(round(((storage_lastmonth-storage_before_lastmonth)*100/storage_before_lastmonth),1)),'%') month_rate
from storage_usaged group by user_id) s on r.user_id = s.id
set r.storage_used = s.used,r.storage_used_rate = used_rate,r.prev_day_rate = day_rate,
r.prev_week_rate = week_rate,r.prev_month_rate = month_rate,r.date = DATE_FORMAT(NOW(),'%Y-%m-%e %H:%i:%s')
where r.user_id = s.id
本人知识匮乏,欢迎留下更简单的SQL语句和更好的方法......