[mysql] query *

[Verwijderd]

Ik heb de volgende tabel(gedeelte) qua layout.

id rit datum tijd
-------------------------------
1 1 1-1-2008 11:19
2 1 1-1-2008 11:20
3 2 2-1-2008 13:01
4 2 2-1-2008 23:59
5 2 3-1-2008 01:05

elke rit bestaat uit minimaal 2 punten bv rit 1 is 2 punten en rit 2 is 3 punten

nou wil ik dat ik elke unieke rit met zijn begin en eindtijd krijg dus bv

rit 1 begin 11:19 op 1-1-2008 en eindigd op 11:20 op 1-1-2008

dit zou ik redden met een query van select min(datum),min(tijd) from tabel where rit=1 union select max(datum) max(tijd) from tabel where rit=1

dit werkt voor als de rit niet naar een volgende dag gaat want dan word zoals bij rit 2 de min(tijd) 01:15 ipv 13:01

ik kan het oplossen door meerdere querys te gebruiken maar vind het niet de meest nette oplossing.

heeft iemand een idee of een schop in de goede richting van hoe ik dit het best kan aanpakken.

[ Voor 4% gewijzigd door Verwijderd op 25-11-2008 18:47 ]

[Dido]

rit 2 komt 3 keer voor. welke records wil je hebben?

Kun je nog een normale datetime in je tabel zetten? Dan is eea met een simpele join op te lossen

[GlowMouse]

Kijk eens hoe je de datum en tijd samen kunt pakken. Je zoekt dan SELECT rit, min(datum en tijd), max(datum en tijd) FROM table GROUP BY rit.

[HendrikN]

Ik weet niet helemaal precies wat je wil, maar ik heb het idee dat een self-join kan helpen in dit geval...

[code]
SELECT * FROM tabel t1, tabel t2 WHERE t1.rit = t2.rit ORDER BY datetime
[/code]

Zoiets?

Ha, een GROUP BY is natuurljk veel makkelijker

[ Voor 44% gewijzigd door HendrikN op 25-11-2008 18:49 ]

[Verwijderd]

nee een normale datetime kan er helaas niet meer in.

ben al wel bezig geweest met concat (datum, ' ',tijd) as datetime)
maar dat veld kan ik dan niet gebruiken om op de joinen

[GlowMouse]

Er hoeft helemaal niets gejoined te worden.

[Macros]

Maak een nieuw datetime object van je afzonderlijke date en time, misschien dmv to_char en to_date enzo (eigenlijk had je gewoon 1 date kolom moeten hebben). Groepeer op id, en neem dan de min en de max van je nieuwe datetime object. Tada....

[Sergeant_Sam]

Ha, ik ken geen mysql maar hopelijk verschilt dat niet teveel van oracle...

ik zou het volgende proberen:

stap 1: selecteer de juiste records (je wilt record 4 elimineren voor rit 2)
select rit, min(id), max(id) from ritten group by rit

waarbij min(id) altijd dus start van je rit zal bevatten en max(id) vice versa het einde. De ritten zijn al uniek via het veldje ID neem ik aan...

resultaat van de query zou dan moeten zijn:
rit - min(id) - max(id)
1 - 1 - 2
2 - 3 - 5

stap 2: selecteer de bijbehorende tijden uit de juiste records
maak een nieuwe selectie door het resultaat uit stap 1 te combineren met de oorspronkelijke rittentabel
- de query uit stap 1 krijgt hier de alias t3
- de rittentabel wordt 2 keer opgenomen, 1x voor de starttijd record, 1x voor de eindtijd record. let op dat je de joins goed legt !

select t3.rit, t1.datum, t1.tijd, t2.datum, t2.tijd from
(query uit stap 1) t3, ritten t2, ritten t1
where
t3.rit = t1.rit and t3.min(id) = t1.id and
t3.rit = t2.rit and t3.max(id) = t2.id

[ Voor 7% gewijzigd door Sergeant_Sam op 25-11-2008 19:14 ]

[RobIII]

Sergeant_Sam schreef op dinsdag 25 november 2008 @ 19:12:
select t3.rit, t1.datum, t1.tijd, t2.datum, t2.tijd from
(query uit stap 1) t3, ritten t2, ritten t1
where
t3.rit = t1.rit and t3.min(id) = t1.id and
t3.rit = t2.rit and t3.max(id) = t2.id

Behalve dat je hier de 'from x, y, z' "join" methode propageert boven de expliciete joins (inner join, left outer join etc.) ga je er hier ook van uit dat een hoger ID altijd latere data bevat. Dat zal meestal wel zo zijn, maar ik zou er mijn leven niet om wagen

De juiste oplossing ligt in het correct casten van datum + tijd naar een datetime en met concat gaat dat niet werken gezien het feit dat die met interne strings zal gaan rommelen en daarbij mogelijk met datumnotatie dd/mm/yy of mm/dd/yy e.d. de mist in gaat. Ik heb even geen MySQL bij de hand, maar kun je ze niet 'gewoon' optellen om een datetime te krijgen? Hmmmz, even getest op een remote bak, maar dat pikt 'ie dus niet zo te zien... dan zul je wat workarounds (converteren naar een timestamp en terug ofzo) moeten gebruiken denk ik.

Zoiets:

SQL:

Select AddDate(<datumveld>, INTERVAL Time_To_Sec(<tijdveld>) Second) as MyNewDateTime

