none
Promedio y sumatoria SQL SERVER RRS feed

  • Pregunta

  • Buenos dias, como pueden darse cuenta soy novato en esto de el sql, estoy tratando de realizar lo siguiente tengo mi siguiente query 

     declare 
    	
    	@Emp_CGCA01ID NVARCHAR(50),
    	@Emp_CGCA02ID NVARCHAR(50),
    	@Emp_CGCA03ID NVARCHAR(50),
    	@AnioBono INT,
    	@FechaInicRevisionIndicador DATE,
    	@FechaInicRevisionIndicador1 DATE,
    	@CNUSERID NVARCHAR(50),
    	@Gpo INT
     
    
        set @Emp_CGCA01ID = '20'
    	SET @Emp_CGCA02ID = 'TR'
    	SET @Emp_CGCA03ID = '88'
    	SET @AnioBono = 2019 
    	SET @FechaInicRevisionIndicador = '01/07/2019'
    	SET @FechaInicRevisionIndicador1 = '31/07/2019'
    	SET @CNUSERID='DVILLATRN'
    	
       SELECT	*  FROM	dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID)
                                    
    								
    						

    el cual me arroja lo siguientes datos : 

    lo que necesito hacer es sacar mi promedio primero por Nomi_GpoNomina, despues de sacar los promedios por grupo de nomina entonces me promedie los resultados de los promedios de los grupos. 

    no se si me explique: digamos que en el grupo 3242 mi promedio es 6 se como sacar el promedio por grupo de esta manera 

    SET @Gpo = 3242 SELECT AVG(Administrados) AS Administrados FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador,

    @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID)

    WHERE (Nomi_GpoNomina = @Gpo)

    de esta manera me promedia por grupo 3242 , pero si quiero que me realize el promedio de cada uno de los grupos y después me promedio los resultados de los promedios de los grupos como le puedo hacer? espero a verme explicado y agradezco su ayuda y su atención.


    jueves, 29 de agosto de 2019 14:17

Respuestas

  • Hola Luis vazquez2019:

    Es complicado responderte porque no sabemos como se realiza la función dbo.GetAdministradosEjecutivo

    pero creo que te será tan simple como utilizar las funciones de ventana.

    SET @Gpo = 3242;
    SELECT DERIV.*, AVG(DERIV.GRUPO) OVER(ORDER BY MB_CIASID, NOMI_GPONOMINA, DERIV.ADMINISTRADOS, DERIV.GRUPO) AS MEDIA
        FROM (
    	   SELECT  V.*,
    			 AVG(Administrados) OVER(ORDER BY MB_CIASID,NOMI_GPONOMINA) AS Administrados,
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   
    	   FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) V
    		  WHERE(Nomi_GpoNomina = @Gpo)
    	   ) AS DERIV

    Al no saber como esta internamente construida la función, no se si te resultará. 

    Ya comentas

    jueves, 29 de agosto de 2019 15:36
  • Deleted
    jueves, 29 de agosto de 2019 19:19

