Страницы

Поиск по вопросам

четверг, 20 июня 2019 г.

SQL Выборка по уникальному полю и группировка

Подскажите по выборке повторяющихся значений. Есть таблица
( 1, 'user1', 'Новое сообщение', '2014-02-15' ), ( 2, 'user2', 'Новое сообщение', '2015-05-07' ), ( 3, 'user3', 'Новое сообщение', '2015-09-12' ), ( 4, 'user2', 'Регистрация', '2016-05-16' ), ( 5, 'user1', 'Новое сообщение', '2016-05-16' ), ( 6, 'user4', 'Создание темы', '2016-05-12' )
Как написать запрос, что бы получить сколько юзеров в каждом году написали сообщения впервые,т.е. должно на выходе быть
2014 1 2015 2 2016 0
Если написать данный код
SELECT Extract(YEAR from Date) As Year, Count(DISTINCT user) AS Count FROM table WHERE Type LIKE 'Новое%' GROUP BY Extract(YEAR from Date)
то на выходе будет
Year Count 2014 1 2015 2 2016 1
И я, собственно, понимаю почему так происходит. Как решить данную задачу ?


Ответ

Например так:
Сначала отобрать минимальный номер года, в котором у пользователя появилось новое сообщение, а потом из всего этого сделать группировку с суммированием по годам:
select Year, count(user) as Count from ( select min(extract(YEAR from Date)) as Year, user as User from table where Type like 'Новое%' group by user ) as FirstMessageYears group by Year
Уникальность обеспечивается тем, что мы можем получить только одну минимальную дату для каждого пользователя, так что distinct не понадобится.
Для дополнения строками с годами, по которым есть сообщения, но не первые, можно воспользоваться тем, что count(null) возвращает 0:
Внутренний запрос усложнился тем, что теперь он возвращает года, для которых нет пользователя с первым сообщением, со значение null в колонке пользователя.
select AllYears.Year, count(FirstMessageYears.User) as Count from ( select min(extract(YEAR from Date)) as Year, user as User from table where Type like 'Новое%' group by user ) as FirstMessageYears right join ( select distinct extract(YEAR from Date) as Year from table ) as AllYears on FirstMessageYears.Year = AllYears.Year group by AllYears.Year

Давайте попробуем разобраться. Начинаем "выполнять" SQL запрос "изнутри", т.е. с тех частей, что находятся на максимальном уровне вложенности. В нашем случае, вложенным является сложный подзапрос:
( select min(extract(YEAR from Date)) as Year, user as User from table where Type like 'Новое%' group by user ) as FirstMessageYears right join ( select distinct extract(YEAR from Date) as Year from table ) as AllYears on FirstMessageYears.Year = AllYears.Year

Чтобы понять, что этот подзапрос возвращает, давайте разбирать его на запчасти:
( select min(extract(YEAR from Date)) as Year, user as User from table where Type like 'Новое%' group by user ) as FirstMessageYears
right join
( select distinct extract(YEAR from Date) as Year from table ) as AllYears

Вот, уже немножно легче. Имеем два запроса, каждый возвращает таблицу, потом эти таблицы объединяются посредством операции с непонятным названием right join
Сначала разберёмся, что возвращают каждая из частей перед объединением.
Первый результат подзапроса с красноречивым названием FirstMessageYears будет содержать две колонки: пользователь + дата первого сообщения.
Второй результат подзапроса с названием AllYears будет содержать одну колонку, в которой будут перечислены все года, встречающиеся в исходной таблице table
Что будет, если применить к этим двум наборам операцию right join? Получится таблица, состоящая из двух столбцов, номер года и пользователь. Логически, этот набор заполняется в 2 этапа:
Сначала, в результат объединения попадают те строчки из набора AllYears, для которых нет соответствия в наборе FirstMessageYears. Поле пользователь для этих строчек остаётся null
Потом, в результат объединения попадают те строчки из набора FirstMessageYears, для которых есть соответствие в наборе AllYears. Поле пользователь для этих строчек берётся из набора FirstMessageYears

Остаётся провести по этому результирующему колдовству группировку:
select AllYears.Year, count(FirstMessageYears.User) as Count from [результат выполнения подзапроса] group by Year
а мы помним, что колонка FirstMessageYears.User оказалась со значеним null в тех строчках, которые соответствуют годам, в которые не было первого сообщения от кого-либо из пользователей. Count(null) возвращает 0. Ву-а-ля.

Комментариев нет:

Отправить комментарий