[SQL] overlap in tijdschijven uitlijsten

[cor-gijs]

Met het volgende vraagstuk zit ik al een tijdje te stoeien, maar ben helaas nog niet tot een oplossing gekomen. Hopelijk kunnen jullie mij in de juiste richting sturen.

Klanten kunnen een abonnement nemen waarmee ze toegang hebben tot 2 diensten (dienst 1 en dienst 2). Ik wil de volgende overzichten maken:

1) Periode waarop het abonnement loopt, maar waarop geen gebruik gemaakt wordt van een dienst;
2) Periode waarop het abonnement loopt, maar waarop alleen gebruik gemaakt wordt van dienst 1;
3) Periode waarop het abonnement loopt, maar waarop alleen gebruik gemaakt wordt van dienst 2.

Ik heb 3 tabellen:

Tabel 1: tijdschijven abonnementen, met de velden:
• Klantnummer
• Startdatum abonnement
• Einddatum abonnement

Tabel 2: tijdschijven dienst 1, met de velden:
• Klantnummer
• Startdatum dienst 1
• Einddatum dienst 1

Tabel 3: tijdschijven dienst 2, met de velden:
• Klantnummer
• Startdatum dienst 2
• Einddatum dienst 2

Idealiter maakt een klant gedurende de looptijd van het abonnement gebruik van beide diensten, grafisch weergegeven:

|--------------------| Abonnement
|--------------------| Dienst 1
|--------------------| Dienst 2

Maar het volgende komt bijvoorbeeld ook voor:

|--------------------| Abonnement
|----------|.......... Dienst 1
|--------------------| Dienst 2

Of:

|-----------|--------| Abonnement:
..................... Dienst 1:
..|-------| |------|.. Dienst 2:

Ik wil dus per klant de perioden uitlijsten waarop ze geen gebruik maken van een dienst, waarop ze alleen gebruik maken van dienst 1 en waarop ze alleen gebruik maken van dienst 2.
Hoe kan ik dit het beste aanpakken?

Ik heb de tabellen op dit moment in MS Access staan, maar heb ook een MySQL-server draaien.

Ter indicatie: het gaat ongeveer om 10.000 klanten en +- 10 perioden per klant. Handmatig uitzoeken is dus niet echt leuk werk

[Grijze Vos]

Wat heb je al geprobeerd? Waar loop je tegenaan?

[pedorus]

cor-gijs schreef op dinsdag 18 mei 2010 @ 10:09:
Ter indicatie: het gaat ongeveer om 10.000 klanten en +- 10 perioden per klant. Handmatig uitzoeken is dus niet echt leuk werk

Je gaat geen 10.000 grafieken uitprinten neem ik aan, dus hoe wil je dit precies gaan weergeven? Of gaat dit om 1 klant tegelijkertijd bij een formulier over die klant ofzo? Wat voor soort grafiektype had je in gedachten? Scatter?

Gaat dit om een access-frontend, of iets anders? Het probleem lijkt me niet zozeer bij de query-kant te zitten (2 unions zo te zien), maar bij de weergave-kant.

[cor-gijs]

pedorus schreef op dinsdag 18 mei 2010 @ 11:18:
[...]

Wat voor soort grafiektype had je in gedachten? Scatter?

Volgens mij zorgt de term 'uitlijsting' voor wat misverstanden: ik wil geen grafiek of andere grafische weergave maken, ik wil een tabel maken met de volgend velden:

• klantnummer
• startdatum periode
• einddatum periode
• type

Met als type:

1. geen enkele dienst
2. alleen dienst 1
3. alleen dienst 2

Ik heb nog niets concreets geprobeerd: ik weet namelijk niet zo goed hoe ik moet starten.

Wat ik wel bedacht had:
per maand een tabel maken met 31 (of 30 of 29 of 28, afhankelijk van de maand) kolommen, 1 per dag. Vervolgens per klant, per dag uitzoeken of er een record in de 3 tabellen staat die overlap heeft met deze dag.

Dit is natuurlijk een erg omslachtige manier, en ik weet bijna zeker dat dit makkelijker kan, maar ik weet niet hoe.

Concreet komt het dus neer op het volgende:
Stel: ik heb een set perioden gedefinieerd door een begindatum en een einddatum. Hoe kan ik met een query bepalen wat de overlap is tussen deze perioden?
En hoe kan ik een lijst maken met de perioden waarvoor geen overlap is tussen bovengenoemde records?

voorbeeld:
Klant 1 heeft een abonnement van 1-1-2010 t/m 28-2-2010
Hij gebruikt dienst 1 van 1-1-2010 t/m 31-1-2010
Hij gebruikt dienst 2 van 10-1-2010 t/m 10-2-2010

Dan wil ik graag een query bouwen die het volgende resultaat gaat opgeven:

klant	start periode	eind periode	type
1	1-1-2010	9-1-2010	2
1	1-2-2010	10-2-2010	3
1	11-2-2010	28-2-2010	1

[Grijze Vos]

Nogmaals, welke queries heb je zelf al geprobeerd?

[Mental]

Grijze Vos schreef op dinsdag 18 mei 2010 @ 15:02:
Nogmaals, welke queries heb je zelf al geprobeerd?

