Avatar billede ulykken-smed Juniormester
22. februar 2023 - 17:54 Der er 10 kommentarer og
1 løsning

Join fra data fra samme tabel og med sidste værdi

Hvordan laver jeg en join til samme tabel, og med sidste forekomst.
Jeg har en meget stor database/log hvor der bliver lavet en ny række hver gang der bliver oprettet noget, eller ændret noget.

Jeg har lavet nogle testdata hvor der er et JournalId for hver hændelse, og en kode for hver kolonne der skal trækkes ud, så ud fra hver JournalId skal den retunerer en kolonne med den sidste værdi i for hver kode. Tabellen er ikke sorteret, og det er ikke muligt at oprette hjælpe tabeller.

Den første linje den retunerer skal se således ud:
JournalID    Tekst (A)    Tekst(B)    Tekst©    Tekst(D)
GG-88-TY    Cykel    Rød    Metal    2020

Test data:
JournalId    Kode    TidStempel    Tekst
GG-88-TY    A    24-01-2023 04:04    Cykel
GG-88-TY    B    24-01-2023 06:00    Sort
GG-88-TY    B    24-01-2023 07:26    Rød
GG-88-TY    B    24-01-2023 10:48    Blå
GG-88-TY    B    24-01-2023 18:43    Blå
GG-88-TY    B    25-01-2023 00:00    Rød
GG-88-TY    C    24-01-2023 03:21    Plastic
GG-88-TY    C    24-01-2023 10:04    Jern
GG-88-TY    C    24-01-2023 13:12    Plastic
GG-88-TY    C    24-01-2023 18:28    Plastic
GG-88-TY    C    25-01-2023 00:00    Metal
GG-88-TY    D    24-01-2023 00:57    2022
GG-88-TY    D    24-01-2023 06:57    2020
GG-88-TY    D    24-01-2023 09:07    2022
GG-88-TY    D    24-01-2023 14:24    2020
LL-54-AZ    A    24-01-2023 06:43    Cykel
LL-54-AZ    A    24-01-2023 08:52    Knallert
LL-54-AZ    A    24-01-2023 11:45    Knallert
LL-54-AZ    A    24-01-2023 12:00    Cykel
LL-54-AZ    B    24-01-2023 12:14    Sort
LL-54-AZ    C    24-01-2023 00:57    Plastic
LL-54-AZ    C    24-01-2023 01:12    Metal
LL-54-AZ    C    24-01-2023 01:12    Jern
LL-54-AZ    C    24-01-2023 05:45    Plastic
LL-54-AZ    D    24-01-2023 09:07    2021
LL-54-AZ    D    24-01-2023 14:24    2022
LL-54-AZ    D    24-01-2023 15:21    2022
LL-54-AZ    D    24-01-2023 17:45    2021
LL-54-AZ    D    24-01-2023 21:50    2020
AA-21-KK    A    24-01-2023 10:19    Cykel
AA-21-KK    A    24-01-2023 15:36    Knallert
AA-21-KK    A    24-01-2023 18:28    Bil
AA-21-KK    B    24-01-2023 06:43    Sort
AA-21-KK    B    24-01-2023 20:52    Blå
AA-21-KK    C    24-01-2023 01:55    Jern
AA-21-KK    C    24-01-2023 02:24    Plastic
AA-21-KK    C    24-01-2023 09:50    Metal
AA-21-KK    C    24-01-2023 23:02    Metal
AA-21-KK    D    24-01-2023 00:00    2022
AA-21-KK    D    24-01-2023 02:52    2021
Avatar billede acore Ekspert
23. februar 2023 - 16:08 #1
Du kan lade dig inspirere af https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/, der viser princippet. Skal lige tilføje, at jeg ikke har afprøvet det, men at det ser tilforladeligt ud.
Avatar billede ulykken-smed Juniormester
24. februar 2023 - 07:10 #2
Tak Acore, jeg prøver at kigge på det
Avatar billede acore Ekspert
24. februar 2023 - 07:30 #3
Ja, jeg ved godt, at det ikke er en løsning, men tiden var ikke lige til at give det et forsøg, selv om det er en spændende udfordring.
Avatar billede acore Ekspert
24. februar 2023 - 07:31 #4
Du kan håbe på, at arne ser dit spørgsmål - han er en superhaj til sql.
Avatar billede ulykken-smed Juniormester
24. februar 2023 - 17:03 #5
Jeg fik løst det med hjælp fra ChatGPT

SELECT DISTINCT A.JournalID, B.TidStempel, B.Kode , B.ValueString as Afsluttet,
      CASE WHEN C.Kode = 'INC00001' THEN C.ValueString ELSE NULL END AS OpgaveNummer
FROM (
  SELECT JournalID, Kode, MAX(TidStempel) AS MaxTidStempel
  FROM DataBase
  WHERE Kode = 'EXP00029' AND TidStempel >= '2023-02-01'
  GROUP BY JournalID, Kode
) AS M
INNER JOIN DataBase AS A
  ON M.JournalID = A.JournalID AND M.Kode = A.Kode AND M.MaxTidStempel = A.TidStempel
INNER JOIN DataBase AS B
  ON A.JournalID = B.JournalID AND A.Kode = B.Kode AND A.TidStempel = B.TidStempel
LEFT JOIN DataBase AS C
  ON A.JournalID = C.JournalID AND C.Kode = 'INC00001'
