sql - 在同一查询中使用两个聚合函数

Question

考虑下表:

[Table: talks]
talkID | title        | starred
-------+--------------+--------
1      | talk1-title  | 1
2      | talk2-title  | 1
3      | talk3-title  | 0
4      | talk4-title  | 0
5      | talk5-title  | 0

[Table: talkspeaker]
talkID | speaker
-------+---------
1      | Speaker1
1      | Speaker2
2      | Speaker3
3      | Speaker4
3      | Speaker5
4      | Speaker6
5      | Speaker7
5      | Speaker8

[Table: similartalks]
talkID | similarTo
-------+----------
1      | 3
1      | 4
2      | 3
2      | 4
2      | 5
3      | 2
4      | 5
5      | 3
5      | 4

我想做的是:给定一组加星标的演讲，我想选择前 2 个未加星标的演讲(加星标 = 0)以及与加星标的演讲集最相似的标题和演讲者。问题在于获取发言人需要使用聚合函数，获取最相似的演讲也是如此。

在没有演讲者的情况下，我已经能够使用以下查询获得最相似的演讲:

select t2.talkID, t2.title, count(*) as count 
from similarTalks s, talks t1, talks t2
where s.talkID = t1.talkID
and t1.Starred = 1
and s.similarTo = t2.TalkID
and t2.Starred = 0
group by t2.title, t2.talkID
order by count desc
limit 2

通常，我使用以下聚合函数来获取发言人，并按列进行适当的分组(假设 t = talkspeaker):

group_concat(t.speaker, ', ') as Speakers

如在

select t1.title, group_concat(t2.speaker, ', ') as Speakers 
from talks t1, talkspeaker t2
where t1.talkID = t2.talkID
group by t1.title

但我无法将两者结合在一起。我计划在 sqlite 数据库中运行此查询(这是 group_concat 函数的来源)可能很重要。与加星标对话最相似的前 2 个未加星标对话的答案似乎是 talkID 3 和 4。

Answer 1

首先你可能想阅读 this article关于使用 ANSI 92 Joins 而不是上面使用的陈旧的 ANSI 89 的原因。其次，SQLLite 支持 GROUP_CONCAT 函数，因此您可以使用它。

您只需将第二个查询作为子查询添加到第一个查询中即可获得所需的结果:

SELECT  Talks.TalkID, 
        Talks.Title, 
        ts.Speakers, 
        COUNT(*) AS SimilarTalks
FROM    Talks
        INNER JOIN SimilarTalks 
            ON Talks.TalkID = SimilarTalks.SimilarTo
        INNER JOIN Talks t2
            ON SimilarTalks.TalkID = t2.TalkID
            AND t2.Starred = 1
        INNER JOIN
        (   SELECT  TalkID, GROUP_CONCAT(Speaker, ',') AS Speakers
            FROM    TalkSpeaker
            GROUP BY TalkID
        ) ts
            ON ts.TalkID = Talks.TalkID
WHERE   Talks.Starred = 0
GROUP BY Talks.TalkID, Talks.Title, ts.Speakers
ORDER BY COUNT(*) DESC
LIMIT 2;

Example on SQL Fiddle

编辑

您也可以在没有子查询的情况下使用 DISTINCT 执行此操作:

SELECT  Talks.TalkID, 
        Talks.Title, 
        GROUP_CONCAT(DISTINCT ts.Speaker) AS Speakers,
        COUNT(DISTINCT t2.TalkID) AS SimilarTalks
FROM    Talks
        INNER JOIN SimilarTalks 
            ON Talks.TalkID = SimilarTalks.SimilarTo
        INNER JOIN Talks t2
            ON SimilarTalks.TalkID = t2.TalkID
            AND t2.Starred = 1
        INNER JOIN TalkSpeaker ts
            ON ts.TalkID = Talks.TalkID
WHERE   Talks.Starred = 0
GROUP BY Talks.TalkID, Talks.Title
ORDER BY COUNT(DISTINCT t2.TalkID) DESC
LIMIT 2;

但是我看不出这种方法有任何好处，而且效率可能较低(我没有测试过所以不能确定)