Вычисляемые столбцы
функциональныйДобавьте столбец, вычисляемый формулой Excel по столбцам самого результата, — со строкой формул, цветами и функциями Excel.
Если вы умеете писать формулы в Excel, вы сможете добавить столбец к любому результату запроса в SQLly. Маржа, накопительный итог, изменение с прошлого месяца, число рабочих дней между датами: введите формулу, которую написали бы в Excel, — и новый столбец появится рядом с данными. Не нужно знать, как записать её на SQL, и не нужно перезапускать запрос.
Это работает как вычисляемый столбец в таблице Excel: одна формула на весь столбец, которая считается для каждой строки. Единственное новое, чему нужно научиться, — как ссылаться на ячейку, потому что у результата имена столбцов вместо букв.
Добавьте свой первый вычисляемый столбец
- Выполните запрос. Любой результат подходит; например, один с
QtyиPriceстолбцах. - Щёлкните правой кнопкой мыши по заголовку столбца, после которого должен идти новый, и выберите Добавить вычисляемый столбец….
- Над результатами откроется строка формулы: fx, поле Name,
=, и поле формулы. SQLly подставит имя (Computed) и начинает формулу с ссылки на столбец, по которому вы кликнули, например=!Price. - Дайте имя столбцу и введите формулу - например Сумма по строке и
=!Qty * !Price. - Нажмите Enter или щёлкните Добавить столбец. Esc или Отмена закрывает панель, ничего не меняя.


