时间戳

首页 > 解决方案 > 你能解决 SQL 中元素链的最大间隙吗？

sql - 你能解决 SQL 中元素链的最大间隙吗？

问题描述

我有一个困难的查询，我必须在 SQL（postgressql）中进行。我试图解释下面的问题。

我有一个元素链，每个元素都与下一个有最大间隙。所以我想计算“距离”矩阵。所以采取以下4个元素：

example_id,id,max_gap
0,0,2
0,1,5
0,2,
0,3,4

那么对于这个例子，每个元素之间的 max_gap 应该如下

example_id,id,max_gap
0,0,0,0
0,0,1,2
0,0,2,7
0,0,3,
0,1,0,-2
0,1,1,0
0,1,2,5
0,1,3,
0,2,0,-7
0,2,1,-5
0,2,2,0
0,2,3,
0,3,0,
0,3,1,
0,3,2,
0,3,3,0

因此，如果两个元素之间的任何元素的 max_gap 为无穷大，则两个元素之间的 max_gap 为无穷大。

挑战是在 SQL 中解决这个问题（因为需要在 sql 触发器中有这个问题）。

以下 Python 代码可用于创建 test_cases：

from random import randint, random
from itertools import groupby 
n_examples = 100

def generate_examples(n):
    out = []
    for i in range(n):
        for j in range(randint(1,10)):
            max_dist = randint(0,10)
            if random()>0.75:
                max_dist = None
            out.append([i,j,max_dist])
    return out

def max_dist_between_all(example):
    example_id = example[0][0]
    n=len(example)
    return [(example_id,i,j,calc_dist(i,j,example)) for i in range(n) for j in range(n)]

def calculate_max_dist_between_all_examples(examples):
    return [result
            for _, example in groupby(examples, lambda x:x[0])  
        for result in max_dist_between_all(list(example))
      
    ]
    
def calc_dist(i,j,example):
    if j<i:
        i,j = j,i
        sign =-1
    else:
        sign=1
    max_dist = 0
    for k in range(i,j):
        max_dist_between_step = example[k][2]
        if max_dist_between_step is None:
            return None
        max_dist+=max_dist_between_step
    return sign*max_dist

examples =generate_examples(n_examples)        

def print_in_csv(input_, headers):
    print(",".join(headers))
    print("\n".join([",".join(str(e) if e is not None else "" for e in l) for l in input_]))
print_in_csv(examples, ["example_id","id","max_gap"])
print()
print_in_csv(calculate_max_dist_between_all_examples(examples), ["example_id","id","max_gap"])

标签： sqlpostgresql

解决方案

你只是想要一个自我加入吗？

select e1.example_id, e1.id, e2.id, e1.max_gap - e2.max_gap
from elements e1 join
     elements e2
     on e1.example_id = e2.example_id

推荐阅读