WHERE B.Kode = 'EXP00029' AND B.TidStempel >= '2023-02-01'
ORDER BY A.JournalID
Avatar billede ulykken-smed Juniormester
24. februar 2023 - 17:04 #6
Jeg har ikke testet koden på testdata, men den virkede på det den skulle, og den var forbavsende hurtig, tabellen indeholder over 107 millioner rækker(1,1 million efter filter dato), og den kørte koden på 50 sekunder
Avatar billede sfsoeren Novice
25. februar 2023 - 14:10 #7
Prøv om denne (også) virker:
SELECT DISTINCT O.JournalId
, A.Tekst 'Tekst(A)'
, B.Tekst 'Tekst(B)'
, C.Tekst 'Tekst(C)'
, D.Tekst 'Tekst(D)'

FROM DataBase O
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM DataBase WHERE Kode = 'A') A ON A.JournalId = O.JournalId AND A.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM DataBase WHERE Kode = 'B') B ON B.JournalId = O.JournalId AND B.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM DataBase WHERE Kode = 'C') C ON C.JournalId = O.JournalId AND C.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM DataBase WHERE Kode = 'D') D ON D.JournalId = O.JournalId AND D.Rnk = 1
Avatar billede ulykken-smed Juniormester
27. februar 2023 - 11:37 #8
Jeg prøver at test den i aften :-)
Avatar billede ulykken-smed Juniormester
27. februar 2023 - 19:22 #9
Tak sfsoeren

Det virker perfekt.

Jeg har lagt hele koden ind hvis andre skulle have samme udfordringer


--Drop table Test

Create Table Test(
  JournalId varchar(255)
  ,Kode varchar(255) 
  ,TidStempel varchar(255)
  ,Tekst varchar(255))
  Insert into Test values
('GG-88-TY','A','24-01-2023 04:04','Cykel')
,('GG-88-TY','B','24-01-2023 06:00','Sort')
,('GG-88-TY','B','24-01-2023 07:26','Rød')
,('GG-88-TY','B','24-01-2023 10:48','Blå')
,('GG-88-TY','B','24-01-2023 18:43','Blå')
,('GG-88-TY','B','25-01-2023 00:00','Rød')
,('GG-88-TY','C','24-01-2023 03:21','Plastic')
,('GG-88-TY','C','24-01-2023 10:04','Jern')
,('GG-88-TY','C','24-01-2023 13:12','Plastic')
,('GG-88-TY','C','24-01-2023 18:28','Plastic')
,('GG-88-TY','C','25-01-2023 00:00','Metal')
,('GG-88-TY','D','24-01-2023 00:57','2022')
,('GG-88-TY','D','24-01-2023 06:57','2020')
,('GG-88-TY','D','24-01-2023 09:07','2022')
,('GG-88-TY','D','24-01-2023 14:24','2020')
,('LL-54-AZ','A','24-01-2023 06:43','Cykel')
,('LL-54-AZ','A','24-01-2023 08:52','Knallert')
,('LL-54-AZ','A','24-01-2023 11:45','Knallert')
,('LL-54-AZ','A','24-01-2023 12:00','Cykel')
,('LL-54-AZ','B','24-01-2023 12:14','Sort')
,('LL-54-AZ','C','24-01-2023 00:57','Plastic')
,('LL-54-AZ','C','24-01-2023 01:12','Metal')
,('LL-54-AZ','C','24-01-2023 01:12','Jern')
,('LL-54-AZ','C','24-01-2023 05:45','Plastic')
,('LL-54-AZ','D','24-01-2023 09:07','2021')
,('LL-54-AZ','D','24-01-2023 14:24','2022')
,('LL-54-AZ','D','24-01-2023 15:21','2022')
,('LL-54-AZ','D','24-01-2023 17:45','2021')
,('LL-54-AZ','D','24-01-2023 21:50','2020')
,('AA-21-KK','A','24-01-2023 10:19','Cykel')
,('AA-21-KK','A','24-01-2023 15:36','Knallert')
,('AA-21-KK','A','24-01-2023 18:28','Bil')
,('AA-21-KK','B','24-01-2023 06:43','Sort')
,('AA-21-KK','B','24-01-2023 20:52','Blå')
,('AA-21-KK','C','24-01-2023 01:55','Jern')
,('AA-21-KK','C','24-01-2023 02:24','Plastic')
,('AA-21-KK','C','24-01-2023 09:50','Metal')
,('AA-21-KK','C','24-01-2023 23:02','Metal')
,('AA-21-KK','D','24-01-2023 04:00','2022')
,('AA-21-KK','D','24-01-2023 02:52','2021')

SELECT DISTINCT O.JournalId
, A.Tekst 'Tekst(A)'
, B.Tekst 'Tekst(B)'
, C.Tekst 'Tekst(C)'
, D.Tekst 'Tekst(D)'

FROM Test O
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM Test WHERE Kode = 'A') A ON A.JournalId = O.JournalId AND A.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM Test WHERE Kode = 'B') B ON B.JournalId = O.JournalId AND B.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM Test WHERE Kode = 'C') C ON C.JournalId = O.JournalId AND C.Rnk = 1
INNER JOIN
(SELECT JournalId, Tekst, RANK() OVER (Partition BY JournalId ORDER BY TidStempel DESC) Rnk FROM Test WHERE Kode = 'D') D ON D.JournalId = O.JournalId AND D.Rnk = 1
Avatar billede sfsoeren Novice
28. februar 2023 - 08:47 #10
Det lyder godt :-)
Jeg blev lidt i tvivl, om der altid skal/vil være data i alle 4 Tekst felter - hvis ikke, så kan du bare ændre erstatte alle 4 "INNER JOIN" til "LEFT OUTER JOIN".
Avatar billede ulykken-smed Juniormester
01. marts 2023 - 14:37 #11
Ja den virker bedre med "LEFT OUTER JOIN", da der godt kan mangle data nogle gange
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester