SQL som sammentæller mange-til-mange kombinationer

Nu kunne du jo nok have hjulpet lidt ved at vise os den sql du selv var kommet frem til, samt fortælle lidt mere om hvordan dine tabeller ser ud.

Jeg antager følgende
Posts indeholder en kolonne der hedder PostID.
Categories indeholder en kolonne der hedder CategoryID
Categorizations indeholder PostID og CategoryID

SELECT Posts.PostID, Category.CategoryID, COUNT(*) AS Antal
FROM
  Posts
  INNER JOIN Categorizations ON Posts.PostID = Categorizations.PostID
  INNER JOIN Categories ON Categorizations.CategoryID = Categories.CategoryID
GROUP BY Posts.PostID, Category.CategoryID

Hvis det tager lang tid, så skal du nok have indexer på PostID og CategoryID i både Posts-tabellen, Category-tabellen samt i Categorizations-tabellen.

Tak sjang. Ja, jeg kan godt se jeg skulle have suppleret med flere oplysninger + at jeg slet ikke har forklaret mit mål tydeligt nok. Undskyld.

Her er din SQL tilpasset til de faktiske tabel- og kolonnenavne:
SELECT  Posts.Id AS PostId, Categories.Id AS CategoryId, COUNT(*) AS Antal
FROM    Posts INNER JOIN
        Categorizations ON Posts.Id = Categorizations.PostId INNER JOIN
        Categories ON Categorizations.CategoryId = Categories.Id
GROUP BY Posts.Id, Categories.Id

Det jeg ville var, at finde for hver kategori hvor mange Posts den har i kombination med alle andre kategorier. Ud fra ovenstående kan jeg se at jeg havde beskrevet output forkert.

Det skulle være:
kategori1, kategori2, antal
kategori1, kategori3, antal
kategori1, kategori4, antal
kategori2, kategori1, antal
kategori2, kategori3, antal
kategori2, kategori4, antal
kategori3, kategori1, antal
kategori3, kategori2, antal
kategori3, kategori4, antal

Hvor antal er det vigtige, nemlig: "Hvor mange Posts er medlem af begge kategorier?"

Igen, beklager den første upræcise formulering af spørgsmålet :(

Jeg tror nok I får styr på SQL'en til denne her, men hvis det stadig tager lang tid er det nok fordi du ikke har indexer på de rigtige kolonner.

Alle tabeller skal have en primær nøgle, som i sig selv er et index. Din mange-til-mange tabel skal desuden have indexer på de to kolonner der relaterer til hovedtabellerne.

Men gør nu sjang den tjeneste at give ham tabelstrukturen (i hvert fald de kolonner der er involveret), som fx:

Tabel Posts:
id, int, primær nøgle (identity)
kolonne1, varchar()
...

Tabel Categories:
id, int, primær nøgle (identity)
kolonne1, varchar()
...

Tabel Categorizations (relationstabel):
id, int, primær nøgle (identity)
PostId, int
CategoryId, int

- hvilket i øvrigt måske er tæt på virkeligheden ? :o)

Jeg ville bare hold spørgsmålet kort (men det blev vist FOR kort)

Her er den relevante del af tabel-strukturen:

Tabel Posts:
Id, int, primær nøgle (identity)

Tabel Categories:
Id, int, primær nøgle (identity)

Tabel Categorizations:
Id, int, primær nøgle (identity)
CategoryId, int
PostId, int

Ud fra følgende mangler du Index på:
CategoryId, int
PostId, int

I din Tabel Categorizations:

Indexes er ikke for børn ... quries kan gå lidt ned i execution time og _MEGET_

har en table med 47 mill rows ... kom til at disable et index som gjorde at den så tog over 30 sekunder (timeout) .... enabled det igen ... og så er vi nede på 300 ms igen.

Jeg tør slet ikke tænke på hvor lang tid den ville have taget hvis den skulle have lavet et "table scan" på det .... OMG.

// oUT

Er dette spørgsmål stadig levende?

Et par opklarende spørgsmål.

Ønsker du svar på ALLE kombination uanset om kombinationen er brugt eller ej, eller ønsker du kun statistik på de ANVENDTE kategorier pr. post? Det gør en forskel...

/thomas

Pudsigt nok løste jeg det i går aftes. Jeg har ikke SQL'en lige her, men jeg joinede sådan og det virkede helt perfekt:

Category -> Categorization -> Post -> Categorization2 -> Category2

hvor Category.Id <> Category2.Id

SELECT Category.Alias, Count(Post.Id) as Amount

Jeg takker mange gange for opmærksomheden!

OK. Jeg ville også have gjort sådan der.

Sætter du det lige ind ved lejlighed?

