Интервалы сопоставления и итеративная загрузка

Префикс Intervalmatch для операторов LOAD или SELECT используется для связывания дискретных числовых значений с одним или несколькими числовыми интервалами. Это очень полезная функция, которая может использоваться, например, в производственных средах.

Использование префикса IntervalMatch()

Основной пример сопоставления интервалов, это когда в одной таблице перечислены числа или даты (события), а в другой — интервалы. Цель — связать две таблицы. В целом, это отношение между множествами, т. е. к интервалу может относиться много дат, а дата может относиться ко многим интервалам. Для решения этой проблемы необходимо создать таблицу пересчета двух исходных таблиц. Это можно сделать несколькими способами.

Самый простой способ для решения этой проблемы в программе Qlik Sense — использовать префикс IntervalMatch() перед оператором LOAD или SELECT. Оператор LOAD/SELECT должен содержать только два поля: From и To, которые используются для определения интервалов. Префикс IntervalMatch() создаст все возможные комбинации загруженных интервалов с ранее загруженным числовым полем, указанным в качестве параметра для префикса.

Выполните следующие действия.

1. Создайте новое приложение и дайте ему имя.
2. Добавьте новый раздел скрипта в Редакторе загрузки данных.
3. Вызовите разделы Events.

4. Под элементом DataFiles в меню справа щелкните Выбрать данные.

5. Загрузите, а затем выберите Events.txt.
6. В окне Выбрать данные из щелкните команду Вставить скрипт.
7. Загрузите, а затем выберите Intervals.txt.
8. В окне Выбрать данные из щелкните команду Вставить скрипт.
9. В скрипте назовите первую таблицу События, а вторую таблицу — Intervals.
   
10. В конце скрипта добавьте оператор IntervalMatch, чтобы создать третью таблицу, которая выполнит пересчет двух первых таблиц:

BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

11. Скрипт должен выглядеть следующим образом:

Events:
LOAD
    EventID,
    EventDate,
    EventAttribute
FROM [lib://DataFiles/Events.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
Intervals:
LOAD
    IntervalID,
    IntervalAttribute,
    IntervalBegin,
    IntervalEnd
FROM [lib://DataFiles/Intervals.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

12. Щелкните команду Загрузить данные.
13. Откройте раздел Просмотр модели данных. Модель данных выглядит так:



Модель данных содержит составной ключ (поля IntervalBegin и IntervalEnd), который станет синтетическим ключом Qlik Sense.

Основные таблицы:

• Таблица Events содержит только по одной записи для каждого события.
• Таблица Intervals содержит только по одной записи для каждого интервала.
• Таблица пересчета, которая содержит только по одной записи для комбинации события и интервала и объединяет две предыдущие таблицы.

Обратите внимание, что событие может принадлежать нескольким интервалам, если интервалы накладываются друг на друга. Также, разумеется, к интервалу могут относиться несколько событий.

Эта модель данных является оптимальной в том смысле, что она нормализована и компактна. Обе таблицы Events и Intervals остались неизменны и содержат исходное число записей. Все вычисления Qlik Sense, выполняемые в этих таблицах, например Count(EventID), будут работать правильно.

Использование цикла While и итеративной загрузки IterNo()

Почти такую же таблицу пересчета можно получить с помощью цикла While с функцией IterNo(), который создает счетные значения между нижней и верхней границами интервала.

Цикл внутри оператора LOAD можно создать с помощью предложения While: Пример.

Такой оператор LOAD будет охватывать каждую введенную запись и выполнять ее загрузку несколько раз до тех пор, пока выражение в предложении While является истинным. Функция IterNo() возвращает значение «1» в первой итерации, «2» во второй итерации и так далее.

У пользователя есть основной ключ для интервалов, IntervalID, поэтому единственным отличием этого скрипта будет способ создания таблицы пересчета:

Выполните следующие действия.

1. Замените существующие операторы Bridgetable следующим скриптом.

BridgeTable:
LOAD distinct * Where Exists(EventDate);
LOAD IntervalBegin + IterNo() - 1 as EventDate, IntervalID
    Resident Intervals
    While IntervalBegin + IterNo() - 1 <= IntervalEnd;

2. Щелкните команду Загрузить данные.
3. Откройте раздел Просмотр модели данных. Модель данных выглядит так:



Обычно лучшим решением является использование трех таблиц, поскольку это позволяет создавать отношения между множествами, т. е. интервалами и событиями. Но часто возникает ситуация, когда пользователь знает, что событие может принадлежать только одному интервалу. В этом случае в таблице пересчета нет необходимости. IntervalID можно сохранить непосредственно в таблице событий. Существует несколько способов, но наиболее удобный — объединить таблицы Bridgetable и Events.

4. Добавьте следующий скрипт в конец вашего скрипта:

Join (Events)
LOAD EventDate, IntervalID
Resident BridgeTable;
 
Drop Table BridgeTable;

5. Щелкните команду Загрузить данные.
6. Откройте раздел Просмотр модели данных. Модель данных выглядит так:

Открытые и закрытые интервалы

Тип интервала (закрытый/открытый) определяется конечными точками, а именно, включены они в интервал или нет.

• Если конечные точки включены в интервал, это закрытый интервал:

[a,b] = {x ∈ ℝ ∣ a ≤ x ≤ b}

• Если конечные точки не включены в интервал, это открытый интервал:

]a,b[ = {x ∈ ℝ ∣ a < x < b}

• Если одна конечная точка включена в интервал, это полуоткрытый интервал:

[a,b[ = {x ∈ ℝ ∣ a ≤ x < b}

Если интервалы накладываются друг на друга, а число может принадлежать нескольким интервалам, необходимо использовать закрытые интервалы.

Тем не менее, бывают ситуации, когда пользователю не нужны накладывающиеся интервалы, и число должно принадлежать только одному интервалу. Следовательно, если точка является одновременно концом одного интервала и началом другого, возникает проблема. Число с этим значением будет принадлежать обоим интервалам. Поэтому в таком случае необходимо использовать полуоткрытые интервалы.

Разумным решением данной проблемы является вычитание очень небольшой суммы из конечного значения всех интервалов, создавая, таким образом, закрытые, но не перекрывающиеся интервалы. Если используемые числа являются датами, самым простым решением будет использование функции DayEnd(), которая возвращает последнюю миллисекунду дня:

Также можно вычесть небольшое количество вручную. При вычитании убедитесь, что вычитаемая сумма не слишком мала, поскольку операция будет округлена до 52 значимых двоичных значений (14 десятичных значений). Если использовать слишком малое количество, разница будет незначительной, поэтому снова будет использоваться исходное число.