Seleccionar filas donde el valor de la columna ha cambiado
Digamos que tengo la siguiente tabla:
Value Time
0 15/06/2012 8:03:43 PM
1 15/06/2012 8:03:43 PM *
1 15/06/2012 8:03:48 PM
1 15/06/2012 8:03:53 PM
1 15/06/2012 8:03:58 PM
2 15/06/2012 8:04:03 PM *
2 15/06/2012 8:04:08 PM
3 15/06/2012 8:04:13 PM *
3 15/06/2012 8:04:18 PM
3 15/06/2012 8:04:23 PM
2 15/06/2012 8:04:28 PM *
2 15/06/2012 8:04:33 PM
¿Cómo selecciono las filas marcadas, es decir, dónde Value
ha cambiado? Básicamente estoy tratando de encontrar el momento en que Value
ha cambiado para que pueda hacer otras consultas basadas en esos intervalos de tiempo. La solución no debe depender de saber Value
o Time
de antemano.
Me parece que esto no debería ser muy difícil (¡pero es lo suficientemente difícil para mí aparentemente!).
Actualmente estoy usando SQL Server 2008 aunque tengo acceso a 2012 si las nuevas funciones de ventana/analítica son útiles.
He intentado adaptar las soluciones aquí http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ pero mi consulta no se completó después de una hora! Creo que las uniones explotan el tamaño de la fila en algo inmanejable (o lo arruiné).
Puedo resolver este problema con código C# y múltiples llamadas a db, pero parece algo que podría se hace en una función de valor de tabla o SP que sería mucho mejor.
Además, una solución que solo funciona con el aumento de Value
está bien si es más fácil.
3 answers
Creo que esto es lo que buscas:
;WITH x AS
(
SELECT value, time, rn = ROW_NUMBER() OVER
(PARTITION BY Value ORDER BY Time)
FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;
Esto puede ser lento si el conjunto de resultados es grande y no hay un buen índice de soporte...
EDITAR
Ah, espera un segundo, los valores suben y bajan, no solo suben... si ese es el caso, puede probar este enfoque mucho más lento:
DECLARE @x TABLE(value INT, [time] DATETIME)
INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');
;WITH x AS
(
SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;
Resultados:
value time
----- -----------------------
1 2012-06-15 20:03:43.000
2 2012-06-15 20:04:03.000
3 2012-06-15 20:04:13.000
2 2012-06-15 20:04:28.000
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-06-20 20:25:01
DECLARE @x TABLE(value INT, [time] DATETIME)
INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');
; with temp as
(
SELECT
value, [time], lag(value,1,-1) over (order by [time] ) as lastValue
FROM @x
)
SELECT
[value],[time]
FROM
temp
WHERE value <> lastValue
Resultados:
value time
---------------------------
0 2012-06-15 20:03:43.000
1 2012-06-15 20:03:43.000
2 2012-06-15 20:04:03.000
3 2012-06-15 20:04:13.000
2 2012-06-15 20:04:28.000
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-29 15:12:21
Podemos hacer esto usando sub consultas también
SELECT sub1.value, sub1.time FROM
(SELECT *,rn,id FROM
(SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub1
LEFT OUTER JOIN
(SELECT *,rn,id FROM
(SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub2
ON sub1.id = sub2.id + 1
WHERE sub1.rn - sub2.rn <> 1 OR sub2.rn IS NULL;
Por lo tanto, he comparado los valores de 2 filas si cambia, entonces la diferencia de rn no será igual a 1 de lo contrario el valor de rn se incrementará en 1 por lo tanto, he elegido todas las filas cuya diferencia con el valor de rn de la siguiente fila no es 1 y sub2.rn IS NULL se utiliza para la primera fila porque la unión se producirá desde id = 2.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-22 04:55:28