Berechnete Spalten
funktionalFüge Spalten hinzu, die als Excel-Formeln geschrieben sind: laufende Summen, Margen, Änderungen gegenüber der Zeile davor und Datumswerte.
das column: Direktive fügt eine Spalte zum Ergebnis hinzu, berechnet mit einer Excel-Formel aus den eigenen Spalten des Ergebnisses. Die Formelsprache ist die von Excel: dieselben Operatoren, dieselbe Rangfolge, dieselben Funktionen mit denselben Argumenten und dieselben Fehlerwerte. Der einzige Unterschied ist, wie auf eine Zelle verwiesen wird: Ein Ergebnis hat benannte statt mit Buchstaben bezeichnete Spalten, daher benennt eine Referenz die Spalte mit !.
-- 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;
Die Datenbank sieht die Formel nie. SQLly berechnet die Spalte, sobald Zeilen eintreffen, und ein Excel-Export schreibt es als Live-Excel-Formel. Die Direktive musst du nicht eintippen: Klicke mit der rechten Maustaste auf einen Spaltenkopf und wähle Berechnete Spalte hinzufügen…, und SQLly schreibt die Zeile für dich. Die Anleitung zu berechneten Spalten führt dich durch die Formelleiste.
Syntax
-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
| Teil | Bedeutung |
|---|---|
<Name> | Der Name der neuen Spalte. Er darf Leerzeichen und Satzzeichen enthalten, aber nicht =. |
<formula> | Eine Excel-Formel. Das führende = ist optional: Excel verlangt es, SQLly ist in beiden Fällen in Ordnung. |
after=<column> | Platziere die neue Spalte direkt hinter dieser. Ohne diese Angabe landet die neue Spalte am Ende. |
result=<n> | Füge sie nur zum n-ten Ergebnisset des Batches hinzu, gezählt ab 1. Ohne diese Angabe kommt die Spalte in jedes Ergebnisset, das die Spalten enthält, die sie liest. |
A ; innerhalb einer Zeichenfolge in Anführungszeichen oder innerhalb von Klammern gehört zur Formel, also TEXTJOIN("; ", TRUE, !A, !B) funktioniert wie geschrieben. Eine spätere column: Zeile mit demselben Namen ersetzt die frühere. Eine Spalte, die das Ergebnis bereits hat, wird niemals ersetzt: Wenn die Abfrage ein Totalzurückgibt, wird ein berechnetes Total für dieses Ergebnis übersprungen.
Referenzen
Die Zeilen werden in der Reihenfolge nummeriert, in der die Abfrage sie zurückgibt, also füge ein ORDER BY immer dann hinzu, wenn eine Formel eine andere Zeile liest. Spaltennamen werden ohne Beachtung der Groß- und Kleinschreibung abgeglichen.
| Schreiben | Liest | Wie Excels |
|---|---|---|
!Qty | Menge in dieser Zeile. | B7 in Zeile 7 |
!Qty[1] | Menge in der vorherigen Zeile. | B6 |
!Qty[3] | Menge drei Zeilen weiter oben. | B4 |
!Qty[-1] | Menge in der nächsten Zeile. | B8 |
!Qty[$1] | Menge in der ersten Zeile, egal in welcher Zeile man sich befindet. | B$2 |
!"Order Total" | Eine Spalte, deren Name Leerzeichen oder Satzzeichen enthält, in doppelten Anführungszeichen. Funktioniert auch mit Zeilenversätzen: !"Order Total"[1]. | |
!Qty[2]:!Qty | Ein Bereich: Qty in dieser Zeile und den beiden davor. | B5:B7 |
!Jan:!Mar | Ein Bereich über Spalten: jede Spalte von Jan bis Mär in dieser Zeile, in der Spaltenreihenfolge des Ergebnisses. | C7:E7 |
- Vor der ersten Zeile ist leer.
!Qty[1]liest auf der ersten Zeile eine leere Zelle, die in der Arithmetik als 0 zählt, und""als Text. Dasselbe gilt für!Qty[-1]in der letzten Zeile. - Eine Spalte, deren Formel eine Spalte liest, die das Ergebnis nicht hat bleibt in diesem Ergebnis unberücksichtigt, sodass eine Anweisung einen Batch bedienen kann, dessen Ergebnisse unterschiedliche Spalten enthalten.
- Bereiche stehen innerhalb von Funktionen.
SUM(!Qty[2]:!Qty)ist in Ordnung; ein reines!Qty[2]:!Qtyals ganze Formel ist#VALUE!, wie in Excel.
Variablen
@name liest einen Wert, den die Abfrage deklariert, sodass eine Zahl sowohl das SQL als auch die Formeln steuern kann:
-- 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 setWerte werden als Zahlen gelesen, wenn sie wie eine aussehen, und ansonsten als Text.-- sqlly param:Werte behalten ihren deklarierten Typ bei:intunddecimalals Zahlen,boolals TRUE oder FALSE,dateunddatetimeals echte Datumswerte, alles andere als Text. Der in der Parameterleiste eingegebene Wert hat Vorrang vor dem deklarierten Standard, undnullwird als leer gelesen.- Eine nicht deklarierte Variable ergibt
#NAME?.
Operatoren
Dieselben Operatoren wie in Excel, von der lockersten bis zur stärksten Bindung:
| Operator | Bedeutung |
|---|---|
= <> < > <= >= | Vergleich, liefert TRUE oder FALSE. Text wird ohne Berücksichtigung der Groß-/Kleinschreibung verglichen, daher "a" = "A" ist TRUE. |
& | Verkettet Text: !First & " " & !Last. |
+ - | Addieren und subtrahieren. |
* / | Multiplizieren und dividieren. |
^ | Potenz. |
% | Prozent, hinter einer Zahl geschrieben: 15% ist 0,15. |
- (vorne) | Negation. |
: | Bereich, zwischen zwei Bezügen. |
Die Eigenheiten von Excel werden absichtlich übernommen, damit eine Formel im Raster und in der Arbeitsmappe dasselbe Ergebnis liefert: Negation bindet stärker als ^, sodass -2^2 ist 4; Operatoren gleichen Rangs arbeiten von links nach rechts, also 2^3^2 ist 64; und 1+2&3 ist der Text "33". Setze im Zweifel Klammern. Text, der wie eine Zahl aussieht, ist in der Arithmetik eine Zahl ("3"+4 ist 7), und TRUE zählt als 1.
Leere Zellen, NULL-Werte und Fehler
- A
NULList eine leere Zelle. Es zählt in der Arithmetik als 0 und als""im Text, wie eine leere Zelle in Excel.ISBLANK()unterscheidet einen leeren Wert von einer Null. - Fehler sind die von Excel - und sie fließen durch Formeln auf die gleiche Weise:
#DIV/0!(Division durch Null),#VALUE!(die falsche Art von Wert, wie"x"+1),#REF!(eine Spalte, die nicht existiert),#NAME?(eine unbekannte Funktion oder Variable),#NUM!(eine Zahl außerhalb des zulässigen Bereichs, wieSQRT(-1)), und#N/A(kein Wert, vonNA()oder einIFSohne Treffer). In der Zelle erscheint ein Fehler; die Abfrage wird dadurch nie angehalten. - Fange sie wie in Excel ab:
IFERROR(!Profit / !Units, 0).
Funktionen
Jede Funktion unten nimmt dieselben Argumente in derselben Reihenfolge wie in Excel und liefert dasselbe Ergebnis. Ein Name, den SQLly nicht kennt, ergibt #NAME?.
- Logisch
IF,IFS,IFERROR,IFNA,AND,OR,NOT,XOR,SWITCH,CHOOSE,TRUE,FALSE, undCOALESCE(siehe unten).- Information
ISBLANK,ISNUMBER,ISTEXT,ISLOGICAL,ISERROR,ISERR,ISNA,ISEVEN,ISODD,N,T,NA.- Mathematik und Statistik
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.- Text
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.- Datum und Uhrzeit
DATE,TIME,TODAY,NOW,YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEKDAY,WEEKNUM,ISOWEEKNUM,EDATE,EOMONTH,DATEDIF,DAYS,DATEVALUE,TIMEVALUE,NETWORKDAYS,WORKDAY.
COALESCE(a, b, …)ist SQLlys einzige Ergänzung, übernommen aus SQL: das erste Argument, das weder leer noch"", sonst das letzte.COALESCE(!Nickname, !FirstName, "Customer"). In einem Excel-Export wird es als verschachtelteIFs geschrieben, da Excel keine solche Funktion hat.- Datumsangaben funktionieren wie in Excel. Eine Datums- oder Uhrzeitspalte aus der Datenbank wird als echtes Datum gelesen, sodass
!ShippedOn - !OrderedOneine Anzahl von Tagen ist,!Due + 30ist ein Datum, undEDATE,EOMONTHundDATEDIFverhält sich wie erwartet. TODAY()undNOW()lesen die Uhr deines Computers, wenn die Abfrage läuft.TEXTnimmt die Formatcodes von Excel:"#,##0.00","0.0%","000","$#,##0","yyyy-mm-dd","mmm d, yyyy","dddd","h:mm AM/PM","hh:mm:ss".
Beispiele
Die Marge als Anteil am Umsatz, gegen Null abgesichert, hinter der Spalte Kosten platziert. Gib ihr -- sqlly format: column=Margin %; number=1; style=percent an, um sie als Prozentwert anzuzeigen:
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
Eine laufende Summe. Die Spalte liest ihren eigenen Wert aus der Zeile davor; N() wandelt den leeren Wert über der ersten Zeile in 0 um:
-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;
Änderung gegenüber der vorherigen Zeile, sowohl als Betrag als auch als Prozentsatz. Die erste Zeile hat davor nichts, daher zeigen beide dort 0 an, anstatt den gesamten ersten Wert oder ein #DIV/0!:
-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)
Jeder Monat als Prozentsatz des ersten Monats:
-- sqlly column: vs First = !Sales / !Sales[$1]
Eine rollierende Summe und ein Durchschnitt der letzten drei Zeilen:
-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)
Tage zwischen zwei Daten, Arbeitstage und ein Fälligkeitsdatum am Ende des nächsten Monats:
-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)
Text, erstellt aus Zahlen und Daten mit TEXT:
-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")
Eine Rückfalloption, wenn eine Spalte leer ist, und ein Stufenlabel:
-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")
Wie Spalten berechnet werden
- In der Reihenfolge, in der sie deklariert werden. Eine Formel kann eine berechnete Spalte lesen, die in derselben Zeile weiter vorn deklariert ist, sowie jede berechnete Spalte, einschließlich sich selbst, aus früheren Zeilen. Das macht eine laufende Summe möglich.
- In der Reihenfolge, in der die Zeilen eintreffen.
[1]meint immer die Zeile, die die Datenbank vor dieser geliefert hat, nicht die Zeile darüber, nachdem du das Raster sortiert hast. Sortieren oder Filtern des Rasters verschiebt die berechneten Werte mit ihren Zeilen; sie werden nicht neu berechnet. - Jede Spalte erhält einen Typ aus ihrer Formel. Arithmetik liefert Zahlen; Datumsfunktionen und Datumsarithmetik liefern Datumswerte (damit Datumsformate und Datumsfilter auf ihnen funktionieren); Vergleiche,
AND,ORund dieISFunktionen liefern TRUE/FALSE; Textfunktionen und&liefern Text. EinIFdessen Zweige unterschiedliche Typen liefern, ist Text, alsoIF(…, "", !A - !B)macht eine Textspalte; verwende0statt"", um sie numerisch zu halten. - Welche Ergebnismengen die Spalte erhalten. Jede Ergebnismenge, die alle Spalten enthält, die die Formel liest, oder nur Ergebnis n mit
result=n. - Formatierung funktioniert wie bei jeder anderen Spalte.
format:,conditional:,grid:und die Pivot-Tabelle behandeln eine berechnete Spalte wie eine, die von der Datenbank zurückgegeben wurde. - Sehr große Ergebnisse. Ein zu großes Ergebnis, um es im Speicher zu halten, wird von der Festplatte paginiert. Formeln sehen dann nur die Zeilen, die mit jeder Seite geladen werden (plus ein kurzer Rückblick für
[n]Verweise), sodass eine laufende Summe bei jeder Seite neu beginnt. Für eine durchgehende laufende Summe lass die Datenbank sie mit einer Fensterfunktion berechnen, wie zum BeispielSUM(Amount) OVER (ORDER BY InvoiceDate).