base_epigenome_replicates

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

species_name

text

2147483647

√

null

assembly_name

varchar

2147483647

√

null

base_epigenome_id

uuid

2147483647

√

null

epigenome.epigenome_id

Implied Constraint

material

text

2147483647

√

null

term

text

2147483647

√

null

term_for_label

text

2147483647

√

null

term_ontology

text

2147483647

√

null

sex

text

2147483647

√

null

sex_for_label

text

2147483647

√

null

life_stage

text

2147483647

√

null

life_stage_for_label

text

2147483647

√

null

age_value

text

2147483647

√

null

age_unit

text

2147483647

√

null

modified_age_unit

text

2147483647

√

null

extended_age

text

2147483647

√

null

short_age

text

2147483647

√

null

age_for_label

text

2147483647

√

null

experiment_type

text

2147483647

√

null

experiment_target

text

2147483647

√

null

biosample_accession

text

2147483647

√

null

data_source

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH metadata_epig_expr_with_ids AS (
         SELECT DISTINCT me_1.epigenome_id,
            me_1.experiment_type,
            me_1.experiment_target,
            me_1.data_source,
            me_1.biosample_accession,
            array_agg(DISTINCT me_1.material) AS material,
            me_1.term,
            me_1.term_ontology,
            array_agg(DISTINCT me_1.sex) AS sex,
            array_agg(DISTINCT me_1.life_stage) AS life_stage,
            array_agg(DISTINCT me_1.age ORDER BY me_1.age) AS age,
            array_agg(DISTINCT me_1.age_unit) AS age_unit,
            me_1.species_name,
            me_1.assembly_name
           FROM curated_experiment_metadata me_1
          WHERE (me_1.term <> 'iPS-18c'::text)
          GROUP BY me_1.epigenome_id, me_1.term, me_1.term_ontology, me_1.experiment_type, me_1.experiment_target, me_1.data_source, me_1.biosample_accession, me_1.species_name, me_1.assembly_name
        ), age_n_sex_preprocessing AS (
         SELECT metadata_epig_expr_with_ids.epigenome_id,
            metadata_epig_expr_with_ids.experiment_type,
            metadata_epig_expr_with_ids.experiment_target,
            metadata_epig_expr_with_ids.data_source,
            metadata_epig_expr_with_ids.biosample_accession,
            metadata_epig_expr_with_ids.material,
            metadata_epig_expr_with_ids.term,
            metadata_epig_expr_with_ids.term_ontology,
            metadata_epig_expr_with_ids.sex,
            metadata_epig_expr_with_ids.life_stage,
            metadata_epig_expr_with_ids.age,
            metadata_epig_expr_with_ids.age_unit,
            metadata_epig_expr_with_ids.species_name,
            metadata_epig_expr_with_ids.assembly_name,
            ARRAY( SELECT unnest(
                        CASE
                            WHEN ('unknown'::text = ANY (metadata_epig_expr_with_ids.age)) THEN NULL::numeric[]
                            ELSE (metadata_epig_expr_with_ids.age)::numeric[]
                        END) AS unnest
                  ORDER BY (unnest(
                        CASE
                            WHEN ('unknown'::text = ANY (metadata_epig_expr_with_ids.age)) THEN NULL::numeric[]
                            ELSE (metadata_epig_expr_with_ids.age)::numeric[]
                        END))) AS age_sorted,
                CASE
                    WHEN ('unknown'::text = ANY (metadata_epig_expr_with_ids.age)) THEN NULL::text
                    WHEN ((('embryo'::text = ANY (metadata_epig_expr_with_ids.life_stage)) OR ('embryonic'::text = ANY (metadata_epig_expr_with_ids.life_stage))) AND ('day'::text = ANY (metadata_epig_expr_with_ids.age_unit))) THEN 'days post fertilisation'::text
                    ELSE array_to_string(metadata_epig_expr_with_ids.age_unit, ', '::text)
                END AS age_unit_processed,
                CASE
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] @> ARRAY['unknown'::text]) THEN NULL::text
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] @> ARRAY['mixed'::text]) THEN NULL::text
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] @> ARRAY['male'::text, 'female'::text]) THEN NULL::text
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] = ARRAY['male'::text]) THEN 'm'::text
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] = ARRAY['female'::text]) THEN 'f'::text
                    WHEN ((metadata_epig_expr_with_ids.sex)::text[] = ARRAY['phenotypic'::text]) THEN NULL::text
                    ELSE 'Err: not contemplated case'::text
                END AS sex_for_label
           FROM metadata_epig_expr_with_ids
        )
 SELECT asp.species_name,
    asp.assembly_name,
    asp.epigenome_id AS base_epigenome_id,
    lower(array_to_string(asp.material, ', '::text)) AS material,
    asp.term,
        CASE
            WHEN (asp.term <> 'embryo'::text) THEN asp.term
            ELSE NULL::text
        END AS term_for_label,
    asp.term_ontology,
    array_to_string(asp.sex, ', '::text) AS sex,
    asp.sex_for_label,
    array_to_string(asp.life_stage, ', '::text) AS life_stage,
        CASE
            WHEN (((asp.sex)::text[] @> ARRAY['phenotypic'::text, 'unknown'::text]) AND (asp.term = 'embryo'::text)) THEN array_to_string(asp.life_stage, ', '::text)
            ELSE NULL::text
        END AS life_stage_for_label,
        CASE
            WHEN (array_length(asp.age_sorted, 1) = 1) THEN ((asp.age_sorted[1])::real)::text
            ELSE NULL::text
        END AS age_value,
    array_to_string(asp.age_unit, ', '::text) AS age_unit,
        CASE
            WHEN ((asp.age_unit_processed <> ALL (ARRAY['days post fertilisation'::text, 'thermal time'::text])) AND (asp.age_sorted[1] <> (1)::numeric)) THEN concat(asp.age_unit_processed, 's')
            ELSE asp.age_unit_processed
        END AS modified_age_unit,
        CASE
            WHEN (array_length(asp.age_sorted, 1) = 1) THEN concat(((asp.age_sorted[1])::real)::text, ' ',
            CASE
                WHEN ((asp.age_unit_processed <> ALL (ARRAY['days post fertilisation'::text, 'thermal time'::text])) AND (asp.age_sorted[1] <> (1)::numeric)) THEN concat(asp.age_unit_processed, 's')
                ELSE asp.age_unit_processed
            END)
            ELSE NULL::text
        END AS extended_age,
    ((
        CASE
            WHEN (array_length(asp.age_sorted, 1) IS NULL) THEN NULL::text
            WHEN (array_length(asp.age_sorted, 1) = 1) THEN ((asp.age_sorted[1])::real)::text
            ELSE concat(((asp.age_sorted[1])::real)::text, '-', ((asp.age_sorted[array_upper(asp.age_sorted, 1)])::real)::text)
        END || ' '::text) ||
        CASE asp.age_unit_processed
            WHEN 'days post fertilisation'::text THEN 'dpf'::text
            WHEN 'month'::text THEN 'mo'::text
            WHEN 'year'::text THEN 'y'::text
            WHEN 'week'::text THEN 'w'::text
            WHEN 'day'::text THEN 'd'::text
            WHEN 'thermal time'::text THEN 'tt'::text
            ELSE asp.age_unit_processed
        END) AS short_age,
        CASE
            WHEN (array_length(asp.age_sorted, 1) IS NULL) THEN NULL::text
            WHEN (((asp.assembly_name)::text = 'GRCm39'::text) AND (array_to_string(asp.life_stage, ', '::text) = 'embryonic'::text)) THEN concat('E',
            CASE
                WHEN (array_length(asp.age_sorted, 1) = 1) THEN ((asp.age_sorted[1])::real)::text
                ELSE concat(((asp.age_sorted[1])::real)::text, '-', ((asp.age_sorted[array_upper(asp.age_sorted, 1)])::real)::text)
            END)
            ELSE ((
            CASE
                WHEN (array_length(asp.age_sorted, 1) IS NULL) THEN NULL::text
                WHEN (array_length(asp.age_sorted, 1) = 1) THEN ((asp.age_sorted[1])::real)::text
                ELSE concat(((asp.age_sorted[1])::real)::text, '-', ((asp.age_sorted[array_upper(asp.age_sorted, 1)])::real)::text)
            END || ' '::text) ||
            CASE asp.age_unit_processed
                WHEN 'days post fertilisation'::text THEN 'dpf'::text
                WHEN 'month'::text THEN 'mo'::text
                WHEN 'year'::text THEN 'y'::text
                WHEN 'week'::text THEN 'w'::text
                WHEN 'day'::text THEN 'd'::text
                WHEN 'thermal time'::text THEN 'tt'::text
                ELSE asp.age_unit_processed
            END)
        END AS age_for_label,
    asp.experiment_type,
    asp.experiment_target,
    asp.biosample_accession,
    asp.data_source
   FROM age_n_sex_preprocessing asp;

base_epigenome_replicates

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views