离散数据的匹配间隔

LOADSELECT 语句的 intervalmatch 前缀用于链接离散数值和一个或多个时间间隔数字。此功能十分强大,例如,其可用于生产环境,如下例所示。

Intervalmatch 示例

仔细察看以下两个表格。第一个表格显示了不同订单生产的开始时间和结束时间。第二个表格显示了几件离散事件。我们如何才能将离散事件和这些订单相关联,以便我们了解,例如找出哪些订单受到干扰的影响,哪些订单依据哪次轮班处理?

表格 OrderLog
开始 结束 排序
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 链接到通过 StartEnd 字段定义的时间间隔:

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 主要受生产线停止影响,但减缓生产线速度也可能会影响订单 BCShift 2 只能处理订单 CD 的一部分。

请在使用 intervalmatch 语句时务必注意以下几点:

  • 在使用 intervalmatch 语句之前,必须已将包含离散数据点(上例中的 Time)的字段读取到 Qlik Senseintervalmatch 语句不会从数据库表格中读取此字段!
  • 读入 intervalmatch LOADSELECT 语句的表格必须始终包含两个完整字段(上例中的 StartEnd)。要建立与其他字段之间的链接,您必须在单独的 LOADSELECT 语句(上面示例中的第一个 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 加载数据会生成以下表格:

Table with data loaded from 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 值映射到所列出的字段。

通过使用 previousorder by 创建 KeyFirstDateLastDate(属性字段),然后使用此关键字表格替换已删除的 IntervalTable

Transact 加载数据会生成以下表格:

具有加载自 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 表格的合成钥替代上述关键字段,生成以下表格:

带前置 inner joinintervalmatch 语句的表格
团队 名称 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