Todas las respuestas

  • Hola Luis vazquez2019:

    Es complicado responderte porque no sabemos como se realiza la función dbo.GetAdministradosEjecutivo

    pero creo que te será tan simple como utilizar las funciones de ventana.

    SET @Gpo = 3242;
    SELECT DERIV.*, AVG(DERIV.GRUPO) OVER(ORDER BY MB_CIASID, NOMI_GPONOMINA, DERIV.ADMINISTRADOS, DERIV.GRUPO) AS MEDIA
        FROM (
    	   SELECT  V.*,
    			 AVG(Administrados) OVER(ORDER BY MB_CIASID,NOMI_GPONOMINA) AS Administrados,
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   
    	   FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) V
    		  WHERE(Nomi_GpoNomina = @Gpo)
    	   ) AS DERIV

    Al no saber como esta internamente construida la función, no se si te resultará. 

    Ya comentas

    jueves, 29 de agosto de 2019 15:36
  • Hola Luis vazquez2019:

    Es complicado responderte porque no sabemos como se realiza la función dbo.GetAdministradosEjecutivo

    pero creo que te será tan simple como utilizar las funciones de ventana.

    SET @ Gpo = 3242 ; SELECCIONAR DERIV . * AVG ( LEAD . GRUPO ) OVER ( ORDEN POR MB_CIASID , NOMI_GPONOMINA , la deriva . MANAGED , Drift . GRUPO ) AS MEDIA
     DE ( SELECT V . *, AVG ( MANEJÓ ) OVER ( ORDEN POR MB_CIASID ,NOMI_GPONOMINA )  
        
    	     AS gestionado,
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   
    	   DE dbo . GetAdministradosEjecutivo (@ FechaInicRevisionIndicador , @ FechaInicRevisionIndicador1 , @ Emp_CGCA02ID , @ CNUSERID ) V
    		 DONDE ( Nomi_GpoNomina = @ Gpo ) ) COMO DERIVO    
    	    

    Al no saber como esta internamente construida la función, no se si te resultará. 

    Ya comentas

    muchas gracias por tu respuesta , funcion este codigo tiene sinceramente no se que funcion tenga

    ALTER FUNCTION [dbo].[GetAdministradosEjecutivo] ( @fecha DATE, @fecha1 DATE, @Sucursal NVARCHAR(50), @CNUSERID NVARCHAR(50) ) RETURNS TABLE AS RETURN ( SELECT CALNOM2.MB_CIASID, CALNOM2.Nomi_tipo, CALNOM2.Nomi_GpoNomina, CALNOM2.Calno_ano, CALNOM2.Calno_periodo, CNUSER.CNUSERID, CNUSER.CNCDIRID, NMGPNM.NMGPNMCDAJ02ID, COUNT(DISTINCT(HIST_NOM.Emp_nie)) AS Administrados FROM ERP_GEN_HSF_PROD.dbo.CALNOM2 AS CALNOM2 WITH (NOLOCK) INNER JOIN ERP_GEN_HSF_PROD.dbo.NMGPNM AS NMGPNM WITH (NOLOCK) INNER JOIN ERP_GEN_HSF_PROD.dbo.CNUSER AS CNUSER WITH (NOLOCK) ON NMGPNM.CNCDIRID = CNUSER.CNCDIRID ON CALNOM2.Nomi_GpoNomina = NMGPNM.NMGPNMID INNER JOIN ERP_GEN_HSF_PROD.dbo.CNCIAS AS CNCIAS WITH (NOLOCK) ON CALNOM2.MB_CIASID = CNCIAS.CNCIASID INNER JOIN dbo.HIST_NOM AS HIST_NOM WITH(NOLOCK) ON CALNOM2.MB_CIASID = HIST_NOM.MB_CIASID AND CALNOM2.Nomi_tipo = HIST_NOM.Nomi_tipo AND CALNOM2.Nomi_GpoNomina = HIST_NOM.Nomi_GpoNomina AND CALNOM2.Calno_ano = HIST_NOM.Calno_ano AND CALNOM2.Calno_periodo = HIST_NOM.Calno_periodo WHERE (HIST_NOM.Hpl_monto > 0) AND (CALNOM2.Calno_fepag >= @fecha) AND (CALNOM2.Calno_fepag <= @fecha1) AND (CALNOM2.Nomi_tipo = '02' OR CALNOM2.Nomi_tipo = '01' OR CALNOM2.Nomi_tipo = '07' OR CALNOM2.Nomi_tipo = '14' OR CALNOM2.Nomi_tipo = '15' OR CALNOM2.Nomi_tipo = '30') AND (NMGPNM.NMGPNMID <> 0) AND (NMGPNM.NMGPNMSTGP = 'A') AND (NMGPNM.NMGPNMCDAJ02ID = @Sucursal) AND (CNUSER.CNUSERID = @CNUSERID) AND --(CNCIAS.CNCI07ID = 'INT') AND (CALNOM2.MB_CIASID IN (SELECT 55 AS CNCIASID UNION ALL SELECT 60 AS CNCIASID UNION ALL SELECT CNCIASID FROM dbo.MOE_MATRIZ_INDICADORES_COMPANIAS WHERE (IDIndicador = 66) AND (FechaBajaCompania >= @fecha1 OR FechaBajaCompania IS NULL))) GROUP BY CALNOM2.MB_CIASID, CALNOM2.Nomi_tipo, CALNOM2.Nomi_GpoNomina, CALNOM2.Calno_ano, CALNOM2.Calno_periodo, CNUSER.CNUSERID, CNUSER.CNCDIRID, NMGPNM.NMGPNMCDAJ02ID )



    jueves, 29 de agosto de 2019 15:43
  • agradezco te tomes el tiempo de responder amigo gracias.
    jueves, 29 de agosto de 2019 15:44
  • Hola Luis vazquez2019:

    Intenta probar el código recomendado, haber si no te presenta ningún error, y comentas

    jueves, 29 de agosto de 2019 15:46
  • tengo varias dudas amigo el DERIV para que se usa y el V, me marca eso al final en el as DERIV 

    jueves, 29 de agosto de 2019 15:53
  • Hola Luis vazquez2019:

    Intenta probar el código recomendado, haber si no te presenta ningún error, y comentas

    no comprendo exactamente que hace cuando usas DERIV, y V. gracias por tu atencion Javi.

    jueves, 29 de agosto de 2019 16:05
  • en cuanto al over estuve investigando mas sin embargo no comprendo su función , agradecería muchísimo tu respuesta.
    jueves, 29 de agosto de 2019 17:40
  • Por partes.

    V es la salida de tu función. Esta te devuelve tantas columnas como muestras en el código de la función.

    v.MB_CIASID,
    v.Nomi_tipo,
    v.Nomi_GpoNomina,
    v.Calno_ano,
    v.Calno_periodo,
    v.CNUSERID,
    v.CNCDIRID,
    v.NMGPNMCDAJ02ID,
    v.Administrados

    Deriv es la salida del conjunto que envuelve a tu función. O lo que es lo mismo una tabla derivada.

    Tablas derivadas

    https://javifer2.blogspot.com/search/label/Tabla%20derivadas

    Sobre lo que devuelve la tabla derivada, aplico la media de los grupos.

    Y over, aunque es un poco más complejo, voy a intentar explicarlo de un modo muy sencillo.

    Group by 

    https://javifer2.blogspot.com/search/label/group%20by

    Al contrario que group by, over te permite hacer la función de agregación, pero no para todas las columnas del conjunto, sino solo para la mencionada en la función, y/o en partition. Por tanto la media se reinicia cuando la columna mencionada en partition se reinicia.

    SET @Gpo = 3242;
    SELECT 
    	deriv.MB_CIASID, 
    	deriv.Nomi_tipo, 
    	deriv.Nomi_GpoNomina, 
    	deriv.Calno_ano, 
    	deriv.Calno_periodo, 
    	deriv.CNUSERID, 
    	deriv.CNCDIRID, 
    	deriv.NMGPNMCDAJ02ID,
    	deriv.Administrados,
    	deriv.GRUPO,
         AVG(DERIV.GRUPO) OVER(ORDER BY MB_CIASID, NOMI_GPONOMINA, DERIV.ADMINISTRADOS, DERIV.GRUPO) AS MEDIA
        FROM (
    	   SELECT 
    			 v.MB_CIASID, 
    			 v.Nomi_tipo, 
    			 v.Nomi_GpoNomina, 
    			 v.Calno_ano, 
    			 v.Calno_periodo, 
    			 v.CNUSERID, 
    			 v.CNCDIRID, 
    			 v.NMGPNMCDAJ02ID,
    			 v.Administrados
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) As V
    		  WHERE Nomi_GpoNomina = @Gpo
    	   ) AS DERIV
    Prueba este código, haber si se acerca más a lo solicitado.

    jueves, 29 de agosto de 2019 19:13
  • Deleted
    jueves, 29 de agosto de 2019 19:19
  • estimado javi fernandez  muchas gracias por tu explicación y por tomarte el tiempo.

    checando la parte que me mandaste lo deje de esta manera para que cierto modo entendiera que estoy haciendo a mi nivel

    SET @Gpo = 3242

    SELECT avg(Administrados) as Administrados FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador,@FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) where ( Nomi_GpoNomina = @Gpo ) SELECT AVG(DERIV.GRUPO) OVER(ORDER BY DERIV.GRUPO) AS MEDIA FROM (SELECT AVG(Administrados) OVER(PARTITION BY NOMI_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) As V WHERE Nomi_GpoNomina = @Gpo ) AS DERIV

    entonces me muestra lo siguiente

    quedo así y si es realmente lo que necesito, pero aquí la cuestión es que tal vez olvide que solo puse un gpo como ejemplo que fue el 3242, sin embargo busco hacer que me tome por grupos todos los que tengo como información me promedio primero por grupo los administrados para que después los resultados administrados por grupo , me aga un promedio general de los promedios administrados que resultaron de cada grupo. digamos que en el grupo 3242 tengo mi administrado 6 que es el promedio por grupo, y mi gpo 3243, tiene un promedio por grupo de 1, entonces tenemos 6 y 1 como promedio por grupo, estos dos resultados me saque los promedios entre los dos , pero asi sea en cada uno de mis grupos que existan. 

    me encuentro un tanto desorientado por conocer muy poco de el tema, es por ello que busco ayuda en foros y contigo javi , siempre insistiendo que es importante agradecer que te tomes la molestia en contestar aun estando muy novato, gracias.

    jueves, 29 de agosto de 2019 19:42
  • Por partes.

    V es la salida de tu función. Esta te devuelve tantas columnas como muestras en el código de la función.

    v.MB_CIASID,
    v.Nomi_tipo,
    v.Nomi_GpoNomina,
    v.Calno_ano,
    v.Calno_periodo,
    v.CNUSERID,
    v.CNCDIRID,
    v.NMGPNMCDAJ02ID,
    v.Administrados

    Deriv es la salida del conjunto que envuelve a tu función. O lo que es lo mismo una tabla derivada.

    Tablas derivadas

    https://javifer2.blogspot.com/search/label/Tabla%20derivadas

    Sobre lo que devuelve la tabla derivada, aplico la media de los grupos.

    Y over, aunque es un poco más complejo, voy a intentar explicarlo de un modo muy sencillo.

    Group by 

    https://javifer2.blogspot.com/search/label/group%20by

    Al contrario que group by, over te permite hacer la función de agregación, pero no para todas las columnas del conjunto, sino solo para la mencionada en la función, y/o en partition. Por tanto la media se reinicia cuando la columna mencionada en partition se reinicia.

    SET @Gpo = 3242;
    SELECT 
    	deriv.MB_CIASID, 
    	deriv.Nomi_tipo, 
    	deriv.Nomi_GpoNomina, 
    	deriv.Calno_ano, 
    	deriv.Calno_periodo, 
    	deriv.CNUSERID, 
    	deriv.CNCDIRID, 
    	deriv.NMGPNMCDAJ02ID,
    	deriv.Administrados,
    	deriv.GRUPO,
         AVG(DERIV.GRUPO) OVER(ORDER BY MB_CIASID, NOMI_GPONOMINA, DERIV.ADMINISTRADOS, DERIV.GRUPO) AS MEDIA
        FROM (
    	   SELECT 
    			 v.MB_CIASID, 
    			 v.Nomi_tipo, 
    			 v.Nomi_GpoNomina, 
    			 v.Calno_ano, 
    			 v.Calno_periodo, 
    			 v.CNUSERID, 
    			 v.CNCDIRID, 
    			 v.NMGPNMCDAJ02ID,
    			 v.Administrados
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) As V
    		  WHERE Nomi_GpoNomina = @Gpo
    	   ) AS DERIV
    Prueba este código, haber si se acerca más a lo solicitado.

    espero tener alguna forma de poder agradecer tu apoyo en esto.
    jueves, 29 de agosto de 2019 19:43
  • De nada.

    Una de las posibles soluciones es OVER.

    Puedes usar la función AVG (y dentro una columna) OVER (Partition by (caso de ser necesario, quien reiniciará la cuenta) ORDER BY (en que orden se tomarán las filas de esta partición)) as columnaMedia.

    Para todos los GPOS, quita el Where, y ya nos cuentas.

    jueves, 29 de agosto de 2019 20:24
  • De nada.

    Una de las posibles soluciones es OVER.

    Puedes usar la función AVG (y dentro una columna) OVER (Partition by (caso de ser necesario, quien reiniciará la cuenta) ORDER BY (en que orden se tomarán las filas de esta partición)) as columnaMedia.

    Para todos los GPOS, quita el Where, y ya nos cuentas.

    gracias javi.
    jueves, 29 de agosto de 2019 21:41
  • no me habia percatado de tu respuesta estimado Jose Diz enseguida lo reviso y te cuento gracias.
    jueves, 29 de agosto de 2019 21:43
  • no habia notado que me muestra el resultado , al final. agradezco tu atencion amigo y es justo lo que buscaba GRACIAS INFINITAMENTE POR su apoyo tanto de usted jose, como de javi por su tiempo saludos, no se si tenga que hacer algo mas aqui en el post ya marque ambas como respuesta puesto que los dos me sirven, gracias.

    jueves, 29 de agosto de 2019 22:01
  • lo que necesito hacer es sacar mi promedio primero por Nomi_GpoNomina, despues de sacar los promedios por grupo de nomina entonces me promedie los resultados de los promedios de los grupos.

    Luis, ¿has probado la sugerencia de Javier?

    He aquí otra sugerencia:

    -- código #2 v2
    --
    IF Object_ID ('tempDB..#promedio_1', 'U') is not null
      DROP TABLE #promedio_1;

    --
    declare @Emp_CGCA01ID NVARCHAR(50), @FechaInicRevisionIndicador DATE, @FechaInicRevisionIndicador1 DATE, @CNUSERID NVARCHAR(50); set @Emp_CGCA01ID= '20'; SET @FechaInicRevisionIndicador= convert (date, '1/7/2019', 103); SET @FechaInicRevisionIndicador1= convert (date, '31/7/2019', 103); SET @CNUSERID='DVILLATRN';

    SELECT Nomi_GpoNomina,
           avg (1.0 * Administrados) as promedio
      into #promedio_1
      from dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) as P
      group by Nomi_GpoNomina;

    with promedio_2 as (
    SELECT 1 as seq, Nomi_GpoNomina, promedio
      from #promedio_1
    union all
    SELECT 2, cast('promedio general' as sql_variant), avg (promedio)
      from #promedio_1
    )
    SELECT Nomi_GpoNomina, promedio
      from promedio_2
      order by seq, Nomi_GpoNomina;

    No lo he probado; puede contener error(es).

     

    Si esta respuesta te ayudó a resolver tu problema, recuerda marcarla.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    muchas gracias por tu atencion.
    jueves, 29 de agosto de 2019 22:07
  • Por partes.

    V es la salida de tu función. Esta te devuelve tantas columnas como muestras en el código de la función.

    v.MB_CIASID,
    v.Nomi_tipo,
    v.Nomi_GpoNomina,
    v.Calno_ano,
    v.Calno_periodo,
    v.CNUSERID,
    v.CNCDIRID,
    v.NMGPNMCDAJ02ID,
    v.Administrados

    Deriv es la salida del conjunto que envuelve a tu función. O lo que es lo mismo una tabla derivada.

    Tablas derivadas

    https://javifer2.blogspot.com/search/label/Tabla%20derivadas

    Sobre lo que devuelve la tabla derivada, aplico la media de los grupos.

    Y over, aunque es un poco más complejo, voy a intentar explicarlo de un modo muy sencillo.

    Group by 

    https://javifer2.blogspot.com/search/label/group%20by

    Al contrario que group by, over te permite hacer la función de agregación, pero no para todas las columnas del conjunto, sino solo para la mencionada en la función, y/o en partition. Por tanto la media se reinicia cuando la columna mencionada en partition se reinicia.

    SET @Gpo = 3242;
    SELECT 
    	deriv.MB_CIASID, 
    	deriv.Nomi_tipo, 
    	deriv.Nomi_GpoNomina, 
    	deriv.Calno_ano, 
    	deriv.Calno_periodo, 
    	deriv.CNUSERID, 
    	deriv.CNCDIRID, 
    	deriv.NMGPNMCDAJ02ID,
    	deriv.Administrados,
    	deriv.GRUPO,
         AVG(DERIV.GRUPO) OVER(ORDER BY MB_CIASID, NOMI_GPONOMINA, DERIV.ADMINISTRADOS, DERIV.GRUPO) AS MEDIA
        FROM (
    	   SELECT 
    			 v.MB_CIASID, 
    			 v.Nomi_tipo, 
    			 v.Nomi_GpoNomina, 
    			 v.Calno_ano, 
    			 v.Calno_periodo, 
    			 v.CNUSERID, 
    			 v.CNCDIRID, 
    			 v.NMGPNMCDAJ02ID,
    			 v.Administrados
    			 AVG(Administrados) OVER(PARTITION BY NOM_GPONOMINA ORDER BY MB_CIASID,NOMI_GPONOMINA) AS GRUPO
    	   FROM dbo.GetAdministradosEjecutivo(@FechaInicRevisionIndicador, @FechaInicRevisionIndicador1, @Emp_CGCA02ID, @CNUSERID) As V
    		  WHERE Nomi_GpoNomina = @Gpo
    	   ) AS DERIV
    Prueba este código, haber si se acerca más a lo solicitado.

    muchas gracias javi por tu atencion
    jueves, 29 de agosto de 2019 22:07
  • Deleted
    viernes, 30 de agosto de 2019 13:30