Question:
I have a table consisting of three date fields formatted YYYY-MM-DD HH-MM-SS: in , out and near . If there is in, there must also be out , and both may or may not be accompanied by near ; near can exist on its own. It is not possible that all three are NULL ; for example:
in_time out_time near_time
2019-10-02 10:46:31 2019-10-02 12:34:43 2019-10-02 09:51:57
NULL NULL 2019-10-02 11:51:08
2019-10-02 12:02:40 2019-10-02 12:08:56 NULL
And I need to convert it into a table that for daily intervals of half an hour counts the number of in , out and near ; such that:
interval qt_in qt_out qt_near
2019-10-02 08:30:00 1 0 2
2019-10-02 09:00:00 1 1 5
2019-10-02 09:30:00 2 0 3
That is, I need to know the number of entries, departures and approaches for each day in half-hour sections; It is possible that there are records in any section, be it [00:00 – 00:30], [12:00 – 12:30] or [23:30 – 24:00].
Start of the edition:
Right now I have this:
SELECT
from_unixtime((unix_timestamp(in_time) DIV (60 * 30)) * (30 * 60)) AS interval_in,
sum(CASE WHEN in_time IS NOT NULL THEN 1 ELSE 0 END) AS qt_in
FROM count_config_module.ap_summary
GROUP BY
interval_in
ORDER BY interval_in
;
Analogous queries for out and near return the results I expect for each date field, but now I would need to put it all together in the same table with a common time interval field coming from … any of them? Of all? None?
There are many more nears than in and out , although sometimes there are in and out without near , so it is quite possible that absolutely all the necessary intervals for in and out are already present in near .
With this I get the common range of intervals I want:
SELECT
DISTINCT from_unixtime((unix_timestamp(in_time) DIV (60 * 30)) * (30 * 60)) AS interval_all
FROM count_config_module.ap_summary
UNION
SELECT
DISTINCT from_unixtime((unix_timestamp(out_time) DIV (60 * 30)) * (30 * 60))
FROM count_config_module.ap_summary
UNION
SELECT
DISTINCT from_unixtime((unix_timestamp(near_time) DIV (60 * 30)) * (30 * 60))
FROM count_config_module.ap_summary
;
However, I see no way to take advantage of it …
End of edition.
Thanks in advance.
Answer:
The solution could be the following:
-- Buscamos los campos que están entre los rangos de fecha
SELECT
interval_bajo intervalo,
SUM(
CASE WHEN in_time BETWEEN interval_bajo AND interval_alto
THEN 1 ELSE 0 END
) in_time,
SUM(
CASE WHEN out_time BETWEEN interval_bajo AND interval_alto
THEN 1 ELSE 0 END
) out_time,
SUM(
CASE WHEN near_time BETWEEN interval_bajo AND interval_alto
THEN 1 ELSE 0 END
) near_time
FROM (
SELECT
DISTINCT FROM_UNIXTIME(interval_all) interval_bajo,
FROM_UNIXTIME(interval_all + (60 * 30) - 1) interval_alto
FROM (
SELECT
DISTINCT (UNIX_TIMESTAMP(in_time) DIV (60 * 30)) * (60 * 30) AS interval_all
FROM ap_summary
UNION
SELECT
DISTINCT (UNIX_TIMESTAMP(out_time) DIV (60 * 30)) * (60 * 30)
FROM ap_summary
UNION
SELECT
DISTINCT (UNIX_TIMESTAMP(near_time) DIV (60 * 30)) * (60 * 30)
FROM ap_summary
) tabla_intermedia
WHERE interval_all IS NOT NULL
) ap_summary_temp
INNER JOIN ap_summary
GROUP BY interval_bajo
I'm based on a subquery like the one you created, but without converting to a date, called ap_summary_temp . In it then I get the non-repeated dates (they could be repeated between fields) while I calculate the date and time of the beginning of the range and the end.
Finally, I review all the records in the ap_summary_temp table, comparing each one with each record in the previous subquery, to add 1 (as you did in your example) if each field is within the range.
The query can probably be optimized considerably, but at least it works without overcomplicating your code.
You can test the result online at this link .
The result for your input data would be:
intervalo in_time out_time near_time
2019-10-02 09:30:00 0 0 1
2019-10-02 10:30:00 1 0 0
2019-10-02 11:30:00 0 0 1
2019-10-02 12:00:00 1 1 0
2019-10-02 12:30:00 0 1 0