Continuous Aggregates Views Structure

ERAS utilizes Continuous aggregates which are materialized views bucketed by time in order to optimize some of it's queries. Continuous aggregates can be queried similarly as a postgres table.

Listed below are our current continuous aggregates and its structures.

daily_classification_count

This continuous aggregate records for each day how many total classifications have been done for that day. (Day is in UTC)

Column Name	Type
day	timestamp
classification_count	integer

daily_classification_count_per_project

This continuous aggregate records for each day how many total classifications have been done for that day for a specific project. (Day is in UTC)

Column Name	Type
day	date
project_id	integer
classification_count	integer

daily_classification_count_per_workflow

This continuous aggregate records for each day how many total classifications have been done for that day for a specific workflow. (Day is in UTC)

Column Name	Type
day	date
workflow_id	integer
classification_count	integer

daily_user_classification_count_and_time

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_user_classification_count_and_time_per_project

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user for a specific project and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_id	integer
project_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_user_classification_count_and_time_per_workflow

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user for a specific workflow and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_id	integer
workflow_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time_per_project

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group for a specific project and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
project_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time_per_workflow

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group for a specific workflow and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
workflow_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time_per_user

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group for a specific user and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
user_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time_per_user_per_project

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group for a specific user for a specific project and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
user_id	integer
project_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_group_classification_count_and_time_per_user_per_workflow

This continuous aggregate records for each day how many total classifications have been done for that day for a specific user group for a specific user for a specific workflow and how long those classifications take . (Day is in UTC)

Column Name	Type
day	date
user_group_id	integer
user_id	integer
workflow_id	integer
classification_count	integer
total_session_time	float (in seconds)

daily_comment_count

This continuous aggregate records for each day how many comments have been done for that day . (Day is in UTC)

Column Name	Type
day	date
comment_count	integer

daily_comment_count_per_user

This continuous aggregate records for each day how many comments have been done for a specific user for that day . (Day is in UTC)

Column Name	Type
day	date
user_id	integer
comment_count	integer

daily_comment_count_per_project_and_user

This continuous aggregate records for each day how many comments have been done for a specific user for a specific user for that day . (Day is in UTC)

Column Name	Type
day	date
project_id	integer
user_id	integer
comment_count	integer