Geek asks and answers

Use two aggregate functions in one query

Consider the following tables:

[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

What I want to do is: Given the set of stellar conversations, I would like to select the top 2 non-stationary conversations (starred = 0) and their names and columns, which are most similar to the set of stellar conversations. The problem is that getting speakers requires the use of an aggregate function, and therefore you get the most similar conversations.

Without speakers in the battle, I was able to get the most similar conversations using the following query:

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

As a rule, I use the following aggregate function to get speakers with the corresponding group by columns (suppose t = talkpeaker):

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

how in

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). , , , talkIDs 3 4.

+5
2

-, 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;

SQL

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;

, , , ( , )

+5

, , :

SELECT unstarred.talkID
FROM talks AS starred
  JOIN similarTalks AS s ON starred.talkID = s.talkID
  JOIN talks AS unstarred ON s.similarTo = unstarred.talkID
WHERE starred.starred
  AND NOT unstarred.starred
GROUP BY unstarred.talkID
ORDER BY COUNT(*) DESC
LIMIT 2

, :

SELECT t.title AS Title,
       group_concat(s.speaker, ', ') AS Speakers
FROM talks AS t JOIN talkspeaker AS s ON t.talkID = s.talkID
WHERE t.talkID IN (SELECT unstarred.talkID
                   FROM talks AS starred
                     JOIN similarTalks AS s ON starred.talkID = s.talkID
                     JOIN talks AS unstarred ON s.similarTo = unstarred.talkID
                   WHERE starred.starred
                     AND NOT unstarred.starred
                   GROUP BY unstarred.talkID
                   ORDER BY COUNT(*) DESC
                   LIMIT 2)
GROUP BY t.talkID
+3

More articles:


All Articles