sql - 选择连续的数据对

Question

我有一个树木库存测量数据库，其架构如下:

sqlite> .schema mmt
CREATE TABLE mmt (mmtid INTEGER PRIMARY KEY, plot TEXT, mmtDate TEXT, treeno NUMERIC, fork TEXT, diameter NUMERIC, height NUMERIC, cwn NUMERIC, form NUMERIC, thin TEXT, comments TEXT, row NUMERIC, heightflag TEXT);
;

每隔几年测量一次树木属性(直径和高度)。下面是对应于绘图 abc101 中树 1 的数据片段。这棵树在 2000 年、2001 年、2003 年、2006 年和 2009 年进行了测量。fork 字段表示对一棵树进行了多项测量，但不适用于以下数据:

sqlite> select * from mmt where plot = "abc101" and treeno = 1 ;
mmtid|plot|mmtDate|treeno|fork|diameter|height|cwn|form|thin|comments|row|heightflag
45488|abc101|2000-06-23 00:00:00|1|NULL|14.7||2|2|||1|
45497|abc101|2001-07-20 00:00:00|1|NULL|15.6||2|2|||1|
53683|abc101|2003-09-03 00:00:00|1|NULL|17.3||1|2|||1|
62435|abc101|2006-08-22 00:00:00|1|NULL|20.4|19.25|1|1|||1|H
71314|abc101|2009-01-14 00:00:00|1|NULL|24.1|20.4|1|1|||1|H

我想构造数据，以便我可以查看连续的测量值对，例如:

plot|mmt1date|treeno|fork|dbh1|mmt2date|dbh2
abc101|2000-06-23 00:00:00|1|NULL|14.7|2001-07-20 00:00:00|15.6
abc101|2001-07-20 00:00:00|1|NULL|15.6|2003-09-03 00:00:00|17.3

ETC

我确信这是一个相当微不足道的问题。不幸的是，我的 sql 不是它可能的样子，在这个阶段我还没有找到一个可行的解决方案。请任何人都可以就合适的方法提出建议吗？

Answer 1

你需要一个自我加入。不幸的是，sqlite 不支持一些涉及窗口函数的巧妙方法。

SELECT *
FROM mmt m1 JOIN mmt m2
ON m1.plot = m2.plot /* whatever else identifies the tree */
AND m2.mmtDate = 
  (SELECT MIN(mmtDate) FROM mmt m3 
   WHERE m3.plot = m1.plot AND m3.mmtDate > m1.mmtDate);

请注意，使用 ORDER BY m3.mmtDate LIMIT 1 可能会获得更好的性能在子查询中。

如果这是一个大表，我建议为有条目的年份制作一个特殊的查找表，按升序键入，增量为 1。

sequence_number   year
1                 2000
2                 2001
3                 2003 /* etc */

等等

然后尝试

SELECT *
FROM mmt m1 JOIN mmt m2
ON m1.plot = m2.plot /* whatever else identifies the tree */
  JOIN year_list y1 ON year(m1.mmtDate)=y1.year
  JOIN year_list y2 ON year(m2.mmtDate)=y2.year
    AND y1.sequence_number = y2.sequence_number-1;