resueltos - numero de empleados por departamento sql

Evaluar una consulta jerárquica basada en resultados de niveles inferiores

Question

Estoy usando Oracle Database 11g y debajo está una tabla ficticia con valores ficticios, que ahora trato de explicar:

Tengo una tabla que describe una relación entre los ID que representan "grupos" y los ID que son miembros del grupo. Los miembros de los grupos pueden ser miembros simples o algunos de los grupos mismos (sin ciclos). Como resultado, algunos grupos son simples grupos de 1 nivel, mientras que algunos podrían tener potencialmente muchos niveles. Cada grupo tiene un valor de "límite mínimo" y cada miembro del grupo tiene algún valor, incluido un grupo, cuando actúa como miembro de ese grupo.

Ahora, lo que estoy tratando de hacer es simplemente verificar, si la suma de los valores de los miembros del grupo es igual o mayor que el valor límite mínimo del grupo. Eso sería bastante fácil si no fuera de la jerarquía. El problema es que si uno de los miembros es un grupo, puedo contar con su valor de miembro ÚNICAMENTE si tiene suficientes valores de miembro para satisfacer su límite mínimo. Por lo tanto, la evaluación debe ir de la hoja a la raíz para que el grupo más bajo se evalúe primero y, en función de eso, se evalúen los niveles más altos de la jerarquía.

Desafortunadamente no pude resolverlo. ¿Alguien puede ayudar? Nota importante : me gustaría resolver esto sin usar R / CTE si es posible. No puedo aceptar ninguna respuesta usando la palabra clave CREAR, ya que estoy restringido para usarla.

CREATE TABLE "MYGROUPS" 
(   
"MYGROUP_ID" VARCHAR2(20 BYTE), 
"MYGROUP_LIMIT" Number, 
"MEMBER" VARCHAR2(20 BYTE), 
"MEMBER_VALUE" Number
);

insert into mygroups
(Select ''g0''  ,1  ,''00''  ,1 from dual) union
(Select ''g1''  ,5  ,''01''  ,1 from dual) union
(Select ''g1''  ,5  ,''02''  ,1 from dual) union
(Select ''g1''  ,5  ,''03''  ,1 from dual) union
(Select ''g1''  ,5  ,''g2''  ,3 from dual) union
(Select ''g2''  ,3  ,''02''  ,2 from dual) union
(Select ''g2''  ,3  ,''05''  ,2 from dual) union
(Select ''g2''  ,3  ,''g3''  ,2 from dual) union
(Select ''g3''  ,5  ,''03''  ,1 from dual) union
(Select ''g3''  ,5  ,''05''  ,1 from dual)

Este conjunto de datos debe dar como resultado g1 OK, g2 OK, g3 NO OK. G1 depende de g2, que a su vez no depende de que g3 esté bien, por lo que g1 también está bien.

En este conjunto alternativo de datos:

insert into mygroups
(Select ''g0''  ,1  ,''00''  ,1 from dual) union
(Select ''g1''  ,5  ,''01''  ,1 from dual) union
(Select ''g1''  ,5  ,''02''  ,1 from dual) union
(Select ''g1''  ,5  ,''03''  ,1 from dual) union
(Select ''g1''  ,5  ,''g2''  ,3 from dual) union
(Select ''g2''  ,3  ,''02''  ,1 from dual) union
(Select ''g2''  ,3  ,''05''  ,1 from dual) union
(Select ''g2''  ,3  ,''g3''  ,2 from dual) union
(Select ''g3''  ,5  ,''03''  ,1 from dual) union
(Select ''g3''  ,5  ,''05''  ,1 from dual)

G1 NO ES ACEPTABLE, porque depende de g2, que también depende de g3, por lo que los tres resultados aquí NO son correctos

Aquí hay una consulta para obtener la idea. Los valores ''OK'' evaluaron que el grupo satisface el límite mínimo. "NO OK" es lo opuesto. ''DUNNO'' es el problema donde no sé cómo podría evaluarlo.

select connect_by_root mygroup_id as root, mygroups.*,level
from ( Select mygroups.*,
             sum(member_value) over (partition by mygroup_id) sum_of_values,
             CASE 
                  WHEN sum (CASE WHEN member like ''g%'' THEN 1 END) over (partition by mygroup_id) > 0 THEN ''DUNNO''
                  WHEN sum(member_value) over (partition by mygroup_id) >= mygroup_limit THEN ''OK''
                  WHEN sum(member_value) over (partition by mygroup_id) < mygroup_limit THEN ''NOT OK''
             END eval
        From mygroups ) mygroups
