按值对linq结果进行分组，对空值或无效值进行分组，然后进行计数 [英] Group linq results by value and group null or invalid values and do counts

问题描述

这是对

this is a follow on question to this question. I now would like to do some counts on the groupings.

原始查询:排除无效邮政编码的操作如下:

Original Query: that excluded invalid zip codes did the following:

  List<DataSourceRecord> md = (from rst in QBModel.ResultsTable
        where (!String.IsNullOrWhiteSpace(rst.CallerZipCode) && rst.CallerZipCode.Length > 2)
        group rst by rst.CallerZipCode.Substring(0, 3) into newGroup
        orderby newGroup.Key
        select new DataSourceRecord()
        {
          State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
          ZipCode = newGroup.Where(z => z.CallerZipCode.StartsWith(newGroup.Key)).Select(x => x.CallerZipCode.Substring(0, 3)).FirstOrDefault(),
          Calls = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0).Distinct().GroupBy(g => new { g.CallerZipCode, g.CTR_ID, g.CALL_ID }).Count(),
          Exposures = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0 && x.ExposureCount > 0).Distinct().GroupBy(x => new { x.CallerState, x.CTR_ID, x.CALL_ID }).Count()
        }).ToList();

新示例1:现在有了新的分组，包括无效的邮政编码分组:

New Example 1: Now with the new groupings including the invalid zip code groupings:

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by GetGroupRepresentation(rst.CallerZipCode) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = ???
    Exposures = ???
  }).ToList();

分组方法:

private string GetGroupRepresentation(string zipCode)
{
    if (string.IsNullOrEmpty(zipCode) || zipCode.Length < 3)
        return "<null>";
    return zipCode.Substring(0,3);
}

新示例2:我也可以做到以下几点:

New Example 2: I could also do the following I think:

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by rst.CallerZipCode == null || rst.CallerZipCode.Trim().Length < 3 ? "<null>" : rst.CallerZipCode.Substring(0, 3) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = ???
    Exposures = ???
  }).ToList();

我正在尝试找出在原始查询中需要更改的内容，以便在新查询中对通话"和曝光"这两个计数进行分组.完成这项工作的方式和条件是什么?

I am trying to figure out what I need to change in the original query for the two counts for 'Calls' and 'Exposures' for the grouping in the new query. How and what is needed to accomplish this?

相同问题的扩展名:

如何使用两个或多个属性配置分组.是belwo

How to configure grouping with two or more properties. Is belwo

List<DataSourceRecord> 
    newset = (from rst in QBModel.ResultsTable
              group rst by GetGroupRepresentation(rst.CallerZipCode, rst.CallerState) into newGroup
              select new MapDataSourceRecord()
              {
                State = ToTitleCase(newGroup.Select(i => i.CallerState).FirstOrDefault()),
                StateFIPS = FipsForStateCD(newGroup.Select(i => i.CallerStateCD).FirstOrDefault()),
                ZipCode = newGroup.Key[0],
                Calls = newGroup.Where(x => x.CALL_ID > 0).Distinct().Count(),
                Exposures = newGroup.Where(x => x.CALL_ID > 0 && x.EXPO_ID > 0).Distinct().Count(),
                InfoRequests = newGroup.Where(x => x.CALL_ID > 0 && x.INFO_ID > 0).Distinct().Count(),
                Population = GetZipCode3Population(newGroup.Key[0])
              }).ToList();

方法:

    private string[] GetGroupRepresentation(string ZipCode, string State)
    {
      string ZipResult;
      string StateResult;
      if (string.IsNullOrEmpty(ZipCode) || ZipCode.Length < 3)
        ZipResult = "<null>";
      else
        ZipResult = ZipCode.Substring(0, 3);

      if (string.IsNullOrEmpty(State))
        StateResult = "<null>";
      else
        StateResult = State;

      return  new string[]{ ZipResult, State };
    }

推荐答案

首先是关于通话的信息​​:

First about the calls:

Calls = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0).Distinct().GroupBy(g => new { g.CallerZipCode, g.CTR_ID, g.CALL_ID }).Count(),

据我了解，您希望该组在CALL_ID > 0处有不同的通话数量.我不明白为什么您要使用邮政编码，CTR_ID和CALL_ID创建新的网上论坛. 如果我理解正确，那么曝光就非常相似.

As I understand, you want for the group the distinct number of calls where CALL_ID > 0. I don't understand why you create a new group with the zip code, the CTR_ID and the CALL_ID. If I have understood correctly, the Exposures are very similar.

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by GetGroupRepresentation(rst.CallerZipCode) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = newGroup.Where(x => x.CALL_ID > 0).Select(x => x.CALL_ID).Distinct().Count(),
    Exposures = newGroup.Where(x => x.CALL_ID > 0 && x.ExposureCount > 0).Distinct().Count()
  }).ToList();

如果您真的想对通话/曝光进行分组，这意味着您想要计算(CTR_ID和CALL_ID/CallerState，CTR_ID和CALL_ID)的唯一组合，那么您当然可以.

If you really want to group the calls/exposures meaning you want to count the unique combinations of (CTR_ID and CALL_ID / CallerState, CTR_ID and CALL_ID), you can of course do so.

这篇关于按值对linq结果进行分组，对空值或无效值进行分组，然后进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！