Columnas calculadas
funcionalAñ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>]
| Parte | Significado |
|---|---|
<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.
| Escribir | Lee | Como en Excel |
|---|---|---|
!Qty | Qty 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]:!Qty | Un rango: Cantidad en esta fila y en las dos anteriores. | B5:B7 |
!Jan:!Mar | Un 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]:!Qtycomo 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 setvalores se leen como números cuando lo parecen, y como texto en caso contrario.-- sqlly param:valores mantener su tipo declarado:intydecimalcomo números,boolcomo TRUE o FALSE,dateydatetimecomo fechas reales, todo lo demás como texto. El valor escrito en la barra de parámetros prevalece sobre el valor por defecto declarado, ynullse lee como vacío.- Una variable no declarada da
#NAME?.
Operadores
Los mismos operadores que Excel, de menor a mayor precedencia:
| Operador | Significado |
|---|---|
= <> < > <= >= | 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
NULLes 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, comoSQRT(-1)), y#N/A(sin valor, deNA()o unIFSsin 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, yCOALESCE(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 anidadoIFs, 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 - !OrderedOnes un número de días,!Due + 30es una fecha, yEDATE,EOMONTHyDATEDIFse comporta como esperas. TODAY()yNOW()lee el reloj de tu computadora cuando se ejecuta la consulta.TEXTtoma 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,ORy laISlas funciones dan TRUE/FALSE; las funciones de texto y&dan texto. UnIFcuyas ramas dan tipos distintos es texto, asíIF(…, "", !A - !B)crea una columna de texto; usa0en 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, comoSUM(Amount) OVER (ORDER BY InvoiceDate).