SQL查找两行之间的差异

Question

我有一个 Informix 数据库，其中包含多个不同位置的测量温度值。 所有位置每 15 分钟进行一次测量，然后将时间戳加载到同一个表中。 表格如下所示：

locId    dtg               temp
aaa      2009-02-25 10:00  15
bbb      2009-02-25 10:00  20
ccc      2009-02-25 10:00  24
aaa      2009-02-25 09:45  13
ccc      2009-02-25 09:45  16
bbb      2009-02-25 09:45  18
ddd      2009-02-25 09:45  12
aaa      2009-02-25 09:30  11
ccc      2009-02-25 09:30  14
bbb      2009-02-25 09:30  15
ddd      2009-02-25 09:30  10

现在我想要一个查询，向我显示所有站点最后两次测量之间的温度变化。 而且，只有那些具有更新测量值的。 例如，在上表中，不会包含位置 ddd。 所以结果就变成了：

locId    change
aaa      2
bbb      2
ccc      8

我尝试了很多，但找不到任何好的解决方案。 实际上，网页会询问大约 700 个位置，因此我认为查询需要相当高效。

非常感谢一些帮助！
//杰斯帕

Answer 1

set now = select max(dtg) from table;
set then = select max(dtg) from table where dtg < now;

select locID, old.temp-new.temp from 
      table as old join
      table as new 
      on old.locId = new.locID
where
      old.dtg = then and
      new.dtg = now;

---

假设所有时间都是准确的

Answer 2

感谢uglysmurf 提供SQL 格式的数据。

使用 IDS (IBM Informix Dynamic Server) 版本 11.50，以下查询有效。

CREATE TEMP TABLE temps
(
    locId   CHAR(3),
    dtg     DATETIME YEAR TO MINUTE,
    temp    SMALLINT
);
INSERT INTO temps VALUES ('aaa', '2009-02-25 10:00', 15);
INSERT INTO temps VALUES ('bbb', '2009-02-25 10:00', 20);
INSERT INTO temps VALUES ('ccc', '2009-02-25 10:00', 24);
INSERT INTO temps VALUES ('aaa', '2009-02-25 09:45', 13);
INSERT INTO temps VALUES ('ccc', '2009-02-25 09:45', 16);
INSERT INTO temps VALUES ('bbb', '2009-02-25 09:45', 18);
INSERT INTO temps VALUES ('ddd', '2009-02-25 09:45', 12);
INSERT INTO temps VALUES ('aaa', '2009-02-25 09:30', 11);
INSERT INTO temps VALUES ('ccc', '2009-02-25 09:30', 14);
INSERT INTO temps VALUES ('bbb', '2009-02-25 09:30', 15);
INSERT INTO temps VALUES ('ddd', '2009-02-25 09:30', 10);

SELECT latest.locID, latest.temp, prior.temp,
       latest.temp - prior.temp as delta_temp,
       latest.dtg, prior.dtg
    FROM temps latest, temps prior
    WHERE latest.locId = prior.locId
      AND latest.dtg = prior.dtg + 15 UNITS MINUTE
      AND latest.dtg = (SELECT MAX(dtg) FROM temps);

结果（比请求的列更多，但您可以轻松修剪选择列表）：

aaa 15 13 2 2009-02-25 10:00 2009-02-25 09:45
ccc 24 16 8 2009-02-25 10:00 2009-02-25 09:45
bbb 20 18 2 2009-02-25 10:00 2009-02-25 09:45

请注意，此解决方案不依赖于 CURRENT（或 NOW）； 它适用于最新记录的数据。 SELECT 语句中 IDS 特定的唯一部分是“+ 15 UNITS MINUTE”； 在 Informix 中也可以写为“+ INTERVAL(15) MINUTE T​​O MINUTE”，在标准 SQL 中也可以写为“+ INTERVAL '15' MINUTE”（如果 DBMS支持 INTERVAL 类型）。 表中 DATETIME YEAR TO MINUTE 的使用是 Informix 特定的； 在这样的上下文中，不存储您不感兴趣的信息（例如秒）是有用的。

Answer 3

