Berechnete Spalten

funktional

Fü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>]
TeilBedeutung
<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.

SchreibenLiestWie Excels
!QtyMenge 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]:!QtyEin Bereich: Qty in dieser Zeile und den beiden davor.B5:B7
!Jan:!MarEin 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]:!Qty als 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 set Werte werden als Zahlen gelesen, wenn sie wie eine aussehen, und ansonsten als Text.
  • -- sqlly param: Werte behalten ihren deklarierten Typ bei: int und decimal als Zahlen, bool als TRUE oder FALSE, date und datetime als echte Datumswerte, alles andere als Text. Der in der Parameterleiste eingegebene Wert hat Vorrang vor dem deklarierten Standard, und null wird als leer gelesen.
  • Eine nicht deklarierte Variable ergibt #NAME?.

Operatoren

Dieselben Operatoren wie in Excel, von der lockersten bis zur stärksten Bindung:

OperatorBedeutung
= <> < > <= >=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 NULL ist 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, wie SQRT(-1)), und #N/A (kein Wert, von NA() oder ein IFS ohne 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, und COALESCE (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 verschachtelte IFs 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 - !OrderedOn eine Anzahl von Tagen ist, !Due + 30 ist ein Datum, und EDATE, EOMONTH und DATEDIF verhält sich wie erwartet.
  • TODAY() und NOW() lesen die Uhr deines Computers, wenn die Abfrage läuft.
  • TEXT nimmt 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, OR und die IS Funktionen liefern TRUE/FALSE; Textfunktionen und & liefern Text. Ein IF dessen Zweige unterschiedliche Typen liefern, ist Text, also IF(…, "", !A - !B) macht eine Textspalte; verwende 0 statt "" , 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 Beispiel SUM(Amount) OVER (ORDER BY InvoiceDate).
Die Sperre bewahrt sie auf. Ein Klick auf die sperren auf ein Ergebnis schreibt die berechneten Spalten zusammen mit dem Rest des Raster-Setups in den Lock-Block, sodass sie bei jedem Lauf wiederkommen.