PostgreSQL 遺傳學應用 - 矩陣相似距離計算 (歐式距離,...XX距離)

PostgreSQL ,

生物科學中相當重要的工作之一解開遺傳密碼?

歐式空間計算，是其中的一個需求，很有意思吧，PostgreSQL可以用來解開遺傳密碼。

<a href="https://en.wikipedia.org/wiki/Euclidean_distance">https://en.wikipedia.org/wiki/Euclidean_distance</a>

<a href="https://www.math.uci.edu/~gpatrick/source/205b06/chapviii.pdf">https://www.math.uci.edu/~gpatrick/source/205b06/chapviii.pdf</a>

實際上PostgreSQL是一個擴充性非常強大的資料庫，比如在文本相似計算方面，就有諸多擴充插件。

<a href="https://github.com/digoal/blog/blob/master/201705/20170524_01.md">《17種相似算法與GIN索引 - pg_similarity》</a>

<a href="https://github.com/eulerto/pg_similarity">https://github.com/eulerto/pg_similarity</a>

<a href="https://baike.baidu.com/item/%E6%AC%A7%E5%87%A0%E9%87%8C%E5%BE%97%E5%BA%A6%E9%87%8F/1274107?fromtitle=%E6%AC%A7%E6%B0%8F%E8%B7%9D%E7%A6%BB&amp;fromid=1798948">https://baike.baidu.com/item/%E6%AC%A7%E5%87%A0%E9%87%8C%E5%BE%97%E5%BA%A6%E9%87%8F/1274107?fromtitle=%E6%AC%A7%E6%B0%8F%E8%B7%9D%E7%A6%BB&amp;fromid=1798948</a>

<a href="https://github.com/digoal/blog/blob/master/201701/20170116_04.md">《PostgreSQL結合餘弦、線性相關算法 在文本、圖檔、數組相似 等領域的應用 - 3 rum, smlar應用場景分析》</a>

<a href="https://github.com/digoal/blog/blob/master/201701/20170116_03.md">《PostgreSQL結合餘弦、線性相關算法 在文本、圖檔、數組相似 等領域的應用 - 2 smlar插件詳解》</a>

<a href="https://github.com/digoal/blog/blob/master/201701/20170116_02.md">《PostgreSQL結合餘弦、線性相關算法 在文本、圖檔、數組相似 等領域的應用 - 1 文本(關鍵詞)分析理論基礎 - TF(Term Frequency 詞頻)/IDF(Inverse Document Frequency 逆向文本頻率)》</a>

在基因科學方面，也有擴充插件應用：

<a href="https://github.com/digoal/blog/blob/master/201606/20160621_01.md">《為了部落 - 如何通過PostgreSQL基因配對，産生優良下一代》</a>

在化學分析方面，也有相似的插件：

<a href="http://www.rdkit.org/">http://www.rdkit.org/</a>

某個生物科技公司，有這樣的一種需求：

每張表有幾十萬行，幾萬列，全部浮點類型，任意列勾選，計算歐氏距離等需求。

因為資料庫設計限制，不能支援一張表幾萬列，不過PostgreSQL可以将多列存成數組。

1、DNA結構如下：

比如每行代表一個物種的測序資料。

2、生成随機浮點數組的函數，可以友善的生成測試資料。

3、生成50萬條測試資料，每組2萬浮點數。

資料大概占用86GB空間。

可以使用plpgsql建立計算兩個浮點數組的歐式距離的函數，長度可以不一樣，因為可能不同物種的遺傳資料不一樣，有的多，有的少。

如果使用C函數，性能會更好。

例子

通過這個函數，傳入要計算的數組即可計算歐式距離。

這個主要用于部分計算，例如人類和猴子，在某一段的相似性，那麼需要從這兩條記錄中，分别取出要計算的部分，重新組成兩個數組，然後計算它們兩的歐氏距離。

例子：

或

比如選中了100個物種，計算它們的任意組合的歐氏距離。

需要一些輔助函數：

1、組合去重函數，隻去掉重複行。

2、組合去重函數，去掉按列值排序後的重複行。

3、比如選中了1,2,3,4這四種物種，如何得到他們的排列組合呢？

4、建立一個函數，用于計算輸入組合物種的排列組合歐式距離。

計算例子：

輸入5個物種的ID，傳回這5個物種的排列組合歐式距離。

PostgreSQL是一個擴充性很好的資料庫，内置了豐富的資料類型。

本例，使用函數程式設計、數組類型兩個特性，解決了生物科學中的遺傳計算的場景的疑難問題（上萬列，任意組合計算排列組合的歐式距離）。

同時PostgreSQL還能支援并行計算，在重計算的場景，可以提高計算響應速度。