C#ToLookup代替GroupBy实现分组统计功能

小车子66

已于 2024-07-05 14:42:17 修改

阅读量159

点赞数 4

分类专栏： C# 数据统计 LINQ 文章标签： c#

于 2024-07-04 16:59:31 首次发布

本文链接：https://blog.csdn.net/a525192407/article/details/140182456

版权

C# 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

数据统计

1 篇文章 0 订阅

订阅专栏

LINQ

1 篇文章 0 订阅

订阅专栏

假设有20万的数据,需要根据来源链接实现第一级分组统计,再根据来源链接中的广告单元实现二级统计

广告数据实体:


public class AdStatisticsOutput
{
    public int Id { get; set; }
    public string SourceUrl { get; set; }
    public string Host { get; set; }
    public string Origin { get; set; }
    public string Referer { get; set; }
    public DateTime ReportTime { get; set; }
    public int AdUnitId { get; set; }
    public int AdMaterialId { get; set; }
    public string AdUnitName { get; set; }
    /// <summary>
    /// 上报类型，0：浏览，1：点击
    /// </summary>
    public int EscalationType { get; set; }
    public int AdTypeId { get; set; }
    public string AdTypeName { get; set; }
    public string SourceUrlArticleTitle { get; set; }
    public string SourceUrlArticleId { get; set; }
}

一级统计主表实体:

/// <summary>
/// 报表-链接
/// </summary>
[SugarTable(null, "报表-链接")]
public partial class AdStatisticsByArticle : EntityBase
{
    /// <summary>
    /// 统计日期
    /// </summary>
    [SugarColumn(ColumnDescription = "统计日期", IsNullable = true)]
    public DateTime StatisticsTime { get; set; }
    /// <summary>
    /// 广告单元Id
    /// </summary>
    [SugarColumn(ColumnDescription = "广告单元Id", IsNullable = true)]
    public int AdUnitId { get; set; }
    /// <summary>
    /// 广告单元Id
    /// </summary>
    [SugarColumn(ColumnDescription = "文章链接", Length = 1000, IsNullable = true)]
    public string SourceUrl { get; set; }
    /// <summary>
    /// 广告单元Id
    /// </summary>
    [SugarColumn(ColumnDescription = "文章链接标题", Length = 2000, IsNullable = true)]
    public string SourceUrlTitle { get; set; }
    /// <summary>
    /// 广告单元名称
    /// </summary>
    [SugarColumn(ColumnDescription = "文章内容Id", IsNullable = true)]
    public string SourceUrlArticleId { get; set; }
    /// <summary>
    /// 曝光量
    /// </summary>
    [SugarColumn(ColumnDescription = "曝光量", DefaultValue = "0")]
    public int TotalExposureCount { get; set; }
    /// <summary>
    /// 点击量
    /// </summary>
    [SugarColumn(ColumnDescription = "点击量", DefaultValue = "0")]
    public int TotalClickCount { get; set; }
    /// <summary>
    /// 点击率
    /// </summary>
    [SugarColumn(ColumnDescription = "点击率，%", DefaultValue = "0")]
    public decimal Rate { get; set; }

}

二级统计子表实体:

/// <summary>
/// 报表-链接详情
/// </summary>
[SugarTable(null, "报表-链接详情")]
public partial class AdStatisticsByArticleDetail : EntityBase
{
    /// <summary>
    /// 父级id
    /// </summary>
    [SugarColumn(ColumnDescription = "父级id", DefaultValue = "0")]
    public long ParentId { get; set; }
    /// <summary>
    /// 统计日期
    /// </summary>
    [SugarColumn(ColumnDescription = "统计日期", IsNullable = true)]
    public DateTime StatisticsTime { get; set; }
    /// <summary>
    /// 广告单元名称
    /// </summary>
    [SugarColumn(ColumnDescription = "广告单元id", IsNullable = true)]
    public int AdUnitId { get; set; }
    /// <summary>
    /// 广告单元名称
    /// </summary>
    [SugarColumn(ColumnDescription = "广告单元名称", IsNullable = true)]
    public string AdUnitName { get; set; }
    /// <summary>
    /// 广告单元名称
    /// </summary>
    [SugarColumn(ColumnDescription = "广告单元类型id", IsNullable = true)]
    public int AdTypeId { get; set; }
    /// <summary>
    /// 广告单元名称
    /// </summary>
    [SugarColumn(ColumnDescription = "广告单元类型名称", IsNullable = true)]
    public string AdTypeName { get; set; }
    /// <summary>
    /// 曝光量
    /// </summary>
    [SugarColumn(ColumnDescription = "曝光量", DefaultValue = "0")]
    public int ExposureCount { get; set; }
    /// <summary>
    /// 点击量
    /// </summary>
    [SugarColumn(ColumnDescription = "点击量", DefaultValue = "0")]
    public int ClickCount { get; set; }
    /// <summary>
    /// 点击率
    /// </summary>
    [SugarColumn(ColumnDescription = "点击率，%", DefaultValue = "0")]
    public decimal Rate { get; set; }

}

