sql—如何只筛选多个相交范围中的一个

ugmeyewa 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(365)

作为一个更长更复杂的查询的一部分，我尝试只保留一个重叠间隔的条目，以及所有不重叠的条目。下面是一个简单的例子：

create table protein (
    seqid varchar(100),
    start SMALLINT(5),
    `end` SMALLINT(5),
    cutoff FLOAT(5,4),
    seq_region TEXT
);

insert into protein (seqid, start, `end`, cutoff, seq_region) values ("A0MZ66", 280, 290, 0.75, "RIQHQQKVKEL");
insert into protein (seqid, start, `end`, cutoff, seq_region) values ("A0MZ66", 314, 556, 0.75, "EEDKKELELKYQNSEEKARNLKHSVDELQKRVNQSENSVPPPPPPPPPLPPPPPNPIRSLMSMIRKRSHPSGSGAKKEKATQPETTEEVTDLKRQAVEEMMDRIKKGVHLRPVNQTARPKTKPESSKGCESAVDELKGILGTLNKSTSSRSLKSLDPENSETELERILRRRKVTAEADSSSPTGILATSESKSMPVLGSVSSVTKTALNKKTLEAEFNSPSPPTPEPGEGPRKLEGCTSSKVT");
insert into protein (seqid, start, `end`, cutoff, seq_region) values ("A0MZ66", 356, 406, 1.0, "PPPPPPLPPPPPNPIRSLMSMIRKRSHPSGSGAKKEKATQPETTEEVTDLK");

SELECT *  from protein;

A0MZ66|280|290|0.75|CCCCCC
A0MZ66|314|556|0.75|ABCDEFG
A0MZ66|356|406|1.0|ABCD

条目2和条目3具有相同的id和重叠范围（从一个开始和结束包含在另一个中），但不同 cutoff 以及 seq_region . 条目#3实际上是条目#2的子字符串。我不能将以下条件放入sql中：
如果来自同一seqid的两个范围重叠，则选择得分==0.75的范围（或最长的sequ区域，因为这些属性是绑定在一起的）
所需输出应为条目#1和#2：

A0MZ66|280|290|0.75|RIQHQQKVKEL
A0MZ66|314|556|0.75|EEDKKELELKYQNSEEKARNLKHSVDELQKRVNQSENSVPPPPPPPPPLPPPPPNPIRSLMSMIRKRSHPSGSGAKKEKATQPETTEEVTDLKRQAVEEMMDRIKKGVHLRPVNQTARPKTKPESSKGCESAVDELKGILGTLNKSTSSRSLKSLDPENSETELERILRRRKVTAEADSSSPTGILATSESKSMPVLGSVSSVTKTALNKKTLEAEFNSPSPPTPEPGEGPRKLEGCTSSKVT

如何将其作为sql查询？重叠条件可以假设一个间隔始终包含在另一个间隔中（开始或结束可以相同）。如果重要的话，它是一个sqlite3数据库。
我想我需要做一些自我内在的连接，或者按操作分组，但我不能完全正确。我将非常感谢你的意见。

sql sqlite Intervals genome genomicranges

来源：https://stackoverflow.com/questions/63192691/how-to-filter-for-only-one-of-many-intersecting-ranges

2条答案

按热度按时间

xt0899hw1#

你可以用 NOT EXISTS :

select p.* from protein p
where not exists (
  select 1 from protein
  where seqid = p.seqid and cutoff <> p.cutoff and seq_region <> p.seq_region
  and seq_region like '%' || p.seq_region || '%'
)

请看演示。
或者如果你想使用列 start 以及 end 要获得重叠间隔：

select p.* from protein p
where not exists (
  select 1 from protein
  where seqid = p.seqid and cutoff <> p.cutoff and seq_region <> p.seq_region
  and start <= p.start and end >= p.end and (end - start) > (p.end - p.start)
)

请看演示。
结果：

| seqid  | start | end | cutoff | seq_region                                                                                                                                                                                                                                          |
| ------ | ----- | --- | ------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| A0MZ66 | 280   | 290 | 0.75   | RIQHQQKVKEL                                                                                                                                                                                                                                         |
| A0MZ66 | 314   | 556 | 0.75   | EEDKKELELKYQNSEEKARNLKHSVDELQKRVNQSENSVPPPPPPPPPLPPPPPNPIRSLMSMIRKRSHPSGSGAKKEKATQPETTEEVTDLKRQAVEEMMDRIKKGVHLRPVNQTARPKTKPESSKGCESAVDELKGILGTLNKSTSSRSLKSLDPENSETELERILRRRKVTAEADSSSPTGILATSESKSMPVLGSVSSVTKTALNKKTLEAEFNSPSPPTPEPGEGPRKLEGCTSSKVT |

赞(0）回复(0）举报 2021-07-27

zqry0prt2#

这是一个“缺口和孤岛”问题。首先，您需要标识同一组中的行，然后根据您的条件从每一行中选择一行。例如，您可以解析此查询，如下所示：

with
y as (
  select *,
    sum(st) over(partition by seqid order by start, end) as grp
  from (
    select *,
      case when start > 
             max(`end`) 
               over(partition by seqid 
               order by start, end
               rows between unbounded preceding and 1 preceding) 
           then 1 else 0 end as st
    from protein
  ) x
),
z as (
  select *,
    row_number() over(partition by seqid, grp 
      order by case when cutoff = 0.75 then 1 else 2 end,
               length(seq_region) desc) as rn
  from y
)
select * from z where rn = 1

结果：

seqid   start  end  cutoff  seq_region    st  grp  rn 
------- ------ ---- ------- ------------- --- ---- -- 
A0MZ66  280    290  0.75    RIQHQQKVKEL   0   0    1  
A0MZ66  314    556  0.75    EEDKKELELK... 1   1    1

参见db fiddle的运行示例。

赞(0）回复(0）举报 2021-07-27

我来回答

sql—如何只筛选多个相交范围中的一个

2条答案

相关问题

热门标签

最新问答