SELECT sg.punto, sg.evento, sg.fecha_hora FROM sentry_geocercas sg INNER JOIN ( SELECT

Question

SELECT sg.punto, sg.evento, sg.fecha_hora FROM sentry_geocercas sg INNER JOIN ( SELECT

punto, date(fecha_hora) AS fecha, MAX(fecha_hora) AS max_fecha_outzone
FROM sentry_geocercas
WHERE no_bus = 22 AND evento = 'outzone' AND fecha_hora <= '2023-05-30 08:00:00'
GROUP BY punto, fecha
) max_outzone ON sg.punto = max_outzone.punto AND date(sg.fecha_hora) = max_outzone.fecha AND sg.fecha_hora = max_outzone.max_fecha_outzone
INNER JOIN (
SELECT punto, date(fecha_hora) AS fecha, MIN(fecha_hora) AS min_fecha_inzone
FROM sentry_geocercas
WHERE no_bus = 22 AND evento = 'inzone' AND fecha_hora >= '2023-05-30 08:00:00'
GROUP BY punto, fecha
) min_inzone ON sg.punto = min_inzone.punto AND date(sg.fecha_hora) = min_inzone.fecha AND sg.fecha_hora = min_inzone.min_fecha_inzone
ORDER BY sg.punto ASC, sg.evento ASC, sg.fecha_hora ASC;

How I can optimizate this query it repeats the date(fecha_hora) multiple times?

#english #programming #sql

0

31.05.2023

7 ответов

18 просмотров

Vaidik

Did you try 'DISTINCT' in the beginning after select?

0

31.05.2023

Fran Автор вопроса

Vaidik
Did you try 'DISTINCT' in the beginning after sele...

Yes already did but only moves the repeated date(fecha_hora) to the next punto(wich means point in this case bus stop)

0

31.05.2023

Vaidik

Fran
Yes already did but only moves the repeated date(f...

Can you share a few records returned by your query?

0

31.05.2023

Vaidik

Ohh, I think it makes sense you group it by point as well in inner join so it will show all the bus stop. If you know the name of bus_stop starting your outzone and inzone. Then specifying it in a where clause can work. If that's what you are trying to get. If not then the date may be the same but time must be different for different bus stops. And if you need only date then separate date from your datetime column, it should work.

0

31.05.2023

Fran Автор вопроса

Vaidik
Ohh, I think it makes sense you group it by point ...

Yes, because I need to filter those dates and the in my code get the nearest date thanks 👍

0

31.05.2023

Akshay Dixit

Fran
Yes already did but only moves the repeated date(f...

avoid to * for all columns

0

02.06.2023

Translator · Accepted Answer

Translator

SELECT sg.point, sg.event, sg.date_time FROM sentry_geofences sg INNER JOIN ( SELECT point, date(date_time) AS date, MAX(date_time) AS max_date_outzone FROM sentry_geofences WHERE no_bus = 22 AND event = 'outzone' AND date_time <= '2023-05-30 08:00:00' GROUP BY point, date ) max_outzone ON sg.point = max_outzone.point AND date(sg.date_time) = max_outzone.date AND sg.date_time = max_outzone.max_date_outzone INNER JOIN (

0

31.05.2023

169 похожих чатов

SELECT sg.punto, sg.evento, sg.fecha_hora FROM sentry_geocercas sg INNER JOIN ( SELECT

7 ответов

Похожие вопросы