2011-04-12 union nomismatch prioritypriority.sql

2011-04-12 union nomismatch priority.sql

DROP TABLE IF EXISTS union_nomismatch;
CREATE TABLE union_nomismatch
SELECT "12" as chrom, pos18, pos19, if(alleles_study IS NOT NULL, alleles_study, alleles_1000g) as alleles, maf1000g, maxmaf, meanmaf, novel, offtarget, thresh, highqual, superhighqual, 
    CASE
        WHEN meanmaf IS NULL THEN maf1000g
        WHEN maf1000g IS NULL THEN meanmaf
        WHEN (meanmaf IS NOT NULL and maf1000g IS NOT NULL) THEN GREATEST(meanmaf, maf1000g)
    END AS bestmaf
FROM (
    SELECT 
        a.pos18, a.pos19, a.refvar AS alleles_study, a.novel, a.offtarget, a.thresh, a.maxmaf, a.meanmaf, a.highqual, a.superhighqual, b.refvar AS alleles_1000g, b.maf AS maf1000g
        FROM studydata a 
        LEFT JOIN 1000g_polymorphic b ON a.pos18=b.pos18
    union
    SELECT 
        b.pos18, b.pos19, a.refvar AS alleles_study, a.novel, a.offtarget, a.thresh, a.maxmaf, a.meanmaf, a.highqual, a.superhighqual, b.refvar AS alleles_1000g, b.maf AS maf1000g
        FROM studydata a 
        RIGHT JOIN 1000g_polymorphic b ON a.pos18=b.pos18
) a
WHERE (
             alleles_study=alleles_1000g
             OR alleles_study IS NULL
             OR alleles_1000g IS NULL
)
ORDER BY pos18;
SELECT COUNT(*) FROM union_nomismatch WHERE alleles IS NOT NULL;

CREATE VIEW union_nomismatch_priority as
select *, 
    CASE
        WHEN (superhighqual=1 AND highqual=1) OR (superhighqual IS NULL AND maf1000g>0.1) THEN 1 #n=?
        WHEN (superhighqual=0 AND highqual=1) OR (highqual IS NULL AND maf1000g>0.05) THEN 2 #n=610
        WHEN (superhighqual=0 AND highqual=1) OR (highqual IS NULL AND maf1000g>0.01) THEN 3 #n=728
        WHEN maxmaf>=0.05 OR (maxmaf IS NULL AND maf1000g>0.01) THEN 4 #n=875
        ELSE 5 #n=total
    END as priority
from union_nomismatch;
select * from union_nomismatch_priority;
select priority, count(*) from union_nomismatch_priority group by priority;