Группировка и подсчет

У меня есть такой набор данных -

**Team  Date    W/L**
Team_1  04/01/0012  W
Team_1  06/01/0012  W
Team_1  07/01/0012  L
Team_1  14/01/0012  W
Team_1  19/01/0012  W
Team_1  30/01/0012  L
Team_1  14/02/0012  W
Team_1  17/02/0012  L
Team_1  20/02/0012  W
Team_2  01/01/0012  W
Team_2  05/01/0012  W
Team_2  09/01/0012  W
Team_2  13/01/0012  L
Team_2  18/01/0012  W
Team_2  25/01/0012  L
Team_2  05/02/0012  L
Team_2  13/02/0012  L
Team_2  19/02/0012  L
Team_3  02/01/0012  W
Team_3  02/01/0012  W
Team_3  06/01/0012  W
Team_3  10/01/0012  W
Team_3  19/01/0012  W
Team_3  31/01/0012  L
Team_3  11/02/0012  W
Team_3  15/02/0012  L
Team_3  21/02/0012  W

И из этого мне нужно выяснить, у кого были самые большие последовательные победы --

Количество команд

Team_3  5
Team_2  3
Team_1  2

Мне разрешено писать только sql-запросы. Как я могу написать это?


sql cumulative-sum oracle11g
person Plymouth Rock    schedule 24.09.2013    source источник
comment
Какую базу данных вы используете? (SQL Server, MySQL, Oracle, ...)   -  person Andomar    schedule 24.09.2013

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете использовать следующее:

SELECT  Team, TotalWins, FirstWin, LastWin
FROM    (   SELECT  Team, 
                    WL,
                    COUNT(*) TotalWins,
                    MIN("Date") FirstWin,
                    MAX("Date") LastWin,
                    ROW_NUMBER() OVER(PARTITION BY Team, WL ORDER BY COUNT(*) DESC) RowNumber
            FROM    (   SELECT  Team,
                                "Date",
                                WL, 
                                ROW_NUMBER() OVER(PARTITION BY Team ORDER BY "Date") - ROW_NUMBER() OVER(PARTITION BY Team, WL ORDER BY "Date") Grouping
                        FROM    T
                    ) GroupedData
            WHERE   WL = 'W'
            GROUP BY Team, WL, Grouping
        ) RankedData
WHERE   RowNumber = 1;

Он использует ROW_NUMBER для ранжирования каждой игры, разделенной на команды, а также по результатам, разница между которыми уникальна для каждой группы последовательных результатов. Итак, для вашей первой команды у вас будет:

Team    Date        W/L RN1     RN2 DIFF
Team_1  04/01/0012  W   1       1   0
Team_1  06/01/0012  W   2       2   0
Team_1  07/01/0012  L   3       1   2
Team_1  14/01/0012  W   4       3   1
Team_1  19/01/0012  W   5       4   1
Team_1  30/01/0012  L   6       2   4
Team_1  14/02/0012  W   7       5   2
Team_1  17/02/0012  L   8       3   5
Team_1  20/02/0012  W   9       6   3

Где RN1 просто разбивается по командам, а rn2 разбивается по командам и результату.

Как видите, если убрать Поражения, то столбец DIFF увеличивается на единицу для каждой группы последовательных побед:

Team    Date        W/L RN1     RN2 DIFF
Team_1  04/01/0012  W   1       1   0
Team_1  06/01/0012  W   2       2   0
---------------------------------------
Team_1  14/01/0012  W   4       3   1
Team_1  19/01/0012  W   5       4   1
---------------------------------------
Team_1  14/02/0012  W   7       5   2
---------------------------------------
Team_1  20/02/0012  W   9       6   3

Затем вы можете сгруппировать по этому, чтобы убедиться, что вы смотрите на последовательные победы, и сделать подсчет, чтобы получить максимальную отдачу. Затем я просто использовал другой номер строки, чтобы получить максимальное количество последовательных побед для каждой команды.

Пример скрипта SQL

person GarethD    schedule 24.09.2013