MySQL query hulp

Door Liefhebber Laravel op 15-11-2016 08:54 gewijzigd op 15-11-2016 08:54

3.732 views

Hoi,

Kunnen jullie me even helpen om een juiste query op te bouwen: Dit is mijn tabel met evenementen:
(Weet dat de echte tabel ongeveer 3000 evenementen bevat!)

[table]
[tr][td]id[/td][td]title[/td][td]start[/td][td]end[/td][/tr]
[tr][td]10[/td][td]evenement1[/td][td]2016-11-05 08:00:00[/td][td]2016-11-05 10:00:00[/td][/tr]
[tr][td]11[/td][td]evenement2[/td][td]2016-11-06 09:00:00[/td][td]2016-11-06 12:00:00[/td][/tr]
[tr][td]12[/td][td]evenement3[/td][td]2016-11-06 14:00:00[/td][td]2016-11-06 16:00:00[/td][/tr]
[tr][td]13[/td][td]evenement4[/td][td]2016-11-08 10:00:00[/td][td]2016-11-06 12:00:00[/td][/tr]
[tr][td]14[/td][td]evenement5[/td][td]2016-11-08 10:00:00[/td][td]2016-11-06 12:00:00[/td][/tr]
[/table]

Ik zou een lijst willen verkrijgen van alle uren / gegroepeerd per dag dat er zich een evenement voordeed.
Dus: de lijst die ik hier wil verkrijgen is:

[li]Datum: 2016-11-05 - Aantal uren: 2:00:00[/li]
[li]Datum: 2016-11-06 - Aantal uren: 5:00:00[/li]
[li]Datum: 2016-11-08 - Aantal uren: 2:00:00[/li]

Merk op dat op datum 6/11 2 evenementen waren, die opgeteld zijn
Merk op dat op datum 8/11 ook 2 evenementen waren maar dat die overlapten, en deze zijn dus samengeteld !!

Kunnen jullie me een query bezorgen die dit resultaat geeft?

Alvast bedankt!

Groetjes!

Ger van Steenderen

19-11-2016 13:14 gewijzigd op 19-11-2016 13:18

@Jan
In de subquery met de union heb je geen aliassen op DATE(start) en TIME(start).
Hoe dan ook zal de oplossing van SO in jouw geval niet werken, want dan moet je gaan bijhouden wanneer de datum veranderd.
Daarnaast zullen events die op andere datum beginnen dan ze eindigen niet goed door gerekend worden.

Onderstaande methode werkt met datasets:


SELECT DATE(t.period_start) adate, SEC_TO_TIME(SUM(t.secs)) atime
FROM
	(SELECT p.period_start,
			CASE WHEN COUNT(e.id) > 0 THEN 900 ELSE 0 END secs
	FROM
		(SELECT
			@begin_period := @begin_period + interval 15 MINUTE period_start,
			@end_period := @begin_period + INTERVAL 14 MINUTE period_end
		FROM
			information_schema.COLLATIONS
		CROSS JOIN
			information_schema.COLLATIONS x
		CROSS JOIN
			(SELECT @begin_period := CURRENT_DATE - INTERVAL 6 DAY - INTERVAL 15 MINUTE) var
		WHERE @begin_period + INTERVAL 29 MINUTE < CURRENT_DATE + INTERVAL 1 DAY
		) p
	LEFT JOIN
		events e ON p.period_start < e.end_time AND p.period_end > e.start_time
	GROUP BY p.period_start
	) t
GROUP BY adate

De subquery p genereert een dataset (virtuele tabel) met een interval van 15 minuten van de afgelopen 7 dagen


+ ------------------- + ------------------- +
| period_start        | period_end          |
+ ------------------- + ------------------- +
| 2016-11-13 00:00:00 | 2016-11-13 00:14:00 |
| 2016-11-13 00:15:00 | 2016-11-13 00:29:00 |
| 2016-11-13 00:30:00 | 2016-11-13 00:44:00 |
| 2016-11-13 03:00:00 | 2016-11-13 03:59:00 |
.....
| 2016-11-19 22:45:00 | 2016-11-19 22:59:00 |
| 2016-11-19 23:00:00 | 2016-11-19 23:14:00 |
| 2016-11-19 23:15:00 | 2016-11-19 23:29:00 |
| 2016-11-19 23:30:00 | 2016-11-19 23:44:00 |
| 2016-11-19 23:45:00 | 2016-11-19 23:59:00 |
+ ------------------- + ------------------- +

Dan een left join op events die in dat bereik plaats hebben, en via COUNT(id) wordt dan bepaald of een bereik mee geteld moet worden.
SQL Fiddle

Reageren

Inloggen om te reageren