代码之家 › 专栏 › 技术社区 › Promit

SQL:为每个唯一键选择最大值?

database sql-server sql

Promit · 技术社区 · 17 年前

SELECT *
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)
ORDER BY ThreadId, HitCount DESC

这给了我:

ThreadId   Function  HitCount
       1        164      6945
       1       3817         1
       4       1328      7053

现在,我只想要每个线程唯一值的最大命中数的结果。换句话说,应该删除第二行。我不知道该怎么办。

[编辑]如果有帮助,这是同一查询的另一种形式:

SELECT *
FROM Samples s1
LEFT OUTER JOIN Callers c1
    ON s1.ThreadId = c1.ThreadId AND s1.FunctionId = c1.CalleeId
WHERE c1.ThreadId IS NULL
ORDER BY ThreadId

[编辑]我最后更改了模式以避免这样做,因为建议的查询看起来相当昂贵。谢谢你的帮助。

3 回复 | 直到 17 年前

Bill Karwin 17 年前

我会这样做:

SELECT s1.*
FROM Samples s1
LEFT JOIN Samples s2 
  ON (s1.Thread = s2.Thread and s1.HitCount < s2.HitCount)
WHERE s1.FunctionId NOT IN (SELECT CalleeId FROM Callers) 
  AND s2.Thread IS NULL
ORDER BY s1.ThreadId, s1.HitCount DESC

换句话说,这场争吵 s1 s2 匹配相同 Thread 而且有更大的 HitCount .

Shannon Severance 17 年前

备选方案1——将包括所有连接的行。如果给定线程的唯一行的命中率都为null,则不包括行:

SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
        MAX(HitCount) over (PARTITION BY Thread) as MaxHitCount
    FROM Samples
    WHERE FunctionId NOT IN
        (SELECT CalleeId FROM Callers)) t 
WHERE HitCount = MaxHitCount 
ORDER BY ThreadId, HitCount DESC

备选方案2——将包括所有连接的行。如果给定线程没有具有非空命中数的行,则将返回该线程的所有行:

SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
        RANK() over (PARTITION BY Thread ORDER BY HitCount DESC) as R
    FROM Samples
    WHERE FunctionId NOT IN
        (SELECT CalleeId FROM Callers)) t
WHERE R = 1
ORDER BY ThreadId, HitCount DESC

备选方案3——在出现平局的情况下,将非决定性地选择一行,并丢弃其他行。如果给定线程的所有行的命中率为空,则将包含一行

SELECT Thread, Function, HitCount
FROM (SELECT Thread, Function, HitCount,
        ROW_NUMBER() over (PARTITION BY Thread ORDER BY HitCount DESC) as R
    FROM Samples
    WHERE FunctionId NOT IN
        (SELECT CalleeId FROM Callers)) t
WHERE R = 1
ORDER BY ThreadId, HitCount DESC

备选方案4&5--如果窗口函数不可用,则使用较旧的构造,并说明比使用联接更简洁的含义。如果spead是优先级,则进行基准测试。两者都返回参与平局的所有行。当HITCUNT没有非null值时,备选方案4将显示HITCUNT为null。备选方案5不会返回命中数为空的行。

SELECT *
FROM Samples s1
WHERE FunctionId NOT IN
    (SELECT CalleeId FROM Callers)
AND NOT EXISTS
    (SELECT *
    FROM Samples s2
    WHERE s1.FunctionId = s2.FunctionId
    AND s1.HitCount < s2.HitCount)
ORDER BY ThreadId, HitCount DESC

SELECT *
FROM Samples s1
WHERE FunctionId NOT IN
    (SELECT CalleeId FROM Callers)
AND HitCount = 
    (SELECT MAX(HitCount)
    FROM Samples s2
    WHERE s1.FunctionId = s2.FunctionId)
ORDER BY ThreadId, HitCount DESC

OMG Ponies 17 年前

WITH maxHits AS(
  SELECT s.threadid,
         MAX(s.hitcount) 'maxhits'
    FROM SAMPLES s
    JOIN CALLERS c ON c.threadid = s.threadid AND c.calleeid != s.functionid
GROUP BY s.threadid
)
SELECT t.*
  FROM SAMPLES t
  JOIN CALLERS c ON c.threadid = t.threadid AND c.calleeid != t.functionid
  JOIN maxHits mh ON mh.threadid = t.threadid AND mh.maxhits = t.hitcount

在任何数据库上工作:

SELECT t.*
  FROM SAMPLES t
  JOIN CALLERS c ON c.threadid = t.threadid AND c.calleeid != t.functionid
  JOIN (SELECT s.threadid,
               MAX(s.hitcount) 'maxhits'
          FROM SAMPLES s
          JOIN CALLERS c ON c.threadid = s.threadid AND c.calleeid != s.functionid
      GROUP BY s.threadid) mh ON mh.threadid = t.threadid AND mh.maxhits = t.hitcount