/// <summary>
/// 拆分大查询
/// </summary>
private void SubQuery()
{
//
DateTime midtime, UTCStartTime, UTCEndTime;
midtime = QueryStartTime.ToUniversalTime();
UTCStartTime = QueryStartTime.ToUniversalTime();
UTCEndTime = QueryEndTime.ToUniversalTime();
//1.得到所有跨越的月
_times.Add(QueryStartTime.ToString("yyyy-MM-dd HH:mm:ss"));
_tables.Add("taglog" + midtime.ToString("yyyyMM"));
do
{
midtime = DateTime.Parse(midtime.AddMonths(1).ToString("yyyy-MM-01 00:00:00"));
if (midtime <= UTCEndTime)
{
_times.Add(midtime.ToLocalTime().ToString("yyyy-MM-dd 07:59:00"));
_tables.Add("taglog" + midtime.ToString("yyyyMM"));
}
}while (midtime <= UTCEndTime);
_times.Add(QueryEndTime.ToString("yyyy-MM-dd HH:mm:ss"));
//2.组织tags
string inStatement = "";
foreach (string tag in QueryTags)
{
inStatement = inStatement + "'" + tag + "',";
}
inStatement = inStatement.Substring(0, inStatement.Length - 1);
//3.组织select语句
List <string> selects = new List <string> {
};
for (int i = 0; i < _times.Count - 1; i++)
{
if (i == 0)
{
string select = " SELECT `Time` ,`TagName`,`Value` From " + _tables[i] + " FORCE INDEX (name_time) " + //强制使用索引
" WHERE time between '" + _times[i] + "' and '" + _times[i + 1]
+ "' and timestampdiff(minute,'" + _times[0] + "',time) %" + QueryStep + "=0 and tagname in (" + inStatement + "); ";
selects.Add(select);
}
else
{
string select = " SELECT `Time` ,`TagName`,`Value` From " + _tables[i] + " FORCE INDEX (name_time) " + //强制使用索引
" WHERE time between '" + DateTime.Parse(_times[i]).AddMinutes(1).ToString("yyy-MM-dd HH:mm:ss") + "' and '" + _times[i + 1]
+ "' and timestampdiff(minute,'" + _times[0] + "',time) %" + QueryStep + "=0 and tagname in (" + inStatement + "); ";
selects.Add(select);
}
}
//4.创建query
foreach (string select in selects)
{
BaseQuery bq = new BaseQuery(connectString, select);
Querys.Add(bq);
}
}
/// <summary>
/// 分割查询
/// </summary>
private void SubQuery()
{
//1.组织时间点和月份以及表名对应关系
List <string> mMonth = new List <string> {
};
foreach (DateTime dt in QueryTimes)
{
mMonth.Add(dt.ToString("yyyy-MM-01 08:00:00"));
}
mMonth = mMonth.Distinct().ToList();
foreach (string month in mMonth)
{
TimePointMonth tm = new TimePointMonth();
tm.TableName = "taglog" + DateTime.Parse(month).ToString("yyyyMM");
foreach (DateTime dt in QueryTimes)
{
if ((DateTime.Parse(month).Year == dt.Year) && (DateTime.Parse(month).Month == dt.Month))
{
tm.TimePoint.Add(dt);
}
}
_months.Add(tm);
}
//2.组织tags
string inStatement = "";
foreach (string tag in QueryTags)
{
inStatement = inStatement + "'" + tag + "',";
}
inStatement = inStatement.Substring(0, inStatement.Length - 1);
//3.组织select语句
List <string> selects = new List <string> {
};
for (int i = 0; i < _months.Count; i++)
{
string times = " WHERE time in ( ";
foreach (DateTime dt in _months[i].TimePoint)
{
times = times + "'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "',";
}
times = times.Substring(0, times.Length - 1) + ")";
string select = " SELECT `Time` ,`TagName`,`Value` From " + _months[i].TableName + times
+ " and tagname in (" + inStatement + "); ";
selects.Add(select);
}
//4.创建query
foreach (string select in selects)
{
BaseQuery bq = new BaseQuery(connectString, select);
Querys.Add(bq);
}
}