在伪 SQL 中，您可以执行以下查询：

@now = Time Now

Select Oldest.LocId, Oldest.timestamp, Oldest.temp - Newest.temp as Change
(Select LocId, temp from Foo where timestamp < @now - 15 mins AND timestamp >= @now - 30 mins) Oldest
   left join
(Select LocId, temp from Foo where timestamp >= TimeNow - 15 mins) Newest
   on Oldest.LocId = Newest.LocId

不确定您是否将其定义为“好的”解决方案，但如果每个位置有两个数据点，它应该可以工作。

Answer 4

declare @dt_latest datetime, @dt_prev datetime  

select @dt_latest = max(dtg) from Measures
select @dt_prev = max(dtg) from Measures where dtg < @dt_latest  

select Latest.Locid, Latest.temp - Prev.temp
from Measures as "Latest"
inner join Measures as "Prev" on Latest.Locid = Prev.Locid
where Latest.dtg = @dt_latest
and Prev.dtg = @dt_prev

编辑：基本上和 BCS 一样，打败我！

Answer 5

我不相信 Informix 具有像 Oracle 这样的分析函数，但如果有的话，这将是使用它们的绝佳场所。 以下是使用分析函数 lag 和 max 的 Oracle 示例。

设置脚本：

drop table temps;
create table temps (
locId varchar2(3),
dtg date,
temp number(3)
);

insert into temps values ('aaa', to_date('2009-02-25 10:00','yyyy-mm-dd hh:mi'), 15);
insert into temps values ('bbb', to_date('2009-02-25 10:00','yyyy-mm-dd hh:mi'), 20);
insert into temps values ('ccc', to_date('2009-02-25 10:00','yyyy-mm-dd hh:mi'), 24);
insert into temps values ('aaa', to_date('2009-02-25 09:45','yyyy-mm-dd hh:mi'), 13);
insert into temps values ('ccc', to_date('2009-02-25 09:45','yyyy-mm-dd hh:mi'), 16);
insert into temps values ('bbb', to_date('2009-02-25 09:45','yyyy-mm-dd hh:mi'), 18);
insert into temps values ('ddd', to_date('2009-02-25 09:45','yyyy-mm-dd hh:mi'), 12);
insert into temps values ('aaa', to_date('2009-02-25 09:30','yyyy-mm-dd hh:mi'), 11);
insert into temps values ('ccc', to_date('2009-02-25 09:30','yyyy-mm-dd hh:mi'), 14);
insert into temps values ('bbb', to_date('2009-02-25 09:30','yyyy-mm-dd hh:mi'), 15);
insert into temps values ('ddd', to_date('2009-02-25 09:30','yyyy-mm-dd hh:mi'), 10);
commit;

使用分析函数的 Oracle 特定查询：

select locId, change
  from (
select t.locId,
       t.dtg,
       t.temp,
       -- difference between this records temperature and the record before it 
       t.temp - lag(t.temp) over (partition by t.locId order by t.dtg) change,
       -- max date for this location
       max(t.dtg) over (partition by t.locId) maxDtg,
       max(t.dtg) over (partition by 1) overallMaxDtg
  from temps t
 order by t.locId, t.dtg
 ) where maxDtg = dtg -- only most recent measurement
     and overallMaxDtg = maxDtg -- only stations with an 'updated measurement'
     ;

结果：

LOCID CHANGE

aaa   2
bbb   2
ccc   8

有关 Oracle 分析的良好资源：http:// /www.psoug.org/reference/analytic_functions.html

Answer 6

尝试一下这样的事情。 它可能不是超级高效，但与其他一些答案不同，它会返回每个 LocID 的 diff

SELECT DISTINCT LocID,
            (SELECT max(t3.temp)-min(t3.temp) from 
                   (SELECT TOP 2 T2.temp 
                    From Table2 T2 
                    Where (t2.Locid=t1.locid) 
                    order by DTG DESC) as t3
             ) as Diff
     FROM Table1 T1

警告：我使用 tSQL 编写了此代码，但为了可移植到 Informix，尝试尽可能坚持标准 ANSI SQL。