从另一列随机选择数据进行对某一列求和的算法

Question

我有一个像这样的表格：

CREATE TABLE Table1
    ([IdeaNr] int, [SubmitterName] varchar(4), [SubmitterDepartment] varchar(4))
;

INSERT INTO Table1
    ([IdeaNr], [SubmitterName], [SubmitterDepartment])
VALUES
    (1, 'Joe', 'Org1'),
    (1, 'Bill', 'Org2'),
    (1, 'Kate', 'Org1'),
    (1, 'Tom', 'Org3'),
    (2, 'Sue', 'Org2'),
    (3, 'Bill', 'Org2'),
    (3, 'Fred', 'Org1'),
    (4, 'Ted', 'Org3'),
    (4, 'Kate', 'Org1'),
    (4, 'Hank', 'Org3')
;

我希望从查询中获取以下结果：

IdeaNr  SubmitterCount   SubmitterRndName   SubmitterRndDepartment
1       4                Joe or ...         Org1 (if Joe)
2       1                Sue                Org2
3       2                Bill or ...        Org2 (if Bill)
4       3                Ted or ...         Org3 (if Ted)

我尝试过使用Table1自连接、派生表和GROUP BY等各种JOIN方法，例如：

SELECT COUNT(IdeaNr) AS SubmitterCount,IdeaNr,SubmitterName,SubmitterDepartment
FROM Table1
GROUP BY IdeaNr,SubmitterName,SubmitterDepartment

我认为问题在于创建一个算法，该算法仅获取出现在IdeaNr组中的第一个（或随机的）名称和部门。显然，您可能会得到那种数据的误导性解释，例如：

• Org1有2个想法
• Org2有1个想法
• Org3有1个想法

但这种“错误的平均值”对于任务是可以接受的。你能帮忙吗？

编辑：在讨论过程中，预期结果发生了变化。期望的结果已更改为：

IdeaNr  SubmitterCount   SubmitterRndName   SubmitterRndDepartment
1       4                Joe, Bill, ...     GroupIdea
2       1                Sue                Org2
3       2                Bill, Fred         GroupIdea
4       3                Ted, ...           GroupIdea

- Bebass

---

我更正了期望结果中的第三行，因为我认为您打错了。如果我做错了，请进一步编辑它。 - Vladimir Baranov

Answer 1

试着这样做：

DECLARE @Table1 TABLE ([IdeaNr] int, [SubmitterName] varchar(4), [SubmitterDepartment] varchar(4));
INSERT INTO @Table1([IdeaNr], [SubmitterName], [SubmitterDepartment])
VALUES
    (1, 'Joe', 'Org1'),
    (1, 'Bill', 'Org2'),
    (1, 'Kate', 'Org1'),
    (1, 'Tom', 'Org3'),
    (2, 'Sue', 'Org2'),
    (3, 'Bill', 'Org2'),
    (3, 'Fred', 'Org1'),
    (4, 'Ted', 'Org3'),
    (4, 'Kate', 'Org1'),
    (4, 'Hank', 'Org3');

SELECT x.IdeaNr
      ,Count(x.IdeaNr)
      ,MAX(Submitter.SubmitterName) AS SubmitterRndName
      ,MAX(Submitter.SubmitterDepartment) AS SubmitterRndDepartment
FROM @Table1 AS x 
CROSS APPLY
(
    SELECT TOP 1 SubmitterName, SubmitterDepartment
    FROM @Table1 AS y 
    WHERE y.IdeaNr=x.IdeaNr
) AS Submitter
GROUP BY x.IdeaNr

还有一个想法，不知道你是否需要：

