D2RQvsONTOP

提供：TogoWiki

2015年2月13日 (金) 07:43時点におけるOrenodbcls (トーク | 投稿記録)による版
(差分) ←前の版 | 最新版 (差分) | 次の版→ (差分)
移動： 案内, 検索
  • Query 1: クラス指定（テーブル指定）してそのテーブルのみから検索
    • D2RQ 
SELECT DISTINCT * WHERE {
?s ?p vocab:bs_data_9606_LC2ad .
}
LIMIT 100

SET net_write_timeout=600
SELECT `bs_data_9606_LC2ad`.`bs_data_id` FROM `bs_data_9606_LC2ad`
SET net_write_timeout=60
    • ontop 
select *
where {
?s ?p <http://dbtss.hgc.jp/rdf/ontology#bs_data_9606_LC2ad> .
}
limit 100

SELECT *
FROM (
SELECT
  1 AS "sQuestType", NULL AS "sLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#bs_data_9606_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`bs_data_id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `s`,
  1 AS "pQuestType", NULL AS "pLang", 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AS `p`
FROM
bs_data_9606_LC2ad QVIEW1
WHERE
QVIEW1.`bs_data_id` IS NOT NULL
) SUB_QVIEW
LIMIT 100
  • Query 2: クラス指定（テーブル指定）してそのテーブルのみから検索（type指定も）
    • D2RQ 
SELECT DISTINCT * WHERE {
?s a vocab:bs_data_9606_LC2ad .
}
LIMIT 100

SET net_write_timeout=600
SELECT `bs_data_9606_LC2ad`.`bs_data_id` FROM `bs_data_9606_LC2ad`
SET net_write_timeout=60
    • ontop 
select *
where {
?s a <http://dbtss.hgc.jp/rdf/ontology#bs_data_9606_LC2ad> .
}
limit 100

SELECT *
FROM (
SELECT
  1 AS "sQuestType", NULL AS "sLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#bs_data_9606_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`bs_data_id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `s`
 FROM
bs_data_9606_LC2ad QVIEW1
WHERE
QVIEW1.`bs_data_id` IS NOT NULL
) SUB_QVIEW
LIMIT 100
  • Query 3: 複数テーブルにまたがるクエリ
    • D2RQ 
select *
where {
?snv vocab:vcf_inf_9606_chr1_LC2ad_pos ?o .
?snv a vocab:vcf_inf_9606_chr1_LC2ad .
?tss vocab:tss_bincount_9606_chr1_LC2ad_pos ?o .
?tss a vocab:tss_bincount_9606_chr1_LC2ad .
}
limit 100

SET net_write_timeout=600
SELECT DISTINCT `T3_-889240557`.`id`, `T1_vcf_inf_9606_chr1_LC2ad`.`pos`, `T1_vcf_inf_9606_chr1_LC2ad`.`id` FROM `tss_bincount_9606_chr1_LC2ad` AS `T3_-889240557`, `vcf_inf_9606_chr1_LC2ad` AS `T1_vcf_inf_9606_chr1_LC2ad`, `vcf_inf_9606_chr1_LC2ad` AS `T2_vcf_inf_9606_chr1_LC2ad`, `tss_bincount_9606_chr1_LC2ad` AS `T4_-877924430` WHERE (`T1_vcf_inf_9606_chr1_LC2ad`.`id` = `T2_vcf_inf_9606_chr1_LC2ad`.`id` AND `T1_vcf_inf_9606_chr1_LC2ad`.`pos` = `T3_-889240557`.`pos` AND `T3_-889240557`.`id` = `T4_-877924430`.`id`)
SET net_write_timeout=60
    • ontop 
select *
where {
?snv <http://dbtss.hgc.jp/rdf/ontology#pos> ?o .
?snv a <http://dbtss.hgc.jp/rdf/ontology#vcf_inf_9606_chr1_LC2ad> .
?tss <http://dbtss.hgc.jp/rdf/ontology#pos> ?o .
?tss a <http://dbtss.hgc.jp/rdf/ontology#tss_bincount_9606_chr1_LC2ad> .
}
limit 100

SELECT *
FROM (
SELECT
  1 AS "snvQuestType", NULL AS "snvLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#vcf_inf_9606_chr1_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `snv`,
  4 AS "oQuestType", NULL AS "oLang", CAST(QVIEW1.`pos` AS CHAR(8000) CHARACTER SET utf8) AS `o`,
  1 AS "tssQuestType", NULL AS "tssLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#tss_bincount_9606_chr1_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW2.`id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `tss`
FROM
vcf_inf_9606_chr1_LC2ad QVIEW1,
tss_bincount_9606_chr1_LC2ad QVIEW2
WHERE
QVIEW1.`id` IS NOT NULL AND
QVIEW1.`pos` IS NOT NULL AND
(QVIEW1.`pos` = QVIEW2.`pos`) AND
QVIEW2.`id` IS NOT NULL
) SUB_QVIEW
LIMIT 100
  • Query 4: 複数テーブルにまたがるクエリ（Filter追加）
    • D2RQ 
select *
where {
?snv vocab:vcf_inf_9606_chr1_LC2ad_pos ?o .
?snv a vocab:vcf_inf_9606_chr1_LC2ad .
?tss vocab:tss_bincount_9606_chr1_LC2ad_pos ?o .
?tss a vocab:tss_bincount_9606_chr1_LC2ad .
FILTER (?o > 3000000 && ?o < 5000000)
}
limit 100

