引用下一行和上一行值 [英] Referencing next and previous row values

问题描述

我最近正在调查一个问题，即写入某个 dbs 表的行的百分比在不应为空的列中具有空值.当按时间戳列对该表进行排序时，我注意到具有空值的行不是随机分散的，而是大块的.该列有数千行为空，然后是数千行具有有效值，以此类推.

我想调查这些波段的边界发生了什么，其中列从空切换到非空，反之亦然.在这些开关周围找到时间模式或数据似乎对调试问题非常有用.

我想不出让 sql 只选择这些行的方法，所以我最终编写了一个快速程序来处理数据并返回我想要的内容.请参阅下面的伪代码以演示该算法.

resultSet = db.query("select * from table t order by t.timestamp")prevRow = 空//为简单起见，我们假设总是至少有 2 行currentRow = resultSet.next()nextRow = resultSet.next()while(resultSet.hasNext())//如果我们关心的列的空状态在当前行周围发生了变化if(hasNullColumn(prevRow) != hasNullColumn(nextRow)) printRow(currentRow)prevRow = 当前行；currentRow = nextRow;nextRow = resultSet.next();

但是我真的很好奇是否有一种方法可以编写一个只返回由上述逻辑打印的行的 sql 查询?那可能吗?

解决方案

假设有一个唯一的 id 列，有趣的是它不跟踪时间戳列的增加.还假设没有相等的时间戳.

select pt.* from(选择 max(ptime) 作为 prevtime，min(ntime) 作为 nexttime from((Select timestamp as ptime) as prev,(选择时间戳为 ntime)作为下一个其中 prev.ptime 

说明:s1 提供了彼此前后的时间对.pn 将它们分组以获得所有相邻时间对的列表.pt 提供 pn 中前一次的其余列，而 nt 提供下一次的其余列.当我命名为 ncol 的列切换值时，前一行被吐出到结果集中.如果有多个非空值并且只对找到空值和非空值之间的切换感兴趣，那么将 pt.ncol!=nt.ncol 更改为 isnull(pt.ncol)!=isnull(nt.ncol).

I was recently investigating an issue where a percentage of rows written to a certain dbs table had null values in a column that should not be null. When ordering that table by its timestamp column, I noticed that the rows with null values weren't scattered about randomly, but came in large chunks. There were thousands of rows with that column null, followed by thousands of rows with a valid value, and so on in large bands like this.

I wanted to investigate what was happening at the borders of these bands, where the column switched from null to non-null and vice versa. Finding a pattern in the times or data surrounding these switches seemed very useful in debugging the problem.

I couldn't think of a way to have sql select only these rows, so I ended up writing a quick program to process the data and return what I wanted. See the pseudo code below to demonstrate the algorithm.

resultSet = db.query("select * from table t order by t.timestamp")
prevRow = null
// for simplicity, let's assume there's always at least 2 rows
currentRow = resultSet.next()
nextRow = resultSet.next()

while(resultSet.hasNext()) 

    // if the null state of column we care about has changed around this current row
    if(hasNullColumn(prevRow) != hasNullColumn(nextRow)) printRow(currentRow)   

    prevRow = currentRow;
    currentRow = nextRow; 
    nextRow = resultSet.next();

But I was left really curious whether there was a way to write a sql query that will return only the rows printed by the above logic? Is that possible?

解决方案

Assume there is a unique id column and to make things interesting that it doesn't track the increases in timestamp column. Also assume that there are no timestamps that are equal.

select pt.* from
(Select max(ptime) as prevtime,min(ntime) as nextime from
((Select timestamp as ptime) as prev,
(Select timestamp as ntime) as next
where prev.ptime < next.ntime and prev.id<>next.id) as s1 group by ptime, ntime) as pn
inner join 
t as pt on pn.prevtime=pt.timestamp inner join 
t as nt on pn.nexttime=nt.timestamp
where pt.ncol!=nt.ncol;

Explanation: s1 provides pairs of times that are before and after each other. pn groups them to get a list of all adjacent pairs of times. pt provides the rest of the columns for the previous time in pn, and nt provides the rest of the columns for the next time. When the column which I named ncol switches values the prior row gets spit out into the resultset. If there are multiple nonnull values and it was only interesting to find the switches between null and non-null, then change pt.ncol!=nt.ncol to isnull(pt.ncol)!=isnull(nt.ncol).

这篇关于引用下一行和上一行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！