Блог

Проблема: ежемесячное отдельное количество участников с первой даты считывания гена до отмены участника.

Участники могут иметь более одного чтения в месяц. Они могут продолжать получать столько чтений, сколько захотят.

Пример:

 member_id    date       gene_a_measurement_done    gene_b_measurement_done
5557153     1/1/2010    y                      
5557153     2/1/2010                                y
222458      2/1/2010    y                           y
222458      1/1/2011                                y
707222      1/1/2011    y
  

В другой таблице указана дата отмены участника:

 member_id    status      date    
5557153     Cancelled    5/1/2011           
222458      Cancelled    12/1/9999      
707222     Cancelled     12/1/9999  
  

Ожидаемый результат :

 month    distinct_count_of_member_with_gene_a_measurement      distinct_count_of_member_with_gene_b_measurement    
1/1/10              1                                       0
2/1/10              2                                       2
3/1/10              2                                       2
4/1/10              2                                       2
5/1/10              1                                       1
6/1/10              1                                       1
7/1/10              1                                       1
8/1/10              1                                       1
9/1/10              1                                       1
10/1/10             1                                       1
11/1/10             1                                       1
12/1/10             1                                       1
1/1/11              2                                       1    
  

Запрос выполнен:

 SELECT 
    sub.last_day,
    sum(sub.distinct_count_of_member_with_gene_a_measurement) as distinct_count_of_member_with_gene_a_measurement,
    sum(sub.distinct_count_of_member_with_gene_b_measurement) as distinct_count_of_member_with_gene_b_measurement,
FROM 
    (SELECT last_day(date),
            COUNT(DISTINCT member_id) as distinct_count_of_member_with_gene_a_measurement,
            null as distinct_count_of_member_with_gene_b_measurement,
    FROM  measurement
    WHERE gene_a_measurement_done is not null
    GROUP BY    last_day(date)
    UNION ALL
    SELECT  last_day(date),
            null as distinct_count_of_member_with_gene_a_measurement,
            COUNT(DISTINCT member_id) as distinct_count_of_member_with_gene_b_measurement,
    FROM  measurement
    WHERE gene_b_measurement_done is not null
    GROUP BY    last_day(date)) as sub
GROUP BY    sub.last_day(date) 
  

Приведенный выше запрос дает только отдельное количество участников за месяц, за который было выполнено измерение, и я не уверен, как лучше всего учитывать дату отмены? (внутреннее соединение с таблицей member_status в member_id и есть условие для фильтрации отмененного участника?)