Crear una columna calculada SQL
Este tema describe el propósito y los usos del tipo de columna Calculation, que se puede agregar a las tablas mediante el Administrador de Datas Warehouse. A continuación se explica qué hacen los cálculos SQL, por qué se utilizan, el proceso para crear un cálculo SQL e incluye dos ejemplos.
Explicación
En el pasado, las columnas que se consideraban advanced solo las podía realizar un analista en el equipo de éxito del cliente aquí en Adobe Commerce Intelligence. Ahora todo el poder está en manos del usuario final y se pueden crear columnas avanzadas en forma de SQL Calculation columnas en la nueva arquitectura de Commerce Intelligence.
El tipo de columna Calculation, ahora disponible como opción en el Administrador de Datas Warehouse, es una misma operación de tabla que le permite transformar las columnas de una tabla mediante la lógica PostgreSQL. La documentación sobre las funciones y los operadores que se pueden usar en el tipo de columna Calculation se encuentra en el sitio web de PostgreSQL aquí.
Las diferentes columnas que se pueden crear con la columna Calculation son casi ilimitadas, pero la mayoría de las columnas se pueden crear con instrucciones IF-THEN y aritmética básica, que se utiliza en los ejemplos siguientes.
Ejemplo 1: ¿Es el último pedido del cliente?
La mayoría de las cuentas tienen una columna denominada Is customer's last order? en su tabla orders para realizar análisis sobre las tasas de compra repetidas y los clientes perdidos. Si su cuenta se encuentra en la nueva arquitectura, esta columna se crea usando una columna Calculation y se puede ver en la captura de pantalla siguiente:
La columna Is customer's last order? utiliza las entradas Customer's lifetime number of orders y Customer's order number con los alias A y B, respectivamente.
Línea a línea, el significado de PostgreSQL es:
- caso: Esto inicia una serie de instrucciones If - Then
- cuando
Aes nulo oBes nulo, entonces nulo: si alguna de las entradas está vacía, la salida también debe estar vacía. Esto sirve para evitar errores de SQL - cuando
A=BentoncesYes: SiCustomer's lifetime number of orderses igual aCustomer's order numberpara esta fila, entonces se devuelveYes. Por lo tanto, si un cliente ha realizado cuatro pedidos, la fila de su cuarto pedido devolveráYesparaIs customer's last order? - else
No: si no se cumple ninguna de las otras condiciones cuando se cumplen, devuelvaNo - end: Esto finaliza las instrucciones If - Then
Los posibles valores que puede devolver esta columna (NULL, Yes, No) contienen caracteres que no son numéricos, por lo que el tipo de datos aquí es Cadena.
Ejemplo 2: Valor total del artículo de pedido (cantidad * precio)
A muchos clientes les gusta analizar los ingresos en el nivel de artículo, dividiéndolos por campos como product name o category. La mayoría de las bases de datos no proporcionan realmente los ingresos de un producto en un pedido; en su lugar, proporcionan la cantidad vendida en el pedido y el precio del artículo.
Para habilitar los análisis de ingresos de productos, la mayoría de las cuentas tienen una columna denominada Order item total value (quantity * price) en su tabla Orders Items. Si su cuenta se encuentra en la nueva arquitectura, esta columna también se crea usando una columna Calculation y se puede ver en la captura de pantalla siguiente:
En el esquema de Commerce, la columna Order item total value (quantity * price) utiliza las entradas qty ordered y base price con los alias A y B respectivamente.
Los valores devueltos por esta nueva columna se expresan en dólares y centavos, por lo que el tipo de datos correcto es Decimal(10,2).
Mecánica
Se puede agregar una nueva columna Calculation a una tabla navegando a Manage Data > Data Warehouse como se muestra a continuación:
Desde aquí puede crear una columna Calculation siguiendo los pasos a continuación:
-
Seleccione la tabla sobre la cual desea agregar la columna
Calculation. -
En la tabla correcta, haga clic en Create New Column en la parte superior derecha de la pantalla.
-
En el menú desplegable
Select a definition, seleccioneSame Table. -
Seleccione
Calculationcomocolumn definition equation. -
Introduzca el nombre de la columna.
-
Elija las
inputcolumnas de la tabla que se utilizan en la lógica de la nueva columna. Cada columna que agregue recibirá un alias de letra, por lo que la primera columna seráA, la segunda seráB, y así sucesivamente. -
En la ventana, escriba la lógica PostgreSQL para la nueva columna utilizando los alias de letras de las entradas. El cálculo SQL debe limitarse a una sola definición de columna, incluida toda la lógica entre las instrucciones SELECT y FROM de una consulta SQL. Las palabras clave SQL que utilicen cualquiera de las letras de entrada deben estar en minúsculas. Por ejemplo, al usar la instrucción
CASE, debe escribirse en minúsculas:case. El sistema supone que unaAmayúscula hace referencia a una de las entradas. -
Seleccione el tipo de datos adecuado.
Integer- Número enteroDecimal(10,2): un número decimal con 10 dígitos totales, de los cuales 2 están a la derecha del separador decimalString: cualquier tipo de texto o serie de caracteres que no utilicen números- Formato
Datetime-yyyy-MM-dd hh:mm:ss
-
Haga clic en test column. Esto genera una lista de cinco valores de prueba para cada una de las entradas y muestra el resultado de la lógica del paso 6 para cada conjunto de valores de prueba. Si alguna parte del SQL genera un error, se devuelve el mensaje de error correspondiente. Solo se pueden generar resultados de muestra si todas las columnas de entrada son campos nativos. Si alguna de las columnas de entrada es una columna calculada, debe validar los resultados agregando la columna a una métrica y visualizándola en el Report Builder visual
-
Cuando esté satisfecho con los resultados, haga clic en Save. La columna habilita para su uso.