table newdata
chr = varchar(16) // название хромосомы
left = bigint // левая координата паттерна в геноме
right = bigint // правая координата паттерна в геноме
count = int // число условно-одинаковых паттернов

table g38_genes
id = bigint // уникальный идентификатор гена
chr = varchar(16)
left = bigint // левая координата гена в геноме
right = bigint // правая координата гена в геноме
priority = int // показатель приоритетности

CREATE temporary table `temp_pos`
(`chr` varchar(16) NOT NULL,
`pos` bigint(20) NOT NULL,
`count` int(11) NOT NULL,
UNIQUE KEY `chr_pos` (`chr`, `pos`) );
INSERT INTO `temp_pos` (`chr`, `pos`, `count`)
SELECT `chr`, `left` pos, `count` FROM `newdata`
where `chr`=1 order by `left` limit 0,200
ON DUPLICATE KEY UPDATE
`temp_pos`.`count` = `temp_pos`.`count` + `newdata`.count ;
INSERT INTO `temp_pos` (`chr`, `pos`, `count`)
SELECT `chr`, `right` pos, `count` FROM `newdata`
where `chr`=1 order by `right` limit 0,200
ON DUPLICATE KEY UPDATE
`temp_pos`.`count` = `temp_pos`.`count` + `newdata`.count ;

select A.*, B.`id`, B.`left`, B.`right`
from `temp_pos` A, `g38_genes` B
where A.`count` > 9 and A.`chr`=1 and B.`chr` = 1 and B.`priority` > 4
and A.`pos` < B.`right` and A.`pos` > B.`left`
order by A.`pos`;

SELECT `chr`, `pos`, SUM(`count`) `count`
FROM
(
  SELECT `chr`, `left` `pos`, SUM(`count`) `count`
  FROM newdata
  -- WHERE `chr`=1
  GROUP BY `chr`, `left`
UNION ALL
  SELECT `chr`, `right` `pos`, SUM(`count`) `count`
  FROM newdata
  -- WHERE `chr`=1
  GROUP BY `chr`, `right`
)
GROUP BY `chr`, `pos`

SELECT A.*, B.`id`, B.`left`, B.`right`
FROM (
      SELECT `chr`, `pos`, SUM(`count`) `count`
      FROM
      (
        SELECT `chr`, `left` `pos`, SUM(`count`) `count`
        FROM newdata
        -- WHERE `chr`=1
        GROUP BY `chr`, `left`
      UNION ALL
        SELECT `chr`, `right` `pos`, SUM(`count`) `count`
        FROM newdata
        -- WHERE `chr`=1
        GROUP BY `chr`, `right`
      )
      GROUP BY `chr`, `pos`
    ) A, `g38_genes` B
WHERE A.`count` > 9
--  AND A.`chr` = 1
--  AND B.`chr` = 1
  AND B.`priority` > 4
  AND A.`pos` < B.`right`
  AND A.`pos` > B.`left`
ORDER BY A.`pos`;