Website: Kill Log Queries

CREATE NONCLUSTERED INDEX IX_PvPKillLog_KillTime ON dbo.PvPKillLog (KillTime DESC) INCLUDE (KillType, Killer, Victim, TotalDmg);

SELECT TOP 20 Id, KillTime, KillType, Killer, KillerGuild, KillerClass, Victim, VictimGuild, VictimClass,
       MapNumber, TotalDmg, TopSkill, AssistCount, FightMs
FROM dbo.PvPKillLog
ORDER BY Id DESC;

SELECT TOP 20 Killer, COUNT(*) AS Kills, SUM(TotalDmg) AS DamageInKills
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE()) AND KillType <> 1
GROUP BY Killer
ORDER BY Kills DESC;

SELECT TOP 10 KillTime, Victim, Killer, TotalDmg, FightMs, AssistCount, MapNumber
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE())
ORDER BY TotalDmg DESC;

SELECT TOP 20 Id, KillTime, KillType,
       CASE WHEN Killer = @name THEN 'Kill' ELSE 'Death' END AS Result,
       CASE WHEN Killer = @name THEN Victim ELSE Killer END AS Opponent,
       MapNumber, TotalDmg, FightMs
FROM dbo.PvPKillLog
WHERE Killer = @name OR Victim = @name
ORDER BY Id DESC;

SELECT TOP 5 Killer, COUNT(*) AS Times
FROM dbo.PvPKillLog
WHERE Victim = @name
GROUP BY Killer
ORDER BY Times DESC;

SELECT KillerGuild AS Guild, COUNT(*) AS Kills
FROM dbo.PvPKillLog
WHERE KillType IN (2, 5) AND KillerGuild <> '' AND KillTime >= DATEADD(day, -7, GETDATE())
GROUP BY KillerGuild
ORDER BY Kills DESC;

SELECT MapNumber, COUNT(*) AS Deaths
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -1, GETDATE())
GROUP BY MapNumber
ORDER BY Deaths DESC;

SELECT TOP 10 TopSkill, COUNT(*) AS Fights
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE()) AND TopSkill < 65533
GROUP BY TopSkill
ORDER BY Fights DESC;

