Analytics Reporting Partition Generation Algorithm

A partition describes a date-based subset of data to calculate analytics on. There are three types of partitions:

The partition generation process requires the following parameters:

def fetch_partition_dates (period_unit, group_size, number_of_groups, now):
  # Fetch all of the partition dates for reporting metrics
  # Returns number_of_groups records
  
  # Truncate the date to get the start of the current group period
    #. e.g. truncate("2022-11-16", month) returns "2022-11-01"
    #. e.g. truncate("2022-11-16", year) returns "2022-01-01"
  current_period_start = truncate(now, period_unit)
  
  # Get the time interval to subtract from the current_period_start to find the start of the
  # first group period being returned
    #. e.g. interval(12, month) returns "12 months"
    #. e.g. interval(3, year) returns "3 years"
  interval_to_subtract = interval(group_size * (number_of_groups - 1), period_unit)  
  
  # Get the period of time to add to move from the start of one period to the start of the next period
  interval_to_add = interval(group_size, period_unit)
    
  group_start = current_period_start - interval_to_subtract
  period_index = 0
  
  while period_index < number_of_groups:
    
    group_end = group_start + interval_to_add;
    group_end = group_end - '0.000001 SECONDS';  # This period ends a microsecond before the next period starts
    
    group_name = # TBD - See ss_reporting_period_groups for details on how then name is formed
  
    return group_name, group_start, group_end
    
    period_index += 1
    
def fetch_partitions (period_unit, group_size, number_of_groups, now, 
                      return_all_data, return_period_data, return_group_data):
  # Fetch all of the partitions for reporting metrics     
  # Reutrns 1 or more records, depending on the return_* parameters                 
                      
  # Return the single "all" data  record
  # No need for calculations for the "all" group 
  if return_all_data:
    return "all", "All/Current", null, now

  # Return the single "period" data record
  if return_period_data:
    partition_type = "period"
    group_name, group_start, group_end = fetch_partition_dates (
      period_unit, group_size * number_of_groups, 1, now):

    return partition_type, group_name, group_start, group_end
    
  # Return the multiple "groups" data records
  if return_period_data:
    partition_type = "groups"
    for group_name, group_start, group_end in fetch_partition_dates (
      period_unit, group_size, number_of_groups, now):
      return partition_type, group_name, group_start, group_end    

When selecting user data, there are three key dates that control the data returned:

When selecting data from the database, you should:

The pseudo-code below shows how you might select the number of assessments for the returned partitions

# Assumes "now" holds the effective now time and "partitions" is a list of the partitions to report on
for partition in partitions:
  # Set the start date for selecting
  if partition.start_group:
     select_start = partition.start_group
  else:
    select_start = '1980-01-01 00:00:00';
  
  # Set the end date for selecting
  if not now:
    now = NOW()
    
  select_end = min(partition.end_group, now)
  
  # Fetch the data
  assessments_for_partion = SELECT COUNT(*) 
                            FROM assessment_assessment
                            WHERE assessment_date BETWEEN select_start AND select_end

Documents how we can return one partition per site if required