У переписывающего подзапроса есть пункт в объединении?

У меня есть таблица с такими столбцами;

сайт, хромосома, нить.

Пара сайта и хромосомы должны быть уникальными, в то время как они могут иметь более одной цепи. Перед загрузкой данных я обнаружил, что некоторые сайты имеют более одной хромосомы, что, очевидно, является ошибкой. Я пытался идентифицировать ошибки, то есть сайты с более чем 1 хромосомой. Я думал об этом и не смог придумать правильный SQL. Я разделил проблему. Сначала я создаю таблицу, выбирая отдельные записи с помощью;

create table distinct_pair 
  as select distinct site, chromosome 
  from original_table;

Тогда я мог бы найти сайты, которые имеют более одной хромосомы этим;

select site 
  from distinct_pair 
  group by site 
  having count(site)>1;

Работало нормально. Затем, пытаясь увидеть всю информацию об ошибках из исходной таблицы, я сделал это;

select * from original_table 
  where site 
  in (select name from distinct_pair
        group by site 
        having count(site)>1);

Тогда этот подзапрос был слишком медленным, хотя все столбцы были проиндексированы.

Я пытался переписать запрос как объединение, но наличие затрудняет. Пожалуйста, помогите мне.

===================

Спасибо всем, кто ответил на этот вопрос. Мои данные выглядят так.

Site | Chromosome | Strand
N111 | 2L         | +
N111 | 2L         | -
N112 | 2L         | +
N112 | 2L         | -
N112 | 3L         | +
N112 | 3L         | -
....

В этом случае N111 в порядке, но N112 является ошибкой, потому что у него есть две хромосомные данные. Подзапрос второго ответа выбрал N111 и N112 из-за нити, что было той же проблемой, что и у меня. Группировка за функцией с несколькими столбцами работала не так, как я догадывался. Тем не менее, предложенный ответ дал мне понять, как работает group by, чтобы я мог немного изменить его, чтобы он работал. Два ответа дают одинаковые результаты. Еще раз спасибо, ребята.

сайт

Источник

2 ответа

Решение

Вы могли бы просто найти тот с другой хромосомой для данного сайта:

SELECT DISTINCT t1.site, t1.chromosome, t2.chromosome
FROM original_table t1
    INNER JOIN original_table t2 USING (site)
WHERE t1.chromosome <> t2.chromosome
Источник

Похоже, вы хотите что-то вроде этого:

     SELECT site, chromosome, strand
       FROM original_table O
INNER JOIN (SELECT site, chromosome
            FROM original_table
            GROUP BY site, chromosome
            HAVING COUNT(*) > 1) T
         ON USING (site)
        AND USING (chromosome)

Подзапрос выбирает пары сайтов и хромосом, которые повторяются более одного раза, а затем вы присоединяете его к большой таблице. Поскольку это INNER JOIN, он возвращает только те строки, у которых есть совпадение в подзапросе.

Источник
Другие вопросы по тегам