He luchado con esto por tres días y ahora no puedo conseguir mi cabeza alrededor de este. Es muy similar a un post reciente de la mina «t-sql secuencial duración», pero no exactamente el mismo…quiero restablecer el número de fila en base a un cambio en la columna x (en mi caso, la columna de «que»)…

Aquí está la primera consulta que devuelve una pequeña muestra de la materia prima(ish) de datos:

SELECT      DISTINCT chr.custno, 
            CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as  moddate, 
            chr.who     
FROM        <TABLE> chr 
WHERE       chr.custno = 581827
            AND LEFT(chr.who, 5) = 'EMSZC'
            AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
ORDER BY    chr.custno

Resultado:

custno      moddate             who
581827      2012-11-08 08:38:00.000     EMSZC14
581827      2012-11-08 08:41:10.000     EMSZC14
581827      2012-11-08 08:53:46.000     EMSZC14
581827      2012-11-08 08:57:04.000     EMSZC14
581827      2012-11-08 08:58:35.000     EMSZC14
581827      2012-11-08 08:59:13.000     EMSZC14
581827      2012-11-08 09:00:06.000     EMSZC14
581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1
581827      2012-11-08 09:05:04.000     EMSZC49
581827      2012-11-08 09:06:32.000     EMSZC49
581827      2012-11-08 09:12:03.000     EMSZC49
581827      2012-11-08 09:12:38.000     EMSZC49
581827      2012-11-08 09:14:18.000     EMSZC49
581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

Segundo paso es agregar el número de la fila (yo no hice esto en la primera consulta, debido a la utilización de la palabra DISTINCT); así que…

WITH c1 AS (
        SELECT      DISTINCT chr.custno
                    CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
                    chr.who
        FROM        <TABLE> chr 
        WHERE       chr.custno = 581827
                    AND LEFT(chr.who, 5) = 'EMSZC'
                    AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
        )

SELECT  ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
FROM    c1

Resultado:

RowID   custno      moddate                      who
1       581827      2012-11-08 08:38:00.000     EMSZC14
2       581827      2012-11-08 08:41:10.000     EMSZC14
3       581827      2012-11-08 08:53:46.000     EMSZC14
4       581827      2012-11-08 08:57:04.000     EMSZC14
5       581827      2012-11-08 08:58:35.000     EMSZC14
6       581827      2012-11-08 08:59:13.000     EMSZC14
7       581827      2012-11-08 09:00:06.000     EMSZC14
8       581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1
9       581827      2012-11-08 09:05:04.000     EMSZC49
10      581827      2012-11-08 09:06:32.000     EMSZC49
11      581827      2012-11-08 09:12:03.000     EMSZC49
12      581827      2012-11-08 09:12:38.000     EMSZC49
13      581827      2012-11-08 09:14:18.000     EMSZC49
14      581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

El siguiente paso es donde estoy atascado: el objetivo es restablecer el RowID a 1 en cada cambio de valor en el «quién» de la columna. El siguiente código se obtiene un «casi» resultado (y cabe señalar que me robó/prestado este código en alguna parte, pero ahora no puedo encontrar la página web):

WITH c1 AS (
        SELECT      DISTINCT chr.custno,
                    CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
                    chr.who
        FROM        <TABLE> chr 
        WHERE       chr.custno = 581827
                    AND LEFT(chr.who, 5) = 'EMSZC'
                    AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
        )
, c1a AS    (
            SELECT  ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
            FROM    c1
            )

SELECT  x.RowID - y.MinID + 1 AS Row,
        x.custno, x.Touch, x.moddate, x.who      
FROM    (
            SELECT  custno, who, MIN(RowID) AS MinID
            FROM    c1a
            GROUP BY custno, who
        ) AS y
        INNER JOIN c1a x ON x.custno = y.custno AND x.who = y.who

Resultado:

Row custno      moddate                    who
1   581827      2012-11-08 08:38:00.000     EMSZC14
2   581827      2012-11-08 08:41:10.000     EMSZC14
3   581827      2012-11-08 08:53:46.000     EMSZC14
4   581827      2012-11-08 08:57:04.000     EMSZC14
5   581827      2012-11-08 08:58:35.000     EMSZC14
6   581827      2012-11-08 08:59:13.000     EMSZC14
7   581827      2012-11-08 09:00:06.000     EMSZC14
1   581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1 (Hooray! It worked!)
2   581827      2012-11-08 09:05:04.000     EMSZC49
3   581827      2012-11-08 09:06:32.000     EMSZC49
4   581827      2012-11-08 09:12:03.000     EMSZC49
5   581827      2012-11-08 09:12:38.000     EMSZC49
6   581827      2012-11-08 09:14:18.000     EMSZC49
14  581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1 (Crappies.)

