[SQL] Gemidelde coördinaten opvragen zonder history

[Tead]

Ik heb hier een tabel vol met gegevens over coördinaten van gebruikers en auto's. In de database is dit als volgt opgeslagen:

code:

id   user_id  car_id      x      y      timestamp
 1         1       1    345    235     1198921256
 2         1       1    124    141     1198932522
 3         1       2    358    111     1198982352
 4         2       3    347    566     1199017371
 5         2       3    266    951     1199029128
 6         3       1    673    352     1199042834
 7         3       2    284    361     1199056104

Nu wil ik per auto de gemiddelde coördinaten (x, y) hebben zonder het meenemen van de historische data. Als je kijkt naar id 1 en 2 dan zie je dat persoon 1 met auto 1 eerst op 345, 235 heeft gestaan en later op 124, 141. Nu wil ik bij het berekenen van de gemiddelde coördinaten alleen gegevens meenemen die het jongst zijn per auto per persoon. Dus als ik het gemiddelde wil weten van auto 1 dan bereken ik het gemiddelde van id 2 en 6. id 1 wordt niet meegerekend omdat gebruiker 1 met auto 1 op een later tijdstip ergens anders is geweest.

Dit heb ik tot dusver (niet veel):

SQL:

SELECT 
   t.car_id, 
   AVG(t.x) AS avgx, 
   AVG(t.y) AS avgy

FROM 
   table t

GROUP BY 
   t.car_id

Dit geeft een gemiddelde van de coördinaten per car_id. Dus voor auto 1 de id's 1, 2 en 6.

Nu zou ik graag van jullie willen weten of dit überhaupt mogelijk is en als het kan mij een zet de goeie richting in kan geven.

Even voor de volledigheid.

• Ik maak gebruik van Microsoft SQL Server 2005 Express.
• Als workaround gebruik ik nu een 2de tabel waar alleen de laatste gegevens worden opgeslagen, niet netjes al die redundante data. (De database is/wordt veel groter dan deze 7 records )
• Ik wil het gelieve in de database oplossen i.p.v in de code.

[farlane]

Alle unieke coordinaten per auto en user in een subquery gooien denk ik.
Vraag ik me nog wel af wat die 'gemiddelde coordinaten' nou precies voorstellen?

[Tead]

farlane schreef op maandag 31 december 2007 @ 00:43:
Alle unieke coordinaten per auto en user in een subquery gooien denk ik.
Vraag ik me nog wel af wat die 'gemiddelde coordinaten' nou precies voorstellen?

Met die sub query kan ik wel even gaan spelen.

En die 'gemiddelde coordinaten', tja. Het geeft weinig informatie maar heb het wel ergens specifiek voor nodig.

[Nic]

Je kunt de fijne functie 'rank()' gebruiken om eenvoudig de records met het laatste timestamp te selecteren per car/user. Samen met common table expressions kun je dan iets moois maken:

code:

;with temp as (
    select rank() over (partition by user_id,car_id order by timestamp desc) as ranking,
    car_id,x,y from tbltest
)
select car_id, avg(x) as avgx, avg(y) as avgy from temp
    where ranking=1 
    group by car_id

of, als je toch liever subqueries gebruikt:

code:

select car_id, avg(x) as avgx, avg(y) as xvgy from (
    select rank() over (partition by user_id,car_id order by timestamp desc) as ranking,
    car_id,x,y from tbltest
) temp
    where ranking=1
    group by car_id

Die rank() functie is erg handig voor dit soort queries. De records worden gegroepeerd door 'partition by', op volgorde gezet door 'order by timestamp desc', en dan bevat rank() het n-de record in het rijtje.
Dat heeft mysql niet

Maar wat je met gemiddelde coordinaten moet, weet ik ook niet zo. Wil je de optimale plek vinden om de auto te parkeren? ;-)

[Tead]

Dat is precies wat ik zoek

Mijn dank ik groot.

En die ideale parkeerplek komt in de buurt. Dat punt is het meest ideale punt om een zend wagen te plaatsen. Het gaat hier om radiografische auto's die een bepaalde frequentie hebben (user_id).

[whoami]

Wat ik niet zo handig vind, is dat je die timestamp daadwerkelijk als een column van het type 'timestamp' hebt geimplementeerd. Het Timestamp type is imho meer voor gebruik bij optimistic concurrency e.d., maar niet om echt een 'tijdstip' aan te geven.