Colonnes calculées

fonctionnel

Ajoutez des colonnes écrites sous forme de formules Excel : totaux cumulés, marges, variations par rapport à la ligne précédente et dates.

Le column: la directive ajoute une colonne au résultat, calculée avec une formule Excel à partir des colonnes du résultat. Le langage de formule est celui d'Excel : les mêmes opérateurs, la même priorité, les mêmes fonctions avec les mêmes arguments, et les mêmes valeurs d'erreur. La seule différence est la manière de pointer une cellule : un résultat a des colonnes nommées au lieu de lettres, donc une référence nomme la colonne avec !.

-- 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 données ne voit jamais la formule. SQLly calcule la colonne au fur et à mesure de l'arrivée des lignes, et Export Excel l'écrit sous forme de vraie formule Excel. Pas besoin de taper la directive : faites un clic droit sur l'en-tête d'une colonne et choisissez Ajouter une colonne calculée…, et SQLly écrit la ligne pour vous. Le guide des colonnes calculées passe en revue la barre de formule.

Syntaxe

-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
PartieSens
<Name>Le nom de la nouvelle colonne. Il peut contenir des espaces et de la ponctuation, mais pas =.
<formula>Une formule Excel. Le = au début est optionnel : Excel le veut, SQLly fonctionne dans les deux cas.
after=<column>Placez la nouvelle colonne juste après celle-ci. Sans cela, la colonne va à la fin.
result=<n>Ajoutez-le uniquement au n-ième ensemble de résultats du lot, en comptant à partir de 1. Sans cela, la colonne rejoint chaque ensemble de résultats qui contient les colonnes qu'elle lit.

A ; à l'intérieur d'une chaîne entre guillemets ou à l'intérieur de parenthèses appartient à la formule, donc TEXTJOIN("; ", TRUE, !A, !B) s'exécute tel qu'écrit. Une ligne ultérieure column: ligne portant le même nom remplace la précédente. Une colonne que le résultat possède déjà n'est jamais remplacée : si la requête retourne un Total, un Total calculé est ignoré pour ce résultat.

Références

Les lignes sont numérotées dans l'ordre dans lequel la requête les a retournées, donc ajoutez un ORDER BY chaque fois qu'une formule lit une autre ligne. Les noms de colonnes sont comparés sans tenir compte de la casse.

ÉcrireLecturesÉquivalent Excel
!QtyQté sur cette ligne.B7 sur la ligne 7
!Qty[1]Qté sur la ligne précédente.B6
!Qty[3]Qté trois lignes au-dessus.B4
!Qty[-1]Qté sur la ligne suivante.B8
!Qty[$1]Qté sur la première ligne, quelle que soit la ligne sur laquelle vous êtes.B$2
!"Order Total"Une colonne dont le nom contient des espaces ou une ponctuation, entre guillemets. Fonctionne aussi avec des décalages de ligne : !"Order Total"[1].
!Qty[2]:!QtyUne plage : Qté sur cette ligne et les deux lignes précédentes.B5:B7
!Jan:!MarUne plage à travers les colonnes : chaque colonne de Jan à Mar sur cette ligne, dans l'ordre des colonnes du résultat.C7:E7
  • Avant la première ligne, c'est vide. !Qty[1] dans la première ligne lit une cellule vide, ce qui compte comme 0 en arithmétique et "" en texte. Il en va de même pour !Qty[-1] dans la dernière ligne.
  • Une colonne dont la formule lit une colonne absente du résultat est laissée de côté de ce résultat, donc une directive peut servir à un lot dont les ensembles de résultats ont différentes colonnes.
  • Les plages vont dans les fonctions. SUM(!Qty[2]:!Qty) est correct ; un simple !Qty[2]:!Qty comme formule entière est #VALUE!, comme dans Excel.

Variables

@name lit une valeur que la requête déclare, donc un nombre peut piloter à la fois le SQL et les formules :

-- 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 valeurs se lisent comme des nombres lorsqu'elles en ont l'apparence, et comme du texte sinon.
  • -- sqlly param: valeurs conserver leur type déclaré : int et decimal comme nombres, bool comme TRUE ou FALSE, date et datetime comme de vraies dates, tout le reste comme du texte. La valeur saisie dans la barre de paramètres l'emporte sur la valeur par défaut déclarée, et null se lit comme vide.
  • Une variable non déclarée donne #NAME?.

Opérateurs

Les mêmes opérateurs qu'Excel, de la priorité la plus faible à la plus forte :

OpérateurSens
= <> < > <= >=Comparaison, donnant VRAI ou FAUX. Le texte se compare sans tenir compte de la casse, donc "a" = "A" est TRUE.
&Concatène du texte : !First & " " & !Last.
+ -Additionner et soustraire.
* /Multiplier et diviser.
^Puissance.
%Pourcentage, écrit après un nombre : 15% est 0,15.
- (devant)Négation.
:Plage, entre deux références.

Les particularités d'Excel sont volontairement présentes, donc une formule donne le même résultat dans la grille et dans le classeur : la négation a une priorité plus forte que ^, donc -2^2 est 4 ; les opérateurs de même priorité s'évaluent de gauche à droite, donc 2^3^2 est 64 ; et 1+2&3 est le texte "33". Utilisez des parenthèses en cas de doute. Un texte qui se lit comme un nombre est un nombre en arithmétique ("3"+4 est 7), et TRUE compte pour 1.

