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

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

Добавьте столбец, вычисляемый формулой Excel по столбцам самого результата, — со строкой формул, цветами и функциями Excel.

Если вы умеете писать формулы в Excel, вы сможете добавить столбец к любому результату запроса в SQLly. Маржа, накопительный итог, изменение с прошлого месяца, число рабочих дней между датами: введите формулу, которую написали бы в Excel, — и новый столбец появится рядом с данными. Не нужно знать, как записать её на SQL, и не нужно перезапускать запрос.

Это работает как вычисляемый столбец в таблице Excel: одна формула на весь столбец, которая считается для каждой строки. Единственное новое, чему нужно научиться, — как ссылаться на ячейку, потому что у результата имена столбцов вместо букв.

Добавьте свой первый вычисляемый столбец

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

От 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) возвращает его из запроса.

SQLly
Результат с двумя вычисляемыми столбцами — «Сумма по строке» и «Накопительный итог» — рядом со столбцами, из которых они считаются, и строки столбцов sqlly вверху запроса.Результат с двумя вычисляемыми столбцами — «Сумма по строке» и «Накопительный итог» — рядом со столбцами, из которых они считаются, и строки столбцов sqlly вверху запроса.
Результат с двумя вычисляемыми столбцами — «Сумма по строке» и «Накопительный итог» — рядом со столбцами, из которых они считаются, и строки столбцов sqlly вверху запроса.

Как только он появится, вычисляемый столбец — это столбец как любой другой. Сортируйте его, фильтруйте, задавайте числовой формат, окрашивайте его с помощью условного форматирования, или используйте его в сводной таблице.

Чтение и редактирование формулы

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

Формулы, которые стоит украсть

СтолбецФормула
Маржа %=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.