Difference between revisions of "Cimmyt Database"

From Poland Lab Wiki
Jump to: navigation, search
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
The cimmyt database schema is shown in the figure below:
+
The CIMMYT database contains phenotypic field data that was collected for CIMMYT experiments collected at multiple locations.
 +
 
 +
The CIMMYT database schema is shown in the figure below:
  
 
[[File:cimmyt.png]]
 
[[File:cimmyt.png]]
 +
 +
==Example Queries==
 +
 +
===Row-to-Column Query===
 +
 +
FOR QUERY OF REPEATED MEASUREMENTS:  This query can be used to extract a column format of a repeat measurements (over dates) for multiple traits. 
 +
 +
The query below is an example of how to generate a report of the G-NDVI and R-NDVI traits from the 16OBR plots with B2I conditions, (bed planting and two irrigations) for the Elite Yield Trial (EYT).
 +
 +
<syntaxhighlight lang="sql">
 +
SET SESSION group_concat_max_len = 10000;
 +
USE cimmyt;
 +
SET @SQL = NULL;
 +
CREATE TEMPORARY TABLE IF NOT EXISTS p AS (SELECT * FROM phenotypes WHERE (YEAR(phenotype_date) = 2016 AND phenotype_value IS NOT NULL AND plot_id LIKE '16-OBR-B2I-EYT%' and trait_id LIKE '%NDVI%'));
 +
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.phenotype_date = ''', `phenotype_date`, ''',p.phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
 +
Order by `phenotype_date` ASC  ) INTO @SQL FROM p;
 +
SET @SQL = CONCAT('SELECT  p.plot_id,p.trait_id, ', @SQL, ' FROM p GROUP BY p.plot_id,p.trait_id ORDER BY p.plot_id');
 +
PREPARE stmt FROM @SQL;
 +
EXECUTE stmt;
 +
DEALLOCATE PREPARE stmt;
 +
DROP TEMPORARY TABLE p;
 +
</syntaxhighlight>

Latest revision as of 18:04, 12 June 2017

The CIMMYT database contains phenotypic field data that was collected for CIMMYT experiments collected at multiple locations.

The CIMMYT database schema is shown in the figure below:

File:Cimmyt.png

Example Queries

Row-to-Column Query

FOR QUERY OF REPEATED MEASUREMENTS: This query can be used to extract a column format of a repeat measurements (over dates) for multiple traits.

The query below is an example of how to generate a report of the G-NDVI and R-NDVI traits from the 16OBR plots with B2I conditions, (bed planting and two irrigations) for the Elite Yield Trial (EYT).

SET SESSION group_concat_max_len = 10000;
USE cimmyt;
SET @SQL = NULL;
CREATE TEMPORARY TABLE IF NOT EXISTS p AS (SELECT * FROM phenotypes WHERE (YEAR(phenotype_date) = 2016 AND phenotype_value IS NOT NULL AND plot_id LIKE '16-OBR-B2I-EYT%' AND trait_id LIKE '%NDVI%'));
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.phenotype_date = ''', `phenotype_date`, ''',p.phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
ORDER BY `phenotype_date` ASC  ) INTO @SQL FROM p;
SET @SQL = CONCAT('SELECT  p.plot_id,p.trait_id, ', @SQL, ' FROM p GROUP BY p.plot_id,p.trait_id ORDER BY p.plot_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TEMPORARY TABLE p;