SELECT x.IdeaNr
      ,Count(x.IdeaNr)
      ,STUFF(
            (
            SELECT ', ' + y.SubmitterName --maybe with DISTINCT
            FROM @Table1 AS y
            WHERE y.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllSubmitters
      ,STUFF(
            (
            SELECT ', ' + z.SubmitterDepartment --maybe with DISTINCT
            FROM @Table1 AS z
            WHERE z.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllDepartments
FROM @Table1 AS x 
GROUP BY x.IdeaNr

这将返回：

这会返回

IdeaNr                  AllSubmitters              AllDepartments
1           4           Joe, Bill, Kate, Tom       Org1, Org2, Org1, Org3
2           1           Sue                        Org2
3           2           Bill, Fred                 Org2, Org1
4           3           Ted, Kate, Hank            Org3, Org1, Org3

编辑：根据您在上一条评论中的建议：

SELECT x.IdeaNr
      ,COUNT(x.IdeaNr)
      ,STUFF(
            (
            SELECT DISTINCT ', ' + y.SubmitterName 
            FROM @Table1 AS y
            WHERE y.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllSubmitters
      ,CASE WHEN COUNT(x.IdeaNr)=1 THEN (SELECT TOP 1 z.SubmitterDepartment FROM @Table1 AS z WHERE z.IdeaNr=x.IdeaNr)
            ELSE 'GroupIdea' END AS Departments
FROM @Table1 AS x 
GROUP BY x.IdeaNr

- Shnugo

---

谢谢您的回答！您提供的两个例子都很好。对于第一个程序：我以前从未听说过CROSS APPLY。我在这里找到了一些解释，比较了JOIN和CROSS APPLY：http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ - Bebass

@Bebass，很高兴能帮助你。CROSS APPLY非常适合基于行的访问，特别是与XML和函数相关联。一个提示：您可以轻松地将第二种方法更改为“Joe（Org1），Bill（Org2），Kate（Org1），Tom（Org3）”等等。如果这正是您所需要的，请投票或标记为已接受，谢谢！ - Shnugo

对于列[AllSubmitters]，你第二个程序的解决方案非常好（获取用逗号分隔的名称列表）。对于列[AllDepartments]，当只有一个部门时，我想要读取该部门的名称，并在有多个部门时使用表达式“GroupIdea”（就像你写的那样）。我会尝试将你的代码调整到这个想法上。 - Bebass

@Bebass，在我的回答底部编辑后添加。如果有帮助，请投票或标记为已接受，谢谢！ - Shnugo

太好了，它完美地运行了！感谢您与我一起开发这个解决方案。 - Bebass

Answer 2

如果你想了解更多关于这个主题的内容，请搜索 top-N-per-group 。 在SQL Server中，可以使用CROSS APPLY来轻松实现。

SQL Fiddle

WITH
CTE
AS
(
    SELECT
        IdeaNr
        ,COUNT(*) AS SubmitterCount
    FROM @Table1
    GROUP BY IdeaNr
)
SELECT
    CTE.IdeaNr
    ,CTE.SubmitterCount
    ,CA.SubmitterName
    ,CA.SubmitterDepartment
FROM
    CTE
    CROSS APPLY
    (
        SELECT TOP(1)
            T.SubmitterName
            ,T.SubmitterDepartment
        FROM @Table1 AS T
        WHERE T.IdeaNr = CTE.IdeaNr

        --ORDER BY T.SubmitterName
        --ORDER BY T.SubmitterDepartment
        --ORDER BY CRYPT_GEN_RANDOM(4)

    ) AS CA
ORDER BY CTE.IdeaNr;

如果在CROSS APPLY部分中没有放置任何ORDER BY，服务器将会随机选择一行。它并不是真正的随机，但是当你多次运行此查询时，结果可能相同也可能不同。实际上，如果你在表格上创建或删除索引，结果很可能会有所不同，但如果表格很大，每次查询运行时结果可能都会不同。

如果你想为每个选择特定的行，则使用ORDER BY Name、Department或某个ID等。

如果你想随机选择一行，则使用ORDER BY CRYPT_GEN_RANDOM(4)。

当我在没有任何索引的情况下使用表变量进行测试时，以下是我得到的结果，没有使用任何ORDER BY：

IdeaNr    SubmitterCount    SubmitterName    SubmitterDepartment
1         4                 Joe              Org1
2         1                 Sue              Org2
3         2                 Bill             Org2
4         3                 Ted              Org3

看起来好像是按照它们添加到表中的顺序为每个IdeaNr选择了“第一”行。但是，不要被骗，如果没有明确的ORDER BY，那么顺序不能得到保证。如果您想按照它们添加到表中的顺序获取每个IdeaNr的第一行，则需要以某种方式存储有关此顺序的信息。例如，向表中添加一个列ID int IDENTITY，它会随着添加新行而自动递增，然后您可以像这样使用它ORDER BY ID DESC来获得保证的结果。

在SQL Fiddle上进行操作，以查看其工作方式。

- Vladimir Baranov

---

非常感谢您的答复。我经常使用SQLite，因此一直避免使用CTE，直到现在。但是对我来说，它似乎是一个非常有用的概念。我在这里找到了一些介绍：https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/ 即使是SQLite现在似乎也具备了它：http://www.sqlite.org/lang_with.html - Bebass

我也喜欢你使用ORDER BY CRYPT_GEN_RANDOM(4)的建议！ - Bebass

@Bebass，CTE使查询更易读。在这种情况下，可以将SELECT ... GROUP BY作为嵌套查询放入主查询中。但是，看起来你的问题已经发展了，你想要不同的结果，所以这个答案变得不太相关了。 - Vladimir Baranov

是的，问题已经发展了。我在问题中加了一条评论说明了变化。尽管如此，非常感谢您详细的回答 - 这将鼓励我更多地学习CTE。 - Bebass