sql Server: ранжування за сумою балів і порядку за рейтингом

У мене є таблиця ігор з такими полями:

ID    Name       Email      Points
----------------------------------
1     John     [email protected]    120
2     Test     [email protected]     100
3     John     [email protected]    80
4     Bob      [email protected]     50
5     John     [email protected]    80

Я хочу об'єднати їх по електронній пошті (електронна пошта Визначає, що обидва гравці однакові незалежно від того, які рядки 2 і 4 мають різні імена), а також мають суму очок і останнє введено в результатах і класифікують їх з найбільшою кількістю балів до найнижчого

Результат, який я хочу отримати з прикладної таблиці:

Ranking     Name       Points   Games_Played      Average_Points 
------------------------------------------------------------------------------------------
 1          John        200         2                100
 2          Bob         150         2                75
 3          John        80          1                80

Я міг домогтися отримання рейтингу, суми балів і середніх балів, але отримавши останнє введене ім'я, я думаю, що потрібно знову приєднатися до тієї ж таблиці, і здається, що це трохи неправильно.

Будь-які ідеї, як це зробити?

5

7 Відповіді

Відображення імені та групування електронної пошти призведе до використання напр. MIN (ім'я) і призводять до дублювання імен.

Select Rank() over (order by Points desc) as Rank
,Name,Points,Games_Played,Average_Points
from
(
Select Min(Name) as Name,Email,Sum(Points) as Points
,Count(*) as Games_Played,AVG(Points) as Average_Points
From @a Group by Email
) a 
order by Rank

SQLFiddle

у скрипці є дві прокоментовані рядки, які ви повинні розкомментировать, щоб побачити поведінку на ідентичних результатах.

4
додано
Чудовий відповідь. Спасибі ..... вирішили мою проблему і дізналися деякі нові речі. не повинно бути Макс (ім'я)? що означає Min (назва)?
додано Автор Ashkan Mobayen Khiabani, джерело
Ну, я просто хочу знати, що таке функціонал Max або Min для імені, як його не число, чи працює він так, як словник?
додано Автор Ashkan Mobayen Khiabani, джерело
Я б використовував електронну пошту, щоб бути чітким і пропустити ім'я, це до вас, використовуючи MINinimum або MAXimum Name, це свого роду випадковість у будь-якому випадку.
додано Автор bummi, джерело
@AshkanMobayenKhiabani Так ... sqlfiddle.com/#!3/d41d8/16096
додано Автор bummi, джерело

Ви можете скористатися функціями рейтингу з SQL- Server 2005 вгору:

WITH Points 
     AS (SELECT Sum_Points = Sum(points) OVER ( 
                                 partition BY email), 
                Games_Played = Count(ID) OVER ( 
                                 partition BY email), 
                Average_Points = AVG(Points) OVER ( 
                                 partition BY email), 
                Rank = DENSE_RANK()  OVER ( 
                              Partition BY email Order By Points DESC),
                * 
         FROM   dbo.Game)
SELECT Ranking=DENSE_RANK()OVER(ORDER BY Sum_Points DESC), 
       Name, 
       Points=Sum_Points, 
       Games_Played,
       Average_Points
FROM   Points 
WHERE Rank = 1
Order By Sum_Points DESC;

DEMO

Зверніть увагу, що результат відрізняється, оскільки я показую рядок з найвищою точкою у випадку, якщо електронна пошта не є унікальною, тому "Тест" замість "Боб".

3
додано

Ви можете скористатися функціями рейтингу з SQL- Server 2005 вгору:

WITH Points 
     AS (SELECT Sum_Points = Sum(points) OVER ( 
                                 partition BY email), 
                Games_Played = Count(ID) OVER ( 
                                 partition BY email), 
                Average_Points = AVG(Points) OVER ( 
                                 partition BY email), 
                Rank = DENSE_RANK()  OVER ( 
                              Partition BY email Order By Points DESC),
                * 
         FROM   dbo.Game)
SELECT Ranking=DENSE_RANK()OVER(ORDER BY Sum_Points DESC), 
       Name, 
       Points=Sum_Points, 
       Games_Played,
       Average_Points
FROM   Points 
WHERE Rank = 1
Order By Sum_Points DESC;

DEMO

Зверніть увагу, що результат відрізняється, оскільки я показую рядок з найвищою точкою у випадку, якщо електронна пошта не є унікальною, тому "Тест" замість "Боб".

3
додано

Нижче наведено окремі рішення для SQL Server 2012+, 2005 - 2008 R2 і 2000:

2012+

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
VALUES
      (1, 'John', '[email protected]', 120)
    , (2, 'Test', '[email protected]', 100)
    , (3, 'John', '[email protected]', 80)
    , (4, 'Bob', '[email protected]', 50)
    , (5, 'John', '[email protected]', 80)

WITH BaseData
AS
    (SELECT ID
        , Email
        , Points
        , LastRecordName = LAST_VALUE(Name) OVER
            (PARTITION BY Email
            ORDER BY ID DESC
            ROWS UNBOUNDED PRECEDING)
    FROM #PlayerPoints)
SELECT Email
    , LastRecordName = MAX(LastRecordName)
    , Points = SUM(Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(Points)
FROM BaseData
GROUP BY Email
ORDER BY Points DESC;

2005 - 2008 R2

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
VALUES
      (1, 'John', '[email protected]', 120)
    , (2, 'Test', '[email protected]', 100)
    , (3, 'John', '[email protected]', 80)
    , (4, 'Bob', '[email protected]', 50)
    , (5, 'John', '[email protected]', 80)

WITH BaseData
AS
    (SELECT ID
        , Email
        , Name
        , ReverseOrder = ROW_NUMBER() OVER
            (PARTITION BY Email
            ORDER BY ID DESC)
    FROM #PlayerPoints)
SELECT pp.Email
    , LastRecordName = MAX(bd.Name)
    , Points = SUM(pp.Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(pp.Points)
FROM #PlayerPoints pp
JOIN BaseData bd
    ON pp.Email = bd.Email
    AND bd.ReverseOrder = 1
GROUP BY pp.Email
ORDER BY Points DESC;

2000

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
SELECT 1, 'John', '[email protected]', 120
UNION ALL
SELECT 2, 'Test', '[email protected]', 100
UNION ALL
SELECT  3, 'John', '[email protected]', 80
UNION ALL
SELECT 4, 'Bob', '[email protected]', 50
UNION ALL
SELECT 5, 'John', '[email protected]', 80;

SELECT pp.Email
    , LastRecordName = MAX(sppmi.Name)
    , Points = SUM(pp.Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(pp.Points)
FROM #PlayerPoints pp
JOIN 
    (SELECT spp.Email
        , spp.Name
    FROM #PlayerPoints spp
    JOIN 
        (SELECT Email
            , MaximumID = MAX(ID)
        FROM #PlayerPoints
        GROUP BY Email) mi
        ON spp.ID = mi.MaximumID) sppmi
    ON pp.Email = sppmi.Email
GROUP BY pp.Email
ORDER BY Points DESC;
2
додано
+1. . . Ваше єдине рішення, яке коректно обробляє фактичне обмеження на назву. Я вважаю, що рішення обмежене, покладаючись на функцію, доступну тільки в SQL Server 2012, коли це не вказано в ОП.
додано Автор Gordon Linoff, джерело
Я також додам рішення 2005+.
додано Автор Registered User, джерело
І додано 2000 рішення.
додано Автор Registered User, джерело

Нижче наведено окремі рішення для SQL Server 2012+, 2005 - 2008 R2 і 2000:

2012+

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
VALUES
      (1, 'John', '[email protected]', 120)
    , (2, 'Test', '[email protected]', 100)
    , (3, 'John', '[email protected]', 80)
    , (4, 'Bob', '[email protected]', 50)
    , (5, 'John', '[email protected]', 80)

WITH BaseData
AS
    (SELECT ID
        , Email
        , Points
        , LastRecordName = LAST_VALUE(Name) OVER
            (PARTITION BY Email
            ORDER BY ID DESC
            ROWS UNBOUNDED PRECEDING)
    FROM #PlayerPoints)
SELECT Email
    , LastRecordName = MAX(LastRecordName)
    , Points = SUM(Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(Points)
FROM BaseData
GROUP BY Email
ORDER BY Points DESC;

2005 - 2008 R2

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
VALUES
      (1, 'John', '[email protected]', 120)
    , (2, 'Test', '[email protected]', 100)
    , (3, 'John', '[email protected]', 80)
    , (4, 'Bob', '[email protected]', 50)
    , (5, 'John', '[email protected]', 80)

WITH BaseData
AS
    (SELECT ID
        , Email
        , Name
        , ReverseOrder = ROW_NUMBER() OVER
            (PARTITION BY Email
            ORDER BY ID DESC)
    FROM #PlayerPoints)
SELECT pp.Email
    , LastRecordName = MAX(bd.Name)
    , Points = SUM(pp.Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(pp.Points)
FROM #PlayerPoints pp
JOIN BaseData bd
    ON pp.Email = bd.Email
    AND bd.ReverseOrder = 1
GROUP BY pp.Email
ORDER BY Points DESC;

2000

CREATE TABLE #PlayerPoints
    ( ID INT PRIMARY KEY
    , Name VARCHAR(10) NOT NULL
    , Email VARCHAR(20) NOT NULL
    , Points INT NOT NULL);

INSERT INTO #PlayerPoints (ID, Name, Email, Points)
SELECT 1, 'John', '[email protected]', 120
UNION ALL
SELECT 2, 'Test', '[email protected]', 100
UNION ALL
SELECT  3, 'John', '[email protected]', 80
UNION ALL
SELECT 4, 'Bob', '[email protected]', 50
UNION ALL
SELECT 5, 'John', '[email protected]', 80;

SELECT pp.Email
    , LastRecordName = MAX(sppmi.Name)
    , Points = SUM(pp.Points)
    , Games_Played = COUNT(*)
    , Average_Points = AVG(pp.Points)
FROM #PlayerPoints pp
JOIN 
    (SELECT spp.Email
        , spp.Name
    FROM #PlayerPoints spp
    JOIN 
        (SELECT Email
            , MaximumID = MAX(ID)
        FROM #PlayerPoints
        GROUP BY Email) mi
        ON spp.ID = mi.MaximumID) sppmi
    ON pp.Email = sppmi.Email
GROUP BY pp.Email
ORDER BY Points DESC;
2
додано
+1. . . Ваше єдине рішення, яке коректно обробляє фактичне обмеження на назву. Я вважаю, що рішення обмежене, покладаючись на функцію, доступну тільки в SQL Server 2012, коли це не вказано в ОП.
додано Автор Gordon Linoff, джерело
Я також додам рішення 2005+.
додано Автор Registered User, джерело
І додано 2000 рішення.
додано Автор Registered User, джерело

Я думаю, що це те, що вам потрібно

select ROW_NUMBER() OVER (ORDER BY sum(r1.points) Desc) as Ranking,
    r1.name as Name,
    sum(r1.points) as Points,
    r3.gplayed as 'Games Played',
    r2.points 'Average Points'
from ranks r1
    join (select avg(points) as points, email from ranks group by email) r2 
        on r1.email = r2.email
    join (select email, count(*) as gplayed from ranks group by email) r3 
        on r1.email = r3.email
group by 
    r1.email, 
    r1.name, 
    r2.points, 
    r3.gplayed

Ось скрипка SQL .

0
додано

Тільки рішення з @RegisteredUser, схоже, обробляє обмеження на name . Тим не менш, для цього потрібно SQL Server 2012, тому тут є більш загальне рішення:

      Select dense_rank() over (order by sum(points) desc) as ranking
             max(case when islastid = 1 then Name end) as Name, Email, Sum(Points) as Points, 
             Count(*) as Games_Played, AVG(Points) as Average_Points
      From (select g.*,
                   row_number() over (partition by email order by id desc) as islastid
            from games g
           ) t
      Group by Email;

Ви не маєте достатньо інформації в запиті, щоб вибрати між rank() і dense_rank() .

Крім того, ця версія є простішою щодо інших версій, тому що ви можете змішувати віконні функції та функції агрегації.

0
додано