sql Сервер: рейтингі бойынша ұпай саны мен рейтингі бойынша ранжирлеу

Мына өрістермен ойын үстелім бар:

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

Мен оларды электрондық пошта арқылы жинағым келеді (email 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 (Name) және қайталанатын атауларға әкеледі.

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

Fiddle-де бірдей нәтиже бойынша мінез-құлықты көру үшін түсіндірілмеген екі түсінік сызығы бар.

4
қосылды
Әдемі жауап. Рахмет ..... менің проблемамды шешіп, жаңа нәрселерді үйренді. Макс (аты) болмауы керек пе? Мин дегеніміз не?
қосылды автор Ashkan Mobayen Khiabani, көзі
Жақсы ғана білуге ​​келеді, бұл Max немесе Min функционалдығы атау үшін нөмір емес, ол сөздікті тапсырыс ретінде жұмыс істейді?
қосылды автор Ashkan Mobayen Khiabani, көзі
Электрондық поштаны нақты анықтап, есімін алып тастаймын, MININimum немесе MAXimum Name арқылы бұл сізге қатысты, бұл кездейсоқ кез келген жағдайда.
қосылды автор bummi, көзі
@AshkanMobayenHhiabani Иә ... sqlfiddle.com/#!3/d41d8/16062
қосылды автор bummi, көзі

SQL-файлдан Ranking Functions пайдалануыңызға болады. 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

Электрондық пошта бірегей болмаса, ең жоғары нүкте бар жолды көрсететіндіктен нәтиже әртүрлі екенін ескеріңіз, сондықтан «Bob» орнына «Тест».

3
қосылды

SQL-файлдан Ranking Functions пайдалануыңызға болады. 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

Электрондық пошта бірегей болмаса, ең жоғары нүкте бар жолды көрсететіндіктен нәтиже әртүрлі екенін ескеріңіз, сондықтан «Bob» орнына «Тест».

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 Fiddle .

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
қосылды