Columnas calculadas

funcional

Añade columnas escritas como fórmulas de Excel: totales acumulados, márgenes, cambios respecto a la fila anterior y fechas.

La column: la directiva añade una columna al resultado, calculada con una fórmula de Excel a partir de las propias columnas del resultado. El lenguaje de fórmulas es el de Excel: los mismos operadores, la misma precedencia, las mismas funciones con los mismos argumentos y los mismos valores de error. La única diferencia es cómo se apunta a una celda: un resultado tiene columnas con nombre en vez de letras, así que una referencia nombra la columna con !.

-- sqlly column: Line Total = !Qty * !Price
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
SELECT OrderId, Qty, Price, Revenue, Cost FROM order_lines ORDER BY OrderId;

La base de datos nunca ve la fórmula. SQLly calcula la columna a medida que llegan las filas, y un Exportación a Excel lo escribe como una fórmula de Excel en vivo. No tienes que escribir la directiva: haz clic derecho en un encabezado de columna y elige Añadir columna calculada…, y SQLly te escribe la línea. La Guía de columnas calculadas recorre la barra de fórmulas.

Sintaxis

-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
ParteSignificado
<Name>El nombre de la nueva columna. Puede contener espacios y signos de puntuación, pero no =.
<formula>Una fórmula de Excel. El = inicial es opcional: Excel lo agradece, y a SQLly le da igual.
after=<column>Coloca la nueva columna justo después de esta. Si no, la columna va al final.
result=<n>Añádelo solo al enésimo conjunto de resultados del lote, contando desde 1. Sin él, la columna se añade a todos los conjuntos de resultados que tengan las columnas que lee.

A ; dentro de una cadena entre comillas o entre paréntesis pertenece a la fórmula, así que TEXTJOIN("; ", TRUE, !A, !B) funciona tal como está escrito. Una línea posterior column: con el mismo nombre reemplaza a la anterior. Una columna que el resultado ya tiene nunca es reemplazada: si la consulta devuelve un Total, un Total calculado se omite para ese resultado.

Referencias

Las filas están numeradas en el orden en que la consulta las devolvió, así que añade un ORDER BY siempre que una fórmula lea otra fila. Los nombres de las columnas se comparan sin distinguir mayúsculas de minúsculas.

EscribirLeeComo en Excel
!QtyQty de esta fila.B7 en la fila 7
!Qty[1]Qty de la fila anterior.B6
!Qty[3]Qty de tres filas más arriba.B4
!Qty[-1]Qty de la fila siguiente.B8
!Qty[$1]Qty de la primera fila, estés en la fila que estés.B$2
!"Order Total"Una columna cuyo nombre tiene espacios o signos de puntuación, entre comillas dobles. También funciona con desplazamientos de fila: !"Order Total"[1].
!Qty[2]:!QtyUn rango: Cantidad en esta fila y en las dos anteriores.B5:B7
!Jan:!MarUn rango entre columnas: todas las columnas de enero a marzo en esta fila, en el orden de columnas del resultado.C7:E7
  • Lo que hay antes de la primera fila está en blanco. !Qty[1] en la primera fila lee una celda vacía, que cuenta como 0 en aritmética y "" en texto. Lo mismo ocurre con !Qty[-1] en la última fila.
  • Una columna cuya fórmula lee una columna que el resultado no tiene se omite de ese resultado, así que una directiva puede servir para un lote cuyos conjuntos de resultados tienen columnas distintas.
  • Los rangos van dentro de las funciones. SUM(!Qty[2]:!Qty) está bien; un !Qty[2]:!Qty como fórmula completa da #VALUE!, como en Excel.

Variables

@name lee un valor que declara la consulta, así que un solo número controla tanto el SQL como las fórmulas:

-- sqlly set rate = 0.0825
-- sqlly param: @region text = West
-- sqlly column: Tax = ROUND(!Amount * @rate, 2)
-- sqlly column: Label = @region & " - " & !Customer
SELECT Customer, Amount FROM invoices WHERE Region = @region;
  • -- sqlly set valores se leen como números cuando lo parecen, y como texto en caso contrario.
  • -- sqlly param: valores mantener su tipo declarado: int y decimal como números, bool como TRUE o FALSE, date y datetime como fechas reales, todo lo demás como texto. El valor escrito en la barra de parámetros prevalece sobre el valor por defecto declarado, y null se lee como vacío.
  • Una variable no declarada da #NAME?.

Operadores

Los mismos operadores que Excel, de menor a mayor precedencia:

OperadorSignificado
= <> < > <= >=Comparación, que devuelve TRUE o FALSE. El texto se compara sin distinguir mayúsculas de minúsculas, así que "a" = "A" es TRUE.
&Une texto: !First & " " & !Last.
+ -Suma y resta.
* /Multiplicar y dividir.
^Potencia.
%Porcentaje, escrito después de un número: 15% es 0,15.
- (delante)Negación.
:Rango, entre dos referencias.

Las peculiaridades de Excel están ahí a propósito, para que una fórmula dé el mismo resultado en la cuadrícula y en el libro: la negación tiene mayor precedencia que ^, así que -2^2 es 4; los operadores del mismo nivel se evalúan de izquierda a derecha, así que 2^3^2 es 64; y 1+2&3 es el texto "33". Si tienes dudas, usa paréntesis. Un texto que parece un número se comporta como número en la aritmética ("3"+4 es 7), y TRUE cuenta como 1.

