Я пытаюсь увидеть, как совокупное количество подписчиков менялось с течением времени в зависимости от уникальных адресов электронной почты и даты их создания. Ниже приведен пример таблицы, с которой я работаю.
Я пытаюсь превратить это в таблицу ниже. Электронный адрес [email protected] создавался дважды, и я хочу один раз его пересчитать. Я не могу понять, как создать столбец Различное количество запусков.
Спасибо за помощь.
Обычно я делал это с помощью row_number():
select date, count(*),
sum(count(*)) over (order by date),
sum(sum(case when seqnum = 1 then 1 else 0 end)) over (order by date)
from (select t.*,
row_number() over (partition by email order by date) as seqnum
from t
) t
group by date
order by date;
Это похоже на версию, использующую lag(). Однако я нервничаю из-за задержки, если одно и то же электронное письмо появляется несколько раз в один и тот же день.
Получение общего и совокупного подсчета несложно. Чтобы получить кумулятивное количество уникальных записей, используйте lag, чтобы проверить, есть ли в электронном письме строка с предыдущей датой, и установите флаг в 0, чтобы она игнорировалась во время подсчета суммы.
select distinct dt
,count(*) over(partition by dt) as day_total
,count(*) over(order by dt) as cumsum
,sum(flag) over(order by dt) as cumdist
from (select t.*
,case when lag(dt) over(partition by email order by dt) is not null then 0 else 1 end as flag
from tbl t
) t
Вот решение, которое не использует ни sum over, ни _2 _... И дает правильные результаты.
Следовательно, может показаться, что его проще читать и поддерживать.
select
t1.date_created,
(select count(*) from my_table where date_created = t1.date_created) emails_created,
(select count(*) from my_table where date_created <= t1.date_created) cumulative_sum,
(select count( distinct email) from my_table where date_created <= t1.date_created) running_count_distinct
from
(select distinct date_created from my_table) t1
order by 1
