试图简化一个没有使用UNION的SQL查询

Question

我是一个有用的助手，可以帮助您进行文本翻译。

我很菜，因此让我尝试阐明我的问题。我有一个类似于以下表格的表格：

 Source    Value    User
========  =======  ======
  old1       1      Phil
  new        2      Phil
  old2       3      Phil
  new        4      Phil
  old1       1      Mike
  old2       2      Mike
  new        1      Jeff
  new        2      Jeff

我需要做的是创建一个查询，根据来源和值为用户获取值。它应该遵循以下规则:

对于每个用户，获取最高值。但是，如果该用户存在“old1”或“old2”，则忽略“new”源。

因此，根据这些规则，我的查询应该从这个表中返回以下结果：

 Value    User
=======  ======
   3      Phil
   2      Mike
   2      Jeff

我想出了一个查询，几乎符合所需要求：

SELECT      MAX([Value]), [User]
FROM
(
    SELECT  CASE [Source]
                WHEN 'old1' THEN 1
                WHEN 'old2' THEN 1
                WHEN 'new'  THEN 2
            END                 AS [SourcePriority],
            [Value],
            [User]
    FROM    #UserValues
) MainPriority
WHERE       [SourcePriority] = 1
GROUP BY    [User]
UNION
SELECT      MAX([Value]), [User]
FROM
(
    SELECT  CASE [Source]
                WHEN 'old1' THEN 1
                WHEN 'old2' THEN 1
                WHEN 'new'  THEN 2
            END                 AS [SourcePriority],
            [Value],
            [User]
    FROM    #UserValues
) SecondaryPriority
WHERE       [SourcePriority] = 2
GROUP BY    [User]

然而，这将返回以下结果：

 Value    User
=======  ======
   3      Phil
   4      Phil
   2      Mike
   2      Jeff

显然，Phil = 4 的额外值是不需要的。我该如何尝试修复这个查询？我也知道这是一个相当复杂的解决方案，可能可以通过正确使用聚合更轻松地解决，但我还不太熟悉聚合，所以才会采用联合的方式。本质上，我正在寻求创建最清晰的解决方案的帮助。

如果有人想自己填充表格，请看以下 SQL 代码：

CREATE TABLE #UserValues
(
    [Source] VARCHAR(10),
    [Value]  INT,
    [User]   VARCHAR(10)
)
INSERT INTO #UserValues VALUES
('old1', 1, 'Phil'),
('new',  2, 'Phil'),
('old2', 3, 'Phil'),
('new',  4, 'Phil'),
('old1', 1, 'Mike'),
('old2', 2, 'Mike'),
('new',  1, 'Jeff'),
('new',  2, 'Jeff')

- Jon Warren

Answer 1

您可以轻松地解决这个问题，而不必使用窗口函数。在这种情况下，您需要最大值，其中 ((not new) OR (不存在 old1 或 old2 条目))。

以下是一个查询，在您的示例数据中可以正常工作：

SELECT
    MAX(U1.[Value]) as 'Value'
    ,U1.[User]
FROM
    #UserValues U1
WHERE
    U1.[Source] <> 'new' 
    OR NOT EXISTS (SELECT * FROM #UserValues U2 WHERE U2.[User] = U1.[User] AND U2.[Source] IN ('old1','old2'))
GROUP BY U1.[User]

- Shawn Pence

Answer 2

你可以使用row_number()和order by来设定优先级：

select top (1) with ties uv.*
from #UserValues uv
order by row_number() over (partition by [user] 
                            order by (case when source = 'old2' then 1 when source = 'old1' then 2 else 3 end), value desc 
                           );

然而，如果你的source只有3个限制，那么你也可以这样做：

. . . 
order by row_number() over (partition by [user] 
                            order by (case when source = 'new' then 2 else 1 end), value desc 
                           )

- Yogesh Sharma

---

谢谢大家提供的解决方案！当我将其调整以适应我的实际业务问题时，这个解决方案不仅最为紧凑，而且提供了最快的结果。 - Jon Warren

Answer 3

with raw_data
      as (
    select row_number() over(partition by a.[user] order by a.value desc) as rnk
          ,count(case when a.source in('old1','old2') then 1 end) over(partition by a.[user]) as cnt_old 
          ,a.*
      from uservalues a
         )
        ,curated_data  
         as(select *
                  ,row_number() over(partition by rd.[user] order by rd.value desc) as rnk2
             from raw_data rd
            where 0 = case when rnk=1 and source='new' and cnt_old>0 then 1 else 0 end 
           )
    select *
      from curated_data
     where rnk2=1

我正在做以下事情：

1. raw_data ->首先，我根据每个用户可用的最大值对价值进行排名。同时，我检查用户是否有任何记录被固定在源列的old1或old2。

2. curated_data ->如果记录具有最高值（rnk=1），并且它们具有cnt_old >0，则将其作为新记录删除。现在，我根据此结果集中可用的最高值对记录进行排名（rnk2）。

3. 我从curated_data中选择可用的最高值（即rnk2=1）

- George Joseph

Answer 4

我认为你应该考虑设置一个XREF表来定义每个来源的优先级，以备将来可能出现更复杂的优先级情况。我会用一个临时表来实现：

CREATE TABLE #SourcePriority
(
    [Source]         VARCHAR(10),
    [SourcePriority] INT
)
INSERT INTO #SourcePriority VALUES
('old1', 1),
('old2', 1), 
('new',  2)

您也可以创建一个视图来查找原始表中的SourcePriority。我使用CTE进行操作，下面是如何使用最高值查找顶部优先级的可能实现：

;WITH CTE as (
    SELECT s.[SourcePriority], u.[Value], u.[User]
    FROM   #UserValues as u
        INNER JOIN #SourcePriority as s on u.[Source] = s.[Source]
)
SELECT MAX (v.[Value]) as [Value], v.[User]
FROM (
    SELECT MIN ([SourcePriority]) as [TopPriority], [User]
    FROM   cte
    GROUP BY [User]
    ) as s
    INNER JOIN cte as v
        ON s.[User] = v.[User] and s.[TopPriority] = v.[SourcePriority]
GROUP BY v.[User]

- Dávid Laczkó

Answer 5

我想你想要的是：

select top (1) with ties uv.*
from (select uv.*,
             sum(case when source in ('old1', 'old2') then 1 else 0 end) over (partition by user) as cnt_old
      from #UserValues uv
     ) uv
where cnt_old = 0 or source <> 'new'
order by row_number() over (partition by user order by value desc);

- Gordon Linoff