[SQL] subselect

[? ?]

Het gaat over het bijhouden van traject van objecten.
Bv.
- object nr 1 gaat op 1/1/2014 naar locatie 1
- object nr 1 gaat op 3/1/2014 naar locatie 2
- object nr 1 gaat op 5/1/2014 naar locatie 3
- object nr 1 gaat op 7/1/2014 naar locatie 4
- object nr 1 gaat op 16/1/2014 naar locatie 5


De database tabel waar de geschiedenis van de locaties opgeslagen wordt ziet er uit als volgt.

code:

MOVEMENTS
----------------------
id (auto nr)
nr (nr)
datestamp (date)
location (nr)

LOCATION
----------------
id (auto nr)
name (string)

Ik wil kunnen tonen waar alle objecten zich op een bepaald tijdstip bevonden. (vandaar de MAX in de subquery en de @viewdate variabele datum)
Niet relevant denk ik, maar:
- een object volgt geen vast traject, kan een paar locaties overslaan bv.
- de objecten doen 'rondjes', ze gaan na verloop van tijd weer terug en herbeginnen.
Met:

code:

SELECT m1.nr, m1.datestamp, m1.location
FROM movements m1, movements m2
WHERE m1.datestamp = (SELECT MAX(m2.datestamp) FROM movements m2 WHERE m1.nr = m2.nr AND  m2.datestamp<=@viewdate)                
AND m1.id=m2.id            
ORDER BY m1.nr ASC

Dit loopt prima en snel.
Maar nu zou ik graag in dezelfde query willen weten wanneer het object op de voorgaande locatie was (op een bepaalde datum).
Om te kunnen berekenen hoe lang iets tussen de verschillende locaties onderweg was.
Ik dacht dat het hier mee ging lukken, maar dit duurt zééééér lang en geeft niet het goede resultaat.

SELECT m1.nr, m1.datestamp, m1.location,
(SELECT MAX(m3.datestamp) FROM movements m3 WHERE m3.id=m1.id AND m3.datestamp<=@viewdate AND m3.location<>m1.location) AS datumOpVorigeLocatie
FROM movements m1, movements m2
WHERE m1.datestamp = (SELECT MAX(m2.datestamp) FROM movements m2 WHERE m1.nr = m2.nr AND m2.datestamp<=@viewdate)
AND m1.id=m2.id
ORDER BY m1.nr ASC

code:

SELECT m1.nr, m1.datestamp, m1.location,
(SELECT MAX(m3.datestamp) FROM movements m3 WHERE m3.datestamp<=@viewdate AND m3.location<>m1.location) AS datumOpVorigeLocatie
FROM movements m1, movements m2
WHERE m1.datestamp = (SELECT MAX(m2.datestamp) FROM movements m2 WHERE m1.nr = m2.nr AND  m2.datestamp<=@viewdate)                            
AND m1.id=m2.id
ORDER BY m1.nr ASC

Edit: dit zou beter moeten gaan, er was geen m1.id=m3.id nodig, want dan zat je sowieso met hetzelfde record dat geen ander kon vinden...

Bovenstaande is in MS Access gedaan als test. Komt in MSSQL syntax.

Alles loopt goed en vlug, maar de datumOpVorigeLocatie doet het precies niet of wel (edit 47)...
Ik kijk er vanavond nog eens naar, tot dan on hold, nu naar huis!

Sowieso moet er ook MAX() komen om slechts 1 waarde te selecteren van datumOpVorigeLocatie. (zie ik nu)
Misschien moet ik mijn in de toekomst maar eens in Word typen om te beginnen, het uitschrijven werkt blijkbaar therapeutisch.

Tips en verbeteringen altijd welkom.

[ Voor 32% gewijzigd door ? ? op 17-01-2014 17:01 ]

[Verwijderd]

Waarom maak je geen join met die tabel? Gaat wel iets sneller dan.

[SPee]

Wil je alle voorgaande locaties weten?

SELECT m1.nr, m1.datestamp, m1.location
FROM movements m1, movements m2
WHERE m1.datestamp = (SELECT MAX(m2.datestamp) FROM movements m2 WHERE m1.nr = m2.nr AND m2.datestamp<=@viewdate)
AND m1.id=m2.id
ORDER BY m1.nr ASC


Je kan diepere SELECT lagen maken (inception ).

Laatste tijdstip:

SQL:

SELECT nr, MAX(datestamp) as datestamp
FROM movements
WHERE datestamp <= @viewdate 
GROUP BY nr
ORDER BY nr ASC

En met locatie:

SQL:

SELECT m1.nr, m1.datestamp, m1.location, l.name
FROM movements m1
JOIN (SELECT nr, MAX(datestamp) as datestamp FROM movements WHERE datestamp <= @viewdate GROUP BY nr) as latest ON m1.nr = latest.nr AND m1.datestamp = latest.datestamp
JOIN location l ON m1.location = l.id
ORDER BY m1.nr, m1.datestamp ASC

De voorgaande query zit nu in de JOIN. Je hebt nu van alle objecten de locatie op een bepaalde tijdstip.

Alle voorgaande locaties:

SQL:

SELECT *
FROM movements m1
JOIN (SELECT nr, MAX(datestamp) as datestamp FROM movements WHERE datestamp <= @viewdate GROUP BY nr) as latest ON m1.nr = latest.nr
JOIN location l ON m1.location = l.id
WHERE m1.datestamp < latest.datestamp
ORDER BY m1.nr, m1.datestamp ASC

De conditie voor datestamp is nu veranderd. Je controleert nu op alle voorgaande tijdstippen.

Je kan dan extra filters plaatsten of andere subselects.

Let op met filteren van location. Je kan beter het id van movements gebruiken om te filteren. Anders kan het zijn dat object een rondje heeft gemaakt, maar dat niet zichtbaar is.

code:

1 - 2 - 1 - 3 - 4 - 1 - 5
2 - 3 - 4 - 5 (bij filter van locatie 1)