Поиск счетчика для периода в sql

Ответ 1

Вы можете использовать переменную для вычисления общего количества активных пользователей:

SELECT Period,
       @total:[email protected]+cnt AS `Count of Active Users`
FROM (       
SELECT CONCAT(MONTHNAME(order_date), '-', YEAR(order_date)) AS Period,
       COUNT(DISTINCT user_id) AS cnt       
FROM mytable 
GROUP BY Period
ORDER BY YEAR(order_date), MONTH(order_date) ) t,
(SELECT @total:=0) AS var

Подзапрос возвращает количество отдельных активных пользователей за месяц/год. Внешний запрос использует переменную @total, чтобы рассчитать общее количество активных пользователей.

Скрипт Демо здесь

Ответ 2

У меня есть два вопроса, которые делают это. Я не уверен, какой из них самый быстрый. Проверьте их в своей базе данных:

SQL Fiddle

Запрос 1:

select per.yyyymm,
(select count(DISTINCT o.user_id) from orders o where o.order_date >= 
(per.yyyymm - INTERVAL 1 YEAR) and o.order_date < per.yyyymm + INTERVAL 1 MONTH) as `count`
from
(select DISTINCT LAST_DAY(order_date) + INTERVAL 1 DAY - INTERVAL 1 MONTH as yyyymm
from orders) per
order by per.yyyymm

Результаты:

|                    yyyymm | count |
|---------------------------|-------|
| October, 01 2013 00:00:00 |     1 |
| January, 01 2014 00:00:00 |     5 |
|   March, 01 2014 00:00:00 |     6 |

Запрос 2:

select DATE_FORMAT(order_date, '%Y-%m'),
(select count(DISTINCT o.user_id) from orders o where o.order_date >= 
 (LAST_DAY(o1.order_date) + INTERVAL 1 DAY - INTERVAL 13 MONTH) and 
 o.order_date <= LAST_DAY(o1.order_date)) as `count`
from orders o1
group by DATE_FORMAT(order_date, '%Y-%m')

Результаты:

| DATE_FORMAT(order_date, '%Y-%m') | count |
|----------------------------------|-------|
|                          2013-10 |     1 |
|                          2014-01 |     5 |
|                          2014-03 |     6 |

Ответ 3

Лучшее, что я мог сделать, это следующее:

SELECT Date, COUNT(*) as ActiveUsers
FROM 
(
    SELECT DISTINCT userId, CONCAT(YEAR(order_date), "-", MONTH(order_date)) as Date
    FROM `a` 
    ORDER BY Date
)
AS `b`
GROUP BY Date

Вывод следующий:

|    Date | ActiveUsers |
|---------|-------------|
| 2013-10 |           1 |
|  2014-1 |           4 |
|  2014-3 |           4 |

Теперь для каждой строки вам нужно суммировать количество активных пользователей в предыдущих строках. Например, вот код в С#.

int total = 0;
while (reader.Read())
{ 
    total += (int)reader['ActiveUsers'];
    Console.WriteLine("{0} - {1} active users", reader['Date'].ToString(), reader['ActiveUsers'].ToString());
}

Кстати, в марте 2014 года ответ 9, потому что одна строка дублируется.

Ответ 4

Попробуйте это, но это не обрабатывает последнюю часть: значение Jan 2014 - включает Oct -2013

select TO_CHAR(order_dt,'MON-YYYY'), count(distinct User_ID ) cnt from [orders] 
where User_ID  in 
(select User_ID from
 (select a.User_ID from  [orders] a,
(select a.User_ID,count (a.order_dt) from [orders] a 
where a.order_dt > (select max(b.order_dt)-365 from [orders] b where a.User_ID=b.User_ID)
group by a.User_ID
having count(order_dt)>1) b
where a.User_ID=b.User_ID) a
)
group by TO_CHAR(order_dt,'MON-YYYY');

Ответ 5

Вот что я думаю, что вы ищете

SET @cnt = 0;
SELECT Period, @cnt := @cnt + total_active_users AS total_active_users
FROM (
  SELECT DATE_FORMAT(order_date, '%b-%Y') AS Period , COUNT( id) AS total_active_users
  FROM t
  GROUP BY DATE_FORMAT(order_date, '%b-%Y')
  ORDER BY order_date
) AS t

Это результат, который я получаю

Period      total_active_users
Oct-2013    1
Jan-2014    6
Mar-2014    10

Вы также можете сделать COUNT (DISTINCT id), чтобы получить только уникальные идентификаторы

Вот SQL Fiddle