Вычисляемые столбцы

функциональный

Добавляйте столбцы, записанные как формулы 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).
Замок сохраняет их. Щелчок по заблокировать на результате записывает свои вычисленные столбцы в блок блокировки вместе с остальной настройкой сетки, поэтому они возвращаются при каждом запуске.