在两列上分组并创建两个独立的计数 [英] Group on Two Columns and Create Two Separate Counts

问题描述

我们有一个StudentId和LectureId的表，我们想知道两件事.

We have a table of StudentId and LectureId, and we want to know two things.

1. CountStudentId每个StudentId出现多少次.
2. CountStudentIdLectureId每对StudentId + LectureId对出现多少次.

1. CountStudentId How many times each StudentId occurs.
2. CountStudentIdLectureId How many times each StudentId + LectureId pair occurs.

(2)在下面完成. (1)不是.

(2) is done in below. (1) is not.

换句话说，我们如何在一个查询中计算两个不同的组?

对此的另一种思考方式是对StudentId + LectureId组进行计数，并对每个StudentId的计数求和.

Another way of thinking about this, would be to count the StudentId + LectureId group and also sum that count for each StudentId.

以下查询组位于StudentId + LectureId.它确实计算StudentId + LectureId组出现的次数.它不计算StudentId组出现的次数.那也是我们想要的.

The following query groups on StudentId + LectureId. It does count how many times the StudentId + LectureId group occurs. It doesn't count how many times the StudentId group occurs. That's what we also want.

var query = joinTable
    .GroupBy(jt => new { jt.StudentId, jt.LectureId } )
    .Select(g => new {
        StudentId = g.Key.StudentId,
        LectureId = g.Key.LectureId, 
        CountStudentId =  -1, // Count all StudentId (i.e. 10)?
        CountStudentIdLectureId = g.Count()
    });

这是我们当前收到的结果.在每一行中，-1的值应为10(因为我们为JoinTable注入了每个StudentId的十个值)，但尚未实现.

This is the result we're currently receiving. In each row, the -1 value should be 10 (because we seeded the JoinTable with ten of each StudentId) and we haven't achieved that.

...但在每种情况下都使用10而不是-1.**

...but with 10 instead of -1 in each case.**

StudentId      LectureId      CountStudentId   CountStudentLectureId
0              0              -1               3              
0              1              -1               3              
0              2              -1               3              
0              3              -1               1              
1              0              -1               2              
1              1              -1               3              
1              2              -1               3              
1              3              -1               2

在这些结果中，我们需要CountStudentId成为10而不是-1(后者目前只是一个占位符.)

In those results, we need CountStudentId to be 10 not -1 (the latter is just a placeholder for now.)

这是预期的结果，因为每个StudentId出现10次，并且每个StudentId的CountStudentLectureId之和为10，这只是说同一件事的两种方式.

That's the expected result, because each StudentId occurs 10 times and because the sum of CountStudentLectureId for each StudentId is 10, which is just two ways of saying the same thing.

这是完整的Fiddle代码供参考.

using System;
using System.Linq;
using System.Collections.Generic;

public static class Program
{
    public static void Main()
    {
        var joinTable = SeedJoinTable();

        var query = joinTable
            .GroupBy(jt => new { jt.StudentId, jt.LectureId } )
            .Select(g => new {
                StudentId = g.Key.StudentId,
                LectureId = g.Key.LectureId, 
                CountStudentId =  -1, // Count all StudentId (i.e. 10)?
                CountStudentIdLectureId = g.Count()
            });

        // this is just the printing of the results
        Console.WriteLine(
            "StudentId".PadRight(15) +
            "LectureId".PadRight(15) +
            "CountStudentId".PadRight(17) +
            "CountStudentLectureId".PadRight(15));

        foreach(var x in query)
        {
            Console.WriteLine(string.Format("{0}{1}{2}{3}", 
                x.StudentId.ToString().PadRight(15), 
                x.LectureId.ToString().PadRight(15), 
                x.CountStudentId.ToString().PadRight(17), 
                x.CountStudentIdLectureId.ToString().PadRight(15)));
        }
    }

    public static List<JoinTable> SeedJoinTable()
    {
        var list = new List<JoinTable>();
        var studentId = 0;
        var lectureId = 0;

        // insert 20 records
        for(int i = 0; i < 20; ++i)
        {
            if(i != 0)
            {
                if(i % 10 == 0) 
                {   
                    // 10 of each studentId
                    ++studentId;
                    lectureId = 0;
                }
                if(i % 3 == 0)
                {
                    // 3 of each lectureId per student
                    ++lectureId;
                }
            }

            list.Add(new JoinTable() { 
                StudentId = studentId, 
                LectureId = lectureId 
            });
        }
        return list;
    }

    public class JoinTable
    {
        public int StudentId { get; set; }
        public int LectureId { get; set; }
    }
}

推荐答案

这是一个工作的DotNotFiddle，它会产生所需的结果实现.

您将要按StudentId分组并将其值设置为LectureId.这样，您就可以同时获取studentId和studentIdLectureId对的数量.

You will want to group by StudentId and set the value to LectureId. This allows you to get the count of both studentId and studentIdLectureId pairs.

        var query = joinTable
        .GroupBy(jt => jt.StudentId, jt => jt.LectureId)
        .Select(x => 
             new {
            StudentId = x.Key,
            CountStudentId = x.Count(),
            LectureIds = x.GroupBy(y => y),
        });

这确实改变了循环遍历最终列表的方式，但是将为您提供相同的数据以及相同的循环次数:

This does alter how you will loop through the final list, but will provide you the same data with the same amount of loops:

    foreach(var x in query)
    {
        foreach(var lectureId in x.LectureIds)
        {
            Console.WriteLine(string.Format("{0}{1}{2}{3}", 
                x.StudentId.ToString().PadRight(15), 
                lectureId.Key.ToString().PadRight(15), 
                x.CountStudentId.ToString().PadRight(17), 
                lectureId.Count().ToString().PadRight(15)));
        }
    }

如果您想在演讲者ID中包含任何内容(演讲者姓名，教授等)，则可以这样操作:

If you want to include anything with the lectureId (lecture name, professor, etc.) you can do so like this:

        var query = joinTable
        .GroupBy(jt => jt.StudentId, jt => new {LectureId = jt.LectureId, ProfessorId = jt.ProfessorId})
        .Select(x => 
             new {
            StudentId = x.Key,
            CountStudentId = x.Count(),
            LectureIds = x.GroupBy(y => y),
        });

这篇关于在两列上分组并创建两个独立的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！