open_chromatin_base_epigenome

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

species_name

text

2147483647

√

null

assembly_name

varchar

2147483647

√

null

uuid

2147483647

√

null

material

text

2147483647

√

null

epigenome_name

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

extended_age

text

2147483647

√

null

age

text

2147483647

√

null

age_for_label

text

2147483647

√

null

organs

_text

2147483647

√

null

systems

_text

2147483647

√

null

tissue_group

text

2147483647

√

null

biological_group

text

2147483647

√

null

sample_type

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH slims_before_agg AS (
         SELECT curated_open_chromatin_assay_metadata.epigenome_id,
                CASE
                    WHEN (curated_open_chromatin_assay_metadata.organ_slims = ARRAY[]::text[]) THEN ARRAY['EMPTY'::text]
                    WHEN (curated_open_chromatin_assay_metadata.organ_slims IS NULL) THEN ARRAY['UNANNOTATED'::text]
                    ELSE curated_open_chromatin_assay_metadata.organ_slims
                END AS organs_mod,
                CASE
                    WHEN (curated_open_chromatin_assay_metadata.system_slims = ARRAY[]::text[]) THEN ARRAY['EMPTY'::text]
                    WHEN (curated_open_chromatin_assay_metadata.system_slims IS NULL) THEN ARRAY['UNANNOTATED'::text]
                    ELSE curated_open_chromatin_assay_metadata.system_slims
                END AS systems_mod
           FROM curated_open_chromatin_assay_metadata
        ), metadata_epig_with_ids AS (
         SELECT DISTINCT curated_open_chromatin_assay_metadata.species_name,
            curated_open_chromatin_assay_metadata.assembly_name,
            curated_open_chromatin_assay_metadata.epigenome_name,
            curated_open_chromatin_assay_metadata.epigenome_id,
            curated_open_chromatin_assay_metadata.epigenome_description,
            array_agg(DISTINCT curated_open_chromatin_assay_metadata.material) AS material,
            curated_open_chromatin_assay_metadata.term,
            curated_open_chromatin_assay_metadata.term_ontology,
            unnest_2d_1d(array_agg(DISTINCT slims_before_agg.organs_mod)) AS organs,
            unnest_2d_1d(array_agg(DISTINCT slims_before_agg.systems_mod)) AS systems,
            array_agg(DISTINCT curated_open_chromatin_assay_metadata.sex) AS sex,
            array_agg(DISTINCT curated_open_chromatin_assay_metadata.life_stage) AS life_stage,
            array_agg(DISTINCT curated_open_chromatin_assay_metadata.age ORDER BY curated_open_chromatin_assay_metadata.age) AS age,
            array_agg(DISTINCT curated_open_chromatin_assay_metadata.age_unit) AS age_unit
           FROM (curated_open_chromatin_assay_metadata
             JOIN slims_before_agg ON ((curated_open_chromatin_assay_metadata.epigenome_id = slims_before_agg.epigenome_id)))
          GROUP BY curated_open_chromatin_assay_metadata.species_name, curated_open_chromatin_assay_metadata.assembly_name, curated_open_chromatin_assay_metadata.epigenome_name, curated_open_chromatin_assay_metadata.epigenome_id, curated_open_chromatin_assay_metadata.epigenome_description, curated_open_chromatin_assay_metadata.term, curated_open_chromatin_assay_metadata.term_ontology
        ), slims_after_agg AS (
         SELECT metadata_epig_with_ids_1.epigenome_id,
                CASE
                    WHEN (metadata_epig_with_ids_1.organs = ARRAY['EMPTY'::text]) THEN ARRAY[]::text[]
                    WHEN (metadata_epig_with_ids_1.organs = ARRAY['UNANNOTATED'::text]) THEN NULL::text[]
                    ELSE metadata_epig_with_ids_1.organs
                END AS organs,
                CASE
                    WHEN (metadata_epig_with_ids_1.systems = ARRAY['EMPTY'::text]) THEN ARRAY[]::text[]
                    WHEN (metadata_epig_with_ids_1.systems = ARRAY['UNANNOTATED'::text]) THEN NULL::text[]
                    ELSE metadata_epig_with_ids_1.systems
                END AS systems,
                CASE
                    WHEN ('unknown'::text = ANY (metadata_epig_with_ids_1.age)) THEN NULL::numeric[]
                    ELSE (metadata_epig_with_ids_1.age)::numeric[]
                END AS age,
            metadata_epig_with_ids_1.age_unit
           FROM metadata_epig_with_ids metadata_epig_with_ids_1
        ), handle_age_after_agg AS (
         SELECT metadata_epig_with_ids_1.epigenome_id,
                CASE
                    WHEN ('unknown'::text = ANY (metadata_epig_with_ids_1.age)) THEN NULL::numeric[]
                    ELSE (metadata_epig_with_ids_1.age)::numeric[]
                END AS age
           FROM metadata_epig_with_ids metadata_epig_with_ids_1
        ), sort_agg_age AS (
         SELECT handle_age_after_agg.epigenome_id,
            ARRAY( SELECT unnest(handle_age_after_agg.age) AS unnest
                  ORDER BY (unnest(handle_age_after_agg.age))) AS age_sorted
           FROM handle_age_after_agg
        ), handle_sorted_age AS (
         SELECT sort_agg_age.epigenome_id,
                CASE
                    WHEN (ARRAY[]::numeric[] = sort_agg_age.age_sorted) THEN NULL::text
                    WHEN (array_length(sort_agg_age.age_sorted, 1) = 1) THEN ((sort_agg_age.age_sorted[1])::real)::text
                    ELSE ((sort_agg_age.age_sorted)::real[])::text
                END AS age
           FROM sort_agg_age
        ), handle_age_unit_after_agg AS (
         SELECT metadata_epig_with_ids_1.epigenome_id,
                CASE
                    WHEN ('unknown'::text = ANY (metadata_epig_with_ids_1.age)) THEN NULL::text
                    WHEN ((('embryo'::text = ANY (metadata_epig_with_ids_1.life_stage)) OR ('embryonic'::text = ANY (metadata_epig_with_ids_1.life_stage))) AND ('day'::text = ANY (metadata_epig_with_ids_1.age_unit))) THEN 'days post fertilisation'::text
                    ELSE array_to_string(metadata_epig_with_ids_1.age_unit, ', '::text)
                END AS age_unit
           FROM metadata_epig_with_ids metadata_epig_with_ids_1
        ), handle_age_unit_singular_plural AS (
         SELECT handle_age_unit_after_agg.epigenome_id,
                CASE
                    WHEN ((handle_age_unit_after_agg.age_unit <> 'days post fertilisation'::text) AND (handle_age_unit_after_agg.age_unit <> 'thermal time'::text) AND (sort_agg_age.age_sorted[1] <> (1)::numeric)) THEN concat(handle_age_unit_after_agg.age_unit, 's')
                    ELSE handle_age_unit_after_agg.age_unit
                END AS age_unit
           FROM (handle_age_unit_after_agg
             JOIN sort_agg_age ON ((handle_age_unit_after_agg.epigenome_id = sort_agg_age.epigenome_id)))
        ), age_range AS (
         SELECT sort_agg_age.epigenome_id,
                CASE
                    WHEN (ARRAY[]::numeric[] = sort_agg_age.age_sorted) THEN NULL::text
                    WHEN (array_length(sort_agg_age.age_sorted, 1) = 1) THEN ((sort_agg_age.age_sorted[1])::real)::text
                    ELSE concat(((sort_agg_age.age_sorted[1])::real)::text, '-', ((sort_agg_age.age_sorted[array_upper(sort_agg_age.age_sorted, 1)])::real)::text)
                END AS age_range
           FROM sort_agg_age
        ), shorten_age_unit AS (
         SELECT handle_age_unit_after_agg.epigenome_id,
                CASE
                    WHEN (handle_age_unit_after_agg.age_unit = 'days post fertilisation'::text) THEN 'dpf'::text
                    WHEN (handle_age_unit_after_agg.age_unit = 'month'::text) THEN 'mo'::text
                    WHEN (handle_age_unit_after_agg.age_unit = 'year'::text) THEN 'y'::text
                    WHEN (handle_age_unit_after_agg.age_unit = 'week'::text) THEN 'w'::text
                    WHEN (handle_age_unit_after_agg.age_unit = 'day'::text) THEN 'd'::text
                    WHEN (handle_age_unit_after_agg.age_unit = 'thermal time'::text) THEN 'tt'::text
                    ELSE handle_age_unit_after_agg.age_unit
                END AS short_age_unit
           FROM handle_age_unit_after_agg
        ), shorten_sex AS (
         SELECT metadata_epig_with_ids_1.epigenome_id,
                CASE
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] @> ARRAY['unknown'::text]) THEN NULL::text
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] @> ARRAY['mixed'::text]) THEN NULL::text
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] @> ARRAY['male'::text, 'female'::text]) THEN NULL::text
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] = ARRAY['male'::text]) THEN 'm'::text
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] = ARRAY['female'::text]) THEN 'f'::text
                    WHEN ((metadata_epig_with_ids_1.sex)::text[] = ARRAY['phenotypic'::text]) THEN NULL::text
                    ELSE 'Err: not contemplated case'::text
                END AS sex_for_label
           FROM metadata_epig_with_ids metadata_epig_with_ids_1
        ), lifestage_in_label AS (
         SELECT metadata_epig_with_ids_1.epigenome_id,
                CASE
                    WHEN ((((metadata_epig_with_ids_1.sex)::text[] @> ARRAY['phenotypic'::text]) OR ((metadata_epig_with_ids_1.sex)::text[] @> ARRAY['unknown'::text])) AND (metadata_epig_with_ids_1.term = 'embryo'::text)) THEN metadata_epig_with_ids_1.life_stage
                    ELSE NULL::text[]
                END AS life_stage_info_for_label
           FROM metadata_epig_with_ids metadata_epig_with_ids_1
        )
 SELECT DISTINCT metadata_epig_with_ids.species_name,
    metadata_epig_with_ids.assembly_name,
    metadata_epig_with_ids.epigenome_id AS id,
    lower(array_to_string(metadata_epig_with_ids.material, ', '::text)) AS material,
    metadata_epig_with_ids.epigenome_name,
    metadata_epig_with_ids.term,
        CASE
            WHEN (metadata_epig_with_ids.term <> 'embryo'::text) THEN metadata_epig_with_ids.term
            ELSE NULL::text
        END AS term_for_label,
    metadata_epig_with_ids.term_ontology,
    array_to_string(metadata_epig_with_ids.sex, ', '::text) AS sex,
    shorten_sex.sex_for_label,
    array_to_string(metadata_epig_with_ids.life_stage, ', '::text) AS life_stage,
        CASE
            WHEN ((((metadata_epig_with_ids.sex)::text[] @> ARRAY['phenotypic'::text]) OR ((metadata_epig_with_ids.sex)::text[] @> ARRAY['unknown'::text])) AND (metadata_epig_with_ids.term = 'embryo'::text)) THEN array_to_string(metadata_epig_with_ids.life_stage, ', '::text)
            ELSE NULL::text
        END AS life_stage_for_label,
        CASE
            WHEN (handle_sorted_age.age IS NULL) THEN NULL::text
            ELSE concat(handle_sorted_age.age, ' ', handle_age_unit_singular_plural.age_unit)
        END AS extended_age,
        CASE
            WHEN (handle_sorted_age.age IS NULL) THEN NULL::text
            ELSE concat(age_range.age_range, ' ', shorten_age_unit.short_age_unit)
        END AS age,
        CASE
            WHEN (((metadata_epig_with_ids.assembly_name)::text = 'GRCm39'::text) AND (array_to_string(metadata_epig_with_ids.life_stage, ', '::text) = 'embryonic'::text) AND (age_range.age_range IS NOT NULL)) THEN concat('E', age_range.age_range)
            WHEN (handle_sorted_age.age IS NULL) THEN NULL::text
            ELSE concat(age_range.age_range, ' ', shorten_age_unit.short_age_unit)
        END AS age_for_label,
    slims_after_agg.organs,
    slims_after_agg.systems,
    biosample_term_group.term_group AS tissue_group,
    biosample_biological_group.biological_group,
    biosample_sample_type.sample_type
   FROM (((((((((metadata_epig_with_ids
     JOIN handle_sorted_age ON ((metadata_epig_with_ids.epigenome_id = handle_sorted_age.epigenome_id)))
     JOIN age_range ON ((metadata_epig_with_ids.epigenome_id = age_range.epigenome_id)))
     JOIN handle_age_unit_singular_plural ON ((metadata_epig_with_ids.epigenome_id = handle_age_unit_singular_plural.epigenome_id)))
     JOIN shorten_age_unit ON ((metadata_epig_with_ids.epigenome_id = shorten_age_unit.epigenome_id)))
     JOIN slims_after_agg ON ((metadata_epig_with_ids.epigenome_id = slims_after_agg.epigenome_id)))
     JOIN shorten_sex ON ((metadata_epig_with_ids.epigenome_id = shorten_sex.epigenome_id)))
     JOIN biosample_term_group ON ((metadata_epig_with_ids.epigenome_id = biosample_term_group.epigenome_id)))
     LEFT JOIN biosample_biological_group ON ((metadata_epig_with_ids.epigenome_id = biosample_biological_group.epigenome_id)))
     LEFT JOIN biosample_sample_type ON ((metadata_epig_with_ids.epigenome_id = biosample_sample_type.epigenome_id)))
  WHERE (((metadata_epig_with_ids.assembly_name)::text <> 'ARS-UCD1.3'::text) AND ((metadata_epig_with_ids.assembly_name)::text <> 'GRCg6a'::text) AND (metadata_epig_with_ids.term <> 'iPS-18c'::text));

open_chromatin_base_epigenome

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views