Как представить количество групп в разделе с помощью MySQL
Я уже некоторое время почесываю голову над этим, поэтому у меня есть данные, которые выглядят так:
USER GROUP DATE
001 AA 02-20-21
007 AA 02-20-21
002 DD 02-20-21
003 DD 02-21-21
004 BB 02-21-21
018 BB 02-22-21
005 AA 02-22-21
006 EE 02-22-21
022 AA 02-22-21
И в настоящее время я собираю эти данные следующим образом:
DATE GROUP USER_COUNT NEW_USER_COUNT
02-20-21 AA 2 2
02-20-21 DD 1 1
02-21-21 DD 2 1
02-21-21 BB 1 1
02-22-21 BB 2 1
02-22-21 AA 4 2
02-22-21 EE 1 1
Это дает мне счетчик пользователей для каждой из отдельных групп, а также новый счетчик пользователей от последней итерации группы до следующей.
Теперь это сработало, но возникла критическая проблема. Для наглядности мне нужно, чтобы каждая группа была представлена на каждой дате. В нынешнем виде, если группа не появляется на дате, она не отображается; Мне нужно, чтобы каждая группа появлялась в каждую дату, поэтому, если у нас нет пользователей в заданную дату, мы прибегаем к предыдущему user_count без новых пользователей.
Вот как я бы хотел, чтобы вышеприведенные данные выглядели:
DATE GROUP USER_COUNT NEW_USER_COUNT
02-20-21 AA 2 2
02-20-21 DD 1 1
02-21-21 AA 2 0
02-21-21 DD 2 1
02-21-21 BB 1 1
02-22-21 AA 4 2
02-22-21 DD 2 0
02-22-21 BB 2 1
02-22-21 EE 1 1
Обратите внимание, что после появления AA и DD они продолжают появляться на каждом DATE, даже если они не выросли, они используют свой последний номер.
Итак, в основном, после первого появления новой группы, она появляется каждый день в будущем. Новые группы не фиксируются, новые могут появиться в любой момент.
Вот мой существующий запрос:
WITH NEW_USER AS (
SELECT USER,
DATE,
GROUP,
ROW_NUMBER() OVER (
PARTITION BY USER
ORDER BY DATE
) AS row_n
FROM dt
),
/*Increment count of unique users based on GROUP. */
CUMULATIVE_USER_COUNT AS (
SELECT DATE,
GROUP,
SUM(COUNT(*)) OVER (
PARTITION BY GROUP
ORDER BY DATE
) AS USER_COUNT,
COUNT(DISTINCT USER) AS NEW_USER_COUNT
FROM NEW_USER
WHERE row_n = 1
GROUP BY DATE,
GROUP
ORDER BY DATE,
GROUP
)
SELECT *
FROM CUMULATIVE_USER_COUNT
Также, к вашему сведению, ИД ПОЛЬЗОВАТЕЛЯ не уникален для каждой строки, необходим DISTINCT USER.
@nbk NEW_USER_COUNT - это в основном разница группы USER_COUNT изо дня в день. Итак, когда DD впервые появляется 20.02.21, это единица, потому что 1 + 0 = 1 (USER COUNT). Затем, когда 21.02.21 снова появится DD, мы получим 2 для USER_COUNT и 1 для NEW_USER_COUNT. (1 из USER_COUNT 20.02.21 + 1 = 2 USER_COUNT 21.02.21. Имеет смысл?
Пожалуйста, поясните данные вашего образца - 02-20-21 не является допустимым значением DATE в MySQL.
еще одна сложность заключается в том, что необходимо добавить недостающие даты, что делает запрос еще более сложным, кроме того, что я должен использовать ST_TO_DATE
Ответы 2
Вы можете генерировать строки с помощью cross join - с поворотом для обработки дат. Затем внесите существующие данные:
select d.date, g.group,
count(dt.date) as num_on_day,
sum(count(dt.date)) over (partition by g.group order by d.date) as running_num
from (select distinct date
from dt
) d join
(select group, min(date) as min_date
from dt
group by group
) g
on d.date >= g.min_date left join
(select dt.*,
row_number() over (partition by group, user_id order by date) as seqnum
from dt
) dt
on dt.date = d.date and dt.group = g.group and dt.seqnum = 1
group by d.date, g.group;
WITH
cte1 AS ( SELECT DISTINCT `DATE`
FROM test ),
cte2 AS ( SELECT DISTINCT `GROUP`
FROM test ),
cte3 AS ( SELECT `DATE`,
`GROUP`,
SUM(COUNT(test.USER)) OVER (PARTITION BY `GROUP` ORDER BY `DATE`) USER_COUNT,
COUNT(test.USER) NEW_USER_COUNT
FROM cte1
CROSS JOIN cte2
LEFT JOIN test USING (`DATE`, `GROUP`)
GROUP BY `DATE`, `GROUP` )
SELECT *
FROM cte3
WHERE USER_COUNT
ORDER BY `DATE`, `GROUP`
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bb4c55ac8897c5f5dcc84c1267f080c0
я до сих пор не понимаю, как вы строите NEW_USER_COUNT