Подтвердить что ты не робот

Выберите только последнее значение, используя команду 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)
4b9b3361

ОТВЕТЫ

Ответ 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, который будет выбирать только строки с самой высокой меткой времени в каждой группе.