linq划分按值和组无效值或无效值的结果,并且确实计数[英] Group linq results by value and group null or invalid values and do counts

本文是小编为大家收集整理的关于linq划分按值和组无效值或无效值的结果,并且确实计数的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

这是这个问题.我现在想对分组做一些计数.

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

  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:现在有了新的分组,包括无效的邮政编码分组:

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:我也可以做以下我认为:

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();

我正在尝试找出我需要在新查询中的"呼叫"和"曝光"的两个计数中更改的原始查询.完成此操作的方法和需要什么?

[edit] 扩展到同一问题:

如何使用两个或多个属性配置分组.是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 };
    }

推荐答案

首先关于呼叫:

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创建一个新组. 如果我正确理解的话,这些暴露非常相似.

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 and call_id/callerstate,ctr_id and call_id)的唯一组合,则当然可以这样做.

本文地址:https://www.itbaoku.cn/post/1556875.html

问题描述

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();

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();

Grouping method:

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

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?

[Edit] Extension to same issue:

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();

The method:

    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(),

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();

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.