[MYSQL]查询单位时间消耗量

请求哪位大神给我优化一下mysql语句.

数据库表:(日/月/年 数据表和data_hour结构一样,懒得复制了,不然太长)

DROP TABLE IF EXISTS data_source;#数据源
create table IF not EXISTS  data_source (
num  int unsigned  not null auto_increment PRIMARY KEY   COMMENT'序号   值唯一',
id  varchar(40)  not null  COMMENT 'ID',
data_current  DOUBLE COMMENT '瞬时数据  随时间累加或不变',
data_time    TIMESTAMP COMMENT '数据时间'
)charset=gb2312;


DROP TABLE IF EXISTS data_hour;#YH_ES 小时数据汇总
create table IF not EXISTS  data_hour (
num  int unsigned  not null auto_increment PRIMARY KEY   COMMENT'序号   值唯一',
id  varchar(40)  not null  COMMENT 'ID',
data_s   DOUBLE COMMENT '起始读数',
data_e   DOUBLE COMMENT '末期读数',
hour_data   DOUBLE COMMENT '小时消耗量',
s_time   TIMESTAMP COMMENT '起始读数时间',
e_time   TIMESTAMP COMMENT '期末读数时间'
)charset=gb2312;

查询上一小时消耗量:

计算方法1.当前小时最小时间对应读数 - 上一小时最小时间对应读数

计算方法2.上一小时最大时间对应读数 - 上一小时最小时间对应读数(没做,误差太大,上一小时最大时间对应读数和当前小时最小时间对应读数之间差值没有统计在内.)

方法1实现:

SET @time_result=DATE_FORMAT(DATE_SUB(CURRENT_TIME(),INTERVAL -1 HOUR),'%Y-%m-%d %H');
SET @time_now=DATE_FORMAT(CURRENT_TIME(),'%Y-%m-%d %H');
INSERT INTO test.data_hour ( id, 
data_s,   data_e,   hour_data,
s_time, e_time) 	
SELECT te.id, 
ts.data_current AS data_s,te.data_current AS data_e,
ROUND((te.data_current -ts.data_current),3) AS hour_data,
ts.data_time AS s_time,te.data_time AS e_time FROM
	(SELECT a.id,a.data_current,a.data_time from data_source AS a 
	INNER   JOIN (SELECT m.id id, MIN(m.data_time) min_time from data_source AS m
	WHERE  DATE_FORMAT(data_time,'%Y-%m-%d %H') = @time_result
	GROUP BY id)AS f ON  a.data_time=f.min_time AND a.id=f.id GROUP BY a.id ) AS ts,
	(SELECT a.id,a.data_current,a.data_time  from data_source AS a 
	INNER   JOIN (SELECT m.id id, MIN(m.data_time) min_time from data_source AS m
	WHERE  DATE_FORMAT(data_time,'%Y-%m-%d %H') =@time_now
	GROUP BY id)AS f ON  a.data_time=f.min_time AND a.id=f.id GROUP BY a.id )
AS te WHERE ts.id=te.id;

同理,查询上一天消耗量:

SET @time_result=DATE_FORMAT(DATE_SUB(CURRENT_TIME(),INTERVAL 1 DAY),'%Y-%m-%d');
-- INSERT INTO test.data_day ( id, 
-- data_s,   data_e,   day_data,
-- s_time, e_time) 	
	SELECT te.id,	ts.data_s as data_s,	te.data_e as data_e,
	ROUND((te.data_e -ts.data_s),3) as day_data,
	ts.s_time as s_time,te.e_time as e_time FROM
			(SELECT a.id,a.data_s,a.s_time from data_hour as a 
			INNER   JOIN (SELECT MIN(m.s_time) min_time from data_hour as m
			WHERE  DATE_FORMAT(s_time,'%Y-%m-%d') = @time_result GROUP BY id)as f
			on  a.s_time=f.min_time GROUP BY a.id  ORDER BY  a.id) as ts,
			(SELECT a.id,a.data_e,a.e_time  from data_hour as a 
			INNER   JOIN (SELECT MAX(m.e_time) max_time from data_hour as m
			WHERE  DATE_FORMAT(s_time,'%Y-%m-%d') = @time_result GROUP BY id)as f
			on  a.e_time=f.max_time GROUP BY a.id  ORDER BY  a.id)
	 as te WHERE ts.id=te.id;

同理可算月/年数据

测试:

插入数据:

#define MFT(format,...) std_string_format(format,##__VA_ARGS__)

std::string  std_string_format( const char * _Format, ...) {
	std::string tmp;
	va_list marker = NULL;
	va_start(marker, _Format);
	size_t num_of_chars = _vscprintf(_Format, marker);
	if (num_of_chars >= tmp.capacity()) 
	{
		tmp.resize(num_of_chars + 1);
	}
	vsprintf_s((char *)tmp.data(), tmp.capacity(), _Format, marker);
	va_end(marker);
	string strRet(tmp.data());
	return strRet;
}


