private AnalysisModel Analysis(bool uniq = false)
{
var all = InterviewBll.SqlQuery <InterviewAnalysisDto>("select id,ip,BrowserType,Province,ViewTime,OnlineSpanSeconds from Interview ORDER BY ViewTime desc").ToList();
//var unique = InterviewBll.SqlQuery<InterviewAnalysisDto>("select ip,BrowserType,Province,ViewTime,OnlineSpanSeconds from Interview where Id in(select max(Id) from Interview group by ip ) ORDER BY ViewTime desc").ToList();
var unique = all.GroupBy(i => i.IP).Select(g => g.LastOrDefault()).ToList();
var entities = uniq ? unique : all;
var dap = InterviewBll.SqlQuery <BounceRate>(@"DECLARE @a float DECLARE @b int
SET @a=((SELECT count(1) from (SELECT interviewid FROM InterviewDetail GROUP BY interviewid HAVING count(1)=1) as t)*1.0)
SET @b=(SELECT count(1) from (SELECT interviewid FROM InterviewDetail GROUP BY interviewid) as t)
SET @b=case when @b=0 then 1 else @b end
SELECT @a Dap,@b [All],@a/@b Result").FirstOrDefault(); //计算跳出率
var dapAgg = InterviewBll.SqlQuery <BounceRateAggregate>("SELECT CONVERT(datetime,t.tt) [Time],sum(CASE WHEN t.c=1 THEN 1 ELSE 0 END) as Dap,count(t.c) as [All],sum(CASE WHEN t.c=1 THEN 1 ELSE 0 END)*1.0/count(t.c) as Rate from (SELECT interviewid,convert(char(10),[Time],23) tt,count(InterviewId) c FROM [dbo].[InterviewDetail] GROUP BY convert(char(10),[Time],23),InterviewId) as t GROUP BY t.tt ORDER BY Time").ToList(); //每日跳出率统计
//今日统计
var todaypv = all.Count(i => i.ViewTime >= DateTime.Today);
var todayuv = unique.Count(i => i.ViewTime >= DateTime.Today);
//本月统计
var monthStart = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var monthpv = all.Count(i => i.ViewTime >= monthStart);
var monthuv = unique.Count(i => i.ViewTime >= monthStart);
//YTD统计
var yearStart = new DateTime(DateTime.Now.Year, 1, 1);
var yearpv = all.Count(i => i.ViewTime >= yearStart);
var yearuv = unique.Count(i => i.ViewTime >= yearStart);
//完全统计
var totalpv = all.Count;
var totaluv = unique.Count;
var client = new List <object>();
var region = new List <object>();
//省份和世界地区统计
entities.Where(i => i.Province != "XX").GroupBy(i => i.Province).OrderBy(g => Guid.NewGuid()).ForEach(g =>
{
if (string.IsNullOrEmpty(g.Key))
{
return;
}
string name;
if (g.Key.Contains("新疆"))
{
name = "新疆";
}
else if (g.Key.Contains("广西"))
{
name = "广西";
}
else if (g.Key.Contains("西藏"))
{
name = "西藏";
}
else if (g.Key.Contains("香港"))
{
name = "香港";
}
else if (g.Key.Contains("澳门"))
{
name = "澳门";
}
else if (g.Key.Contains("内蒙古"))
{
name = "内蒙古";
}
else if (g.Key.Contains("宁夏"))
{
name = "宁夏";
}
else if (g.Key.Contains("海南"))
{
name = "海南";
}
else if (g.Key.Contains("台湾"))
{
name = "台湾";
}
else if (g.Key.Contains("省"))
{
name = g.Key.Replace("省", "");
}
else if (g.Key.Contains("市"))
{
name = g.Key.Replace("市", "");
}
else if (g.Key.Contains("XX"))
{
return;
}
else
{
name = g.Key;
}
client.Add(new
{
name,
value = g.Count()
});
region.Add(new dynamic[]
{
name,
g.Count()
});
});
var browser = entities.GroupBy(i => i.BrowserType).OrderBy(g => g.Key).Select(g => new object[]
{
g.Key,
g.Count()
}).ToList();
var reduce = all.OrderBy(i => i.ViewTime).GroupBy(i => i.ViewTime.Date).Select(g => new
{
g.Key,
pv = g.Count(),
uv = g.DistinctBy(i => i.IP).Count()
}).ToList(); //汇总统计
//找出PV最高的一天
var p = reduce.FirstOrDefault(e => e.pv == reduce.Max(a => a.pv));
var highpv = new
{
date = p.Key,
p.pv,
p.uv
};
//找出UV最高的一天
var u = reduce.FirstOrDefault(e => e.uv == reduce.Max(a => a.uv));
var highuv = new
{
date = u.Key,
u.pv,
u.uv
};
//汇总统计
var pv = reduce.Select(g => new List <object>
{
g.Key.GetTotalMilliseconds(),
g.pv
}).ToList(); //每日PV
var uv = reduce.Select(g => new List <object>
{
g.Key.GetTotalMilliseconds(),
g.uv
}).ToList(); //每日UV
var iv = all.OrderBy(i => i.ViewTime).DistinctBy(e => e.IP).GroupBy(i => i.ViewTime.Date).Select(g => new List <object>
{
g.Key.GetTotalMilliseconds(),
g.Count()
}).ToList(); //每日新增独立访客
//访问时长统计
InterviewAnalysisDto maxSpanViewer = all.OrderByDescending(i => i.OnlineSpanSeconds).FirstOrDefault(); //历史最久访客
InterviewAnalysisDto maxSpanViewerToday = all.Where(i => DateTime.Today == i.ViewTime.Date).OrderByDescending(i => i.OnlineSpanSeconds).FirstOrDefault(); //今日最久访客
double average = 0;
double average2 = 0;
if (all.Any(i => DateTime.Today == i.ViewTime.Date && i.OnlineSpanSeconds > 0))
{
average = all.Where(i => i.OnlineSpanSeconds > 0).Average(i => i.OnlineSpanSeconds);
average2 = all.Where(i => DateTime.Today == i.ViewTime.Date && i.OnlineSpanSeconds > 0).Average(i => i.OnlineSpanSeconds);
}
var averSpan = TimeSpan2String(TimeSpan.FromSeconds(average)); //平均访问时长
var averSpanToday = TimeSpan2String(TimeSpan.FromSeconds(average2)); //今日访问时长
BounceRateAggregate todayDap = dapAgg.LastOrDefault();
return(new AnalysisModel()
{
Browser = browser,
Client = client,
Highpv = highpv,
Highuv = highuv,
Iv = iv,
Monthpv = monthpv,
Monthuv = monthuv,
Pv = pv,
Region = region,
Todaypv = todaypv,
Todayuv = todayuv,
Totalpv = totalpv,
Totaluv = totaluv,
Uv = uv,
Yearpv = yearpv,
Yearuv = yearuv,
BounceRate = $"{dap?.Dap}/{dap?.All}({dap?.Result:P})",
BounceRateAggregate = dapAgg.Select(a => new object[]
{
a.Time.GetTotalMilliseconds(),
a.Rate * 100
}).ToList(),
BounceRateToday = $"{todayDap?.Dap}/{todayDap?.All}({todayDap?.Rate:P})",
OnlineSpanAggregate = new
{
maxSpanViewerToday = new
{
maxSpanViewerToday?.IP,
maxSpanViewerToday?.BrowserType,
maxSpanViewerToday?.Province,
maxSpanViewerToday?.ViewTime,
OnlineSpanSeconds = TimeSpan2String(TimeSpan.FromSeconds(maxSpanViewerToday?.OnlineSpanSeconds ?? 0))
},
averSpanToday,
maxSpanViewer = new
{
maxSpanViewer?.IP,
maxSpanViewer?.BrowserType,
maxSpanViewer?.Province,
maxSpanViewer?.ViewTime,
OnlineSpanSeconds = TimeSpan2String(TimeSpan.FromSeconds(maxSpanViewer?.OnlineSpanSeconds ?? 0))
},
averSpan,
}
});
}
private (AnalysisModel, AnalysisModel) Analysis()
{
List <Interview> list;
using (RedisHelper redisHelper = RedisHelper.GetInstance())
{
list = redisHelper.ListRange <Interview>($"Interview:{DateTime.Today:yyyy:MM:dd}");
for (int i = -60; i < 0; i++)
{
list.AddRange(redisHelper.ListRange <Interview>($"Interview:{DateTime.Today.AddDays(i):yyyy:MM:dd}"));
}
list = list.OrderBy(i => i.ViewTime).ToList();
}
if (!list.Any())
{
return(null, null);
}
CommonHelper.AverageCount = list.Count * 1.0 / list.GroupBy(i => i.ViewTime.Date).Count();
var dap = list.Select(i =>
{
var(a, d) = (list.GroupBy(g => g.Uid).Count(), list.Count(g => g.InterviewDetails.Count == 1));
return(new BounceRate()
{
All = a,
Dap = d,
Result = d * 1.0 / a
});
}).FirstOrDefault();
var dapAgg = list.GroupBy(i => i.ViewTime.Date).Select(gs =>
{
var(a, d) = (gs.GroupBy(g => g.Uid).Count(), gs.Count(g => g.InterviewDetails.Count == 1));
return(new BounceRateAggregate()
{
Time = gs.Key,
Dap = d,
All = a,
Rate = d * 1.0m / a
});
});
int todaypv = list.Count(i => i.ViewTime >= DateTime.Today);
int todayuv = list.DistinctBy(i => i.IP).Count(i => i.ViewTime >= DateTime.Today);
//本月统计
var monthStart = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
int monthpv = list.Count(i => i.ViewTime >= monthStart);
int monthuv = list.DistinctBy(i => i.IP).Count(i => i.ViewTime >= monthStart);
////YTD统计
//var yearStart = new DateTime(DateTime.Now.Year, 1, 1);
//int yearpv = list.Count(i => i.ViewTime >= yearStart);
//int yearuv = list.DistinctBy(i => i.IP).Count(i => i.ViewTime >= yearStart);
////完全统计
//int totalpv = list.Count();
//int totaluv = list.DistinctBy(i => i.IP).Count();
var allClient = new List <object>();
var uniClient = new List <object>();
var allRegion = new List <object>();
var uniRegion = new List <object>();
//省份和世界地区统计
list.Where(i => !string.IsNullOrEmpty(i.Province)).GroupBy(i => i.Province).Select(gs => new KeyCount()
{
Key = gs.Key,
Count = gs.Count(),
UniqueCount = gs.DistinctBy(i => i.IP).Count()
}).OrderBy(g => Guid.NewGuid()).ForEach(g =>
{
if (string.IsNullOrEmpty(g.Key))
{
return;
}
string name;
if (g.Key.Contains("新疆"))
{
name = "新疆";
}
else if (g.Key.Contains("广西"))
{
name = "广西";
}
else if (g.Key.Contains("西藏"))
{
name = "西藏";
}
else if (g.Key.Contains("香港"))
{
name = "香港";
}
else if (g.Key.Contains("澳门"))
{
name = "澳门";
}
else if (g.Key.Contains("内蒙古"))
{
name = "内蒙古";
}
else if (g.Key.Contains("宁夏"))
{
name = "宁夏";
}
else if (g.Key.Contains("海南"))
{
name = "海南";
}
else if (g.Key.Contains("台湾"))
{
name = "台湾";
}
else if (g.Key.Contains("省"))
{
name = g.Key.Replace("省", "");
}
else if (g.Key.Contains("市"))
{
name = g.Key.Replace("市", "");
}
else if (g.Key.Contains("XX"))
{
return;
}
else
{
name = g.Key;
}
allClient.Add(new
{
name,
value = g.Count
});
uniClient.Add(new
{
name,
value = g.UniqueCount
});
allRegion.Add(new dynamic[]
{
name,
g.Count
});
uniRegion.Add(new dynamic[]
{
name,
g.UniqueCount
});
});
var allBrowser = list.GroupBy(i => i.BrowserType).OrderBy(g => g.Key).Select(g => new object[]
{
g.Key,
g.Count()
}).ToList();
var uniBrowser = list.GroupBy(i => i.BrowserType).OrderBy(g => g.Key).Select(g => new object[]
{
g.Key,
g.DistinctBy(i => i.IP).Count()
}).ToList();
Dictionary <DateTime, int> dic = list.DistinctBy(i => i.IP).GroupBy(g => g.ViewTime.Date).ToDictionary(g => g.Key, g => g.Count());
var reduce = list.GroupBy(i => i.ViewTime.Date).Select(g =>
{
var count = g.DistinctBy(i => i.IP).Count();
return(new
{
Date = g.Key,
TimeStamp = g.Key.GetTotalMilliseconds(),
pv = g.Count(),
uv = count,
iv = count - dic[g.Key]
});
}).ToList(); //汇总统计
//找出PV最高的一天
var p = reduce.FirstOrDefault(e => e.pv == reduce.Max(a => a.pv));
var highpv = new
{
date = p.Date,
p.pv,
p.uv
};
//找出UV最高的一天
var u = reduce.FirstOrDefault(e => e.uv == reduce.Max(a => a.uv));
var highuv = new
{
date = u.Date,
u.pv,
u.uv
};
//汇总统计
var pv = reduce.Select(g => new List <object>
{
g.TimeStamp,
g.pv
}).ToList(); //每日PV
var uv = reduce.Select(g => new List <object>
{
g.TimeStamp,
g.uv
}).ToList(); //每日UV
var iv = reduce.Select(g => new List <object>
{
g.TimeStamp,
g.iv
}).ToList(); //每日新增独立访客
////访问时长统计
//InterviewAnalysisDto maxSpanViewer = list.OrderByDescending(i => i.OnlineSpanSeconds).Select(i => new InterviewAnalysisDto
//{
// ViewTime = i.ViewTime,
// IP = i.IP,
// BrowserType = i.BrowserType,
// Province = i.Province,
// OnlineSpanSeconds = i.OnlineSpanSeconds
//}).FirstOrDefault(); //历史最久访客
//InterviewAnalysisDto maxSpanViewerToday = list.Where(i => i.ViewTime >= DateTime.Today).OrderByDescending(i => i.OnlineSpanSeconds).Select(i => new InterviewAnalysisDto
//{
// ViewTime = i.ViewTime,
// IP = i.IP,
// BrowserType = i.BrowserType,
// Province = i.Province,
// OnlineSpanSeconds = i.OnlineSpanSeconds
//}).FirstOrDefault(); //今日最久访客
double average = 0;
double average2 = 0;
if (list.Any(i => i.OnlineSpanSeconds > 0 && i.ViewTime >= DateTime.Today))
{
average = list.Where(i => i.OnlineSpanSeconds > 0).Average(i => i.OnlineSpanSeconds);
average2 = list.Where(i => i.OnlineSpanSeconds > 0 && i.ViewTime >= DateTime.Today).Average(i => i.OnlineSpanSeconds);
}
var averSpan = TimeSpan2String(TimeSpan.FromSeconds(average)); //平均访问时长
var averSpanToday = TimeSpan2String(TimeSpan.FromSeconds(average2)); //今日访问时长
BounceRateAggregate todayDap = dapAgg.LastOrDefault();
var all = new AnalysisModel()
{
Browser = allBrowser,
Client = allClient,
Region = allRegion,
Highpv = highpv,
Highuv = highuv,
Iv = iv,
Monthpv = monthpv,
Monthuv = monthuv,
Pv = pv,
Todaypv = todaypv,
Todayuv = todayuv,
//Totalpv = totalpv,
//Totaluv = totaluv,
Uv = uv,
//Yearpv = yearpv,
//Yearuv = yearuv,
BounceRate = $"{dap?.Dap}/{dap?.All}({dap?.Result:P})",
BounceRateAggregate = dapAgg.Select(a => new object[]
{
a.Time.GetTotalMilliseconds(),
a.Rate * 100
}).ToList(),
BounceRateToday = $"{todayDap?.Dap}/{todayDap?.All}({todayDap?.Rate:P})",
OnlineSpanAggregate = new
{
//maxSpanViewerToday = new
//{
// maxSpanViewerToday?.IP,
// maxSpanViewerToday?.BrowserType,
// maxSpanViewerToday?.Province,
// maxSpanViewerToday?.ViewTime,
// OnlineSpanSeconds = TimeSpan2String(TimeSpan.FromSeconds(maxSpanViewerToday?.OnlineSpanSeconds ?? 0))
//},
averSpanToday,
//maxSpanViewer = new
//{
// maxSpanViewer?.IP,
// maxSpanViewer?.BrowserType,
// maxSpanViewer?.Province,
// maxSpanViewer?.ViewTime,
// OnlineSpanSeconds = TimeSpan2String(TimeSpan.FromSeconds(maxSpanViewer?.OnlineSpanSeconds ?? 0))
//},
averSpan,
}
};
var unique = all.Copy();
unique.Browser = uniBrowser;
unique.Client = uniClient;
unique.Region = uniRegion;
return(all, unique);
}
