Project

General

Profile

« Previous | Next » 

Revision 12069

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/traits/bien3_validations_traits_bien3.sql: include only records from the bien2_traits datasource, to ensure that the scope of all queries is limited to the datasource

View differences:

trunk/validation/aggregating/traits/bien3_validations_traits_bien3.sql
13 13 
CREATE OR REPLACE VIEW traits_1_count_records AS
14 14 
SELECT COUNT(*) AS totalRecords
15 15 
FROM trait
16 
JOIN taxonoccurrence USING (taxonoccurrence_id)
17 
WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')
16 18 
;
17 19 

  		




  18
  20
  
    
-- ------------------


  		




  ......




  21
  23
  
    
CREATE OR REPLACE VIEW traits_2_count_trait_names AS


  		




  22
  24
  
    
SELECT COUNT(DISTINCT name) AS traits


  		




  23
  25
  
    
FROM trait


  		




  24
  
  
    
;


  		




  
  26
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  
  27
  
    
WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits');


  		




  25
  28
  
    

  		




  26
  29
  
    
-- ------------------


  		




  27
  30
  
    
-- 3. List trait names


  		




  ......




  29
  32
  
    
CREATE OR REPLACE VIEW traits_3_list_trait_names AS


  		




  30
  33
  
    
SELECT DISTINCT name AS trait


  		




  31
  34
  
    
FROM trait


  		




  
  35
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  
  36
  
    
WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')


  		




  32
  37
  
    
ORDER BY name


  		




  33
  38
  
    
;


  		




  34
  39
  
    

  		




  ......




  38
  43
  
    
CREATE OR REPLACE VIEW traits_4_count_records_per_trait AS


  		




  39
  44
  
    
SELECT name AS trait, COUNT(*) AS measurements


  		




  40
  45
  
    
FROM trait


  		




  
  46
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  
  47
  
    
WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')


  		




  41
  48
  
    
GROUP BY name


  		




  42
  49
  
    
ORDER BY name


  		




  43
  50
  
    
;


  		




  ......




  74
  81
  
    
CREATE OR REPLACE VIEW traits_7_trait_value_and_units_for_first_5000_records_ AS


  		




  75
  82
  
    
SELECT name AS trait, value, units


  		




  76
  83
  
    
FROM trait


  		




  
  84
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  
  85
  
    
WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')


  		




  77
  86
  
    
ORDER BY name, value, units


  		




  78
  87
  
    
LIMIT 5000


  		




  79
  88
  
    
;


  		












Also available in:  Unified diff