Difference between revisions of "Cimmyt Database"
From Poland Lab Wiki
| Line 2: | Line 2: | ||
[[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> | ||
Revision as of 16:16, 12 June 2017
The cimmyt database schema is shown in the figure below:
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;
