Agregar campos calculados dinámicamente generalmente está mal visto y por una buena razón. El almacenamiento de cálculos puede causar muchos problemas, pero ¿qué sucede si tenemos el requisito de almacenarlos y no solo almacenarlos, sino habilitar la capacidad de crearlos dinámicamente?

Tenemos un proyecto en el que el cliente necesita recopilar métricas y muchas de ellas. Sin embargo, el cliente también está agregando nuevas métricas para rastrear y debido a que necesitan una instantánea de las estadísticas en diferentes momentos, fue necesario almacenar los cálculos. Sin embargo, no queremos crear una nueva columna para cada punto de datos que recopilan, y recopilan numerosos puntos de datos. Por lo tanto, implementamos algo similar a un modelo EAV. Por lo tanto, tenemos un par de tablas similar a esta:

CREATE TABLE dbo.tblMetric (
  MetricID int NOT NULL IDENTITY
    constraint Pr309cK_tblMetric PRIMARY KEY CLUSTERED,
  MetricName nvarchar(255) NOT NULL
    constraint Ur309cQ_tblMetric UNIQUE
);
 
CREATE TABLE dbo.tblMeasurement (
  MeasurementID int NOT NULL IDENTITY
    constraint Pr309cK_tblMeasurement PRIMARY KEY CLUSTERED,
  MetricID int NOT NULL
    constraint Fr309cK_tblMeasurement_MetricID FOREIGN KEY
      REFERENCES dbo.tblMetric(MetricID)
  GroupID int NOT NULL
    constraint Fr309cK_tblMeasurement_GroupID FOREIGN KEY
      REFERENCES dbo.tblMeasurementGroup(GroupID)
  Measurement decimal NULL
);

Como puede ver, las medidas se almacenan como filas, no como columnas. También se agrupan junto con el GroupID, por lo que las mismas métricas se pueden volver a tomar en diferentes momentos. Pero no todas las métricas son simplemente registrar los números; algunos son cálculos basados en otras métricas. En un diseño de tabla típico, esto es fácil de hacer en una consulta. Uno simplemente tiene que hacer SELECCIONAR Columna A + Columna B AS Total ....Pero con las mediciones almacenadas como filas, esto ahora es problemático. Además, no todas las mediciones pueden ingresarse a mano; pueden ser simplemente el resultado del contenido de otras tablas. Usemos esto como un ejemplo:

SELECT
  SUM(Quantity) AS TotalQuantity,
  COUNT(Order) AS TotalOrder,
  SUM(Quantity) / COUNT(Order) AS AverageQtyPerOrder
FROM dbo.tblOrders

Suponiendo que tenemos 3 métricas con el mismo nombre, queremos recopilar las medidas para ese punto en el tiempo y agregarlas a las mediciones. Teniendo en cuenta que el objetivo es mantener el sistema flexible para permitir agregar nuevas métricas, incluidas las métricas calculadas, no nos sirve de mucho escribir varias consultas one-trick que agregarán la medición necesaria, entre codificar el MetricName en el código SQL. El objetivo es crear un procedimiento almacenado que pueda contener cualquier consulta como la muestra y luego insertarlo en la medición con un conocimiento mínimo sobre las métricas para mapear. El problema se agrava por el hecho de que tenemos una consulta de 3 columnas cuando necesitamos insertar en 3 filas.

Enter UNPIVOT

Afortunadamente, SQL Server viene con una palabra clave UNPIVOT que podemos usar para transponer las columnas en filas. Para continuar con el ejemplo anterior, la consulta no dividida probablemente se vería así:

WITH RawData AS (
  SELECT
    SUM(Quantity) AS TotalQuantity,
    COUNT(Order) AS TotalOrder,
    SUM(Quantity) / COUNT(Order) AS AverageQtyPerOrder
  FROM dbo.tblOrders 
)
SELECT 
  p.MetricName,
  p.Measurement
FROM RawData AS d
UNPIVOT ( [Measurement] FOR [MetricName] 
  IN ('TotalQuantity', 'TotalOrder', 'AverageQtyPerOrder')
) AS p;

Aunque ahora tenemos una consulta de 3 filas, la declaración UNPIVOT sigue siendo un pony one-trick. Teníamos que tener el nombre de las columnas codificado en la cláusula IN de la expresión UNPIVOT. Entonces, la siguiente pregunta es ¿cómo obtenemos los nombres de columna que necesitamos para completar la cláusula IN?

Debido a que tenemos que conocer el nombre de la tabla / vista para recopilar los nombres de las columnas, debemos simplificar las cosas al requerir el uso de una tabla temporal. Por lo tanto, modificaremos la consulta original para que sea una instrucción SELECT INTO:

SELECT
  SUM(Quantity) AS TotalQuantity,
  COUNT(Order) AS TotalOrder,
  SUM(Quantity) / COUNT(Order) AS AverageQtyPerOrder
INTO #tmpCalculations
FROM dbo.tblOrders

Ahora está en una tabla temporal, usando la vista de metadatos sys.columns se vuelve más fácil:

SELECT c.column_id, c.name
FROM tempdb.sys.columns AS c
WHERE c.object_id = OBJECT_ID('tempdb..#tmpCalculations');