Thursday, 29 August 2013

Boundary determination of continuous records with the same classification

确定相同类型(条件)的连续记录区间段的方法

需要处理的问题是,有这样一组连续观测数据,(表名:obstable)
  Id,Value
  1,0
  2,0
  3,0
  4,5
  5,6
  6,15
  7,20
  8,20
  9,5
  10,0
  ...
现在想要知道,从哪个Id开始,到哪个Id为止,Value <= 5;哪个区间,Value > 5等等,类似的确定相同条件的记录,连续区间段的要求。常规的方法,是逐个读入数据序列,采用序贯处理的方法,判断、确定区段起止记录号。这里,尝试用SQL Query直接得到各区间段。(以条件 Value <= 5 为例)

1. 确定区间下界Id;
选取符合条件的记录序列,并排向下错开一个记录号。抓取无匹配的那个记录号,为区间开始记录号(下界Id)。
SELECT Value, AId
  FROM (SELECT Id AS AId, Value FROM obstable WHERE Value <= 5)
  LEFT JOIN (SELECT Id AS BId FROM obstable WHERE Value <= 5) ON AId = BId + 1
  WHERE BId IS NULL ORDER BY AId ASC
2. 确定区间上界Id;
类似的,选取符合条件的记录序列,并排向上错开一个记录号。抓取无匹配的那个记录号,为区间结束记录号(上界Id)。
SELECT Value, AId
  FROM (SELECT Id AS AId, Value FROM obstable WHERE Value <= 5)
  LEFT JOIN (SELECT Id AS CId FROM obstable WHERE Value <= 5) ON AId = CId - 1
  WHERE CId IS NULL ORDER BY AId ASC
3. 同时确定区间上下界Id;
把上述两个查询合并,同时给出区间的下界Id和上界Id。
SELECT Value, AId
  FROM (SELECT Id AS AId, Value FROM obstable WHERE Value <= 5)
  LEFT JOIN (SELECT Id AS BId FROM obstable WHERE Value <= 5) ON AId = BId + 1
  LEFT JOIN (SELECT Id AS CId FROM obstable WHERE Value <= 5) ON AId = CId - 1
  WHERE BId IS NULL OR CId IS NULL ORDER BY AId ASC
4. 潜在的问题和改进方法;
上述查询,当区间中,有多个记录符合条件时,能依次给出下界Id和上界Id。但是,当区间中,只有一个符合条件的记录时,下界Id和上界Id相同。上述查询就只会给出一个Id,而不是给出两个相同Id,分别作为下界Id和上界Id。那么,在查看Id序列的时候,一旦出现单个记录区间和多个记录区间混合的情况,就无法判断哪个是下界Id、哪个是上界Id,从而出现混乱。
为避免出现这种潜在的混乱,有必要再附加上标志,看该Id是否是是下界Id,还是是上界Id,亦或皆是。
SELECT Value, AId, 1-AId+IFNULL(BId,AId) AS isLower, 1+AId-IFNULL(CId,AId) AS isUpper
  FROM (SELECT Id AS AId, Value FROM obstable WHERE Value <= 5)
  LEFT JOIN (SELECT Id AS BId FROM obstable WHERE Value <= 5ON AId = BId + 1
  LEFT JOIN (SELECT Id AS CId FROM obstable WHERE Value <= 5) ON AId = CId - 1
  WHERE BId IS NULL OR CId IS NULL ORDER BY AId ASC

这里,isLower,1:是下界Id,0:不是;isUpper,1:是上界Id,0:不是
5. 使用Union,同时确定区间上下界Id;
用Union的方法,把上述2、3两个查询合并,也能准确给出区间的下界Id和上界Id。遇到单个记录区间,能够分别给出上下界Id。方法简单,只是没啥技巧,傻傻的。
SELECT Value, AId
  FROM (SELECT Id AS AId, Value FROM obstable WHERE Value <= 5)
  LEFT JOIN (SELECT Id AS BId FROM obstable WHERE Value <= 5) ON AId = BId + 1
  WHERE BId IS NULL
UNION ALL
SELECT Value, AId
  FROM (SELECT
 Id AS AIdValue FROM obstable WHERE Value <= 5)
  
LEFT JOIN (SELECT Id AS CId FROM obstable WHERE Value <= 5) ON AId = CId - 1
  WHERE CId IS NULL

ORDER BY AId ASC
实际应用中,可以独立使用Query 1和Query 2,分别获取下界Id和上界Id。也可以用Query 4或5。