SQL：为每个唯一键选择最大值？ [英] SQL: Select maximum value for each unique key?

问题描述

对不起，我不确定该如何表达，而且我对SQL真的不太满意。数据库引擎i SQL Server Compact。我目前有这个查询：

Sorry, I'm not sure how to phrase that and I'm really not very good with SQL. The db engine i SQL Server Compact. I currently have this query:

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

这给了我： / p>

Which gives me:

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

现在，我只希望结果具有Thread每个唯一值的最大命中数。换句话说，应该删除第二行。我不确定如何实现此目标。

Now, I only want the result with the maximum hit count for each unique value of Thread. In other words, that second row should be dropped. I'm not sure how to pull this off.

如果有帮助，这是同一查询的另一种形式：

If it helps, this is an alternate form of the same query:

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

我最终进行了模式更改，以避免这样做，因为建议的查询看起来相当昂贵。感谢您的所有帮助。

I ended up making schema changes to avoid doing this, as the suggested queries were looking rather expensive. Thanks for all the help.

推荐答案

SQL Server Compact是否支持窗口函数？

Does SQL Server compact support windowed functions?

替代1-将包括所有关联的行。如果给定线程的所有行的HitCount都为空，则将不包含行：

Alternative 1--Will include all rows that tie. Will not include a row, if the only rows for a given Thread all have null for HitCount:

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-将包括所有关联的行。如果给定线程的HitCount不为空，则没有行，将返回该线程的所有行：

Alternative 2--Will include all rows that tie. If there is no row for a given thread with non-null HitCount, will return all rows for that thread:

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-在出现平局的情况下不确定地选择一行并丢弃其他行。如果给定线程的所有行的空HitCount都将包含一行

Alternative 3--Will non-determistically pick one row in case of ties and discard others. Will include a row if all rows for a given thread have null HitCount

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-如果窗口功能不可用，则使用较旧的构造，并表示比使用联接更干净。优先考虑基准测试。两者都返回参与平局的所有行。当非null值不可用于HitCount时，备选方案4将HitCount为null。备选方案5将不会返回HitCount为null的行。

Alternative 4 & 5--Uses older constructs, if the windowed functions aren't available, and says what is meant a little cleaner than using joins. Benchmark if spead is a priority. Both return all rows that participate in a tie. Alternative 4 will HitCount is null when non-null values are not available for HitCount. Alternative 5 will not return rows with HitCount is null.

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

这篇关于SQL：为每个唯一键选择最大值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！