Вычисляемые столбцы
функциональныйДобавляйте столбцы, записанные как формулы Excel: накопительные итоги, маржи, изменения по сравнению с предыдущей строкой и даты.
Этот column: директива добавляет столбец в результат, вычисляемый по формуле Excel на основе собственных столбцов результата. Язык формул — как в Excel: те же операторы, тот же приоритет, те же функции с теми же аргументами и те же значения ошибок. Отличие одно — как указывается ячейка: столбцы результата названы, а не обозначены буквами, поэтому в ссылке столбец задаётся именем с !.
-- 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;
База данных никогда не видит формулу. SQLly вычисляет столбец по мере поступления строк, и Экспорт в Excel записывает это как активную формулу Excel. Вам не нужно вводить директиву: щелкните правой кнопкой мыши заголовок столбца и выберите Добавить вычисляемый столбец…, и SQLly напишет эту строку за вас. Руководство по вычисляемым столбцам разбирает строку формул.
Синтаксис
-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
| Часть | Значение |
|---|---|
<Name> | Имя нового столбца. Оно может содержать пробелы и знаки препинания, но не =. |
<formula> | Формула Excel. Начальный = необязательно: Excel хочет этого, SQLly нормально в любом случае. |
after=<column> | Новый столбец встанет сразу после этого. Без него столбец попадёт в конец. |
result=<n> | Добавляйте его только в n-й набор результатов пакета, считая с 1. Без него столбец добавляется во все наборы результатов, где есть столбцы, которые он читает. |
A ; в кавычках или в скобках — это часть формулы, поэтому TEXTJOIN("; ", TRUE, !A, !B) работает, как написано. Позже column: строка с тем же именем заменяет предыдущую. Столбец, который уже есть в результате, никогда не заменяется: если запрос возвращает Total, вычисляемый Total пропускается для этого результата.
Ссылки
Строки нумеруются в порядке, в котором их вернул запрос, поэтому добавьте ORDER BY всякий раз, когда формула читает другую строку. Имена столбцов сопоставляются без учёта регистра.
| Пишите | Читает | Как в Excel |
|---|---|---|
!Qty | Количество в текущей строке. | B7 в строке 7 |
!Qty[1] | Количество из предыдущей строки. | B6 |
!Qty[3] | Количество на три строки выше. | B4 |
!Qty[-1] | Количество из следующей строки. | B8 |
!Qty[$1] | Количество из первой строки, на какой бы строке вы ни находились. | B$2 |
!"Order Total" | Столбец, имя которого содержит пробелы или знаки препинания, в двойных кавычках. Работает и со смещением по строкам: !"Order Total"[1]. | |
!Qty[2]:!Qty | Диапазон: Qty в этой строке и в двух предыдущих. | B5:B7 |
!Jan:!Mar | Диапазон по столбцам: все столбцы с Jan по Mar в этой строке, в порядке столбцов результата. | C7:E7 |
- Перед первой строкой пусто.
!Qty[1]в первой строке считывается пустая ячейка, которая учитывается как 0 в арифметике и""в тексте. То же самое относится к!Qty[-1]в последней строке. - Столбец, формула которого обращается к столбцу, которого нет в результате опускается для этого результата, так что одна директива может обслуживать пакет, где наборы результатов имеют разные столбцы.
- Диапазоны указываются внутри функций.
SUM(!Qty[2]:!Qty)допустимо; пустой!Qty[2]:!Qtyкак вся формула является#VALUE!, как в Excel.
Переменные
@name считывает значение, объявленное запросом, так что одно число задаёт и SQL, и формулы:
-- 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значения читаются как числа, если похожи на числа, и как текст в остальных случаях.-- sqlly param:значения сохраняют свой объявленный тип:intиdecimalкак числа,boolкак TRUE или FALSE,dateиdatetimeкак реальные даты, всё остальное как текст. Значение, введённое на панели параметров, имеет приоритет над объявленным значением по умолчанию, иnullсчитывается как пустое.- Необъявленная переменная дает
#NAME?.
Операторы
Те же операторы, что в Excel, — от самой слабой связки к самой сильной:
| Оператор | Значение |
|---|---|
= <> < > <= >= | Сравнение, возвращающее TRUE или FALSE. Текст сравнивается без учёта регистра, поэтому "a" = "A" истина. |
& | Объединяет текст: !First & " " & !Last. |
+ - | Сложение и вычитание. |
* / | Умножение и деление. |
^ | Возведение в степень. |
% | Процент, записанный после числа: 15% равен 0,15. |
- (перед числом) | Отрицание. |
: | Диапазон, между двумя ссылками. |
Причуды Excel перенесены намеренно, чтобы формула давала один и тот же ответ в таблице и в книге: отрицание имеет более высокий приоритет, чем ^, так что -2^2 равно 4; операторы одинакового уровня выполняются слева направо, поэтому 2^3^2 равно 64; и 1+2&3 — это текст "33". Используйте скобки, если есть сомнения. Текст, который читается как число, является числом в арифметике ("3"+4 равно 7), а TRUE считается как 1.
Пустые ячейки, NULL и ошибки
- A
NULLявляется пустой ячейкой. В арифметике это считается за 0, а как""в тексте, так же, как пустая ячейка в Excel.ISBLANK()позволяет отличить пустое значение от нуля. - Ошибки в Excel - и они проходят через формулы так же:
#DIV/0!(деление на ноль),#VALUE!(неподходящий тип значения, как"x"+1),#REF!(столбец, которого нет),#NAME?(неизвестная функция или переменная),#NUM!(число вне диапазона, какSQRT(-1)), и#N/A(нет значения, изNA()илиIFSбез совпадения). Ошибка отображается в ячейке; она никогда не останавливает запрос. - Обрабатывайте их так же, как в Excel:
IFERROR(!Profit / !Units, 0).
Функции
Каждая функция ниже принимает те же аргументы в том же порядке, что и в Excel, и даёт тот же результат. Имя, которое SQLly не знает, даёт #NAME?.
- Логические
IF,IFS,IFERROR,IFNA,AND,OR,NOT,XOR,SWITCH,CHOOSE,TRUE,FALSE, иCOALESCE(см. ниже).- Информация
ISBLANK,ISNUMBER,ISTEXT,ISLOGICAL,ISERROR,ISERR,ISNA,ISEVEN,ISODD,N,T,NA.- Математика и статистика
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.- Текст
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,TIME,TODAY,NOW,YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEKDAY,WEEKNUM,ISOWEEKNUM,EDATE,EOMONTH,DATEDIF,DAYS,DATEVALUE,TIMEVALUE,NETWORKDAYS,WORKDAY.
COALESCE(a, b, …)— это единственное дополнение SQLly, заимствованное из SQL: первый аргумент, который не является пустым или"", иначе последний.COALESCE(!Nickname, !FirstName, "Customer"). В экспорте в Excel это записывается как вложенныеIFы, поскольку в Excel нет такой функции.- Даты работают как в Excel. Столбец даты или времени из базы данных читается как реальная дата, поэтому
!ShippedOn - !OrderedOn— это количество дней,!Due + 30это дата, иEDATE,EOMONTHиDATEDIFведет себя так, как вы ожидаете. TODAY()иNOW()считывает часы вашего компьютера при выполнении запроса.TEXTпринимает коды формата Excel:"#,##0.00","0.0%","000","$#,##0","yyyy-mm-dd","mmm d, yyyy","dddd","h:mm AM/PM","hh:mm:ss".
Примеры
Маржа как доля выручки, с защитой от деления на ноль, размещена после столбца Cost. Дайте ей -- sqlly format: column=Margin %; number=1; style=percent чтобы показать это в процентах:
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
Накопительный итог. Столбец считывает свое собственное значение в строке выше; N() превращает пустое значение над первой строкой в 0:
-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;
Изменение относительно предыдущей строки, в виде суммы и в процентах. У первой строки нет предыдущей, поэтому оба показателя там равны 0, а не вся первая величина или #DIV/0!:
-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)
Каждый месяц как процент от первого месяца:
-- sqlly column: vs First = !Sales / !Sales[$1]
Скользящая сумма и среднее значение последних трёх строк:
-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)
Дни между двумя датами, рабочие дни и дата выполнения в конце следующего месяца:
-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)
Текст, построенный из чисел и дат с TEXT:
-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")
Резервный вариант, когда столбец пуст, и метка уровня:
-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")
Как вычисляются столбцы
- В том порядке, в котором они объявлены. Формула может читать вычисляемый столбец, объявленный выше неё в той же строке, и любой вычисляемый столбец, включая саму себя, в предыдущих строках. Это то, что делает возможным накопительный итог.
- В том порядке, в котором строки поступают.
[1]всегда означает строку, которую база данных отправила перед этой, а не строку выше нее после сортировки сетки. Сортировка или фильтрация сетки перемещает вычисленные значения вместе с их строками; она не пересчитывает их. - Каждый столбец получает тип из своей формулы. Арифметика даёт числа; функции дат и арифметика дат дают даты (так что к ним применимы форматы дат и фильтры по датам); сравнения,
AND,ORиISфункции дают TRUE/FALSE; текстовые функции и&дают текст. ФункцияIFу которого ветви дают разные типы, оказывается текстом, поэтомуIF(…, "", !A - !B)создаёт текстовый столбец; используйте0вместо""чтобы сохранить его числовым. - Какие наборы результатов получают столбец. Каждый набор результатов, где есть все столбцы, которые читает формула, — или только результат n с
result=n. - Форматирование работает так же, как для любого другого столбца.
format:,conditional:,grid:и сводная таблица обрабатывает вычисляемый столбец как если бы его вернула база данных. - Очень большие результаты. Результат, слишком большой для хранения в памяти, подгружается с диска по страницам. Формулы видят только строки, загруженные каждой страницей (плюс небольшой просмотр назад для
[n]ссылок), поэтому накопительный итог начинается заново на каждой странице. Для накопительного итога на длинном наборе поручите вычисление базе данных с помощью оконной функции, напримерSUM(Amount) OVER (ORDER BY InvoiceDate).