Cellules vides, NULL et erreurs

  • A NULL est une cellule vide. Cela compte comme 0 en arithmétique et comme "" dans le texte, de la même manière qu'une cellule vide dans Excel. ISBLANK() distingue une cellule vide d'un zéro.
  • Les erreurs sont celles d'Excel - et elles se propagent dans les formules de la même manière : #DIV/0! (division par zéro), #VALUE! (le mauvais type de valeur, comme "x"+1), #REF! (une colonne qui n'existe pas), #NAME? (une fonction ou variable inconnue), #NUM! (un nombre hors plage, comme SQRT(-1)), et #N/A (aucune valeur, provenant de NA() ou un IFS sans correspondance). Une erreur s'affiche dans la cellule ; elle n'arrête jamais la requête.
  • Rattrapez-les comme dans Excel : IFERROR(!Profit / !Units, 0).

Fonctions

Chaque fonction ci-dessous prend les mêmes arguments dans le même ordre que dans Excel, et donne la même réponse. Un nom que SQLly ne connaît pas donne #NAME?.

Logique
IF, IFS, IFERROR, IFNA, AND, OR, NOT, XOR, SWITCH, CHOOSE, TRUE, FALSE, et COALESCE (voir ci-dessous).
Information
ISBLANK, ISNUMBER, ISTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISEVEN, ISODD, N, T, NA.
Mathématiques et statistiques
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.
Texte
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.
Date et heure
DATE, TIME, TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, EDATE, EOMONTH, DATEDIF, DAYS, DATEVALUE, TIMEVALUE, NETWORKDAYS, WORKDAY.
  • COALESCE(a, b, …) est l'unique ajout de SQLly, emprunté à SQL : le premier argument qui n'est ni vide ni "", sinon le dernier. COALESCE(!Nickname, !FirstName, "Customer"). Dans un export Excel, il est écrit comme des IFs imbriqués, puisque Excel n'a pas de fonction de ce type.
  • Les dates fonctionnent comme dans Excel. Une colonne de date ou d'heure de la base de données est lue comme une vraie date, donc !ShippedOn - !OrderedOn est un nombre de jours, !Due + 30 est une date, et EDATE, EOMONTH et DATEDIF se comportent comme vous vous y attendez.
  • TODAY() et NOW() lit l'horloge de votre ordinateur lorsque la requête s'exécute.
  • TEXT utilise les codes de format d'Excel: "#,##0.00", "0.0%", "000", "$#,##0", "yyyy-mm-dd", "mmm d, yyyy", "dddd", "h:mm AM/PM", "hh:mm:ss".

Exemples

Marge en fraction du chiffre d'affaires, protégée contre la division par zéro, placée après la colonne Coût. Donnez-lui -- sqlly format: column=Margin %; number=1; style=percent pour l'afficher en pourcentage :

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

Un total cumulé. La colonne lit sa propre valeur sur la ligne précédente ; N() transforme la cellule vide au-dessus de la première ligne en 0 :

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

Changement par rapport à la ligne précédente, en montant et en pourcentage. La première ligne n'ayant rien avant elle, les deux montrent 0 à cet endroit plutôt que toute la première valeur ou un #DIV/0!:

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

Chaque mois en pourcentage du premier mois :

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

Une somme glissante et une moyenne glissante des trois dernières lignes :

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

Jours entre deux dates, jours ouvrables, et date d'échéance à la fin du mois prochain :

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

Texte construit à partir de nombres et de dates avec TEXT:

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

Une valeur de secours lorsqu'une colonne est vide, et un libellé de niveau :

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

Comment les colonnes sont calculées

  • Dans l'ordre dans lequel elles sont déclarées. Une formule peut lire une colonne calculée déclarée au-dessus d'elle sur la même ligne, ainsi que toute colonne calculée, y compris elle-même, sur les lignes précédentes. C'est ce qui rend possible un total cumulatif.
  • Dans l'ordre d'arrivée des lignes. [1] désigne toujours la ligne que la base de données a envoyée avant celle-ci, et non la ligne qui se trouve au-dessus une fois la grille triée. Trier ou filtrer la grille déplace les valeurs calculées avec leurs lignes ; cela ne les recalcule pas.
  • Chaque colonne obtient un type à partir de sa formule. L'arithmétique donne des nombres ; les fonctions de date et l'arithmétique de date donnent des dates (donc les formats de date et les filtres de date fonctionnent dessus) ; les comparaisons, AND, OR et le IS les fonctions donnent TRUE/FALSE ; les fonctions texte et & donnent du texte. Un IF dont les branches renvoient des types différents est du texte, donc IF(…, "", !A - !B) crée une colonne de texte ; utilisez 0 au lieu de "" pour qu'il reste numérique.
  • Quels ensembles de résultats obtiennent la colonne. Chaque ensemble de résultats qui contient toutes les colonnes que la formule lit, ou seulement le résultat n avec result=n.
  • Le formatage fonctionne comme sur n'importe quelle autre colonne. format:, conditional:, grid: et le tableau croisé traitent tous une colonne calculée comme une colonne que la base de données a renvoyée.
  • Les résultats très volumineux. Un résultat trop volumineux pour tenir en mémoire est paginé depuis le disque. Les formules ne voient alors que les lignes chargées à chaque page (plus un court retour en arrière pour [n] les références), donc un total cumulatif recommence avec chaque page. Pour un total cumulatif prolongé, laissez la base de données le calculer avec une fonction de fenêtre, telle que SUM(Amount) OVER (ORDER BY InvoiceDate).
Le verrou les garde. Cliquer sur le verrouiller sur un résultat écrit ses colonnes calculées dans le bloc de verrouillage avec le reste de la configuration de la grille, de sorte qu'elles reviennent à chaque exécution.