sql从表1中查找不在表2中或表2中有条件的记录

hvvq6cgz 于 2021-06-26 发布在 Impala

关注(0)|答案(4)|浏览(413)

我看到了许多关于如何查找不在另一个表中的记录的示例，但是查找不在表2中或在表2中但freq column值小于10%的记录时遇到了很多问题。
我首先加入一个变种的列表，其中包含brca1，brca2的ensembl基因名，以及任何以brc开头的基因，其中一个变种位于起始和终止位置之间。
从这些结果中，我想检查kaviar等位基因频率（k）并返回结果，这些结果要么在kaviar表中没有条目，要么在kaviar表中alle\u freq<.10的结果。第一次连接的结果需要通过chr、pos、ref和alt与kaviar匹配。
我试过：

SELECT DISTINCT *
FROM puzz p, ensembl ens, kaviar k
WHERE (ens.gene_name IN ('BRCA1', 'BRCA2')
   OR ens.gene_name LIKE 'RAS%')
AND p.chr = ens.chromosome
AND p.pos >= ens.start AND p.pos <= ens.stop
AND NOT EXISTS
 (SELECT k.chromosome, k.pos, k.ref, k.alt, k.alle_freq, k.alle_cnt
  FROM public_hg19.kaviar k
  WHERE  p.chr = k.chromosome
  AND p.pos = k.pos
  AND p.ref = k.ref
  AND p.alt = k.alt
  )
  AND p.pos = k.pos
  AND p.ref = k.ref
  AND p.alt = k.alt
  AND k.alle_freq < .10

我也试过：

WITH puzz AS (
SELECT * 
FROM puzz p
WHERE p.gt IS NOT NULL
)

SELECT DISTINCT t1.*, kav.*
FROM
(SELECT puzz.*, ens.*
FROM puzz, public_hg19.ensembl_genes AS ens
WHERE (ens.gene_name IN IN ('BRCA1', 'BRCA2')
   OR ens.gene_name LIKE 'RAS%')
AND puzz.chr = ens.chromosome
AND puzz.pos BETWEEN ens.start AND ens.stop
AND ens.chromosome NOT LIKE "H%") t1

LEFT JOIN  

public_hg19.kaviar as kav
ON kav.chromosome = t1.chr
AND kav.pos = t1.pos
AND kav.ref = t1.ref
AND kav.alt = t1.alt
AND (kav.alle_freq < .10 OR kav.alle_freq IS NULL)

解决方案：感谢@johnbollinger为解决方案提供了框架。
因为impala不编制索引，所以最快的解决方案是创建一个临时表，缩小传递给字符串操作的行数，如enstemp表所示。

WITH ens AS (
 SELECT DISTINCT chromosome as chr, start, stop, gene_name
   FROM public_hg19.ensembl_genes
  WHERE (gene_name IN ( 'BRCA1', 'BRCA2')
     OR gene_name LIKE 'RAS%')
    AND chromosome NOT LIKE "H%"
)
SELECT p.*, k.chromosome, k.pos, k.id, k.ref, k.alt,
       k.qual, (k.alle_freq * 100) as kav_freqPct, k.alle_cnt as kav_count
  FROM
(SELECT DISTINCT p.sample_id, p.chr, p.pos, p.id,
                 p.ref, p.alt, p.qual, p.filter,
                 ens.gene_name
  FROM ens, p7_ptb.itmi_102_puzzle p
  WHERE p.chr = ens.chr
  AND p.gt IS NOT NULL
  AND p.pos >= ens.start AND p.pos <= ens.stop
) AS p
LEFT JOIN public_hg19.kaviar k
      ON p.chr = k.chromosome
      AND p.pos = k.pos
      AND p.ref = k.ref
      AND p.alt = k.alt
WHERE COALESCE(k.alle_freq, 0.0) < .10

正如@gordon linoff所指出的，下面的一行也可能是

