Colunas calculadas
funcionalAdicione colunas escritas como fórmulas do Excel: totais acumulados, margens, alterações em relação à linha anterior e datas.
O column: diretiva adiciona uma coluna ao resultado, calculada com uma fórmula do Excel a partir das próprias colunas do resultado. A linguagem da fórmula é a do Excel: os mesmos operadores, a mesma precedência, as mesmas funções com os mesmos argumentos e os mesmos valores de erro. A única diferença é como você aponta para uma célula: um resultado tem colunas nomeadas em vez de colunas com letras, então uma referência nomeia a coluna com !.
-- 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;
O banco de dados nunca vê a fórmula. O SQLly calcula a coluna à medida que as linhas chegam, e um Exportação para Excel escreve como uma fórmula ativa do Excel. Você não precisa digitar a diretiva: clique com o botão direito no cabeçalho de uma coluna e escolha Adicionar coluna calculada…, e o SQLly escreve a linha para você. O Guia de colunas calculadas percorre a barra de fórmulas.
Sintaxe
-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
| Parte | Significado |
|---|---|
<Name> | O nome da nova coluna. Ele pode conter espaços e pontuação, mas não =. |
<formula> | Uma fórmula do Excel. O = é opcional: o Excel quer, e o SQLly funciona de qualquer jeito. |
after=<column> | Coloque a nova coluna logo após esta. Sem isso, a coluna vai para o final. |
result=<n> | Adicione-o apenas ao n-ésimo conjunto de resultados do lote, contando a partir de 1. Sem ele, a coluna entra em todo conjunto de resultados que tenha as colunas que ela lê. |
A ; dentro de uma string entre aspas ou dentro de parênteses pertence à fórmula, então TEXTJOIN("; ", TRUE, !A, !B) funciona como está escrito. Uma linha posterior column: com o mesmo nome substitui a anterior. Uma coluna que o resultado já possui nunca é substituída: se a consulta retorna um Total, um Total é ignorado para esse resultado.
Referências
As linhas são numeradas na ordem em que a consulta as retornou, então adicione um ORDER BY sempre que uma fórmula lê outra linha. Os nomes das colunas são comparados sem distinção entre maiúsculas e minúsculas.
| Escrever | Lê | Como no Excel |
|---|---|---|
!Qty | Quantidade nesta linha. | B7 na linha 7 |
!Qty[1] | Quantidade na linha anterior. | B6 |
!Qty[3] | Quantidade três linhas acima. | B4 |
!Qty[-1] | Quantidade na linha seguinte. | B8 |
!Qty[$1] | Quantidade na primeira linha, qualquer que seja a linha em que você esteja. | B$2 |
!"Order Total" | Uma coluna cujo nome contém espaços ou pontuação, entre aspas duplas. Funciona também com deslocamentos de linha: !"Order Total"[1]. | |
!Qty[2]:!Qty | Um intervalo: Qty nesta linha e nas duas anteriores. | B5:B7 |
!Jan:!Mar | Um intervalo entre colunas: todas as colunas de Jan a Mar desta linha, na ordem das colunas do resultado. | C7:E7 |
- A linha anterior à primeira está em branco.
!Qty[1]na primeira linha lê uma célula vazia, que conta como 0 em aritmética e""em texto. O mesmo vale para!Qty[-1]na última linha. - Uma coluna cuja fórmula lê uma coluna que o resultado não tem é deixada fora desse resultado, então uma diretiva pode servir a um lote cujos conjuntos de resultados têm colunas diferentes.
- Intervalos vão dentro de funções.
SUM(!Qty[2]:!Qty)está bem; um simples!Qty[2]:!Qtycomo a fórmula inteira é#VALUE!, como no Excel.
Variáveis
@name lê um valor que a consulta declara, então um único número pode controlar tanto o SQL quanto as 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 lidos como números quando têm cara de número, e como texto caso contrário.-- sqlly param:valores mantêm o tipo declarado:intedecimalcomo números,boolcomo TRUE ou FALSE,dateedatetimecomo datas reais, todo o resto como texto. O valor digitado na barra de parâmetros prevalece sobre o padrão declarado, enulllê como em branco.- Uma variável não declarada dá
#NAME?.
Operadores
Os mesmos operadores do Excel, da ligação mais fraca à mais forte:
| Operador | Significado |
|---|---|
= <> < > <= >= | Comparação, que resulta em VERDADEIRO ou FALSO. Texto compara sem diferenciar maiúsculas de minúsculas, então "a" = "A" é TRUE. |
& | Concatena texto: !First & " " & !Last. |
+ - | Adição e subtração. |
* / | Multiplique e divida. |
^ | Potência. |
% | Porcentagem, escrita após um número: 15% é 0,15. |
- (na frente) | Negação. |
: | Intervalo, entre duas referências. |
As peculiaridades do Excel vêm junto de propósito, para que uma fórmula dê a mesma resposta na grade e na pasta de trabalho: a negação tem precedência maior que ^, então -2^2 é 4; operadores de mesma precedência avaliam da esquerda para a direita, então 2^3^2 é 64; e 1+2&3 é o texto "33". Use parênteses em caso de dúvida. Texto que parece um número é um número em aritmética ("3"+4 é 7), e TRUE conta como 1.
Células em branco, NULLs e erros
- A
NULLé uma célula em branco. Conta como 0 na aritmética e como""no texto, como fica uma célula vazia no Excel.ISBLANK()distingue um espaço em branco de um zero. - Os erros são os do Excel - e fluem pelas fórmulas da mesma forma:
#DIV/0!(divisão por zero),#VALUE!(o tipo errado de valor, como"x"+1),#REF!(uma coluna que não existe),#NAME?(uma função ou variável desconhecida),#NUM!(um número fora do intervalo, comoSQRT(-1)), e#N/A(sem valor, deNA()ou umIFSsem correspondência). Um erro aparece na célula; ele nunca interrompe a consulta. - Capture-os como no Excel:
IFERROR(!Profit / !Units, 0).
Funções
Cada função abaixo aceita os mesmos argumentos na mesma ordem que no Excel e fornece a mesma resposta. Um nome desconhecido para o SQLly dá #NAME?.
- Lógico
IF,IFS,IFERROR,IFNA,AND,OR,NOT,XOR,SWITCH,CHOOSE,TRUE,FALSE, eCOALESCE(veja abaixo).- Informações
ISBLANK,ISNUMBER,ISTEXT,ISLOGICAL,ISERROR,ISERR,ISNA,ISEVEN,ISODD,N,T,NA.- Matemática e estatística
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.- Data e 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, …)é a única adição do SQLly, emprestada do SQL: o primeiro argumento que não está em branco nem"", senão o último.COALESCE(!Nickname, !FirstName, "Customer"). Em uma exportação do Excel, é gravado como uma sequência aninhada deIFs, já que o Excel não possui essa função.- Datas funcionam como no Excel. Uma coluna de data ou hora do banco de dados é lida como uma data real, então
!ShippedOn - !OrderedOné um número de dias,!Due + 30é uma data, eEDATE,EOMONTHeDATEDIFcomporte-se como você espera. TODAY()eNOW()leia o relógio do seu computador quando a consulta for executada.TEXTaceita os códigos de formato do Excel:"#,##0.00","0.0%","000","$#,##0","yyyy-mm-dd","mmm d, yyyy","dddd","h:mm AM/PM","hh:mm:ss".
Exemplos
Margem como uma fração da receita, protegida contra zero, colocada após a coluna de Custo. Dê a ela -- sqlly format: column=Margin %; number=1; style=percent para mostrar como porcentagem:
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
Um total acumulado. A coluna lê seu próprio valor na linha anterior; N() transforma o espaço em branco acima da primeira linha em 0:
-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;
Alteração em relação à linha anterior, como valor e como porcentagem. A primeira linha não tem nada antes dela, então ambas mostram 0 ali em vez de todo o primeiro valor ou um #DIV/0!:
-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)
Cada mês como porcentagem do primeiro mês:
-- sqlly column: vs First = !Sales / !Sales[$1]
Uma soma e média móveis das últimas três linhas:
-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)
Dias entre duas datas, dias úteis e uma data de vencimento no final do próximo mês:
-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)
Texto criado a partir de números e datas com TEXT:
-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")
Uma alternativa quando uma coluna está vazia, e um rótulo de nível:
-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")
Como as colunas são calculadas
- Na ordem em que são declaradas. Uma fórmula pode ler uma coluna calculada declarada acima dela na mesma linha, e qualquer coluna calculada, ela própria incluída, em linhas anteriores. É isso que torna possível um total acumulado.
- Na ordem em que as linhas chegam.
[1]sempre significa a linha que o banco de dados enviou antes desta, não a linha acima dela depois que você ordenar a grade. Ordenar ou filtrar a grade move os valores computados com suas linhas; isso não os recalcula. - Cada coluna recebe um tipo a partir de sua fórmula. A aritmética fornece números; funções de data e aritmética de datas fornecem datas (assim, formatos de data e filtros de data funcionam sobre elas); comparações,
AND,ORe oISfunções retornam VERDADEIRO/FALSO; funções de texto e&dar texto. UmIFcujos ramos resultam em tipos diferentes é texto, entãoIF(…, "", !A - !B)cria uma coluna de texto; use0em vez de""para mantê-lo numérico. - Quais conjuntos de resultados obtêm a coluna. Todo conjunto de resultados que possui todas as colunas que a fórmula lê, ou apenas o resultado n com
result=n. - A formatação funciona como em qualquer outra coluna.
format:,conditional:,grid:e o pivot trata uma coluna calculada como se fosse uma retornada pelo banco de dados. - Resultados muito grandes. Um resultado grande demais para manter na memória é paginado a partir do disco. As fórmulas então veem apenas as linhas carregadas em cada página (mais uma breve janela retroativa para
[n]referências), então o total acumulado recomeça a cada página. Para um total acumulado que se estenda por muitas linhas, deixe o banco de dados calculá-lo com uma função de janela, comoSUM(Amount) OVER (ORDER BY InvoiceDate).