Resultado DESEADO:

Row custno      moddate                     who
1   581827      2012-11-08 08:38:00.000     EMSZC14
2   581827      2012-11-08 08:41:10.000     EMSZC14
3   581827      2012-11-08 08:53:46.000     EMSZC14
4   581827      2012-11-08 08:57:04.000     EMSZC14
5   581827      2012-11-08 08:58:35.000     EMSZC14
6   581827      2012-11-08 08:59:13.000     EMSZC14
7   581827      2012-11-08 09:00:06.000     EMSZC14
1   581827      2012-11-08 09:04:39.000     EMSZC49 Reset row number to 1 
2   581827      2012-11-08 09:05:04.000     EMSZC49
3   581827      2012-11-08 09:06:32.000     EMSZC49
4   581827      2012-11-08 09:12:03.000     EMSZC49
5   581827      2012-11-08 09:12:38.000     EMSZC49
6   581827      2012-11-08 09:14:18.000     EMSZC49
1   581827      2012-11-08 09:17:35.000     EMSZC14 Reset row number to 1

Cualquier ayuda es apreciada. Usted puede reír a mí también, porque estoy seguro de que esto es bastante simple de resolver, simplemente me parece que no puede salir de mi propia manera.

Gracias.

OriginalEl autor Kris | 2012-11-15

4 Comentarios

  1. 22

    Si usted está en SQL Server 2012 puede utilizar LAG para comparar el valor de la fila anterior y se puede utilizar SUMA y MÁS para grabar los cambios.

    with C1 as
    (
      select custno,
             moddate,
             who,
             lag(who) over(order by moddate) as lag_who
      from chr
    ),
    C2 as
    (
      select custno,
             moddate,
             who,
             sum(case when who = lag_who then 0 else 1 end) 
                over(order by moddate rows unbounded preceding) as change 
      from C1
    )
    select row_number() over(partition by change order by moddate) as RowID,
           custno,
           moddate,
           who
    from C2

    SQL Violín

    Actualización:

    Una versión de SQL Server 2005. Utiliza una CTE recursiva y una tabla temporal para el almacenamiento intermedio de los datos que necesita iterar.

    create table #tmp
    (
      id int primary key,
      custno int not null,
      moddate datetime not null,
      who varchar(10) not null
    );
    
    insert into #tmp(id, custno, moddate, who)
    select row_number() over(order by moddate),
           custno,
           moddate,
           who
    from chr;
    
    with C as
    (
      select 1 as rowid,
             T.id,
             T.custno,
             T.moddate,
             T.who,
             cast(null as varchar(10)) as lag_who
      from #tmp as T
      where T.id = 1
      union all
      select case when T.who = C.who then C.rowid + 1 else 1 end,
             T.id,
             T.custno,
             T.moddate,
             T.who,
             C.who
      from #tmp as T
        inner join C
          on T.id = C.id + 1
    )
    select rowid,
           custno,
           moddate,
           who
    from C
    option (maxrecursion 0);
    
    drop table #tmp;

    SQL Violín

    Por desgracia, todavía estamos ejecutando 2005 con planes para actualizar a 2008R2 el próximo mes. La función LAG sería una solución limpia para este problema. Gracias por traer a mi atención…sólo otro argumento para pasar a SQL Server 2012.
    Muy fresco solución. Mi única preocupación (y no fundada en un hecho) es el uso de 0 con maxrecursion. Que podría provocar un bucle infinito, pero al pensar en mi estructura de datos, probablemente no con los datos que tengo. Gracias!!!
    Son bienvenidos.
    Este intervalo la función es exactamente lo que necesito! Gracias!

    OriginalEl autor Mikael Eriksson

  2. 6

    I tenido éxito con este problema mediante el uso de Rank():

    SELECT RANK() OVER (PARTITION BY who ORDER BY custno, moddate) AS RANK

    Esta devuelve los resultados deseados. De hecho, encontré este post tratando de resolver el mismo problema.

    OriginalEl autor Thrifty2100

  3. 2

    Lugar de:

    PARTITION BY custno ORDER BY custno, moddate, who)

    tratar:

    PARTITION BY custno, who ORDER BY custno, moddate)
    Trató de que ya (parece que debería funcionar, ¿no?) pero todavía se devuelve el mismo resultado con el último número de la fila = 14. Seguir tirando ideas en mí, aunque. Estoy sólo faltan algunos simple paso, estoy seguro.

    OriginalEl autor JeffO

  4. -1

    La única solución que se me ocurre es usar un cursor (ugh) y de sufrir a través de la RBAR proceso. NO es una solución elegante como el cursor se tiene que leer en exceso de 1 millón de filas. Bummer.

    OriginalEl autor Kris

Dejar respuesta

Please enter your comment!
Please enter your name here