CREATE TABLE dbo.WEB_PvPLadder
(
    Name        varchar(10) NOT NULL PRIMARY KEY,
    Kills       int         NOT NULL DEFAULT 0,
    Deaths      int         NOT NULL DEFAULT 0,
    Assists     int         NOT NULL DEFAULT 0,
    DamageDone  bigint      NOT NULL DEFAULT 0,
    DamageTaken bigint      NOT NULL DEFAULT 0,
    BiggestHit  bigint      NOT NULL DEFAULT 0,
    LastFight   datetime    NULL
)
GO
CREATE TABLE dbo.WEB_PvPLadderState (LastId int NOT NULL)
INSERT INTO dbo.WEB_PvPLadderState (LastId) VALUES (0)
GO
CREATE PROCEDURE dbo.WEB_PvPLadderUpdate
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    DECLARE @From int, @To int;
    SELECT @From = LastId FROM dbo.WEB_PvPLadderState WITH (UPDLOCK, HOLDLOCK);
    SELECT @To = ISNULL(MAX(Id), @From) FROM dbo.PvPKillLog;

    -- New rows to count. Edit the KillType list to choose what counts:
    -- 0 world, 1 duel, 2 siege, 3 event, 4 gens, 5 guild war
    SELECT Id, KillTime, Victim, Killer, TotalDmg, Detail
    INTO #rows
    FROM dbo.PvPKillLog
    WHERE Id > @From AND Id <= @To
      AND KillType IN (0, 2, 3, 4, 5);

    -- Read the Detail blob (format version 1). Numbers in it are little-endian.
    -- Attacker i starts at byte 13 + i*32, skill row j at 13 + AttackerCount*32 + j*21.
    SELECT r.Id, n.v AS Idx, 13 + n.v * 32 AS P, r.Detail
    INTO #a
    FROM #rows r
    JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7)) n(v)
      ON n.v < CAST(SUBSTRING(r.Detail, 2, 1) AS int)              -- AttackerCount
    WHERE SUBSTRING(r.Detail, 1, 1) = 0x01;                         -- Version 1

    SELECT r.Id,
           CAST(SUBSTRING(r.Detail, s.P, 1) AS int) AS Idx,
           CAST(CAST(SUBSTRING(r.Detail, s.P + 20, 1) + SUBSTRING(r.Detail, s.P + 19, 1)
                   + SUBSTRING(r.Detail, s.P + 18, 1) + SUBSTRING(r.Detail, s.P + 17, 1) AS binary(4)) AS bigint) AS Biggest
    INTO #s
    FROM #rows r
    CROSS APPLY (SELECT 13 + CAST(SUBSTRING(r.Detail, 2, 1) AS int) * 32 + k.v * 21 AS P
                   FROM (SELECT TOP (48) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS v FROM sys.all_objects) k
                  WHERE k.v < CAST(SUBSTRING(r.Detail, 3, 1) AS int)) s   -- SkillCount
    WHERE SUBSTRING(r.Detail, 1, 1) = 0x01;

    SELECT a.Id,
           LEFT(x.RawName, CHARINDEX(CHAR(0) COLLATE Latin1_General_BIN, x.RawName COLLATE Latin1_General_BIN + CHAR(0)) - 1) AS Name,
           CAST(CAST(SUBSTRING(a.Detail, a.P + 24, 1) + SUBSTRING(a.Detail, a.P + 23, 1)
                   + SUBSTRING(a.Detail, a.P + 22, 1) + SUBSTRING(a.Detail, a.P + 21, 1) AS binary(4)) AS bigint)     -- HP damage
         + CAST(CAST(SUBSTRING(a.Detail, a.P + 28, 1) + SUBSTRING(a.Detail, a.P + 27, 1)
                   + SUBSTRING(a.Detail, a.P + 26, 1) + SUBSTRING(a.Detail, a.P + 25, 1) AS binary(4)) AS bigint) AS Damage,  -- + SD damage
           CAST(SUBSTRING(a.Detail, a.P + 31, 1) AS int) AS Flags,       -- 1 = killer, 2 = merged "others"
           (SELECT MAX(s.Biggest) FROM #s s WHERE s.Id = a.Id AND s.Idx = a.Idx) AS Biggest
    INTO #att
    FROM #a a
    CROSS APPLY (SELECT CAST(SUBSTRING(a.Detail, a.P, 10) AS varchar(10)) AS RawName) x;

    -- Totals per character
    SELECT Name,
           SUM(Kills) AS Kills, SUM(Deaths) AS Deaths, SUM(Assists) AS Assists,
           SUM(DamageDone) AS DamageDone, SUM(DamageTaken) AS DamageTaken,
           MAX(BiggestHit) AS BiggestHit, MAX(LastFight) AS LastFight
    INTO #sum
    FROM
    (
        SELECT Killer AS Name, 1 AS Kills, 0 AS Deaths, 0 AS Assists, CAST(0 AS bigint) AS DamageDone, CAST(0 AS bigint) AS DamageTaken, CAST(0 AS bigint) AS BiggestHit, KillTime AS LastFight FROM #rows
        UNION ALL
        SELECT Victim, 0, 1, 0, 0, TotalDmg, 0, KillTime FROM #rows
        UNION ALL
        SELECT a.Name, 0, 0, CASE WHEN a.Flags & 3 = 0 THEN 1 ELSE 0 END, a.Damage, 0, ISNULL(a.Biggest, 0), r.KillTime
        FROM #att a JOIN #rows r ON r.Id = a.Id
        WHERE a.Flags & 2 = 0 AND a.Name <> ''
    ) x
    GROUP BY Name;

    UPDATE l SET
        Kills       = l.Kills + s.Kills,
        Deaths      = l.Deaths + s.Deaths,
        Assists     = l.Assists + s.Assists,
        DamageDone  = l.DamageDone + s.DamageDone,
        DamageTaken = l.DamageTaken + s.DamageTaken,
        BiggestHit  = CASE WHEN s.BiggestHit > l.BiggestHit THEN s.BiggestHit ELSE l.BiggestHit END,
        LastFight   = CASE WHEN l.LastFight IS NULL OR s.LastFight > l.LastFight THEN s.LastFight ELSE l.LastFight END
    FROM dbo.WEB_PvPLadder l
    JOIN #sum s ON s.Name = l.Name;

    INSERT INTO dbo.WEB_PvPLadder (Name, Kills, Deaths, Assists, DamageDone, DamageTaken, BiggestHit, LastFight)
    SELECT s.Name, s.Kills, s.Deaths, s.Assists, s.DamageDone, s.DamageTaken, s.BiggestHit, s.LastFight
    FROM #sum s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.WEB_PvPLadder l WHERE l.Name = s.Name);

    UPDATE dbo.WEB_PvPLadderState SET LastId = @To;

    COMMIT;
END
GO

EXEC dbo.WEB_PvPLadderUpdate;

SELECT TOP 100 l.Name, c.Class, c.cLevel, l.Kills, l.Deaths, l.Assists,
       CAST(l.Kills AS float) / NULLIF(l.Deaths, 0) AS KD
FROM dbo.WEB_PvPLadder l
JOIN dbo.Character c ON c.Name = l.Name          -- hides deleted characters
ORDER BY l.Kills DESC, l.Deaths ASC;

SELECT TOP 100 l.Name, l.Kills, l.Deaths, CAST(l.Kills AS float) / NULLIF(l.Deaths, 0) AS KD
FROM dbo.WEB_PvPLadder l
JOIN dbo.Character c ON c.Name = l.Name
WHERE l.Kills >= 20
ORDER BY CASE WHEN l.Deaths = 0 THEN 1 ELSE 0 END DESC, KD DESC;