GroupBy方式实现:

public class GroupByToData(SqlSugarScope db, List<AdStatisticsOutput> source)
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    try
    {
        //根据域名完成groupBy
        var groupByList = source.GroupBy(x => x.SourceUrl);
        var inslist = new List<AdStatisticsByArticle>();
        foreach (var item in groupByList)
        {
            var exposureCount = source.Where(x => x.SourceUrl == item.Key).Where(x => x.EscalationType == 0).Count();
            var clickCount = source.Where(x => x.SourceUrl == item.Key).Where(x => x.EscalationType == 1).Count();
            var ins = new AdStatisticsByArticle
            {
                StatisticsTime = DateTime.Now.Date,
                SourceUrl = item.Key,
                TotalExposureCount = exposureCount,
                TotalClickCount = clickCount,
                Rate = exposureCount > 0 ? Math.Round((decimal)clickCount / (decimal)exposureCount, 4) : 0,
                SourceUrlTitle = source.Where(x => x.SourceUrl == item.Key).Select(x => x.SourceUrlArticleTitle).FirstOrDefault(),
                SourceUrlArticleId = source.Where(x => x.SourceUrl == item.Key).Select(x => x.SourceUrlArticleId).FirstOrDefault()
            };
            //当前循环处理会有较大的性能损耗
            inslist.Add(ins);
        }
        //所有域名下的数据批量入主表数据
        var dbmainIds = await db.Insertable(inslist).ExecuteReturnPkListAsync<int>();
        //使用SqlSugar的批量插入返回批量插入的主键(当前实现方式用时很久能执行完毕)
        //批量插入后主表的ID和统计字段
        var childList = new List<AdStatisticsByArticleDetail>();
        for (int i = 0; i < dbmainIds.Count; i++)
        {
            //根据域名和主表ID完成子表数据统计
            var childListByDomainName = source.Where(x => x.SourceUrl == inslist[i].SourceUrl).ToList();
            var childListByDomainNameGroupBy = childListByDomainName.GroupBy(x => x.AdUnitId);

            foreach (var item in childListByDomainNameGroupBy)
            {
                string? adUnitName = childListByDomainName.Where(x => x.AdUnitId == item.Key).Select(x => x.AdUnitName).FirstOrDefault();
                var adTypeId = childListByDomainName.Where(x => x.AdUnitId == item.Key).Select(x => x.AdTypeId).FirstOrDefault();
                var adTypeName = childListByDomainName.Where(x => x.AdUnitId == item.Key).Select(x => x.AdTypeName).FirstOrDefault();
                var totalExposureCount = childListByDomainName.Where(x => x.AdUnitId == item.Key && x.EscalationType == 0).Count();
                var totalClickCount = childListByDomainName.Where(x => x.AdUnitId == item.Key && x.EscalationType == 1).Count();
                var child = new AdStatisticsByArticleDetail
                {
                    ParentId = dbmainIds[i],
                    StatisticsTime = DateTime.Now.Date,
                    AdUnitId = item.Key,
                    AdUnitName = adUnitName,
                    AdTypeId = adTypeId,
                    AdTypeName = adTypeName,
                    ExposureCount = totalExposureCount,
                    ClickCount = totalClickCount,
                    Rate = totalExposureCount > 0 ? Math.Round((decimal)totalClickCount / (decimal)totalExposureCount, 4) : 0
                };
                childList.Add(child);
            }
        }
        //处理子表数据
        await db.Insertable(childList).ExecuteCommandAsync();
        pr.PR(ServiceName, $"报表-来源链接 统计成功", 1);
        stopwatch.Stop();
        pr.PR(ServiceName, $"报表-来源链接 统计完成，耗时：{stopwatch.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"报表-来源链接 统计完成，耗时：{stopwatch.Elapsed.TotalSeconds}秒");
    }
    catch (Exception ex)
    {
        Log.Error(ex.Message);
        pr.PR(ServiceName, $"报表-来源链接 统计失败，请查看日志", 1);
    }
}

ToLookup方式实现:

