sqlite - 如何在基因组数据库 SQLite3 中创建相关表
问题描述
我很陌生,sqlite3
但我想用它来存储我拥有的一些基因组数据,因为从 R 进行操作需要花费大量时间来处理。建立数据库后,我想进行一些基本查询,但我的问题是,我不知道应该创建哪些表才能进行适当的查询。
这是我的大桌子的样子:
Chr Start End Ref Alt Callers GATK_Illumina.counts GATK_Illumina.samples GATK_SOLiD.counts GATK_SOLiD.samples LIFE_SOLiD.counts LIFE_SOLiD.samples TVC_Ion.counts TVC_Ion.samples Func.refGene Gene.refGene
chr1 14948 14948 G A GATK_SOLiD 0.38 noSample 1.125 XK713 0.125 noSample 13.43 17E334|17E424|17H593|17J782|17J913|1B566 ncRNA_intronic;downstream WASH7P;DDX11L1
chr1 14948 14948 G A TVC_Ion 0.38 noSample 1.125 XK713 0.125 noSample 13.43 17E334|17E424|17H593|17J782|17J913|1B566 ncRNA_intronic;downstream WASH7P;DDX11L1
chr1 15820 15820 G T GATK_SOLiD 0.38 noSample 1.125 1E695 0.125 noSample 4.43 17E574|17H906|5K083B|6C418 ncRNA_exonic WASH7P
chr1 15820 15820 G T TVC_Ion 0.38 noSample 1.125 1E695 0.125 noSample 4.43 17E574|17H906|5K083B|6C418 ncRNA_exonic WASH7P
chr1 17452 17452 C T GATK_SOLiD 0.38 noSample 1.125 1H823 0.125 noSample 12.43 17G118|17G937|17H906|17J610|17M152|4E832|5C725|5F445|5F685|5H986|5J427 ncRNA_intronic;upstream WASH7P;MIR6859-1;MIR6859-2;MIR6859-3;MIR6859-4
chr1 17452 17452 C T TVC_Ion 0.38 noSample 1.125 1H823 0.125 noSample 12.43 17G118|17G937|17H906|17J610|17M152|4E832|5C725|5F445|5F685|5H986|5J427 ncRNA_intronic;upstream WASH7P;MIR6859-1;MIR6859-2;MIR6859-3;MIR6859-4
chr1 17538 17538 C A GATK_SOLiD 0.38 noSample 3.125 1E695|1H586|9J385 0.125 noSample 24.43 17C851B|17C918|17D521B|17E424|17F076 ncRNA_intronic;upstream WASH7P;MIR6859-1;MIR6859-2;MIR6859-3;MIR6859-4
chr1 17538 17538 C A TVC_Ion 0.38 noSample 3.125 1E695|1H586|9J385 0.125 noSample 24.43 17C851B|17C918|17D521B|17E424|17F076 ncRNA_intronic;upstream WASH7P;MIR6859-1;MIR6859-2;MIR6859-3;MIR6859-4
我的查询将涉及按Chr
,搜索以显示哪些呼叫者具有这些坐标Start
,End
以及按基因搜索。但我的疑问是如何创建表格。我可以创建一个表,Chr Start End Ref Alt Callers
但如何与另一个包含样本或基因的表链接?一个坐标 ( Chr Start End
) 可以链接多个样本或调用者。
查询的示例是键入坐标并显示所有信息,并按基因搜索并显示包含该基因的所有坐标
基本上我想知道我应该创建多少表以及如何链接它们。我知道我的坐标表将是父表。
哪些元素应该是不同表中的键?
解决方案
我对基因组一无所知,但根据数据和您的描述,这似乎是一些 1:n 关系,您正在寻找使用主键/外键创建关系实体。我不确定您对 SQL 的熟悉程度,您可以在此处查看有关使用它的更多信息:https ://www.w3schools.com/sql/
您可能希望像这样创建表,但如果有额外的 1:n 或 n:n 关系,则进一步:
genes
gene_id, gene_name, data1, data2
// 1 gene -> many coords
gene_coords
gene_id, chr, start, end, data1, data2
// 1 coord -> many callers (and/or samples)
gene_callers // Are callers & samples 1:1?
gene_id, caller, sample
您可以使用坐标 && chr 来查询调用者,如下所示:
SELECT gene_callers.callers FROM gene_callers
JOIN gene_coords ON gene_coords.gene_id = gene_callers.gene_id
WHERE gene_coords.start = 14948 AND gene_coords.end = 14948
AND gene_coords.chr = 'chr1';
我不确定您是否会根据坐标范围查询多个基因,如果是这样,那么您可能不想将开始/结束存储为相同的值,只需将其存储为坐标并在该字段上使用 BETWEEN。例如,此查询还将为您获取基因数据。
SELECT gene.*, gene_callers.callers FROM gene_callers
JOIN gene ON gene.gene_id = gene_callers.gene_id
JOIN gene_coords ON gene_coords.gene_id = gene_callers.gene_id
WHERE gene_coords.coord BETWEEN 14948 AND 17538;
要按基因名称获取呼叫者,您可以执行以下操作:
SELECT gene_callers.callers FROM gene_callers
JOIN gene ON gene.gene_id = gene_callers.gene_id
WHERE gene.gene_name = 'a_gene';
您可能需要根据任何空值和您正在寻找的数据集来调整连接类型。如果样本与调用者的比例不是 1:1,您可能需要为样本创建另一个表。希望我正确解释了您的数据,这至少可以为您指明正确的方向。
推荐阅读
- java - 将 .class 文件导入 Eclipse 项目
- python - 如果我们创建的线程数超过了 CPU 的承受能力,会发生什么?操作系统是否处理它?: Python - 线程
- r - mlr 包 - 尝试集成一个新的聚类学习器。par.vals 中的默认值被忽略(在 makeRLearnerCluster 方法中)
- github - 如何在不发送拉取请求的情况下在我的程序中实现在分叉的 Git 存储库中所做的更改?
- r - 在 R 中,分类模型中的弹性网络缺失类
- php - 我有一个数组,我想在页面刷新时随机生成
- arrays - 将相同的数据传递给Scala中的两个或多个方法
- java - 为什么我在 ~/.android 文件夹中没有 debug.keystore(对于 mac)?
- javascript - Html 和 Javascript:如何返回用户输入
- javascript - Sinonjs存根不替换方法