Выберите только последнее значение, используя команду by mysql
У меня есть одна таблица с данными о посещаемости некоторых событий. Я имею в таблице данные о посещаемости каждый раз, когда пользователь отправляет новую посещаемость, информация такова:
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 459 | 1363208604 | 131327 |
| 1326 | 131327 | 123 | 1363208504 | 131327 |
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 99200 | 0 | NULL | 99200 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 93085 | 0 | NULL | 93085 |
+-------------------+-----------+------------+------------+-----------+
14 rows in set (0.00 sec)
(На самом деле это представление, по этой причине некоторые из полей являются нулевыми).
Я могу groupby id_member, поэтому я получаю только одну строку для каждого члена (то есть только последнюю посещаемость, которую отправил пользователь). Однако, когда я это делаю, я получил первое посещение пользователя, а не последнее.
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782 GROUP BY id_event,id_member;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 93085 | 0 | NULL | 93085 |
| 1326 | 99200 | 0 | NULL | 99200 |
+-------------------+-----------+------------+------------+-----------+
12 rows in set (0.00 sec)
Я уже пытался добавить предложения ORDER BY, но они вообще не работают... любые идеи?
Спасибо заранее!
Изменить: это script, который создает таблицу
CREATE OR REPLACE VIEW view_event_attendance
AS
SELECT
tbl_event.id_event,
tbl_member_event.id_member,
tbl_event.id_branch_channel,
tbl_member_event_attendance.id_member_event_attendance,
IF(ISNULL(tbl_member_event_attendance.attendance), 0, tbl_member_event_attendance.attendance) AS attendance,
tbl_member_event_attendance.timestamp
FROM
tbl_event
INNER JOIN
tbl_member_event ON tbl_member_event.id_event = tbl_event.id_event
LEFT OUTER JOIN
tbl_member_event_attendance ON tbl_member_event_attendance.id_member_event = tbl_member_event.id_member_event
ORDER BY
tbl_member_event_attendance.timestamp DESC;
ИЗМЕНИТЬ 2:
Большое спасибо MichaelBenjamin, но проблема при использовании подзапросов - это размер представления:
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16755 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | index | id_event | id_event | 8 | NULL | 16346 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | video_staging.tbl_member_event.id_event | 1 | |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
4 rows in set (0.08 sec)
Как вы видите, в моей таблице много строк, поэтому по этой причине я не хочу использовать подзапросы...
ИЗМЕНИТЬ 3:
Но добавление WHERE в подзапрос выглядит лучше...
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance where id_event = 782 order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.01 sec)
Если я не могу найти что-то еще, не используя подзапросы, я думаю, что я выберу это как ответ...
Изменить 4
После просмотра комментариев в ответе я решил выбрать другой как ответ. Вот DESCRIBE для обоих запросов, и я думаю, что самое лучшее решение:
mysql> DESCRIBE SELECT
-> id_branch_channel,
-> id_member,
-> attendance,
-> timestamp,
-> id_member
-> FROM view_event_attendance AS t1
-> WHERE id_event = 782
-> AND timestamp = (SELECT MAX(timestamp)
-> FROM view_event_attendance AS t2
-> WHERE t1.id_member = t2.id_member
-> AND t1.id_event = t2.id_event
-> GROUP BY id_event, id_member)
-> OR timestamp IS NULL
-> GROUP BY id_event, id_member;
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| 1 | PRIMARY | tbl_event | index | PRIMARY | id_member_branch_channel | 4 | NULL | 208 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | tbl_member_event | ref | id_event | id_event | 4 | video_staging.tbl_event.id_event | 64 | Using index |
| 1 | PRIMARY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | tbl_member_event | eq_ref | id_event,id_member | id_event | 8 | video_staging.tbl_event.id_event,func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> DESCRIBE SELECT *
-> FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_event
-> FROM view_event_attendance
-> WHERE id_event = 782
-> ORDER BY timestamp desc
-> ) as whatever
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)
Ответы
Ответ 1
Используйте простую группу по id_member, но выберите:
substring(max(concat(from_unixtime(timestamp),attendance)) from 20) as attendance
Это означает, что для каждой строки в группе используется метка времени, чтобы можно было выбрать нужную временную метку/посещаемость с помощью max(), а затем извлечь только посещаемость.
То, что возвращает concat(), - 19 символов отформатированной метки времени (YYYY-mm-dd HH: MM: SS) с приложением, начинающимся с символа 20; substring(... from 20) получает только участие от (строгого) максимального значения для группы. Вы можете удалить группу и просто
select concat(from_unixtime(timestamp),attendance), timestamp, attendance
чтобы лучше понять, как он использует max, чтобы получить нужную посещаемость.
Ответ 2
SELECT id_branch_channel, id_member, attendance, timestamp, id_member
FROM (select * from view_event_attendance order by timestamp desc) as whatever
WHERE id_event = 782
GROUP BY id_event,id_member;
EDIT: Это может привести к повышению производительности:
SELECT *
FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_member
FROM view_event_attendance
WHERE id_event = 782
ORDER BY timestamp desc
) as whatever
GROUP BY id_event,id_member;
Пока набор результатов может вписаться в Innodb_buffer_pool, вы не увидите значительного снижения производительности.
Ответ 3
Я вижу ответы с JOINS и Subquerys, но я считаю, что простое предложение HAVING должно выполнить трюк:
SELECT
id_branch_channel,
id_member,
attendance,
timestamp,
id_member
FROM view_event_attendance
WHERE id_event = 782
GROUP BY id_event, id_member
HAVING MAX(timestamp) OR timestamp IS NULL;
EDIT: Добавлена проверка для IS NULL, если вы также хотите включить эти строки.
РЕДАКТИРОВАТЬ 2: Нужно ли даже группироваться по id_event, когда вы уже фильтруете его на 1 событие?
РЕДАКТИРОВАТЬ 3: Не знаю, почему downvote, эта sql скрипта показывает, что она работает.
EDIT 4: Я должен извиниться, @ysth верен, SQL Fiddle работает неправильно. Я заслужил -1, но когда вы голосуете, по крайней мере, объясните, почему я тоже могу чему-то научиться.
Следующие работы, но, к сожалению, снова имеют подзапрос и не будут работать намного лучше, чем другие решения, размещенные здесь.
SELECT
id_branch_channel,
id_member,
attendance,
timestamp,
id_member
FROM view_event_attendance AS t1
WHERE id_event = 782
AND timestamp = (SELECT MAX(timestamp)
FROM view_event_attendance AS t2
WHERE t1.id_member = t2.id_member
AND t1.id_event = t2.id_event
GROUP BY id_event, id_member)
OR timestamp IS NULL
GROUP BY id_event, id_member;
Ответ 4
SUBSTRING_INDEX (SUBSTRING_INDEX (group_concat (% requiredfield%), ',', count (*)), ',', - 1)
Это получит последнее значение "обязательного поля" из любого group_concat, если оно unsorted, это будет последнее значение в таблице по умолчанию.
Можно использовать group_concat_ws для учета возможных нулевых полей.
Ответ 5
Вот один из вариантов (непроверенный):
SELECT v.id_branch_channel, v.id_member, v.attendance, v.timestamp, v.id_member
FROM view_event_attendance v
JOIN (
SELECT id_event, id_member, MAX(attendance) maxattendance
FROM view_event_attendance
GROUP BY id_event, id_member ) m ON
v.id_event = m.id_event AND
v.id_member = m.id_member AND
v.attendance = m.maxattendance
WHERE v.id_event = 782
GROUP BY v.id_member;
Концепция состоит в том, чтобы получить MAX() метки времени и использовать это поле для JOIN на вашем представлении. Возможно, вам не нужны все поля - действительно зависит от вашей структуры таблицы. Но это должно заставить вас двигаться в правильном направлении.
Ответ 6
Один из способов сделать это - использовать функцию окна и подзапрос, если вы добавите запись в список выбора как row_number() over (partition by id_member order by timestamp desc), это будет разрешено для числа, упорядочивающего строки по метке времени (с 1 старейшей) сгруппированной в каждой группе id_member (запустите его, если это не имеет смысла, будет ясно). Затем вы можете выбрать из этого как подзапрос, где дополнительный столбец = 1, который будет выбирать только строки с самой высокой меткой времени в каждой группе.