WHERE (k.alle_freq IS NULL OR k.alle_freq < 0.10)

两个final子句返回相同的结果，但在impala上，coalesce函数的速度更快。

sql mysql impala left-join exists

来源：https://stackoverflow.com/questions/29997704/sql-find-records-from-table-1-that-are-not-in-table-2-or-in-table-2-with-conditi

4条答案

按热度按时间

0kjbasz61#

首先修复基本查询以使用适当的 join 语法和简化逻辑 IN 例如，没有必要）。

SELECT DISTINCT *
FROM puzz p JOIN
     ensembl ens
     ON p.chr = ens.chromosome AND
        p.pos >= ens.start AND p.pos <= ens.stop
WHERE ens.gene_name LIKE 'BRC%'

我怀疑 DISTINCT 不需要。如果不是，请将其拆下。然后，使用 LEFT JOIN 以及你想要的附加逻辑：

SELECT DISTINCT *
FROM puzz p JOIN
     ensembl ens
     ON p.chr = ens.chromosome AND
        p.pos >= ens.start AND p.pos <= ens.stop LEFT JOIN
     kaviar k
     ON p.chr = k.chromosome AND
        p.pos = k.pos AND
        p.ref = k.ref AND
        p.alt = k.alt
WHERE ens.gene_name LIKE 'BRC%' AND
      (k.chr IS NULL OR k.alle_freq < 0.10)

注意：这是您使用 left join ，但第二个表中的条件 WHERE 子句而不是 ON 条款。

赞(0）回复(0）举报 2021-06-26

zzzyeukh2#

你能这么做吗？

select * from `mytables`
where not exists 
(select * from `kaviar`) 
or in (select * from `kaviar` where alle_freq < .1)

赞(0）回复(0）举报 2021-06-26

wgxvkvu93#

你提出的两个问题似乎不相符。表名不同，有些过滤条件根本不相关。尤其是，这种情况从何而来 AND ens.chromosome NOT LIKE "H%" （不正确的引用）？
我确实认为您的外部连接方法很有前途，但我不明白为什么您需要cte或内联视图。
另外，“任何以‘brc’开头的基因”包括‘brca1’和‘brca2’，所以你不需要分别测试它们。删除冗余条件可能会稍微提高性能。
此外，如果碰巧您的数据结构会排除重复的行，那么显式地选择 DISTINCT 争吵无助于你，但可能会伤害你(尽管如此，我还是按照你的建议，把它包括在我建议的查询中。）如果有很多结果的话 SELECT DISTINCT 价格昂贵；尤其是当你选择了很多列的时候。
这似乎准确地表达了您所描述的查询：

SELECT DISTINCT
  p.sample_id, p.chr, p.pos, p.ref,
  p.alt, p.gt, p.qual, p.filter
FROM
  p7_ptb.itmi_102_puzzle p
  join public_hg19.ensembl_genes ens
    ON p.chr = ens.chromosome
  left join public_hg19.kaviar k
    ON p.chr = k.chromosome
      AND p.pos = k.pos
      AND p.ref = k.ref
      AND p.alt = k.alt
WHERE
  ens.gene_name LIKE 'BRC%'
  AND ens.chromosome NOT LIKE 'H%'
  AND p.pos BETWEEN ens.start AND ens.stop
  AND COALESCE(k.alle_freq, 0.0) < .10

如果对您来说不够快，那么您需要检查查询计划以确定瓶颈是什么，而不是试图猜测。

赞(0）回复(0）举报 2021-06-26

7z5jn7bk4#

SELECT t1.* 
     FROM table1 t1
LEFT JOIN table2 t2 
       ON t1.col1 = t2.col1
    WHERE t2.col1 IS NULL
       OR t2.alle_freq < 10

赞(0）回复(0）举报 2021-06-26

我来回答

sql从表1中查找不在表2中或表2中有条件的记录

4条答案

相关问题

热门标签

最新问答