Colunas calculadas

funcional

Adicione 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>]
ParteSignificado
<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.

EscreverLêComo no Excel
!QtyQuantidade 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]:!QtyUm intervalo: Qty nesta linha e nas duas anteriores.B5:B7
!Jan:!MarUm 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]:!Qty como 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 set valores 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: int e decimal como números, bool como TRUE ou FALSE, date e datetime como datas reais, todo o resto como texto. O valor digitado na barra de parâmetros prevalece sobre o padrão declarado, e null lê como em branco.
  • Uma variável não declarada dá #NAME?.

Operadores

Os mesmos operadores do Excel, da ligação mais fraca à mais forte:

OperadorSignificado
= <> < > <= >=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, como SQRT(-1)), e #N/A (sem valor, de NA() ou um IFS sem 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, e COALESCE (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 de IFs, 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, e EDATE, EOMONTH e DATEDIF comporte-se como você espera.
  • TODAY() e NOW() leia o relógio do seu computador quando a consulta for executada.
  • TEXT aceita 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, OR e o IS funções retornam VERDADEIRO/FALSO; funções de texto e & dar texto. Um IF cujos ramos resultam em tipos diferentes é texto, então IF(…, "", !A - !B) cria uma coluna de texto; use 0 em 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, como SUM(Amount) OVER (ORDER BY InvoiceDate).
O cadeado os mantém. Clicando no bloquear em um resultado, grava as colunas calculadas dela no bloco do lock junto com o restante da configuração da grade, para que voltem a cada execução.