Подскажите по выборке повторяющихся значений.
Есть таблица
( 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. Ву-а-ля.
Комментариев нет:
Отправить комментарий