Pas på med at få det rigtige join. INNER JOIN vs. LEFT JOIN. Det har stor betydning i forhold til om der skal tælles for de kombinationer som IKKE findes i tabellen Catgoriszation!

Bemærk i øvrigt også lige din COUNT(post.id) tæller den alle linier sammen (jeg går ud fra du har en GROUP BY sætning også). Hvis databasen tillader at man flere identiske linier i tabellen Categorization (postId = 1, CatID = 2 og igen senere postId = 1, CatId), vil man få en forkert sammentælling. Brug i så fald "Count(DISTINCT Post.Id)".

/Thomas

Ja, berglund, her er resultatet som giver et forfriskende hurtigt resultat. Fra 5 minutter til 200 ms i udførsel :)

SELECT    Categories2.ID AS CategoryId, COUNT(Posts.ID) as Amount
FROM      Categories INNER JOIN
          Categorizations ON Categories.Id = Categorizations.CategoryId INNER JOIN
          Posts ON Categorizations.PostId = Posts.Id INNER JOIN
          Categorizations AS Categorization2 ON Posts.Id = Categorization2.PostId INNER JOIN
          Categories AS Categories2 ON Categorization2.CategoryId = Categories2.Id
WHERE    (Categories.ID = 735) AND (Categories2.ID <> 735) AND (Posts.IsQueued = 0)
group by Categories2.ID
order by amount desc

Categorizations findes kun i én kombination af PostId og CategoryId, så den sammentælling skulle være fin nok ... men det samme Post bliver talt med flere gange, da det indgår i flere kategorier.

Hm... nu har jeg tænkt lidt mere over det her, og er kommet til et helt andet resultat end jeg først havde troet. I din sætning vil der slet ikke blive talt på de kombinationer som IKKE eksisterer, og den ønskede resultatliste fra tidligere:
kategori1, kategori2, antal
kategori1, kategori3, antal
kategori1, kategori4, antal
kategori2, kategori1, antal
kategori2, kategori3, antal
kategori2, kategori4, antal
kategori3, kategori1, antal
kategori3, kategori2, antal
kategori3, kategori4, antal

Er også problematisk fordi kategori1 og kategori2 optræder 2 gange. I denne type spørgsmål er rækkefølgen af kombinationen jo ligegyldig.

Jeg synes derfor at man skal dele søgningen op i to mængder og så lave en sammenligning af de to mængder.

1. mængde:
-----------
Alle mulige kombinationer af kategorier, dog uden gentagelse fordi rækkefølgen ikke er af betyning:

SELECT
    *
FROM
    Categories cat INNER JOIN Categories cat2
    on cat.id <> cat2.id
WHERE
    cat.id < cat2.id -- udelukkes gentaglsen
ORDER BY
    cat.id, cat2.id -- for syns skyld

2. mængde:
-------------
Alle kategorikombinationer pr. bestemt postId i Categorizations, dog ikke CategoryId = CategoryId!

    SELECT
        *
    FROM
        Categorizations catz INNER JOIN Categorizations catz2
        ON catz.PostId = catz2.PostId       
    WHERE
        catz.CategoryId <> catz2.CategoryId


Kombination af 1. og anden 2. mængde med en betingelse og en optælling. Bruger her supquery for at bevare strukturen af 1. mængde. Tæl alle de postId'er i mængde 2 som indeholder pågældende kombination af kategorier i mængde 1:
SELECT
    --cat.id,
    cat.Category,   
    --cat2.id,
    cat2.Category,
    (
    SELECT
        COUNT(*)
    FROM
        Categorizations catz INNER JOIN Categorizations catz2
        ON catz.PostId = catz2.PostId       
    WHERE
        catz.CategoryId <> catz2.CategoryId
        AND
        (catz.CategoryId = cat.id AND catz2.CategoryId = cat2.id)
    ) AS 'amount'
FROM
    Categories cat INNER JOIN Categories cat2
    on cat.id <> cat2.id   
WHERE
    cat.id < cat2.id
ORDER BY
    cat.id, cat2.id

Bemærk, at hvis databasen tillader dubletter af postId'er og CategoryId'er i Categorizations bør du lave COUNT(DISTINCT catz.PostId) i underforspøgslen for at få de FORSKELLIGE postId'er, men det tager på perfomance.

Er det her svar nok, gyxi? Enig?

Efter 3-4 gennemlæsninger forstår jeg godt hvad din plan er. Det vil give et knivskarpt resultat som fuldstændig passer på det der blev "bestilt" i spørgsmålet. Jeg kan ikke afprøve hastigheden lige nu, men er helt sikker på det er et spørgsmål om sekunder og slet ikke timer, ligesom det jeg sad med før.