Как сравнить текущее значение COUNT(*) с предыдущим?

Question

[vadimstroganov]

Здравсвуйте!

К примеру имеется таблица:

+----+----------+----------------------------+
| id | nickname | created_at                 |
+----+----------+----------------------------+
| 1  | user_1   | 2009-07-30 21:51:51.829000 |
+----+----------+----------------------------+
| 2  | user_2   | 2009-08-27 21:52:21.016000 |
+----+----------+----------------------------+
| 3  | user_3   | 2009-09-30 21:52:00.417000 |
+----+----------+----------------------------+
| 4  | user_4   | 2009-08-30 21:51:56.381000 |
+----+----------+----------------------------+
| 5  | user_5   | 2009-09-27 21:52:13.261000 |
+----+----------+----------------------------+
| 6  | user_6   | 2009-09-30 21:52:06.509000 |
+----+----------+----------------------------+

Я хочу узнать в %, с каким соотношением в месяц увеличивалось количество регистраций по отношению к предыдущему месяцу.

Я сгруппировал кол-во регистраций помесячно:

SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*)
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
ORDER BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at);

Результат:

+----+------+-------+-------+
| id | year | month | count |
+----+------+-------+-------+
| 1  | 2009 | 7     | 1     |
+----+------+-------+-------+
| 2  | 2009 | 8     | 2     |
+----+------+-------+-------+
| 3  | 2009 | 9     | 3     |
+----+------+-------+-------+

Не могу понять как считать отношение текущего COUNT к предыдущему (за прошлый месяц)

Должно получиться что-то вроде такого:

+----+------+-------+---------+
| id | year | month | dynamic |
+----+------+-------+---------+
| 1  | 2009 | 7     | 0%      |
+----+------+-------+---------+
| 2  | 2009 | 8     | 100%    |
+----+------+-------+---------+
| 3  | 2009 | 9     | 50%     |
+----+------+-------+---------+

Буду благодарен за любые подсказки

Answer 1

[nozzy]

SELECT
t1.YEAR,
t1.MONTH,
((t1.REGS - t2.REGS) / t1.REGS) * 100 as DYNAMIC
FROM
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t1
LEFT JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t2 ON t2.YEAR = t1.YEAR
	AND t2.MOUNT = (t1.MOUNT) + 1 

ORDER BY t1.YEAR,t1.MONTH;

Answer 2

[Александр]

В oracle вашу задачу можно решить при помощи оператора model:

-- данные для выборки
with data as (
  select 1 as id, 2009 as year, 3 as month, 50  as count from dual union all
  select 2 as id, 2009 as year, 4 as month, 100 as count from dual union all
  select 3 as id, 2009 as year, 5 as month, 133 as count from dual union all
  select 4 as id, 2009 as year, 7 as month, 2   as count from dual union all
  select 5 as id, 2010 as year, 2 as month, 98  as count from dual union all
  select 6 as id, 2010 as year, 4 as month, 600 as count from dual
)

-- сам запрос
select year, month, count, round(result, 2) as increment_rate
from data
model
  -- генерируем порядковый номер для строк, 
  -- по которому будем осуществлять доступ к данным
  dimension by (row_number() over (order by year, month) rn)
  -- указываем столбцы, которые будут отображаться/участвовать в расчетах
  -- также добавляем несуществующий столбец result, 
   -- что бы в дальнейшем помещать в него результат вычислений
  measures (count, year, month, 0 result)
  rules (
    -- делаем соответствующие вычисления
    result[any] = greatest(decode(nvl(count[cv(rn)-1],0), 0, 0, count[cv(rn)] / count[cv(rn)-1])*100 - 100, 0)
  )

Результат:

+------+-------+-------+---------------+
| YEAR | MONTH | COUNT | INCREMENT_RATE|
+------+-------+-------+---------------+
| 2009 | 3     | 50    |  0            |
+------+-------+-------+---------------+
| 2009 | 4     | 100   | 100           |
+------+-------+-------+---------------+
| 2009 | 5     | 133   | 33            |
+------+-------+-------+---------------+
| 2009 | 7     | 2     | 0             |
+------+-------+-------+---------------+
| 2010 | 2     | 98    |  4800         |
+------+-------+-------+---------------+
| 2010 | 4     | 600   | 512,24        |
+------+-------+-------+---------------+

Пояснения к формуле:
result[any] = greatest(decode(nvl(count[cv(rn)-1],0), 0, 0, count[cv(rn)] / count[cv(rn)-1])*100 - 100, 0)

Слева от знака равно мы указываем, что для каждой ячейки (ключевое слово any ) в столбце result в качестве значения мы рассчитываем выражение справа от знака равно.
Для того, что бы рассчитать величину прироста, необходимо взять значение за текущий временной период, разделить его на значение за предыдущий временной период, затем умножить на 100% и вычесть 100%.
Но так как у нас для первого периода в таблице (для первой строки), нет предыдущего периода, а также значением в предыдущем периоде может быть 0, то возникает ошибка деления на 0. Поэтому нужно эту ситуацию обработать, для чего в формуле используется decode, который проверяет, если значение предыдущего периода не существует или равно 0, тогда необходимо возвратить 0, если же значение предыдущего периода не равно 0, то рассчитываем отношение. Также используется функция greatest, для того, чтобы вместо отрицательных значений отображать 0 (когда результат предыдущего периода больше текущего, соответственно прироста нет).
Если вместо прироста необходимо рассчитать процент роста, то вычитание 100% нужно убрать.

Answer 3

[Rsa97]

Например с помощью переменной:

SET @prev = 0;
SELECT `year`, `month`, IF(0 = @prev, 0, `count`/@prev-1) AS `percent`, 
        @prev := `count` AS `absolute`
    FROM (
        здесь ваша выборка
    )
    ORDER BY `year`, `month`;