Overigens, wellicht handig voor TS:
Hoe werkt dat GROUP BY nu eigenlijk?
Hoe werken joins?

[ Voor 53% gewijzigd door RobIII op 25-11-2008 20:02 ]

[Sergeant_Sam]

RobIII schreef op dinsdag 25 november 2008 @ 19:48:
[...]

Behalve dat je hier de 'from x, y, z' "join" methode propageert boven de expliciete joins (inner join, left outer join etc.) ga je er hier ook van uit dat een hoger ID altijd latere data bevat. Dat zal meestal wel zo zijn, maar ik zou er mijn leven niet om wagen

Om op je observaties in te gaan:
1) impliciete joins vs expliciete joins: dit is voor ons sql-codejockeys relevant, maar wellicht niet voor degene die met het probleem zit. Ik geeft je zoverre gelijk hierin dat dit expliciet aangeduid moet worden als er meerdere tabellen gejoined moeten worden waarvan de gegevensverzamelingen niet aan elkaar gelijk zijn. Omdat in mijn selectie 3x dezelfde tabel gebruikt worden in een self-join, is (in mijn bescheiden mening) het gebruik van expliciete joins minder noodzakelijk. Het is ook maar wat je prettig vindt om te gebruiken/lezen en van het gebruikte platform afhankelijk.
2) uniciteit van de records: als het echt een rittentabel is, dan mag je toch aannemen dat eea volgtijdelijk en oplopend geregistreerd wordt? Maaar idd, 'assumption is the mother of all fuckups'. Dan moet je idd eerst alle ritten uniek maken voordat je de eerste/laatste rit eruit gaat destilleren. Linux_freak moet zelf daar een uitspraak over doen/

RobIII schreef op dinsdag 25 november 2008 @ 19:48:
[...]
De juiste oplossing ligt in het correct casten van datum + tijd naar een datetime en met concat gaat dat niet werken gezien het feit dat die met interne strings zal gaan rommelen en daarbij mogelijk met datumnotatie dd/mm/yy of mm/dd/yy e.d. de mist in gaat.

Als je idd deze methode wilt hanteren (uniek maken van de records via datum/tijd), probeer eerst even een conversie naar een getal uit met de cast/convert functie en kijken wat er uitkomt. Als er een integer (heel) getal voor de datum en een fractie (kleiner dan 0 dus) voor de tijd eruitrolt, dan kan je beide velden gewoon met elkaar optellen om een uniek timestamp te krijgen voor elk record. Je moet dan deze selectie als bron voor een min/max query gebruiken.

[Verwijderd]

De ritten horen opvolgend te zijn. maar ik wil daar liever niet naar programmeren.
Dadelijk nog eens stoeien met de query. ik deed hem al casten. SELECT cast( concat( datum, ' ', tijd ) AS DateTime ) AS dt

hij lijkt zo simpel maar krijg hem nog niet zoals ik wil

[ Voor 13% gewijzigd door Verwijderd op 25-11-2008 21:49 ]

[Sergeant_Sam]

om je op weg te helpen:

testselectie:
1) select id, rit, cast(datum as decimal(10,10)), cast(tijd as decimal(10,10)) from ritten

als voor de datum en tijd-conversies integers cq fracties eruit rollen, dan kun je beide velden bij elkaar optellen om een fictieve sleutel te creeren, wat er zo uitziet:

2) select select id, rit, datum, tijd, cast(datum as decimal(10,10)) + cast(tijd as decimal(10,10)) from rit as dt_sleutel from ritten

dit zou dus hopelijk iets dergelijks moeten opleveren:

id rit datum tijd dt_sleutel
-------------------------------
1 1 1-1-2008 11:19 1111,1111
2 1 1-1-2008 11:20 1111,1121
3 2 2-1-2008 13:01 1112,1311
etc...

3) vanaf hier kan je het wel raden denk ik....doe een min/max query over de query uit stap 2

select rit, max(dt_sleutel), min(dt_sleutel) from (select select id, rit, datum, tijd, cast(datum as decimal(10,10)) + cast(tijd as decimal(10,10)) from rit as dt_sleutel from ritten) ritten_uniek group by rit

zoiets ?

edit: idd foutje; weet niet of mysql kolom-aliassen herkent, het is immers een berekend kolom

[ Voor 4% gewijzigd door Sergeant_Sam op 26-11-2008 17:06 ]

[Verwijderd]

krijg alleen de melding bij de dt_sleutel Unknown column 'dt_sleutel' in 'field list'

morgen maar eens verder kijken

[pedorus]

Gaat dit over data van mijn ov-chipkaart ofzo?

Stel je hebt dus zo'n brak datamodel met data waaraan je (nog ) niks kan veranderen:

SQL:

drop table if exists ritten;
drop view if exists tripstops;

create table ritten(id int, rit int, datum varchar(10), tijd varchar(10));
insert into ritten values (1,1,"1-1-2008","11:19"), (2,1,"1-1-2008","11:20"),
    (3,2,"2-1-2008","13:01"), (4,2,"2-1-2008","23:59"), (5,2,"3-1-2008","01:05");

Ik zou dan eens kijken naar str_to_date en eerst een view maken:

SQL:

create view tripstops as 
    select id, rit as trip, 
        str_to_date(concat(datum,' ', tijd),'%e-%c-%Y %H:%i') as stoptime 
    from ritten;

Na ja, voor de rest is het een eitje:

SQL:

select trip, min(stoptime), max(stoptime) from tripstops group by trip;