如何计算具有特定值和特定用户/人的数据（行中）？ [英] How to count data with specific values and for specific user/person (in row)?

问题描述

我有一个问题，我无法解决...

I have one problem, which I can't handle...

我的PostgreSQL表如下：

My PostgreSQL table looks like that:

id student grade class gradeDate
1     1      5     1       2017-03-03
2     1      5     1       2017-03-04
3     1      1     1       2017-03-05
4     1      5     1       2017-03-06
5     1      5     1       2017-03-07
6     1      5     1       2017-03-08
7     1      1     1       2017-03-09
8     2      5     2       2017-03-03
9     3      5     3       2017-03-03

所以我有不同班级（1,2,3,4 ...）的不同学生（1,2,3 ...），他们每天都获得成绩（gradeDate）
-只能是5或1-现在我要从该数据库中选择每位学生连续5年级的最大数量（按行我是指每天不中断的5年级）
因此，在上表中，用户1的最大计数为3（行4,5,6），用户2的计数为1，用户3的计数为e。
如果我将第3行的成绩更改为5，则最大的分数将是学生1的6。
你知道吗？

So I have different students (1,2,3...) in different classes (1,2,3,4...), who gets grade EVERY DAY (gradeDate) - it can be only 5 or 1 - and now I want to select from this database the biggest count of grade 5 in row for each student (by in row I mean grade 5 every day without break) So in the table above the biggest count for user 1 will be 3 (row 4,5,6), for user 2 will be 1 and for user 3 will be one. If I change grade in row 3 for 5, the biggest count will be 6 for student 1 Do you get the idea?

首先我想以某种方式使用SELECT查询，但首先-我不知道如何进行此查询，其次-当此
表中将有成千上万的行时，该查询的效率将非常高，非常低。
我通常可以获取学生1的每一行并用Java对其进行操作。
所以我问-如何解决这个问题？
感谢您的时间和精力。

At first I wanted to use somehow SELECT query, but firstly - I don't know how to make this query and secondly - when there will be thousands or millions of rows in this table, the efficiency of that query will be very, very low. I can normally get every rows for student 1 and manipulate it in Java. So I ask - how can I solve this problem? Thanks for your time and effort.

推荐答案

您需要标识相邻的组。一种简单的方法是行号的差异。要真正理解它，您需要运行子查询并凝视结果。您应该看到差异如何定义组。

You need to identify adjacent groups. One simple method is the difference of row numbers. To really understand it, you'll need to run the subquery and stare at the results. You should "see" how the difference defines the groups.

select student, class, grade, count(*), min(gradeDate), max(gradeDate)
from (select t.*,
             row_number() over (partition by student, class, grade order by gradeDate) as seqnum_scg,
             row_number() over (partition by student, class order by gradeDate) as seqnum_sc
      from t
     ) t
group by student, class, grade, (seqnum_sc - seqnum_scg);

要获得最大值，您可以在 distinct上使用。我将为此使用子查询：

To get the maximum, you can then use distinct on. I'll use a subquery for that:

select distinct on (student, class) scg.*
from (select student, class, grade, count(*) as cnt,
             min(gradeDate), max(gradeDate), min_gradeDate, max_gradeDate
      from (select t.*,
                   row_number() over (partition by student, class, grade order by gradeDate) as seqnum_scg,
                   row_number() over (partition by student, class order by gradeDate) as seqnum_sc
            from t
           ) t
      where grade = 5
      group by student, class, grade, (seqnum_sc - seqnum_scg)
     ) scg
order by student, class, cnt desc;

这篇关于如何计算具有特定值和特定用户/人的数据（行中）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！