COMO USAR UNA CLAUSULA OVER EN UNA CAMPO CALCULADO DE UNA TABLA FISICA

Question

Hola,

Trabajo con SQL Server 2014 y tengo el siguiente problema. Resulta que tengo una tabla fisica llamada Ventas tal que asi:

CREATE TABLE [dbo].[VENTAS](
	[IdArticulo] [int] NOT NULL,
	[Fecha] [datetime] NOT NULL,
	[Cantidad] [decimal](10, 5) NOT NULL,


 CONSTRAINT [PK_Ventas] PRIMARY KEY CLUSTERED 
(
	[IdArticulo] ASC,
	[Fecha] ASC,
	[Cantidad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO dbo.Ventas
VALUES(1,'01/01/2016',24.5),
 (1,'02/01/2016',44.5),
 (2,'02/01/2016',44.5),
 (2,'03/01/2016',23.1),
 (1,'03/01/2016',23.1),
 (1,'04/01/2016',11.5),
 (3,'05/01/2016',20.1),
 (1,'06/01/2016',53.1),
 (4,'06/01/2016',14.7),
 (4,'07/01/2016',11.5),
 (1,'08/01/2016',20.1),
 (3,'08/01/2016',53.1),
 (4,'08/05/2016',14.7),
 (2,'08/05/2016',17.5);

Pues bien, necesito insertar una nueva columna la cual sea la media aritmética de las ultimas 3 ventas de ese articulo. Para ello he intentando insertar un campo calculado:

 ALTER TABLE dbo.Ventas ADD CantidadMedia AS (AVG(Cantidad) OVER (PARTITION BY IdArticulo ORDER BY Fecha ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING))

No obstante, obtengo el siguiente erroLas funciones de división de particiones sólo pueden aparecer en las cláusulas SELECT u ORDER BY.

¿Se os ocurre alguna idea de qué puedo estar haciendo mal? Sé que una alternativa es crear una tabla cte o una tabla temporal o similar pero resulta que esta tabla tendrá millones de registros y he de hacer cientos de miles de  búsquedas repetititvas sobre ellos por lo que creo que lo mejor es hacerlo todo en una tabla fisica. ¿estais de acuerdo?

Muchas gracias

Angel

Answer 1

No puedes usar una funcion de ventana directamente en una columna calculada, restriccion CHECK, etc.

La sub-clausula OVER trabaja sobre el resultado o conjunto que pasa las fases logicas FROM/WHERE/GROUP BY/HAVING de procesamiento de un query, quedando SELECT y ORDER BY.

Dicho lo anterior, pudieras crear una vista, funcion en linea tipo tabla, etc., donde uses una senentecia SELECT y ademas al referenciar la vista o funcion puedes filtrar para las filas de un articulo en especifico.

Vale recordar que una tabla no tiene orden de filas predefinido y por lo tanto esta funcionalidad deja de tener validez en su contexto (OVER como parte de la definicion de una columna calculada que referencia filas de la tabla).

---

AMB

Some guidelines for posting questions...

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

Answer 2

Angel, no se pueden usar funciones de ranking en un campo calculado.

También he probado a crear una vista indexada sobre la tabla.

Msg 10143, Level 16, State 1, Line 45
Cannot create index on view "tempdb.dbo.ventasview" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view.

Para hacer eso físico, solo se me ocurre crear un trigger que lo rellene, aunque la solución no me gusta nada nada.

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 3

OK.

Muchas gracias a todos por vuestras respuestas.

Un saludo

Angel

Answer 4

Estoy barajando una posibilidad pero me surje una duda:

Mi tabla fisica Ventas es tal que así:

Idarticulo(Clave), Fecha(Clave), Cantidad

1    2016-01-01 00:00:00.000    24.50000
1    2016-01-02 00:00:00.000    44.50000
1    2016-01-03 00:00:00.000    23.10000
1    2016-01-04 00:00:00.000    11.50000
1    2016-01-06 00:00:00.000    53.10000
1    2016-01-08 00:00:00.000    20.10000
2    2016-01-02 00:00:00.000    44.50000
2    2016-01-03 00:00:00.000    23.10000
2    2016-05-08 00:00:00.000    17.50000
3    2016-01-05 00:00:00.000    20.10000
3    2016-01-08 00:00:00.000    53.10000
4    2016-01-06 00:00:00.000    14.70000
4    2016-01-07 00:00:00.000    11.50000
4    2016-05-08 00:00:00.000    14.70000

Pues bien puedo montar una CTE para calcular la media del campo cantidad para los ultimos 3 registros ordenados por la fecha:

 ;With Cte1 (fecha,articulo,media) as(

Select Fecha,IdArticulo, AVG(Cantidad) OVER (PARTITION BY Idarticulo ORDER BY fecha ROWS BETWEEN 3 PRECEDING AND 0 PRECEDING) 

from Ventas)

select * from Cte1

Pues bien, obtengo exactamente esto:

IdArticulo, Fecha, CantidadMedia

1    2016-01-01 00:00:00.000    24.500000
1    2016-01-02 00:00:00.000    34.500000
1    2016-01-03 00:00:00.000    30.700000
1    2016-01-04 00:00:00.000    25.900000
1    2016-01-06 00:00:00.000    33.050000
1    2016-01-08 00:00:00.000    26.950000
2    2016-01-02 00:00:00.000    44.500000
2    2016-01-03 00:00:00.000    33.800000
2    2016-05-08 00:00:00.000    28.366666
3    2016-01-05 00:00:00.000    20.100000
3    2016-01-08 00:00:00.000    36.600000
4    2016-01-06 00:00:00.000    14.700000
4    2016-01-07 00:00:00.000    13.100000
4    2016-05-08 00:00:00.000    13.633333

Pues bien, ahora mi pregunta es como insertar cada uno de estos registros en un nuevo campo llamado "cantidadmedia" en la fila correspondiente de la tabla Ventas donde coincidan Idarticulo y Fecha.

Me imagino que sera una especie de insert into desde la tabla cte hasta la tabla ventas pero con consigo darle forma con un tipico insert-select.

Gracias

Angel

Answer 5

Angeleci,

No, no requieres insertar una nueva fila, requieres actualizar una fila.

WITH CTE1 (fecha, articulo, media) AS
(
    SELECT 
	   Fecha,
	   IdArticulo, 
	   AVG(Cantidad) OVER (PARTITION BY Idarticulo ORDER BY fecha ROWS BETWEEN 3 PRECEDING AND 0 PRECEDING) 
    FROM VENTAS
)
UPDATE  v
SET
    v.cantidadmedia = c.media
FROM
    VENTAS v
    INNER JOIN CTE1 c ON (v.Fecha = c.Fecha)
	   AND (v.IdArticulo = c.articulo)

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.