private async Task BySourceUrl(SqlSugarScope db, List<AdStatisticsOutput> source)
{
    pr.PR(ServiceName, "统计 报表-来源链接", 1);
    Stopwatch stopwatch = Stopwatch.StartNew();
    try
    {
        //根据域名完成groupBy
        var groupByList = source.ToLookup(x => x.SourceUrl);

        var inslist = new List<AdStatisticsByArticle>();

        var exposuresource = source.Where(x => x.EscalationType == 0).ToLookup(x => x.SourceUrl);
        var clicksource = source.Where(x => x.EscalationType == 1).ToLookup(x => x.SourceUrl);

        #region 计算主表数据
        Stopwatch stopwatch1 = Stopwatch.StartNew();
        foreach (var item in groupByList)
        {
            var id = SnowFlakeSingle.Instance.NextId();
            var exposureCount = exposuresource[item.Key].Count();
            var clickCount = clicksource[item.Key].Count();
            var ins = new AdStatisticsByArticle
            {
                StatisticsTime = DateTime.Now.Date,
                SourceUrl = item.Key,
                TotalExposureCount = exposureCount,
                TotalClickCount = clickCount,
                Rate = exposureCount > 0 ? Math.Round((decimal)clickCount / (decimal)exposureCount, 4) : 0,
                SourceUrlTitle = item.Select(x => x.SourceUrlArticleTitle).FirstOrDefault(),
                SourceUrlArticleId = item.Select(x => x.SourceUrlArticleId).FirstOrDefault()
            };
            inslist.Add(ins);
        }
        stopwatch1.Stop();
        pr.PR(ServiceName, $"主表内存数据计算完成，耗时：{stopwatch1.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"主表内存数据计算完成，耗时：{stopwatch1.Elapsed.TotalSeconds}秒");
        #endregion

        #region 主表数据入库
        Stopwatch stopwatch2 = Stopwatch.StartNew();
        //所有域名下的数据批量入主表数据
        var dbmainIds = await db.Insertable(inslist).ExecuteReturnPkListAsync<int>();
        stopwatch2.Stop();
        pr.PR(ServiceName, $"主表插入数据完成，耗时：{stopwatch2.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"主表插入数据完成，耗时：{stopwatch2.Elapsed.TotalSeconds}秒");
        #endregion

        #region 计算子表数据  
        Stopwatch stopwatch3 = Stopwatch.StartNew();
        var childList = new List<AdStatisticsByArticleDetail>();
        for (int i = 0; i < dbmainIds.Count; i++)
        {
            //根据域名和主表ID完成子表数据统计
            //var childListByDomainName = source.Where(x => x.SourceUrl == inslist[i].SourceUrl).ToList();
            var childListByDomainName = groupByList[inslist[i].SourceUrl].ToList();

            var childListByDomainNameGroupBy = childListByDomainName.ToLookup(x => x.AdUnitId);

            foreach (var item in childListByDomainNameGroupBy)
            {
                string? adUnitName = item.Select(x => x.AdUnitName).FirstOrDefault();
                var adTypeId = item.Select(x => x.AdTypeId).FirstOrDefault();
                var adTypeName = item.Select(x => x.AdTypeName).FirstOrDefault();
                var totalExposureCount = item.Where(x => x.EscalationType == 0).Count();
                var totalClickCount = item.Where(x => x.EscalationType == 1).Count();

                var child = new AdStatisticsByArticleDetail
                {
                    ParentId = dbmainIds[i],
                    StatisticsTime = DateTime.Now.Date,
                    AdUnitId = item.Key,
                    AdUnitName = adUnitName,
                    AdTypeId = adTypeId,
                    AdTypeName = adTypeName,
                    ExposureCount = totalExposureCount,
                    ClickCount = totalClickCount,
                    Rate = totalExposureCount > 0 ? Math.Round((decimal)totalClickCount / (decimal)totalExposureCount, 4) : 0
                };
                childList.Add(child);
            }
        }
        stopwatch3.Stop();
        pr.PR(ServiceName, $"子表内存数据计算完成，耗时：{stopwatch3.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"子表内存数据计算完成，耗时：{stopwatch3.Elapsed.TotalSeconds}秒");
        #endregion

        #region 子表数据入库
        Stopwatch stopwatch4 = Stopwatch.StartNew();
        //处理子表数据
        await db.Insertable(childList).ExecuteCommandAsync();
        stopwatch4.Stop();
        pr.PR(ServiceName, $"子表数据入库完成，耗时：{stopwatch4.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"子表数据入库完成，耗时：{stopwatch4.Elapsed.TotalSeconds}秒");
        #endregion

        pr.PR(ServiceName, $"报表-来源链接 统计成功", 1);
        stopwatch.Stop();
        pr.PR(ServiceName, $"报表-来源链接 统计完成，耗时：{stopwatch.Elapsed.TotalSeconds}秒", 1);
        Log.Info($"报表-来源链接 统计完成，耗时：{stopwatch.Elapsed.TotalSeconds}秒");
    }
    catch (Exception ex)
    {
        Log.Error(ex.Message);
        pr.PR(ServiceName, $"报表-来源链接 统计失败，请查看日志", 1);
    }
}

两种实现方式在内存中处理效率相差很多(不是一个量级)

GroupBy方式实现用时