Ik heb nog niets concreets geprobeerd: ik weet namelijk niet zo goed hoe ik moet starten.

[Feanathiel]

De geen-dienst periode is natuurlijk relatief. Een datum begint ergens bij 1970 en eindigt ergens rond 2039, afhankelijk van de implementatie. Wil je voor elke persoon deze gegevens opslaan (dus de gehele periode)? Wat nu als een persoon een nieuwe dienst neemt? Ga je die oude periode verwijderen, in tweeën splitsen, opschuiven? Wat als er twee (of zelfs meer) diensten tegelijk worden afgenomen?

Ik denk dat je het beste afkomt wanneer je juist deze periodes niet opslaat. Op deze manier kun je in je code bijvoorbeeld opvragen: ik wil graag een overzicht tussen deze en deze datum. De plekken (data) waar geen dienst van toepassing is, laat je opvullen/herkennen door het script. Meer een view van je gegevens eigenlijk.

Een mogelijke implementatie hierbij is:
Gebruikers: Id, klantnummer.
Diensten: Id, Naam.
GebruikersDiensten: (Id,) KlantId, DienstId, StartDatum, EindDatum.

Belangrijkste vragen hierbij: moet het uitbreidbaar zijn en/of moeten gegevens aangepast worden (of hoeft het alleen maar opgevraagd worden, eenmalig)? Het bovenstaande datamodel staat beide opties toe.

[ Voor 3% gewijzigd door Feanathiel op 18-05-2010 16:08 . Reden: Verduidelijking uitleg (minimaal) ]

[pedorus]

Dit gaat lastig worden in sql.

Ik heb wel een mogelijkheid. Stel we definiëren de mogelijke begindagen:

SQL:

  select klantnummer, begindatum from tabel1
union
  select klantnummer, einddatum+1 from tabel1
union
... --nog 3 queries...
union
   select klantnummer, einddatum+1 from tabel3

Het zelfde doen we voor de mogelijke einddagen (enkel dan op begindatum-1 en einddatum). Vervolgens maken we een query op basis van deze 2 views met alle mogelijke periodes. Iets als:

SQL:

select begindata.klantnummer, begindatum, einddata.einddatum 
from begindata inner join einddata on
    begindata.klantnummer=einddata.klantnummer and 
    einddata.einddatum>=begindata.begindatum
 left join einddata as d2 on 
    begindata.klantnummer=d2.klantnummer and
    d2.einddatum>=begindata.begindatum and 
    d2.einddatum<einddatum.einddatum
where d2.einddatum is null

Vanuit deze view maken we dan 3x een left join op de oorspronkelijke tabellen om het type te bepalen. Je kan je zo voorstellen dat dit niet erg efficiënt is, maar ik vrees dat hier sowieso geen efficiënte oplossing voor is als je een query wil hebben.

Oh, en ik neem aan dat je type 1 alleen voor tussenliggende periodes wil hebben, maar eventueel is dat makkelijk op te lossen met een extra union.

[CyBeRSPiN]

Als je een Oracle DB gebruikt is er hoop, dan heb je de 'lag' en 'lead' analytic functions die je hiervoor bij uitstek kan gebruiken.
Zie hier een verhelderend artikeltje: http://technology.amis.nl...al-functions-lag-and-lead

Mogelijk hebben andere DBMS'en vergelijkbare functies, vermoed dat MySQL dit iig niet (zonder getruc) aankan.

[hemaworst]

Voor zul soort problemen is het altijd het makkelijkst om een hulp
tabel aan te maken met alleen maar de begin en einddatums van alle dienstperiodes er in.
Die datumtabel kan je dan left joinen met je oorspronkelijk tabellen,
om zo te zijn of die datum valt in :

• een dienstperiode 1
• een dienstperiode 2
• berekenen welk type het is(1,2 of 3)

Maak dan een view [vw_calc_perdag]. Je krijgt dan zo'n resultaat:

• klant, datum , type
• 1, 1-1-2010 , 0
• 1, 2-1-2010 , 1
• 1, 3-1-2010 , 1
• 1, 4-1-2010 , 2
• 1, 5-1-2010 , 0

Volgende stap:

left join vw_calc_perdag 2x met zich zelf:

vw_calc_perdag v1 LEFT JOIN vw_calc_perdag v2 ON
v2.datum > v1.datum
v2.dienst <>v1.dienst
v2.klant =v1.klant
LEFT JOIN vw_calc_perdag v3 ON
v3.datum< v1.datum
v3.dienst <>v1.dienst
v3.klant =v1.klant


volgende stap:
resultaat grouperen: alle columns uit linker tabel, MIN(v2.datum) ,Max(v3.datum)

Laatste stap, alleen datums tonen die een beginperiode is:
Voeg een HAVING toe:
MAX(v3.datum) is null

Ik heb nu even geen tijd om het voor je uit te werken, maar wellicht heb je er in wat aan.Er zullen vast wat fouten inzitten, want ik doe het uit mijn hoofd op de pc van de vrouw.
Alhoewel het wel zwaar is moet het op 10.000 records wel binnen een seconde kunnen draaien denk ik.

[ Voor 255% gewijzigd door hemaworst op 20-05-2010 18:17 ]