Estoy usando MySQL con phpMyAdmin , a continuación se muestra mi tabla de datos:
+--------+---------+-----------------+ | userID | context | time | +--------+---------+-----------------+ | 111 | | 7/1/2021 | | 111 | | 7/16/2019 | | 111 | Reset | 7/15/2019 | | 222 | | 7/9/2020 | | 222 | Reset | 7/8/2020 | | 333 | Reset | 5/11/2020 | | 333 | | 5/10/2020 | | 444 | | 9/8/2020 | +--------+---------+-----------------+ Estoy buscando una consulta SELECT que me proporcione el tiempo MIN mayor o igual a la fecha en la que se registra un Restablecimiento en la columna de context . Si no existe ningún marcador Restablecer en la columna de context , me gustaría que se incluyera el resultado.
Entonces, para la tabla anterior, espero el resultado:
+--------+-----------------+ | userID | time | +--------+-----------------+ | 111 | 7/15/2019 | | 222 | 7/8/2020 | | 333 | 5/11/2020 | | 444 | 9/8/2020 | +--------+-----------------+Lo intenté :
SELECT * FROM foo as a WHERE ((SELECT MIN(a.time) FROM foo) >= (SELECT Max(a.time) FROM foo where context = 'Reset')) group by userIDno produce mi salida esperada, pero devuelve:
+--------+-----------+ | userID | time | +--------+-----------+ | 111 | 7/1/2021 | <--- wrong | 222 | 7/8/2020 | | 333 | 5/11/2020 | +--------+-----------+Usar funciones de ventana:
select t.*, coalesce(next_time, time) as imputed_time from (select t.*, sum(context = 'reset') over (partition by user_id) as cnt_reset, min(case when context is null then time end) over (partition by userid order by time rows between current row and unbounded following) as next_time from t ) t where cnt_reset = 0 or context = 'reset';Según el resultado esperado, puede intentar la siguiente consulta:
SELECT userID, MAX(time) FROM YOUR_TABLE WHERE context = 'RESET' GROUP BY userID UNION ALL SELECT userID, MAX(time) FROM YOUR_TABLE WHERE context IS NULL GROUP BY userID ORDER BY userID