none
FUNCION ESCALAR QUE TOME COMO INPUT UNA TABLA ADEMAS DE PARAMETROS ESCALARES RRS feed

  • Pregunta

  • Hola,

    Trabajo con SQL Server 2014.

    Me gustaria consultarle si es posible crear algún tipo de función escalar a la cual se la pueda remitir una tabla obtenida mediante un select.

    Por ejemplo tengo en mi base de datos la siguiente tabla:

    DECLARE @Ventas AS TABLE
    (
       IdArticulo Int,
       FechaAlbaran  Date,
       NumeroAlbaran Int,
       ImporteVenta decimal(15,5)
       
    )
    
    ;
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'07/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'09/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'12/06/2016',6,22.5),
     (9,'13/06/2016',6,11.4),
     (9,'14/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'18/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'20/07/2016',9,14.6),
     (11,'19/07/2016',10,27.8),
     (11,'20/07/2016',10,9.5),
     (11,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (11,'23/07/2016',12,19.2);

    Pues bien, necesitaría enviar a esa función una tabla tal que:

     select * from @ventas where idarticulo=9 

    Mi objetivo es poder determinar el dia las ventas de un determinado articulo alcanzara un determinado nivel de unidades vendidas.

    Por tanto, tendrias varios inputs para enviar a la funcion:

    1- Idarticulo

    2- Nivel de unidades vendidas a alcanzar.

    3- Tabla con el select que contiene las ventas.

    ¿existe alguna manera de darle forma?

    Muchas gracias

    Un saludo

    Angel

    lunes, 24 de octubre de 2016 16:45

Respuestas

  • Si la tabla de ventas existe en la db, entonces no haria falta enviar esa tabla como parametro a la funcion. Tampoco es necesario usar una funcion escalar (usar inline table-valued function), ya que estas tienden a causar pobre desempenio cuando la usamos en un query.

    Dicho lo anterior, cuando calculamos un valor corriente (running total) no podemos parar el calculo al alcanzar determinado valor intermedio sino que habra que esperar que se calcule sobre todo el conjunto para luego escoger en cual fila ese valor se alcanzo o supero.

    Aca un ejemplo de lo que pides, pero reitero que no es la forma optima de solucionarlo.

    SET NOCOUNT ON;
    GO
    USE tempdb
    GO
    CREATE TYPE dbo.ventas AS TABLE (
       IdArticulo Int,
       FechaAlbaran  Date,
       NumeroAlbaran Int,
       ImporteVenta decimal(15,5)
    );
    GO
    CREATE FUNCTION dbo.ufn_whatever (
    @ventas ventas READONLY,
    @IdArticulo Int,
    @UnidadesVendidas decimal(15,5)
    )
    RETURNS date
    AS
    BEGIN
    DECLARE @dt date;
    
    WITH R AS (
    SELECT
       IdArticulo,
       FechaAlbaran,
       NumeroAlbaran,
       ImporteVenta,
       SUM(ImporteVenta) OVER(
       PARTITION BY IdArticulo 
       ORDER BY FechaAlbaran
       ROWS UNBOUNDED PRECEDING
       ) AS rt_ventas
    FROM
    	@ventas
    WHERE
    	IdArticulo = @IdArticulo
    )
    SELECT TOP (1)
    	@dt = FechaAlbaran
    FROM
    	R
    WHERE
    	rt_ventas >= @UnidadesVendidas
    ORDER BY
    	FechaAlbaran;
    
    RETURN @dt;
    END
    GO
    SET DATEFORMAT DMY;
    
    DECLARE @Ventas dbo.ventas;
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'07/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'09/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'12/06/2016',6,22.5),
     (9,'13/06/2016',6,11.4),
     (9,'14/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'18/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'20/07/2016',9,14.6),
     (11,'19/07/2016',10,27.8),
     (11,'20/07/2016',10,9.5),
     (11,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (11,'23/07/2016',12,19.2);
    
    SET DATEFORMAT MDY;
    
    SELECT dbo.ufn_whatever(@Ventas, 9, 25) AS col1;
    GO
    DROP FUNCTION dbo.ufn_whatever
    GO
    DROP TYPE dbo.ventas
    GO

    Aca un ejemplo usando una funcion tipo tabla en linea, con diferentes usos.

    SET NOCOUNT ON;
    GO
    USE tempdb
    GO
    SET DATEFORMAT DMY;
    
    CREATE TABLE dbo.ventas (
    IdArticulo Int,
    FechaAlbaran  Date,
    NumeroAlbaran Int,
    ImporteVenta decimal(15,5)
    );
    
    INSERT INTO dbo.ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'07/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'09/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'12/06/2016',6,22.5),
     (9,'13/06/2016',6,11.4),
     (9,'14/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'18/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'20/07/2016',9,14.6),
     (11,'19/07/2016',10,27.8),
     (11,'20/07/2016',10,9.5),
     (11,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (11,'23/07/2016',12,19.2);
    
    SET DATEFORMAT MDY;
    GO
    CREATE FUNCTION dbo.ufn_whatever (
    @IdArticulo Int,
    @UnidadesVendidas decimal(15,5)
    )
    RETURNS table 
    AS
    RETURN (
    WITH R AS (
    SELECT
       FechaAlbaran,
       SUM(ImporteVenta) OVER(
       PARTITION BY IdArticulo 
       ORDER BY FechaAlbaran
       ROWS UNBOUNDED PRECEDING
       ) AS rt_ventas
    FROM
    	dbo.ventas
    WHERE
    	IdArticulo = @IdArticulo
    )
    SELECT TOP (1)
    	FechaAlbaran
    FROM
    	R
    WHERE
    	rt_ventas >= @UnidadesVendidas
    ORDER BY
    	FechaAlbaran
    )
    GO
    DECLARE @nivel_ventas decimal(15, 5) = 25;
    
    SELECT 
    	T.FechaAlbaran
    FROM
    	dbo.ufn_whatever(9, @nivel_ventas) AS T;
    
    WITH R AS (
    SELECT
    	V.IdArticulo,
    	V.FechaAlbaran,
    	V.NumeroAlbaran,
    	V.ImporteVenta,
    	SUM(V.ImporteVenta) OVER(
    	PARTITION BY V.IdArticulo 
    	ORDER BY V.FechaAlbaran
    	ROWS UNBOUNDED PRECEDING
    	) AS rt_ventas
    FROM
    	dbo.ventas AS V
    )
    SELECT
    	R.IdArticulo,
        R.FechaAlbaran,
        R.NumeroAlbaran,
        R.ImporteVenta,
        R.rt_ventas,
    	@nivel_ventas AS nivel_ventas,
    	MIN(CASE WHEN R.rt_ventas >= @nivel_ventas THEN R.FechaAlbaran END) OVER(
    	PARTITION BY R.IdArticulo
    	ORDER BY R.FechaAlbaran
    	ROWS UNBOUNDED PRECEDING
    	) AS fecha_alcanza_nivel_ventas
    FROM
    	R
    ORDER BY
    	IdArticulo,
    	FechaAlbaran;
    GO
    DROP FUNCTION dbo.ufn_whatever
    GO
    DROP TABLE dbo.ventas
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    • Editado HunchbackMVP lunes, 24 de octubre de 2016 18:13
    • Marcado como respuesta Angeleci martes, 25 de octubre de 2016 14:58
    lunes, 24 de octubre de 2016 18:09
  • Tendrias que volcar la salida de CTE1 hacia una tabla (permanente/temporal/variable tipo tabla) pero no puedes pasar la CTE ya que esta es una definicion y no esta materializada.

    Ademas, si referencias la misma CTE varias veces en tu query es como volverle a ejecutar.

    Ejemplo:

    with R as (
    ...
    )
    select *
    from R as T1 inner join R as T2 on ...

    Como referencias la cte R dos veces, entonces su contenido se ejecutara multiples veces, si a esto le adicionas una funcion como ROW_NUMBER entonces esta tambien se ejecutara dos veces.

    Te comento esto porque aunque las CTEs ayudan con la lectura y claridad del query, su uso no correcto puede dar como resultado desempenio no deseado.

    Yo te aconsejo que prepares algo pequenio, como hicistes en tu post original, que trate de simular lo que tienes y deseas hacer para entonces nosotros tener algo con que trabajar.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marcado como respuesta Angeleci martes, 25 de octubre de 2016 14:57
    lunes, 24 de octubre de 2016 19:45

Todas las respuestas

  • Si la tabla de ventas existe en la db, entonces no haria falta enviar esa tabla como parametro a la funcion. Tampoco es necesario usar una funcion escalar (usar inline table-valued function), ya que estas tienden a causar pobre desempenio cuando la usamos en un query.

    Dicho lo anterior, cuando calculamos un valor corriente (running total) no podemos parar el calculo al alcanzar determinado valor intermedio sino que habra que esperar que se calcule sobre todo el conjunto para luego escoger en cual fila ese valor se alcanzo o supero.

    Aca un ejemplo de lo que pides, pero reitero que no es la forma optima de solucionarlo.

    SET NOCOUNT ON;
    GO
    USE tempdb
    GO
    CREATE TYPE dbo.ventas AS TABLE (
       IdArticulo Int,
       FechaAlbaran  Date,
       NumeroAlbaran Int,
       ImporteVenta decimal(15,5)
    );
    GO
    CREATE FUNCTION dbo.ufn_whatever (
    @ventas ventas READONLY,
    @IdArticulo Int,
    @UnidadesVendidas decimal(15,5)
    )
    RETURNS date
    AS
    BEGIN
    DECLARE @dt date;
    
    WITH R AS (
    SELECT
       IdArticulo,
       FechaAlbaran,
       NumeroAlbaran,
       ImporteVenta,
       SUM(ImporteVenta) OVER(
       PARTITION BY IdArticulo 
       ORDER BY FechaAlbaran
       ROWS UNBOUNDED PRECEDING
       ) AS rt_ventas
    FROM
    	@ventas
    WHERE
    	IdArticulo = @IdArticulo
    )
    SELECT TOP (1)
    	@dt = FechaAlbaran
    FROM
    	R
    WHERE
    	rt_ventas >= @UnidadesVendidas
    ORDER BY
    	FechaAlbaran;
    
    RETURN @dt;
    END
    GO
    SET DATEFORMAT DMY;
    
    DECLARE @Ventas dbo.ventas;
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'07/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'09/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'12/06/2016',6,22.5),
     (9,'13/06/2016',6,11.4),
     (9,'14/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'18/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'20/07/2016',9,14.6),
     (11,'19/07/2016',10,27.8),
     (11,'20/07/2016',10,9.5),
     (11,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (11,'23/07/2016',12,19.2);
    
    SET DATEFORMAT MDY;
    
    SELECT dbo.ufn_whatever(@Ventas, 9, 25) AS col1;
    GO
    DROP FUNCTION dbo.ufn_whatever
    GO
    DROP TYPE dbo.ventas
    GO

    Aca un ejemplo usando una funcion tipo tabla en linea, con diferentes usos.

    SET NOCOUNT ON;
    GO
    USE tempdb
    GO
    SET DATEFORMAT DMY;
    
    CREATE TABLE dbo.ventas (
    IdArticulo Int,
    FechaAlbaran  Date,
    NumeroAlbaran Int,
    ImporteVenta decimal(15,5)
    );
    
    INSERT INTO dbo.ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'07/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'09/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'12/06/2016',6,22.5),
     (9,'13/06/2016',6,11.4),
     (9,'14/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'18/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'20/07/2016',9,14.6),
     (11,'19/07/2016',10,27.8),
     (11,'20/07/2016',10,9.5),
     (11,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (11,'23/07/2016',12,19.2);
    
    SET DATEFORMAT MDY;
    GO
    CREATE FUNCTION dbo.ufn_whatever (
    @IdArticulo Int,
    @UnidadesVendidas decimal(15,5)
    )
    RETURNS table 
    AS
    RETURN (
    WITH R AS (
    SELECT
       FechaAlbaran,
       SUM(ImporteVenta) OVER(
       PARTITION BY IdArticulo 
       ORDER BY FechaAlbaran
       ROWS UNBOUNDED PRECEDING
       ) AS rt_ventas
    FROM
    	dbo.ventas
    WHERE
    	IdArticulo = @IdArticulo
    )
    SELECT TOP (1)
    	FechaAlbaran
    FROM
    	R
    WHERE
    	rt_ventas >= @UnidadesVendidas
    ORDER BY
    	FechaAlbaran
    )
    GO
    DECLARE @nivel_ventas decimal(15, 5) = 25;
    
    SELECT 
    	T.FechaAlbaran
    FROM
    	dbo.ufn_whatever(9, @nivel_ventas) AS T;
    
    WITH R AS (
    SELECT
    	V.IdArticulo,
    	V.FechaAlbaran,
    	V.NumeroAlbaran,
    	V.ImporteVenta,
    	SUM(V.ImporteVenta) OVER(
    	PARTITION BY V.IdArticulo 
    	ORDER BY V.FechaAlbaran
    	ROWS UNBOUNDED PRECEDING
    	) AS rt_ventas
    FROM
    	dbo.ventas AS V
    )
    SELECT
    	R.IdArticulo,
        R.FechaAlbaran,
        R.NumeroAlbaran,
        R.ImporteVenta,
        R.rt_ventas,
    	@nivel_ventas AS nivel_ventas,
    	MIN(CASE WHEN R.rt_ventas >= @nivel_ventas THEN R.FechaAlbaran END) OVER(
    	PARTITION BY R.IdArticulo
    	ORDER BY R.FechaAlbaran
    	ROWS UNBOUNDED PRECEDING
    	) AS fecha_alcanza_nivel_ventas
    FROM
    	R
    ORDER BY
    	IdArticulo,
    	FechaAlbaran;
    GO
    DROP FUNCTION dbo.ufn_whatever
    GO
    DROP TABLE dbo.ventas
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    • Editado HunchbackMVP lunes, 24 de octubre de 2016 18:13
    • Marcado como respuesta Angeleci martes, 25 de octubre de 2016 14:58
    lunes, 24 de octubre de 2016 18:09
  • Hola Hunchback.

    Muchas gracias por tu respuesta.

    No obstante, la tabla de origen no siempre existirá en mi base de datos sino que es posible que provenga de una cte que a su vez provenga de otra cte.

    Es por ello que mi pregunta venia con el objetivo de poder crear estructuras como la siguiente:

    With Cte1 as(
    select 
    Campo1 as valor1,
    Campo2 as valor2,
    Campo3 as valor3,
    Funcion(campo4) as valor4
    from Data),
    
    Cte2 as(
    
    select Función(Select * from valor1, valor4 from Cte1 where valor2>10 )
    
    Select * from Cte2
    
    

    Es por ello que he planteado esta consulta, para este caso en particular...

    ¿crees que habria alguna forma de llevarlo a cabo?

    Gracias

    Un saludo

    Angel

    lunes, 24 de octubre de 2016 18:30
  • Revisando lo que pones diria que no, ya que ese codigo no es valido.

    Quizas si pones un ejemplo practico y dices lo que tratas de resolver entonces podriamos buscar una solucion.

    Chequeastes el codigo que puse?


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 24 de octubre de 2016 18:37
  • Hola Hunchback,

    Te pongo un ejemplo mucho mas cercano a la realidad del problema.

    Partimos de la misma tabla @Ventas especificada al principio.

    Realmente, necesito hacer una serie de cálculo y procesos mediante el uso de de CTE partiendo de esta tabla.

    Para resumir en el ejemplo voy a utilizar una sola tabla CTE aunque como digo habrá varias.

    Pues bien, en algunas de estas tablas tipo CTE necesito aplicar una compleja función estadística/matemática a algunos campos.

    La estructura seria asi:

     With Cte1 as(
     Select ROW_NUMBER() OVER (PARTITION BY IdArticulo ORDER BY FechaAlbaran ASC) as Orden
     ,FechaAlbaran as Fecha
     , NumeroAlbaran as Numero
     ,ImporteVenta as Venta
     ,Avg(ImporteVenta) OVER (PARTITION BY IdArticulo ORDER BY FechaAlbaran ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) as Media
     from @Ventas ),
    
    Cte2 as(
    Select *, FUNCIONCONLAFORMULAMATEMATICA (parametro1,parametro2,parametro3, @TABLA) )
    
    Select * from Cte2

    Decir, que ta tabla @Tabla que indico en mi script provendría de un select tipo: "Select Numero, Venta from Cte1"

    Es decir, que realmente lo que intento con mi consulta es poder utilizar dentro de mis consultas con CTES formulas matemáticas cuya complejidad me impediría darle forma de otra manera distinta a las funciones.

    Gracias

    Angel

    • Marcado como respuesta Angeleci martes, 25 de octubre de 2016 14:58
    • Desmarcado como respuesta Angeleci martes, 25 de octubre de 2016 14:58
    lunes, 24 de octubre de 2016 19:19
  • Tendrias que volcar la salida de CTE1 hacia una tabla (permanente/temporal/variable tipo tabla) pero no puedes pasar la CTE ya que esta es una definicion y no esta materializada.

    Ademas, si referencias la misma CTE varias veces en tu query es como volverle a ejecutar.

    Ejemplo:

    with R as (
    ...
    )
    select *
    from R as T1 inner join R as T2 on ...

    Como referencias la cte R dos veces, entonces su contenido se ejecutara multiples veces, si a esto le adicionas una funcion como ROW_NUMBER entonces esta tambien se ejecutara dos veces.

    Te comento esto porque aunque las CTEs ayudan con la lectura y claridad del query, su uso no correcto puede dar como resultado desempenio no deseado.

    Yo te aconsejo que prepares algo pequenio, como hicistes en tu post original, que trate de simular lo que tienes y deseas hacer para entonces nosotros tener algo con que trabajar.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marcado como respuesta Angeleci martes, 25 de octubre de 2016 14:57
    lunes, 24 de octubre de 2016 19:45
  • Gracias Hunchback,

    Desconocía ese aspecto de las Ctes. Voy entonces a probar con usar una tabla temporal a ver si consigo acercarme a la solución con un rendimiento razonable.

    Un saludo

    Angel

    martes, 25 de octubre de 2016 14:57