SET net_write_timeout=600
SELECT DISTINCT `T3_-889240557`.`id`, `T1_vcf_inf_9606_chr1_LC2ad`.`pos`, `T1_vcf_inf_9606_chr1_LC2ad`.`id` FROM `tss_bincount_9606_chr1_LC2ad` AS `T3_-889240557`, `vcf_inf_9606_chr1_LC2ad` AS `T1_vcf_inf_9606_chr1_LC2ad`, `vcf_inf_9606_chr1_LC2ad` AS `T2_vcf_inf_9606_chr1_LC2ad`, `tss_bincount_9606_chr1_LC2ad` AS `T4_-877924430` WHERE (`T1_vcf_inf_9606_chr1_LC2ad`.`id` = `T2_vcf_inf_9606_chr1_LC2ad`.`id` AND `T1_vcf_inf_9606_chr1_LC2ad`.`pos` = `T3_-889240557`.`pos` AND `T3_-889240557`.`id` = `T4_-877924430`.`id`)
SET net_write_timeout=60
    • ontop 
select *
where {
?snv <http://dbtss.hgc.jp/rdf/ontology#pos> ?o .
?snv a <http://dbtss.hgc.jp/rdf/ontology#vcf_inf_9606_chr1_LC2ad> .
?tss <http://dbtss.hgc.jp/rdf/ontology#pos> ?o .
?tss a <http://dbtss.hgc.jp/rdf/ontology#tss_bincount_9606_chr1_LC2ad> .
FILTER (?o > 3000000 && ?o < 5000000)
}
limit 100

SELECT *
FROM (
SELECT
  1 AS "snvQuestType", NULL AS "snvLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#vcf_inf_9606_chr1_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `snv`,
  4 AS "oQuestType", NULL AS "oLang", CAST(QVIEW1.`pos` AS CHAR(8000) CHARACTER SET utf8) AS `o`,
  1 AS "tssQuestType", NULL AS "tssLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#tss_bincount_9606_chr1_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW2.`id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `tss`
FROM
vcf_inf_9606_chr1_LC2ad QVIEW1,
tss_bincount_9606_chr1_LC2ad QVIEW2
WHERE
QVIEW1.`id` IS NOT NULL AND
QVIEW1.`pos` IS NOT NULL AND
(QVIEW1.`pos` = QVIEW2.`pos`) AND
QVIEW2.`id` IS NOT NULL AND
((QVIEW1.`pos` < 5000000) AND (QVIEW1.`pos` > 3000000))
) SUB_QVIEW
LIMIT 100
  • Query 5: 全データ 
select *
where {
?s ?p ?o .
}
limit 100
    • D2RQ 
SET net_write_timeout=600
SELECT `cpg_bincount_9606_ucsc`.`id`, `cpg_bincount_9606_ucsc`.`obsExp`, `cpg_bincount_9606_ucsc`.`perCpg`, `cpg_bincount_9606_ucsc`.`gcNum`, `cpg_bincount_9606_ucsc`.`perGc`, `cpg_bincount_9606_ucsc`.`start`, `cpg_bincount_9606_ucsc`.`cpgNum`, `cpg_bincount_9606_ucsc`.`length`, `cpg_bincount_9606_ucsc`.`chr`, `cpg_bincount_9606_ucsc`.`name`, `cpg_bincount_9606_ucsc`.`end`, `cpg_bincount_9606_ucsc`.`bin` FROM `cpg_bincount_9606_ucsc`
SET net_write_timeout=60
    • ontop 
SELECT *
FROM (
SELECT
  1 AS "sQuestType", NULL AS "sLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#chip_inf_9606_chr9_LC2ad_H3K4me3/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `s`,
  1 AS "pQuestType", NULL AS "pLang", 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AS `p`,
  1 AS "oQuestType", NULL AS "oLang", 'http://dbtss.hgc.jp/rdf/ontology#chip_inf_9606_chr9_LC2ad_H3K4me3' AS `o`
FROM
chip_inf_9606_chr9_LC2ad_H3K4me3 QVIEW1
WHERE
QVIEW1.`id` IS NOT NULL
UNION ALL
SELECT
  1 AS "sQuestType", NULL AS "sLang", CONCAT('http://dbtss.hgc.jp/rdf//ontology#bs_data_9606_LC2ad/', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1.`bs_data_id` AS CHAR(8000) CHARACTER SET utf8),' ', '%20'),'!', '%21'),'@', '%40'),'#', '%23'),'$', '%24'),'&', '%26'),'*', '%42'), '(', '%28'), ')', '%29'), '[', '%5B'), ']', '%5D'), ',', '%2C'), ';', '%3B'), ':', '%3A'), '?', '%3F'), '=', '%3D'), '+', '%2B'), ', '%22'), '/', '%2F')) AS `s`,
  1 AS "pQuestType", NULL AS "pLang", 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AS `p`,
  1 AS "oQuestType", NULL AS "oLang", 'http://dbtss.hgc.jp/rdf/ontology#bs_data_9606_LC2ad' AS `o`
FROM
bs_data_9606_LC2ad QVIEW1
WHERE
QVIEW1.`bs_data_id` IS NOT NULL
UNION ALL
....

SELETの数が787個

「/mw/D2RQvsONTOP」より作成