Project

General

Profile

task #9231

Create statistics for Euro+Med

Added by Andreas Müller about 1 month ago. Updated 22 days ago.

Status:
In Progress
Priority:
New
Category:
data
Target version:
Start date:
09/24/2020
Due date:
% Done:

20%

Severity:
normal

Description

Generally absent taxa or taxa without distribution should not be included. Same for unpublished taxa and "valueless" taxa.
Also distributions from deprecated areas like Rs and child areas should not be used.

Statistics should include

  1. For all E+M are, seperated by native (including "undefined") and non-native (including "cultivated") - unclear "formerly native"

    a. count by ranks for all ranks from genus downwords (higher ranks not needed because they are in flow anyway) including apomictic taxa
    b. count of all "taxa" (including apomictic taxa) with taxa defined as species and subspecies (no other ranks) but not those species having at least 1 subspecies child
    c. apomictic taxa

  2. Same as 1. but for super areas "EUR", "MED", "MAC" and "CAUC" (being defined by markers on each area)

  3. Same as 1. for each "normal" area where subareas such as "Ab(A)" are counted for its parent area (here "Ab"), still open how to handle more complex or outdated areas such as Ju and Cz

  4. per family xxx

  5. Twenty most diverse plant families in each geographical area xxxx

  6. taxa per year

History

#3 Updated by Andreas Müller about 1 month ago

  • Description updated (diff)
  • Target version changed from Unassigned CDM tickets to Release 5.18

#4 Updated by Andreas Müller about 1 month ago

  • Description updated (diff)

#5 Updated by Andreas Müller about 1 month ago

Query for 1.

 -- E+M Full Area               

