离散数据的匹配间隔
LOAD 或 SELECT 语句的 intervalmatch 前缀用于链接离散数值和一个或多个时间间隔数字。此功能十分强大,如可用于生产环境。
Intervalmatch 示例
仔细察看以下两个表格。第一个表格显示了不同订单生产的开始时间和结束时间。第二个表格显示了几件离散事件。我们如何才能将离散事件和这些订单相关联,以便我们了解,例如找出哪些订单受到干扰的影响,哪些订单依据哪次轮班处理?
开始 | 结束 | 排序 |
---|---|---|
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
时间 | 事件 | 注释 |
---|---|---|
00:00 | 0 | 班次 1 开始 |
01:18 | 1 | 生产线停止 |
02:23 | 2 | 生产线重新启动 50% |
04:15 | 3 | 生产线速度 100% |
08:00 | 4 | 班次 2 开始 |
11:43 | 5 | 生产结束 |
通常首先加载两个表格,然后将字段 Time 链接到通过 Start 和 End 字段定义的时间间隔:
SELECT * from OrderLog;
SELECT * from EventLog;
Intervalmatch (Time) SELECT Start,End from OrderLog;
现在,可以在 Qlik Sense 中创建表格,如下所示:
Time | 事件 | 注释 | 排序 | 开始 | End |
---|---|---|---|---|---|
0:00 | 0 | 班次 1 开始 | - | - | - |
1:18 | 1 | 生产线停止 | A | 1:00 | 3:35 |
2:23 | 2 | 生产线重新启动 50% | A | 1:00 | 3:35 |
4:15 | 3 | 生产线速度 100% | B | 2:30 | 7:58 |
4:15 | 3 | 生产线速度 100% | C | 3:04 | 10:.... |
8:00 | 4 | 班次 2 开始 | C | 3:04 | 10:.... |
8:00 | 4 | 班次 2 开始 | D | 7:23 | 11:.... |
11:43 | 5 | 生产结束 | E | 7:23 | 11:..... |
我们现在可以很容易看到订单 A 主要受生产线停止影响,但减缓生产线速度也可能会影响订单 B 和 C。Shift 2 只能处理订单 C 和 D 的一部分。
请在使用 intervalmatch 语句时务必注意以下几点:
- 在使用 intervalmatch 语句之前,必须已将包含离散数据点(上例中的 Time)的字段读取到 Qlik Sense。intervalmatch 语句不会从数据库表格中读取此字段。
- 读入 intervalmatch LOAD 或 SELECT 语句的表格必须始终包含两个完整字段(上例中的 Start 和 End)。要建立与其他字段之间的链接,您必须在单独的 LOAD 或 SELECT 语句(上面示例中的第一个 SELECT 语句)中连同另外字段读取间隔字段。
- 时间间隔始终是封闭区间。即间隔的端点包括在时间间隔之中。非数值限制会导致省略时间间隔(未定义),同时 NULL 限制会无限扩展时间间隔(无限)。
- 时间间隔可以重叠,离散值可以链接所有匹配的时间间隔。
使用扩展的 intervalmatch 语法解决维度变化缓慢问题
扩展的 intervalmatch 语法可以用于处理源数据中著名的维度变化缓慢的问题。
样本脚本:
SET NullInterpret='';
IntervalTable:
LOAD Key, ValidFrom, Team
FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is IntervalTable);
Key:
LOAD
Key,
ValidFrom as FirstDate,
date(if(Key=previous(Key),
previous(ValidFrom) - 1)) as LastDate,
Team
RESIDENT IntervalTable order by Key, ValidFrom desc;
drop table IntervalTable;
Transact:
LOAD Key, Name, Date, Sales
FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is Transact);
INNER JOIN intervalmatch (Date,Key) LOAD FirstDate, LastDate, Key RESIDENT Key;
nullinterpret 语句仅在从表格文件中读取数据时才需要,因为缺失值被定义为空白字符串而不是 NULL 值。
从 IntervalTable 加载数据会生成以下表格:
Key | FirstDate | Team |
---|---|---|
000110 | 2011-01-21 | Southwest |
000110 | - | Northwest |
000120 | - | Northwest |
000120 | 2013-03-05 | Southwest |
000120 | 2013-03-05 | Northwest |
000120 | 2013-03-05 | Southwest |
nullasvalue 语句允许将 NULL 值映射到所列出的字段。
通过使用 previous 和 order by 创建 Key、FirstDate、LastDate(属性字段),然后使用此关键字表格替换已删除的 IntervalTable。
从 Transact 加载数据会生成以下表格:
键 | 名称 | 日期 | 销售额 |
---|---|---|---|
000110 | Spengler Aaron | 2009-08-18 | 100 |
000110 | Spengler Aaron | 2009-12-25 | 200 |
000110 | Spengler Aaron | 2011-02-03 | 300 |
000110 | Spengler Aaron | 2011-05-05 | 400 |
000120 | Ballard John | 2011-06-04 | 500 |
000120 | Ballard John | 2013-01-20 | 600 |
000120 | Ballard John | 2013-03-10 | 700 |
000120 | Ballard John | 2013-03-13 | 800 |
000120 | Ballard John | 2013-09-21 | 900 |
intervalmatch 语句前面的 inner join 语句将使用链接 Transact 表格的合成钥替代上述关键字段,生成以下表格:
键 | 团队 | 名称 | FirstDate | LastDate | 日期 | 销售额 |
---|---|---|---|---|---|---|
000110 | 西北 | Spengler Aaron | - | 2011-01-20 | 2009-08-18 | 100 |
000110 | 西北 | Spengler Aaron | - | 2011-01-20 | 2009-12-25 | 200 |
000110 | 西南 | Spengler Aaron | 2011-01-21 | 2011-02-03 | 300 | |
000110 | 西南 | Spengler Aaron | 2011-01-21 | 2011-05-05 | 400 | |
000120 | 西北 | Ballard John | 2013-01-05 | 2011-06-04 | 500 | |
000120 | 西南 | Ballard John | 2013-01-06 | 2013-03-04 | 2013-01-20 | 600 |
000120 | 西南 | Ballard John | 2013-03-05 | 2013-03-10 | 700 | |
000120 | 西南 | Ballard John | 2013-03-05 | 2013-03-13 | 800 | |
000120 | 西南 | Ballard John | 2013-03-05 | 2013-09-21 | 900 |