Перейти к содержанию

Offset

Предупреждение

Функция Offset в настоящее время недоступна для MySQL/MariaDB, MongoDB и Druid.

Функция Offset возвращает значение выражения из другой строки. Offset может использоваться только в блоке "Суммировать" конструктора запросов (вы не можете использовать Offset для создания произвольного столбца).

Синтаксис: Offset(выражение, смещение-относительно-строки)

выражение — это значение, которое нужно получить из другой строки.

смещение-относительно-строки — это число относительно текущей строки. Например, -1 для предыдущей строки или 1 для следующей строки.

Пример: Offset(Sum([Total]), -1) получит значение Sum([Total]) из предыдущей строки.

Порядок группировок имеет значение

Поскольку Offset ссылается на другие строки, порядок группировок имеет значение (группировки — это группы в разделе "Суммировать" в блоке суммирования). Glarus BI будет сортировать по первой группе, а затем разделять по любым дополнительным группировкам. Например, если вы хотите увидеть количество заказов по категориям продуктов за время и количество по категориям продуктов за предыдущий период, вы должны сначала группировать по Дата создания, а затем по категории продукта.

Offset не учитывает отсутствующие данные

Offset может ссылаться только на строки, которые у вас есть, что означает, что он может давать правильные, но неожиданные результаты. Например, вы хотите сравнить количество для каждого дня с предыдущим днём.

Возьмите следующую таблицу, где столбец Предыдущий создан выражением Offset(Count, -1):

Дата Количество Предыдущий
1-окт-24 6
2-окт-24 3 6
4-окт-24 2 3

Значение в столбце Предыдущий для 4 октября равно 3, это значение предыдущей строки. На основе этих данных противоречий не видно, но предыдущей строкой для 4 октября могло быть 3 октября, которого здесь нет. Если вы хотите использовать offset для сравнения предыдущих дней (недель или чего-либо ещё), вам нужно убедиться, что ваши данные включают строку для каждой точки данных, которую вы хотите сравнить. В данном случае ваши данные должны включать строку для каждого дня, включая дни, где количество было равно нулю. Если в ваших данных отсутствуют даты, вы можете объединить таблицу календаря с вашими данными, чтобы убедиться, что каждый день представлен строкой в ваших данных.

Типы данных

Функция Offset возвращает любое значение, которое находится в смещённой строке.

Тип данных (документация Metabase, англ.) Возвращается Offset
Строка ✅
Число ✅
Временна́я метка ✅
Логический ✅
JSON ✅

Пример сравнения временны́х рядов год к году с использованием Offset

В образце базы данных вы можете использовать Offset для сравнения количества заказов год к году (YoY).

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

Offset(Sum([Total]), -1)

Затем группируйте результаты по полю Дата создания с разбивкой по годам:

Сравнение год к году

Что это даёт:

Сумма итогов заказов год к году

С этими смещениями (суммы в столбце "Предыдущий период") мы можем затем создать произвольные столбцы для вычисления разности между годовыми суммами:

[Sum of total] - [Previous period]

И процентное изменение:

[Difference] / [Previous period] * 100

Разность и процентное изменение

Пример скользящего среднего с использованием Offset

Вы можете использовать пользовательское выражение с Offset для вычисления скользящего среднего.

Например, допустим, вы хотите вычислить скользящую среднюю сумму итогов заказов за последние три месяца. Вы можете создать пользовательское выражение для вычисления:

(Sum([Total]) + Offset(Sum([Total]), -1) + Offset(Sum([Total]), -2)) / 3

Приведённое выше выражение складывает итог этого периода плюс итоги за предыдущие два периода (смещение на -1 и -2), а затем делит на три, чтобы получить среднее значение по этим периодам.

Скользящее среднее

Связанные функции

SQL

Функция Offset сравнима с оконными функциями SQL LAG и LEAD.

Например, если вы пытаетесь создать линейную диаграмму с двумя сериями для сравнения количества заказов этого месяца с количеством заказов предыдущего месяца, вы бы подсчитали (count) заказы за этот месяц, а затем использовали выражение offset для подсчёта заказов предыдущего месяца:

Offset(count, -1)

"Под капотом" Glarus BI переведёт это выражение Offset в оконную функцию LAG, как в этом запросе:

SELECT
  "source"."CREATED_AT" AS "CREATED_AT",
  COUNT(*) AS "count",
  LAG(COUNT(*), 1) OVER (

ORDER BY
      "source"."CREATED_AT" ASC
  ) AS "Order count previous period"
FROM
  (
    SELECT
      DATE_TRUNC('month', "PUBLIC"."ORDERS"."CREATED_AT") AS "CREATED_AT"
    FROM
      "PUBLIC"."ORDERS"
  ) AS "source"
GROUP BY
  "source"."CREATED_AT"
ORDER BY
  "source"."CREATED_AT" ASC

Дополнительная литература