От A1 до !Name
Там, где Excel говорит B7, SQLly напишет !Qty: восклицательный знак и название столбца. Всё остальное в формуле — обычный Excel.
| В Excel (в строке 7) | В SQLly | Означает |
|---|---|---|
=B7*C7 | =!Qty * !Price | Значение Qty из этой строки, умноженное на Price из этой строки. |
=B7-B6 | =!Qty - !Qty[1] | Изменение по сравнению с предыдущей строкой. [2] на две строки выше. |
=B8 | =!Qty[-1] | Следующая строка. |
=B7/B$2 | =!Qty / !Qty[$1] | По сравнению с первой строкой, которая остается на месте в каждой строке. |
=SUM(B5:B7) | =SUM(!Qty[2]:!Qty) | Эта строка и две предыдущие. |
=SUM(C7:E7) | =SUM(!Jan:!Mar) | По столбцам от Jan до Mar. |
=B7*TaxRate | =!Qty * @rate | Именованное значение. @rate читает -- sqlly set или -- sqlly param: значение из запроса. |
Заключите имя столбца с пробелами в двойные кавычки: !"Order Total", или !"Order Total"[1] для предыдущей строки. Имена не различают регистр, поэтому !qty тоже работает.
Функции — из Excel: те же имена, аргументы и результаты: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS и ещё около сотни. То же с операторами, включая & для объединения текста и %. страница справки перечисляет все функции. Одна дополнительная приходит из SQL: COALESCE(a, b, …) возвращает первое непустое значение.
Посмотрите, что читает формула
Excel окрашивает каждую ссылку при вводе формулы и рисует соответствующую рамку вокруг ячейки. SQLly делает то же самое:
- Каждое вхождение получает свой цвет - в том же порядке, что и в Excel: синий, красный, фиолетовый, зелёный, оранжевый и так далее. Ссылка, которую вы используете дважды, сохраняет один цвет. Цветная формула отображается под полем по мере ввода.
- Сетка обводит ячейки, из которых она читает - теми же цветами и подкрашивает эти столбцы. Контуры рисуются по выбранной строке, а если ничего не выбрано — по первой, так что
!Qty[1]явно обводит рамкой ячейку на строку выше. - Ошибки отображаются под полем - например, «Есть ) без соответствующей (» или подсказка записать столбец как
!Name, и Добавить столбец ждёт, пока формула не будет прочитана правильно.
Что происходит, когда вы её применяете
Столбец вычисляется сразу из строк, уже выведенных на экран. В базу данных ничего не отправляется и ничего не выполняется заново, поэтому результат мгновенный, даже если сам запрос был медленным.
SQLly также добавляет формулу в ваш запрос строкой комментария в начале выполненных инструкций:
-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;
Эта строка — то, как столбец возвращается. Запустите запрос завтра, сохраните .sql файл, отправьте его коллеге: столбец будет на месте всегда. База данных игнорирует комментарий, так что запрос выполняется где угодно. Правка одна, так что ⌘Z (CtrlZ на Windows и Linux) возвращает его из запроса.


Как только он появится, вычисляемый столбец — это столбец как любой другой. Сортируйте его, фильтруйте, задавайте числовой формат, окрашивайте его с помощью условного форматирования, или используйте его в сводной таблице.
Чтение и редактирование формулы
- Щелкните любую ячейку в вычисляемом столбце и строка формул показывает её формулу в режиме только для чтения, с теми же цветами и границами. Это самый быстрый способ понять, как получилось число.
- Нажмите «Редактировать формулу» в этой панели или щёлкните правой кнопкой мыши по заголовку столбца и выберите Редактировать формулу…, чтобы изменить её. Кнопка теперь показывает Обновить столбец.
- Применение изменения заменяет строку столбца в запросе, а не добавляет вторую, где бы та ни была, включая блок блокировки. Переименуете столбец в поле «Имя» — строка переименуется вместе с ним.


Формулы, которые стоит украсть
| Столбец | Формула |
|---|---|
| Маржа % | =IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue) |
| Накопительный итог | =!Amount + N(!"Running Total"[1]) (назовите столбец Накопительный итог) |
| Изменение относительно последней строки | =IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1]) |
| Рост % | =IFERROR(!Sales / !Sales[1] - 1, 0) |
| Индекс к первому месяцу | =!Sales / !Sales[$1] |
| Скользящий итог за 3 месяца | =SUM(!Sales[2]:!Sales) |
| Дни на отгрузку | =!ShippedDate - !OrderDate |
| Срок оплаты в конце месяца | =EOMONTH(!InvoiceDate, 1) |
| Дней просрочено | =MAX(0, TODAY() - !DueDate) |
| Понятная метка | =!Customer & ": " & TEXT(!Amount, "$#,##0.00") |
| Лучший контакт | =COALESCE(!Email, !Phone, "none on file") |
| Уровень | =IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze") |
Накопительный итог работает, потому что вычисляемый столбец может считывать собственное значение в предыдущей строке. Формула также может считывать любой вычисляемый столбец, добавленный раньше, в той же строке.
Чем это отличается от электронной таблицы
- Порядок строк определяется запросом.
[1]означает строку, которую база данных отправила перед этой. Для накопительной суммы или изменения по сравнению с прошлым месяцем завершите запрос сORDER BYчтобы строки приходили в нужном вам порядке. Последующая сортировка сетки перемещает значения вместе с их строками, а не пересчитывает их. - Одна формула на столбец. Как в вычисляемом столбце таблицы Excel: формула одна для всех строк, и переопределить отдельную ячейку нельзя.
- Пустые значения — это пустые ячейки. A
NULLиз базы данных считается 0 в арифметических операциях и пустым текстом, как это делает пустая ячейка в Excel. Выше первой строки тоже пусто. - Ошибки выглядят как в Excel. Деление на ноль показывает
#DIV/0!в этой ячейке, неправильно написанная функция#NAME?, и так далее. Оберните формулу вIFERRORтак же, как в Excel. - Тип следует за формулой. Арифметика дат даёт столбец дат, сравнения дают TRUE/FALSE, текстовые функции дают текст. Если
IFможет возвращать либо число, либо"", столбец становится текстовым; возвращайте0вместо этого, чтобы сохранить числовой формат. - Очень большие результаты. Результат, слишком большой, чтобы держать его в памяти, читается с диска постранично, и формулы видят только строки текущей страницы. Поэтому накопительный итог на каждой странице начинается заново.
Хранить вместе с запросом
Формула уже есть в запросе в виде -- sqlly column: строка. Если вы также нажмёте заблокировать перед именем результата SQLly записывает вычисляемые столбцы в блок блокировки вместе с остальными настройками сетки: порядком столбцов, шириной, форматами и цветами.
Перенесите в Excel
Экспортируйте результат в Excel или используйте Открыть в электронной таблице, и каждый вычисляемый столбец становится настоящей формулой Excel. =!Qty * !Price на седьмой строке листа становится =B7*C7, !Qty[1] становится строкой выше, !Qty[$1] становится B$2, и @rate становится именованным значением в книге. Поменяете число в Excel — столбец пересчитается. Подробности на Экспорт в Excel.