connect by prior member = mygroup_id

Además, cualquiera de los grupos puede contener cualquier cantidad de grupos diferentes en cualquier cantidad de niveles y los miembros de los grupos pueden poseer diferentes valores de miembros en diferentes grupos.

Answer 1

Debes recordar MYGROUP_ID y MYGROUP_LIMIT mientras construyes el árbol para que puedas volver a usarlo. Estás en el camino correcto con CONNECT_BY_ROOT , pero no estás almacenando el límite.

No está claro si el valor de un miembro se debe contar dos veces si aparecen varias veces en la jerarquía. Supuse que un miembro se puede contar doblemente ya que algunos tienen valores diferentes; para eliminar esto necesitarás un paso adicional para hacer que tu jerarquía sea única en MEMBER , STARTING_GROUP y STARTING_GROUP_LIMIT

with all_hierarchies as (
 select member_value
      , connect_by_root mygroup_id as starting_group
      , connect_by_root mygroup_limit as starting_group_limit
   from mygroups m
connect by prior member = mygroup_id
        )
select starting_group
  from all_hierarchies
 group by starting_group
having sum(member_value) >= min(starting_group_limit)

Recuerda toda la información necesaria y luego evalúa si los miembros del grupo son mayores o iguales al límite. Si desea devolver todos los datos, solo tiene que volver a unir esta consulta a la jerarquía, algo así como:

with all_hierarchies as (
 select member_value
      , connect_by_root mygroup_id as starting_group
      , connect_by_root mygroup_limit as starting_group_limit
   from mygroups m
connect by prior member = mygroup_id
        )
, qualifying_groups as (
select starting_group
  from all_hierarchies
 group by starting_group
having sum(member_value) >= min(starting_group_limit)
       )
select a.*
  from all_hierarchies a
  join qualifying_groups q
    on a.starting_group = q.starting_group

Esto usa un CTE, pero solo por pulcritud. Puede deshacerse de él si lo desea. Consideraría usar un CTE recursivo ya que es SQL estándar y hace que su consulta sea más portátil.

Answer 2

Traté de resolver el problema con el "conectar por". Pero no encuentro una manera de pasar el resultado del cálculo al siguiente nivel de recursión.

Con R / CTE encontró problemas similares. Y además hubo fuertes limitaciones debido a la imposibilidad de usar las funciones de "grupo" y "ventana" en R / CTE. Intenta usar el "alias CTE" dos veces en la parte recursiva (para unirse a la fila anterior) conduce a ORA-06000 (error interno del servidor) y desconecta la conexión a Oracle.

Hoy, se me ocurrió proporcionar un bucle recurrente "R / CTE" con ayuda de una sola fila (en adelante, "fila de cursor"), y mantener un estado (lista de grupos OK) en esta "fila de cursor". Filas de datos necesarios, numeradas de antemano en el orden "de las hojas a la raíz", unidas en cada iteración a "fila de cursor" por número de fila.

with GRP as (
     select A.*, row_number() over (order by L) N
       from (
         select mygroup_id, min(level) L
           from  mygroups m
          start with not exists(select 1 from mygroups m2
                                 where m2.mygroup_id=m.mygroup_id and m2.member like ''g%''
                               )
        connect by member=prior mygroup_id
          group by mygroup_id
       ) A
),
Cursor_tab(mygroup_id,N,list,result) as(
   select NULL,N,lpad('','',2000),0
     from GRP where N=1
 union all
   select G.mygroup_id,Q.N+1,
          ltrim(Q.list)||decode(R.column_value,0,'''',G.mygroup_id||'',''),
          TO_NUMBER(R.column_value)
     from Cursor_tab Q, GRP G,
          table(cast(multiset(
           select decode(sign(sum(member_value)-min(mygroup_limit)),-1,0,1)
             from mygroups m
            where m.mygroup_id=G.mygroup_id
              and (member not like ''g%'' or Q.list like ''%,''||member||'',%'')
          ) as sys.odcivarchar2list)) R
    where G.N=Q.N
)
select mygroup_id, decode(result,1,''OK'',''NOT OK'')
  from Cursor_tab
 where mygroup_id is not null