TSQL la agrupación en el CTE recursiva

Hay una solución para uso del GRUPO POR el interior de un bucle de CTE o hay alguna solución?

Necesito grupo conjunto de resultados de una CTE mesa y usarlo en otro lazo con el mismo CTE, pero me da el siguiente error:

GROUP BY, HAVING, o funciones de agregado no están permitidos en el
parte recursiva de una expresión de tabla común recursiva ‘cte’.

Aquí está la consulta:

WITH cte
    AS
    (
        SELECT
          id,
          dailyconsumption,
          stock/dailyconsumption as cutoff
        FROM items
        WHERE father IS NULL


        UNION ALL

        SELECT
          i.id,
          SUM(father.dailyconsumption*i.num),
          MAX(stock)/SUM(father.dailyconsumption*i.num)
        FROM cte father
        JOIN items i ON father.id=i.father
        group by i.id
    )

SELECT id, MIN(cutoff)
FROM cte
GROUP BY id

SQL-Violín (con datos de ejemplo)


EDITAR… este es el problema lógico

Tengo un conjunto de usuario final de los elementos (padre=NULL) y otros sub-productos por un número de otros elementos (campo de padre y de campo num pobladas).
Tengo el dailyconsumption sólo para el usuario final de los elementos (inicio mi cte con “DONDE el padre ES NULO”), y sub-elementos del dailyconsumption se calcula mediante la SUMA(el padre.dailyconsumption *elemento.num).

WITH cte AS(
    SELECT
      id,
      dailyconsumption,
      stock/dailyconsumption as cutoff
    FROM items
    WHERE father IS NULL


    UNION ALL

    SELECT
      i.id,
      father.dailyconsumption*i.num
      0
    FROM cte father
    JOIN items i ON father.id=i.father
)

SELECT id, SUM(dailyconsumption)
FROM cte
GROUP BY id

http://sqlfiddle.com/#!3/f4f2a/95

Con este válido consulta voy a tener todos los dailyconsumption pobladas para todos los elementos (usuario final y sub-elementos). Por favor tenga en cuenta que el padre-hijo, la relación puede ser más de 1 nivel de profundidad.

Ahora necesito calcular la frecuencia de corte (¿para cuántos días de mi acción es suficiente).
Para el uso final es muy fácil y ya calculado en el primer CTE: stock/dailyconsumption.
Para los sub-elementos que es un poco más complicado:
punto.stock/punto.dailyconsumption + MIN(padre.de corte)
donde MIN(padre.de corte) es el mínimo de corte de todos los padres de este punto.
Esto es porque necesito otro grupo.

Puede que necesite otra CTE bucle en el mismo padre-hijo de una relación?

Gracias por su atención y perdón por mi inglés.

¿Cuál es el significado de la agrupación en la parte recursiva de cte? Supongo que te han árbol, entonces el punto.id será única en cada iteración.
¿Cuál sería el resultado deseado (el uso de los datos en el SQL-Violín)?
Al parecer, usted no puede tener ningún GRUPO ni a la función de AGREGADO cuando el uso recursivo de la tabla…
Podría usted dar por favor espera conjunto de resultados para el violín de datos? Puede tener buena solución.

OriginalEl autor Tobia | 2012-12-19

2 respuestas

  1. 4
    ;WITH cte AS
     (
      SELECT id, father, 
             dailyconsumption,
             (stock / dailyconsumption) AS cutoff,
             0 AS [Level] 
      FROM items
      WHERE father IS NULL
      UNION ALL
      SELECT i.id, i.father, 
             c.dailyconsumption * i.num,
             i.stock / (c.dailyconsumption * i.num),
             [Level] + 1
      FROM cte c JOIN items i ON c.id = i.father
      )
      SELECT c.id, c.dailyconsumption, c.cutoff AS subItemsCutoff, 
             MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]) AS fatherCutoff,
             (c.cutoff  + ISNULL(MIN(ct.cutoff) OVER(PARTITION BY ct.[Level]), 0)) AS Cutoff
      FROM cte c LEFT JOIN cte ct ON c.father = ct.id

    Demo en SQLFiddle

    OriginalEl autor Aleksandr Fedorenko

  2. 0

    Recomiendo el uso de una variable de tabla en su lugar. Declarar la tabla y, a continuación, insertar los registros en la misma. Usted tendría que encontrar una manera de bucle a través de él en el segundo comando insert into. Tengo esto para empezar:

    DECLARE @staging TABLE
    (
        id                  INT
        ,dailyconsumption   FLOAT
        ,cutoff             FLOAT
    )
    
    INSERT INTO @staging
        SELECT
          id,
          dailyconsumption,
          stock/dailyconsumption as cutoff
        FROM 
          items
        WHERE 
          father IS NULL
    
    INSERT INTO @staging
      SELECT
          i.id,
          SUM(father.dailyconsumption*i.num),
          MAX(stock)/SUM(father.dailyconsumption*i.num)
        FROM 
          @staging father
    
            JOIN items i 
            ON father.id=i.father
        group by 
          i.id
    
    SELECT 
      id
     ,MIN(cutoff)
    FROM 
      @staging
    GROUP BY 
      id
    Él no puede hacer recursividad con una variable de tabla, aunque…
    ¿Por qué es eso? Se puede seleccionar después de la inserción en ella.
    Que hace a un nivel de recursividad, pero el CTE mantendrá recursing más profundo que el que hasta la parte inferior del árbol. Supongo que podría hacerlo con una variable de tabla, pero tendrías que también el uso de algún tipo de while bucle para recurse todo el camino hacia abajo por el árbol.
    Entiendo lo que usted está diciendo. Creo que la creación de un bucle en el segundo comando insert into debería funcionar.

    OriginalEl autor Neil

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *