时间戳

首页 > 解决方案 > 有没有好的方法可以找到与postgresql的另一行之间的差异总和超过阈值的行

sql - 有没有好的方法可以找到与postgresql的另一行之间的差异总和超过阈值的行

问题描述

我正在尝试使用postgresql找到另一行之间的差异总和超过阈值的行。

例如使用下面的脚本，我可以创建测试表。

/*prepare for test table*/
with recursive target_table(time,target,value,flg) as(
    select
        1
        , random()
        , random()
        , false
    union all
    select
        time+1
        , random()
        , random()
        , case when time=9 then true else false end
    from target_table
    where time < 20
)
select *,sum(value) over (order by time) as sum_of_value from target_table;

下表是示例表。

1   0.5955741602068017  0.062468466592012106    false   0.062468466592012106
2   0.9599096994358831  0.2721417844426348  false   0.3346102510346469
3   0.5219622680617206  0.47568365271884616 false   0.8102939037534931
4   0.19330178068888415 0.055219926055169566    false   0.8655138298086626
5   0.7849454445680166  0.869388793530252   false   1.7349026233389147
6   0.3818554639492504  0.6618063662299782  false   2.396708989568893
7   0.14781479428049948 0.2939156384156725  false   2.6906246279845654
8   0.35489557675010985 0.3223190750002196  false   3.012943702984785
9   0.7468542842938106  0.4682370283799777  false   3.4811807313647627
10  0.9827402637131684  0.8947818572002681  false   4.375962588565031
11  0.9795760087001852  0.7735269168553209  false   5.149489505420352
12  0.7201651414850829  0.9190766910614023  false   6.068566196481754
13  0.4675524690241204  0.5993146022105726  false   6.667880798692327
14  0.7946650321278526  0.3035202408540094  false   6.971401039546336
15  0.9985454346140514  0.5523127942270101  true    7.523713833773346
16  0.5638512819364934  0.5059578895998662  false   8.029671723373212
17  0.3087199667465299  0.7731866673265166  false   8.802858390699729
18  0.5435603740732802  0.34633746722375847 false   9.149195857923488
19  0.9042064638800369  0.8654612596373745  false   10.014657117560862
20  0.6421889049801024  0.6367246358906549  false   10.651381753451517

我想找到一个最接近的行，该行在标志为真的第 15 行之间的差异总和超过阈值。

这次如果阈值为 5，我想找到第 15 行的差值总和超过 5 的行。那是第 6 行。因为 7.523713833773346-2.396708989568893=5.1270048442 超过 5 并且距离第 15 位最近。

当然，实际上表中的数据量很大。如何找到适合标志为 true 的行的每一行？

标签： sqlpostgresql

解决方案

这是你想要的？

select max(t.id)
from mytable t
inner join mytable t15 
    on  t15.id = 15
    and t15.sum_of_value - t.sum_of_value > 5

这将为您提供其值与 id 为 15 的记录值相差大于 5 的最大记录 ID。

如果你想要整个记录，那么：

select t.*
from mytable t
inner join mytable t15 
    on  t15.id = 15
    and t15.sum_of_value - t.sum_of_value > 5
order by t1.id desc
limit 1

推荐阅读