/***********
*获取随机数值
*DWORD pt=1, 随机数种子,默认为传入的时间戳
*int min=0,   产生的随机数最小值
*int max=100 ,产生的随机数最大值
*DWORD t=0 ,  防止在循环中使用时候产生的随机数一样
*float dot=0.1f 转换为float的倍数
*返回:随机数
************/
float GetFloatRandValue(DWORD pt=1,int min=0,int max=100 ,DWORD t=0 ,float dot=0.1f)
{
	if (pt==0)
	{
		pt=GetTickCount();
	}
	srand(pt+t);
	float v=(rand()%(max-min+1)+min)*dot;
	return v;

}

/******************
*制作小时数据 一个小时三条数据
*CTools *sqlTool, 数据库操作类指针
*CTime ts,   起始时间 包含
*CTime te,   期末时间  包含
*std::vector<std::string> mAlldev, 设备ID
*返回:无
*********************/
void  SetDataToDB(CTools *sqlTool,CTime ts,CTime te,std::vector<std::string> mAlldev)
{
	float fDataCurrent=0.0f;	//"瞬时数据"
	string strClearSql="TRUNCATE  test.data_source; ";
	sqlTool->delete_sql(strClearSql.data());
	DWORD pt=GetTickCount();
	int n =0;
	CTimeSpan  tsp=te-ts;
	int allMin =(int)tsp.GetTotalMinutes();
	for (int minute = 0; minute <= allMin; minute+=30)
	{
		CTimeSpan  dtsp(0,0,minute,0);
		CTime  tData=ts+dtsp;
		for (int i = 0; i <(int) mAlldev.size(); i++)
		{
			fDataCurrent+=GetFloatRandValue(pt,10,110, n++);
			string mTime=MFT2("%d-%02d-%02d %02d:%02d:%02d",tData.GetYear(),tData.GetMonth(),tData.GetDay(),tData.GetHour(),tData.GetMinute(),n%60);
			string sDevId=mAlldev[i];
			string sqlStr=MFT2("INSERT INTO test.data_source "
					"(id,data_current, data_time) "
					" VALUES ('%s',%.1f, '%s');",
					sDevId.data(),fDataCurrent,mTime.data()
					);
			sqlTool->insert_sql(sqlStr.data());
		}
	}

}

/******************
*计算小时差值,并存入数据库
*CTools *sqlTool, 数据库操作类指针
*CTime ts,   起始时间 包含
*CTime te,   期末时间  包含
*返回:无
**************************/
void CalculateDataOfHour(CTools *sqlTool,CTime ts,CTime te)
{
	string strClearSql="TRUNCATE test.data_hour;";
	sqlTool->delete_sql(strClearSql.data());
	CTimeSpan  tsp=te-ts;
	string sTimeStart=GetTimeStringOfHour(ts);
	int allHour =(int)tsp.GetTotalHours();
	for (int hour = 0; hour <allHour; hour++)
	{
		CTimeSpan tHsp(0,1,0,0);
		ts+=tHsp;
		string sTimeEnd=GetTimeStringOfHour(ts);
		MTRACE("起始:%s  期末:%s\n",sTimeStart.data(),sTimeEnd.data());
		string sqlStr =MFT("INSERT INTO test.data_hour ( id, \n"
							"data_s,   data_e,   hour_data,\n"
							"s_time, e_time) 	\n"
							"	SELECT te.id, \n"
							"	ts.data_current as data_s,te.data_current as data_e,\n"
							"	ROUND((te.data_current -ts.data_current),3) as hour_data,\n"
							"	ts.data_time as s_time,te.data_time as e_time FROM\n"
							"			(SELECT a.id,a.data_current,a.data_time from data_source as a \n"
							"			INNER   JOIN (SELECT MIN(m.data_time) min_time from data_source as m\n"
							"			WHERE  DATE_FORMAT(data_time,'%%Y-%%m-%%d %%H') = '%s' GROUP BY id)as f\n"
							"			on  a.data_time=f.min_time GROUP BY a.id  ORDER BY  a.id) as ts,\n"
							"			(SELECT a.id,a.data_current,a.data_time  from data_source as a \n"
							"			INNER   JOIN (SELECT MIN(m.data_time) min_time from data_source as m\n"
							"			WHERE  DATE_FORMAT(data_time,'%%Y-%%m-%%d %%H') = '%s' GROUP BY id)as f\n"
							"			on  a.data_time=f.min_time GROUP BY a.id  ORDER BY  a.id)\n"
							"	 as te WHERE ts.id=te.id;",sTimeStart.data(),sTimeEnd.data());
		sqlTool->insert_sql(sqlStr.data());
		sTimeStart=sTimeEnd;
	}
}



int _tmain(int argc, _TCHAR* argv[])
{
	
	CTools *sqlTool=new CTools();
	if(!initMysql(sqlTool,"test"))
	{
		printf_s("数据库初始化失败\n");
		delete sqlTool;
		sqlTool=nullptr;
		return 1;
	}
	CTime ts(2023,1,1,0,0,0);
	CTime te(2024,2,1,0,0,0);
	std::vector<std::string> mAlldev;
	mAlldev.push_back("id_1");
	mAlldev.push_back("id_2");
	SetDataToDB(sqlTool,ts,te,mAlldev);
	CalculateDataOfHour(sqlTool,ts,te);
	//CalculateDataOfDay(sqlTool,ts,te);
	system("pause");
	delete sqlTool;
	return 0;
}

结果

1.单小时计算:

 

 按天