SELECT rankName, STATUS, n              
FROM                
(               
SELECT 'All' AS rankName, COUNT(*) AS n, orderIndex, stGroup AS STATUS              
FROM (              
   SELECT tb.id, 1 AS orderIndex,               
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup         
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id      
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa      
   GROUP BY tb.id               
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa           
GROUP BY stGroup                

UNION ALL               

SELECT rankName, COUNT(*) AS n, orderIndex, stGroup AS status               
FROM (              
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,            
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup         
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id      
--      LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id      
--      LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592        
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa      
--      AND (apo.id IS NULL OR apo.flag = 0)        
    GROUP BY tb.id          
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa1          
GROUP BY rankName, rankId, orderIndex, stGroup              

UNION ALL               

SELECT CONCAT('Apomictic-', rankName), COUNT(*) AS n, orderIndex + 100, stGroup AS status               
FROM (              
   SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,             
       CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup               
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id      
        LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id      
        LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592        
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa      
        AND (apo.id IS NOT NULL OR apo.flag = 1)        
    GROUP BY  tb.id         
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa2          
GROUP BY rankName, rankId, orderIndex, stGroup              

UNION ALL               

SELECT 'Taxa (Species+Subspecies)', COUNT(*) AS n, 10 AS orderindex, stGroup                
FROM (              
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,            
        CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup      
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id      
--      LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id      
--      LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592        
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
       AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'           
       AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex IN (45,47)          
--     AND (apo.id IS NULL OR apo.flag = 0)         
       AND NOT EXISTS (         
            SELECT *    
           FROM TaxonNode childNode     
                INNER JOIN TaxonBase childTaxon ON childTaxon.id = childNode.taxon_id
                INNER JOIN TaxonName childName ON childName.id = childTaxon.name_id
                INNER JOIN DefinedTermBase childRank ON childName.rank_id = childRank.id
                INNER JOIN DescriptionBase childDb ON childDb.taxon_id = childTaxon.id
                INNER JOIN DescriptionElementBase childDeb ON childDb.id = childDeb.inDescription_id
                INNER JOIN DefinedTermBase childArea ON childArea.id = childDeb.area_id
            WHERE childNode.parent_id = tn.id   
               AND childTaxon.publish = 1 AND childDeb.DTYPE = 'Distribution'   
              AND childRank.orderIndex = 47     
              AND childArea.id = a.id       
          )         
    GROUP BY tb.id          
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa3          
GROUP BY stGroup                

) AS unionTable             
ORDER BY orderIndex             

#6 Updated by Andreas Müller about 1 month ago

Query for 2:

-- EM Query Superareale

SELECT areaMarker, rankName, STATUS, n 
FROM
(
SELECT 'All' AS rankName, COUNT(*) AS n, orderIndex, stGroup AS STATUS, areaMarker
FROM (
   SELECT tb.id, 1 AS orderIndex,
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
         amt.titleCache areaMarker
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
        LEFT JOIN DefinedTermBase_Marker AM ON AM.DefinedTermBase_id = a.id
       LEFT JOIN Marker arMarker ON AM.markers_id = arMarker.id
       LEFT JOIN DefinedTermBase amt ON amt.id = arMarker.markerType_id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa
        AND arMarker.flag = 1 AND amt.titleCache IN ('cauc', 'mac', 'eur', 'med')
   GROUP BY areaMarker, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa
GROUP BY areaMarker, stGroup

UNION ALL

SELECT rankName, COUNT(*) AS n, orderIndex, stGroup AS status, areaMarker
FROM (
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
         amt.titleCache areaMarker
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
--      LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id
--      LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592
        LEFT JOIN DefinedTermBase_Marker AM ON AM.DefinedTermBase_id = a.id
       LEFT JOIN Marker arMarker ON AM.markers_id = arMarker.id
       LEFT JOIN DefinedTermBase amt ON amt.id = arMarker.markerType_id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa
--      AND (apo.id IS NULL OR apo.flag = 0)
        AND arMarker.flag = 1 AND amt.titleCache IN ('cauc', 'mac', 'eur', 'med')
    GROUP BY areaMarker, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa1
GROUP BY areaMarker, rankName, rankId, orderIndex, stGroup

UNION ALL

SELECT CONCAT('Apomictic-', rankName), COUNT(*) AS n, orderIndex + 100, stGroup AS status, areaMarker
FROM (
   SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
       CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
       amt.titleCache areaMarker
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
        LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id
        LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592
       LEFT JOIN DefinedTermBase_Marker AM ON AM.DefinedTermBase_id = a.id
       LEFT JOIN Marker arMarker ON AM.markers_id = arMarker.id
       LEFT JOIN DefinedTermBase amt ON amt.id = arMarker.markerType_id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa
        AND (apo.id IS NOT NULL OR apo.flag = 1)
        AND arMarker.flag = 1 AND amt.titleCache IN ('cauc', 'mac', 'eur', 'med')
    GROUP BY areaMarker, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa2
GROUP BY areaMarker, rankName, rankId, orderIndex, stGroup

UNION ALL

SELECT 'Taxa (Species+Subspecies)', COUNT(*) AS n, 10 AS orderindex, stGroup, areaMarker
FROM (
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
        CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
        amt.titleCache areaMarker
    FROM TaxonNode tn
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
--      LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id
--      LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592
       LEFT JOIN DefinedTermBase_Marker AM ON AM.DefinedTermBase_id = a.id
       LEFT JOIN Marker arMarker ON AM.markers_id = arMarker.id
       LEFT JOIN DefinedTermBase amt ON amt.id = arMarker.markerType_id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
       AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
       AND rank.orderIndex IN (45,47) 
--     AND (apo.id IS NULL OR apo.flag = 0)
       AND arMarker.flag = 1 AND amt.titleCache IN ('cauc', 'mac', 'eur', 'med')
       AND NOT EXISTS (
            SELECT *
           FROM TaxonNode childNode
                INNER JOIN TaxonBase childTaxon ON childTaxon.id = childNode.taxon_id
                INNER JOIN TaxonName childName ON childName.id = childTaxon.name_id
                INNER JOIN DefinedTermBase childRank ON childName.rank_id = childRank.id
                INNER JOIN DescriptionBase childDb ON childDb.taxon_id = childTaxon.id
                INNER JOIN DescriptionElementBase childDeb ON childDb.id = childDeb.inDescription_id
                INNER JOIN DefinedTermBase childArea ON childArea.id = childDeb.area_id
            WHERE childNode.parent_id = tn.id
               AND childTaxon.publish = 1 AND childDeb.DTYPE = 'Distribution'
              AND childRank.orderIndex = 47
              AND childArea.id = a.id
          )
    GROUP BY areaMarker, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa3
GROUP BY areaMarker, stGroup

) AS unionTable
ORDER BY areaMarker, orderIndex

#7 Updated by Andreas Müller about 1 month ago

  • Description updated (diff)

#8 Updated by Andreas Müller 25 days ago

  • Description updated (diff)

#9 Updated by Andreas Müller 25 days ago

  • Status changed from New to In Progress
  • % Done changed from 0 to 20

Query for 3:

 -- E+M Area statistics

SELECT areaSymbol, areaName, rankName, STATUS, n 
FROM
(
SELECT areaSymbol, areaName, 'All' AS rankName, COUNT(*) AS n, orderIndex, stGroup AS STATUS
FROM (
   SELECT tb.id taxonId, 1 AS orderIndex, 
          CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
         a.titleCache areaName, a.symbol areaSymbol
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077  
        AND rank.orderIndex >= 34
        AND NOT EXISTS (
        SELECT *
            FROM TaxonNode acNode
                INNER JOIN TaxonBase acTaxon ON acTaxon.id = acNode.taxon_id
                INNER JOIN TaxonName acName ON acName.id = acTaxon.name_id
                INNER JOIN DefinedTermBase acRank ON acName.rank_id = acRank.id
                INNER JOIN DescriptionBase acDb ON acDb.taxon_id = acTaxon.id
                INNER JOIN DescriptionElementBase acDeb ON acDb.id = acDeb.inDescription_id
                INNER JOIN DefinedTermBase acArea ON acArea.id = acDeb.area_id
            WHERE (acArea.partOf_id = a.id 
                    OR (a.symbol = 'Bt' AND acArea.symbol IN ('Es','Lt','La','Rf(K)') ) 
                    OR (a.symbol = 'Tcs' AND acArea.symbol IN ('Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'Cc' AND acArea.symbol IN ('Tcs','Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'SM' AND acArea.symbol IN ('Cg','Sr','Ko','Se')) 
                    OR (a.symbol = 'Sr' AND acArea.symbol IN ('Ko','Se')) 
                     )
            AND acTaxon.id = tb.id
            AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
       )
   GROUP BY a.id, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa
GROUP BY areaName, stGroup

UNION ALL

SELECT areaSymbol, areaName, rankName, COUNT(*) AS n, orderIndex, stGroup AS status
FROM (
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
         a.titleCache areaName, a.symbol areaSymbol
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34
        AND NOT EXISTS (
        SELECT *
            FROM TaxonNode acNode
                INNER JOIN TaxonBase acTaxon ON acTaxon.id = acNode.taxon_id
                INNER JOIN TaxonName acName ON acName.id = acTaxon.name_id
                INNER JOIN DefinedTermBase acRank ON acName.rank_id = acRank.id
                INNER JOIN DescriptionBase acDb ON acDb.taxon_id = acTaxon.id
                INNER JOIN DescriptionElementBase acDeb ON acDb.id = acDeb.inDescription_id
                INNER JOIN DefinedTermBase acArea ON acArea.id = acDeb.area_id
            WHERE (acArea.partOf_id = a.id 
                    OR (a.symbol = 'Bt' AND acArea.symbol IN ('Es','Lt','La','Rf(K)') ) 
                    OR (a.symbol = 'Tcs' AND acArea.symbol IN ('Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'Cc' AND acArea.symbol IN ('Tcs','Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'SM' AND acArea.symbol IN ('Cg','Sr','Ko','Se')) 
                    OR (a.symbol = 'Sr' AND acArea.symbol IN ('Ko','Se')) 
                     )
            AND acTaxon.id = tb.id
            AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
       )
    GROUP BY a.id, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa1
GROUP BY areaName, rankName, rankId, orderIndex, stGroup

UNION ALL

SELECT areaSymbol, areaName, CONCAT('Apomictic-', rankName), COUNT(*) AS n, orderIndex + 100, stGroup AS status
FROM (
   SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
       CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
       a.titleCache areaName, a.symbol areaSymbol
    FROM TaxonNode tn 
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
        LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id
        LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077  
        AND rank.orderIndex >= 34
        AND (apo.id IS NOT NULL OR apo.flag = 1)
        AND NOT EXISTS (
        SELECT *
            FROM TaxonNode acNode
                INNER JOIN TaxonBase acTaxon ON acTaxon.id = acNode.taxon_id
                INNER JOIN TaxonName acName ON acName.id = acTaxon.name_id
                INNER JOIN DefinedTermBase acRank ON acName.rank_id = acRank.id
                INNER JOIN DescriptionBase acDb ON acDb.taxon_id = acTaxon.id
                INNER JOIN DescriptionElementBase acDeb ON acDb.id = acDeb.inDescription_id
                INNER JOIN DefinedTermBase acArea ON acArea.id = acDeb.area_id
            WHERE (acArea.partOf_id = a.id 
                    OR (a.symbol = 'Bt' AND acArea.symbol IN ('Es','Lt','La','Rf(K)') ) 
                    OR (a.symbol = 'Tcs' AND acArea.symbol IN ('Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'Cc' AND acArea.symbol IN ('Tcs','Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'SM' AND acArea.symbol IN ('Cg','Sr','Ko','Se')) 
                    OR (a.symbol = 'Sr' AND acArea.symbol IN ('Ko','Se')) 
                     )
            AND acTaxon.id = tb.id
            AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
       )
    GROUP BY a.id, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa2
GROUP BY areaName, rankName, rankId, orderIndex, stGroup

UNION ALL

SELECT areaSymbol, areaName, 'Taxa (Species+Subspecies)', COUNT(*) AS n, 10 AS orderindex, stGroup
FROM (
    SELECT tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,
        CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup,
        a.titleCache areaName, a.symbol areaSymbol
    FROM TaxonNode tn
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id
        INNER JOIN TaxonName n ON n.id = tb.name_id
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
       AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'
       AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077  
        AND rank.orderIndex IN (45,47) 
       AND NOT EXISTS (
            SELECT *
           FROM TaxonNode childNode
                INNER JOIN TaxonBase childTaxon ON childTaxon.id = childNode.taxon_id
                INNER JOIN TaxonName childName ON childName.id = childTaxon.name_id
                INNER JOIN DefinedTermBase childRank ON childName.rank_id = childRank.id
                INNER JOIN DescriptionBase childDb ON childDb.taxon_id = childTaxon.id
                INNER JOIN DescriptionElementBase childDeb ON childDb.id = childDeb.inDescription_id
                INNER JOIN DefinedTermBase childArea ON childArea.id = childDeb.area_id
            WHERE childNode.parent_id = tn.id
               AND childTaxon.publish = 1 AND childDeb.DTYPE = 'Distribution'
              AND childRank.orderIndex = 47
              AND (childArea.id = a.id OR 
                   childArea.partOf_id = a.id 
                    OR (a.symbol = 'Bt' AND childArea.symbol IN ('Es','Lt','La','Rf(K)') ) 
                    OR (a.symbol = 'Tcs' AND childArea.symbol IN ('Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'Cc' AND childArea.symbol IN ('Tcs','Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'SM' AND childArea.symbol IN ('Cg','Sr','Ko','Se')) 
                    OR (a.symbol = 'Sr' AND childArea.symbol IN ('Ko','Se')) 
               )
               AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
          )
       AND NOT EXISTS (
        SELECT *
            FROM TaxonNode acNode
                INNER JOIN TaxonBase acTaxon ON acTaxon.id = acNode.taxon_id
                INNER JOIN TaxonName acName ON acName.id = acTaxon.name_id
                INNER JOIN DefinedTermBase acRank ON acName.rank_id = acRank.id
                INNER JOIN DescriptionBase acDb ON acDb.taxon_id = acTaxon.id
                INNER JOIN DescriptionElementBase acDeb ON acDb.id = acDeb.inDescription_id
                INNER JOIN DefinedTermBase acArea ON acArea.id = acDeb.area_id
            WHERE (acArea.partOf_id = a.id 
                    OR (a.symbol = 'Bt' AND acArea.symbol IN ('Es','Lt','La','Rf(K)') ) 
                    OR (a.symbol = 'Tcs' AND acArea.symbol IN ('Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'Cc' AND acArea.symbol IN ('Tcs','Ar','Ab','Ab(A)','Ab(N)','Gg','Gg(A)','Gg(D)','Gg(G)') ) 
                    OR (a.symbol = 'SM' AND acArea.symbol IN ('Cg','Sr','Ko','Se')) 
                    OR (a.symbol = 'Sr' AND acArea.symbol IN ('Ko','Se')) 
                     )
            AND acTaxon.id = tb.id
            AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
       )
    GROUP BY a.id, tb.id
    ORDER BY st.vocabulary_id, st.orderIndex) AS taxa3
GROUP BY areaName, stGroup

) AS unionTable
ORDER BY areaSymbol, orderIndex

#10 Updated by Andreas Müller 25 days ago

"Family" Query 4:

 -- E+M Families                

SELECT famName, rankName, STATUS, n                 
FROM                
(               
SELECT famName, 'All' AS rankName, COUNT(*) AS n, orderIndex, stGroup AS STATUS             
FROM (              
   SELECT tn.treeIndex, tb.id, 1 AS orderIndex,     
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup         
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34 -- not necessary as distribution data does not exist for higher taxa      
   GROUP BY tb.id) AS taxa
    INNER JOIN (SELECT famTn.treeIndex AS famTreeIndex, famN.nameCache famName
            FROM TaxonNode famTn            
                INNER JOIN TaxonBase famT ON famT.id = famTn.taxon_id       
                INNER JOIN TaxonName famN ON famN.id = famT.name_id
            WHERE famN.rank_id = 820 AND famT.publish = 1
        ) AS famNode ON famNode.famTreeIndex = LEFT(taxa.treeIndex, LENGTH(famNode.famTreeIndex))           
GROUP BY famName, stGroup               

UNION ALL               

SELECT famName, rankName, COUNT(*) AS n, orderIndex, stGroup AS status              
FROM (              
    SELECT tn.treeIndex, tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,  
         CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup         
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id          
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34               
    GROUP BY tb.id) AS taxa1
    INNER JOIN (SELECT famTn.treeIndex AS famTreeIndex, famN.nameCache famName
            FROM TaxonNode famTn            
                INNER JOIN TaxonBase famT ON famT.id = famTn.taxon_id       
                INNER JOIN TaxonName famN ON famN.id = famT.name_id
            WHERE famN.rank_id = 820 AND famT.publish = 1
        ) AS famNode ON famNode.famTreeIndex = LEFT(taxa1.treeIndex, LENGTH(famNode.famTreeIndex))          
GROUP BY famName, rankName, rankId, orderIndex, stGroup             

UNION ALL               

SELECT famName, CONCAT('Apomictic-', rankName), COUNT(*) AS n, orderIndex + 100, stGroup AS status              
FROM (              
   SELECT tn.treeIndex, tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,       
       CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup               
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id
        LEFT JOIN TaxonBase_Marker TBM ON TBM.TaxonBase_id = tb.id      
        LEFT JOIN Marker apo ON TBM.markers_id = apo.id AND apo.markerType_id = 2592        
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
        AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'      
        AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex >= 34   
        AND (apo.id IS NOT NULL OR apo.flag = 1)        
    GROUP BY  tb.id) AS taxa2
    INNER JOIN (SELECT famTn.treeIndex AS famTreeIndex, famN.nameCache famName
            FROM TaxonNode famTn            
                INNER JOIN TaxonBase famT ON famT.id = famTn.taxon_id       
                INNER JOIN TaxonName famN ON famN.id = famT.name_id
            WHERE famN.rank_id = 820 AND famT.publish = 1
        ) AS famNode ON famNode.famTreeIndex = LEFT(taxa2.treeIndex, LENGTH(famNode.famTreeIndex))              
GROUP BY famName, rankName, rankId, orderIndex, stGroup             

UNION ALL               

SELECT famName, 'Taxa (Species+Subspecies)', COUNT(*) AS n, 10 AS orderindex, stGroup               
FROM (              
    SELECT tn.treeIndex, tb.id taxonId, rank.titleCache rankName, rank.id rankId, rank.orderIndex,      
        CASE WHEN LEFT(GROUP_CONCAT(DISTINCT st.idInVocabulary ORDER BY st.vocabulary_id, st.orderIndex ASC SEPARATOR ' '),2) IN ('n ','nd','nq','-?','ne') THEN 'native' ELSE 'non-native' END AS stGroup      
    FROM TaxonNode tn           
        INNER JOIN TaxonBase tb ON tb.id = tn.taxon_id      
        INNER JOIN TaxonName n ON n.id = tb.name_id     
        INNER JOIN DefinedTermBase rank ON rank.id = n.rank_id      
        INNER JOIN DescriptionBase db ON db.taxon_id = tb.id        
        INNER JOIN DescriptionElementBase deb ON db.id = deb.inDescription_id       
        INNER JOIN DefinedTermBase a ON a.id = deb.area_id      
        INNER JOIN DefinedTermBase st ON deb.status_id = st.id      
    WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'         
       AND tb.publish = 1 AND deb.DTYPE = 'Distribution' AND a.symbol <> 'EM' AND a.symbol NOT LIKE 'Rs%'           
       AND (st.absenceTerm = 0 OR st.id = 2066) AND NOT st.id = 2077
        AND rank.orderIndex IN (45,47)          
       AND NOT EXISTS (         
            SELECT *    
           FROM TaxonNode childNode     
                INNER JOIN TaxonBase childTaxon ON childTaxon.id = childNode.taxon_id
                INNER JOIN TaxonName childName ON childName.id = childTaxon.name_id
                INNER JOIN DefinedTermBase childRank ON childName.rank_id = childRank.id
                INNER JOIN DescriptionBase childDb ON childDb.taxon_id = childTaxon.id
                INNER JOIN DescriptionElementBase childDeb ON childDb.id = childDeb.inDescription_id
                INNER JOIN DefinedTermBase childArea ON childArea.id = childDeb.area_id
            WHERE childNode.parent_id = tn.id   
               AND childTaxon.publish = 1 AND childDeb.DTYPE = 'Distribution'   
              AND childRank.orderIndex = 47     
              AND childArea.id = a.id       
          )         
    GROUP BY tb.id) AS taxa3
    INNER JOIN (SELECT famTn.treeIndex AS famTreeIndex, famN.nameCache famName
            FROM TaxonNode famTn            
                INNER JOIN TaxonBase famT ON famT.id = famTn.taxon_id       
                INNER JOIN TaxonName famN ON famN.id = famT.name_id
            WHERE famN.rank_id = 820 AND famT.publish = 1
        ) AS famNode ON famNode.famTreeIndex = LEFT(taxa3.treeIndex, LENGTH(famNode.famTreeIndex))          
GROUP BY famName, stGroup               

) AS unionTable             
ORDER BY famName, orderIndex                        

#11 Updated by Andreas Müller 22 days ago

Zeitabhängigkeit:


SELECT minYear, COUNT(*) AS n
FROM(

SELECT accId, accName.titleCache, MIN(Y) minYear
FROM (
SELECT taxRef.accId, taxRef.refId, CASE WHEN inYear IS NULL OR year < inYear THEN year ELSE inYear END AS Y,
year, inYear
FROM (
SELECT tax.*, 
CAST(LEFT(ref.datePublished_start, 4) AS INT) AS YEAR, CAST(LEFT(inRef.datePublished_start, 4) AS INT) AS inYear
FROM (
SELECT acc.id accId, accRef.id refId
FROM TaxonNode tn
        INNER JOIN TaxonBase acc ON acc.id = tn.taxon_id
        INNER JOIN TaxonName accName ON accName.id = acc.name_id
        LEFT JOIN Reference accRef ON accRef.id = accName.nomenclaturalReference_id
        WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
       AND acc.publish = 1
       AND accName.rank_id = 802

        UNION ALL

SELECT acc.id, synRef.id
FROM TaxonNode tn
        INNER JOIN TaxonBase acc ON acc.id = tn.taxon_id
        INNER JOIN TaxonName accName ON accName.id = acc.name_id
        INNER JOIN TaxonBase syn ON syn.acceptedTaxon_id = acc.id
        INNER JOIN TaxonName synName ON synName.id = syn.name_id
        INNER JOIN Reference synRef ON synRef.id = synName.nomenclaturalReference_id
        WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%'
       AND acc.publish = 1
       AND accName.rank_id = 802
) AS tax
LEFT JOIN Reference ref ON ref.id = tax.refId
LEFT JOIN Reference inRef ON inRef.id = ref.inReference_id
-- WHERE tax.accId = 116693
) AS taxRef
) AS taxRef2
INNER JOIN TaxonBase acc ON acc.id = taxRef2.accId
INNER JOIN TaxonName accName ON accName.id = acc.name_id
GROUP BY accId
HAVING minYear IS NOT NULL
ORDER BY minYear, accId

) AS taxRef3
GROUP BY minYear

#12 Updated by Andreas Müller 22 days ago

  • Description updated (diff)

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)