Valores en blanco, NULL y errores

  • A NULL es una celda en blanco. Cuenta como 0 en aritmética y como "" en texto, igual que una celda vacía en Excel. ISBLANK() distingue un valor en blanco de un cero.
  • Los errores son los mismos que en Excel - y fluyen por las fórmulas de la misma manera: #DIV/0! (división por cero), #VALUE! (un valor de tipo incorrecto, como "x"+1), #REF! (una columna que no existe), #NAME? (una función o variable desconocida), #NUM! (un número fuera de rango, como SQRT(-1)), y #N/A (sin valor, de NA() o un IFS sin coincidencia). Aparece un error en la celda; nunca detiene la consulta.
  • Captúralos como en Excel: IFERROR(!Profit / !Units, 0).

Funciones

Cada función de abajo toma los mismos argumentos en el mismo orden que en Excel y da el mismo resultado. Un nombre que SQLly no conoce da #NAME?.

Lógico
IF, IFS, IFERROR, IFNA, AND, OR, NOT, XOR, SWITCH, CHOOSE, TRUE, FALSE, y COALESCE (ver abajo).
Información
ISBLANK, ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISEVEN, ISODD, N, T, NA.
Matemáticas y estadísticas
ABS, SIGN, ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, CEILING.MATH, FLOOR, FLOOR.MATH, EVEN, ODD, MOD, QUOTIENT, POWER, SQRT, EXP, LN, LOG, LOG10, PI, SIN, COS, TAN, DEGREES, RADIANS, SUM, PRODUCT, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK.
Texto
LEFT, RIGHT, MID, LEN, UPPER, LOWER, PROPER, TRIM, CLEAN, CONCAT, CONCATENATE, TEXTJOIN, SUBSTITUTE, REPLACE, REPT, FIND, SEARCH, EXACT, TEXT, FIXED, VALUE, CHAR, CODE, TEXTBEFORE, TEXTAFTER.
Fecha y hora
DATE, TIME, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, EDATE, EOMONTH, DATEDIF, DAYS, DATEVALUE, TIMEVALUE, NETWORKDAYS, WORKDAY.
  • COALESCE(a, b, …) es la única incorporación de SQLly, tomada de SQL: el primer argumento que no esté en blanco ni "", de lo contrario, el último. COALESCE(!Nickname, !FirstName, "Customer"). En una exportación a Excel se escribe como anidado IFs, ya que Excel no tiene esa función.
  • Las fechas funcionan como en Excel. Una columna de fecha u hora de la base de datos se lee como una fecha real, así que !ShippedOn - !OrderedOn es un número de días, !Due + 30 es una fecha, y EDATE, EOMONTH y DATEDIF se comporta como esperas.
  • TODAY() y NOW() lee el reloj de tu computadora cuando se ejecuta la consulta.
  • TEXT toma los códigos de formato de Excel: "#,##0.00", "0.0%", "000", "$#,##0", "yyyy-mm-dd", "mmm d, yyyy", "dddd", "h:mm AM/PM", "hh:mm:ss".

Ejemplos

El margen como fracción de los ingresos, protegido frente a cero, colocado después de la columna Cost. Dale -- sqlly format: column=Margin %; number=1; style=percent para mostrarlo como un porcentaje:

-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost

Un total acumulado. La columna lee su propio valor en la fila anterior; N() convierte el hueco sobre la primera fila en 0:

-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;

Cambio respecto a la fila anterior, en cantidad y en porcentaje. La primera fila no tiene nada antes, así que ambos muestran 0 ahí en lugar del primer valor completo o un #DIV/0!:

-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)

Cada mes como porcentaje del primer mes:

-- sqlly column: vs First = !Sales / !Sales[$1]

Una suma y un promedio móviles de las últimas tres filas:

-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)

Días entre dos fechas, días laborables y una fecha de vencimiento a fin del próximo mes:

-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)

Texto construido a partir de números y fechas con TEXT:

-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")

Un valor alternativo cuando una columna está vacía y una etiqueta de nivel:

-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")

Cómo se calculan las columnas

  • En el orden en que se declaran. Una fórmula puede leer una columna calculada declarada antes que ella en la misma fila, y cualquier columna calculada, incluida ella misma, en filas anteriores. Eso es lo que hace posible un total acumulado.
  • En el orden en que llegan las filas. [1] siempre significa la fila que la base de datos envió antes que esta, no la fila de arriba después de que ordenes la cuadrícula. Ordenar o filtrar la cuadrícula mueve los valores calculados con sus filas; no los recalcula.
  • Cada columna obtiene un tipo de su fórmula. La aritmética da números; las funciones de fecha y la aritmética de fechas dan fechas (por lo que los formatos de fecha y los filtros de fecha funcionan sobre ellas); las comparaciones, AND, OR y la IS las funciones dan TRUE/FALSE; las funciones de texto y & dan texto. Un IF cuyas ramas dan tipos distintos es texto, así IF(…, "", !A - !B) crea una columna de texto; usa 0 en lugar de "" para mantenerla numérica.
  • Qué conjuntos de resultados reciben la columna. Cada conjunto de resultados que tenga todas las columnas que la fórmula lee, o solo el resultado n con result=n.
  • El formato funciona como en cualquier otra columna. format:, conditional:, grid: y el pivote tratan toda columna calculada como si la hubiera devuelto la base de datos.
  • Resultados muy grandes. Un resultado demasiado grande para mantener en memoria se pagina desde el disco. Las fórmulas entonces solo ven las filas cargadas en cada página (más un pequeño margen de retroceso para [n] referencias), por lo que un total acumulado comienza de nuevo con cada página. Para un total acumulado largo, deja que la base de datos lo calcule con una función de ventana, como SUM(Amount) OVER (ORDER BY InvoiceDate).
El bloqueo los mantiene. Al hacer clic en el bloqueo en un resultado escribe sus columnas calculadas en el bloque de bloqueo junto con el resto de la configuración de la cuadrícula, así que vuelven en cada ejecución.