Solved. Mysql: проходная система, определить во сколько человек пришел на работу

На самом деле это единственный раздел про unix на этом форуме

Модератор: /dev/random

Аватара пользователя
nerve
Сообщения: 267
ОС: OpenBSD

Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение nerve » 04.07.2014 12:16

Всем привет, есть такой запрос:

Код: Выделить всё

# mysql -ppass -e  "use db; \
> SELECT * FROM table \
> WHERE user_id = '2' AND direction = 'in' \
> AND TIME(date) BETWEEN '08:30' AND '09:45' LIMIT 0, 10;"
+------+---------------------+-----------+---------+
| id   | date                | direction | user_id |
+------+---------------------+-----------+---------+
|   65 | 2014-04-02 09:18:01 | In        | 2       |
|   70 | 2014-04-02 09:44:02 | In        | 2       |
| 1109 | 2014-04-14 09:27:10 | In        | 2       |
| 1355 | 2014-04-16 09:29:27 | In        | 2       |
| 2648 | 2014-05-06 09:19:37 | In        | 2       |
| 2770 | 2014-05-07 09:11:38 | In        | 2       |
| 2864 | 2014-05-08 08:47:54 | In        | 2       |
| 2869 | 2014-05-08 08:59:32 | In        | 2       |
| 2976 | 2014-05-12 09:14:22 | In        | 2       |
| 2985 | 2014-05-12 09:37:27 | In        | 2       |
+------+---------------------+-----------+---------+

Хочется получить только первую строку с датой:

Код: Выделить всё

|   65 | 2014-04-02 09:18:01 | In        | 2       |
| 1109 | 2014-04-14 09:27:10 | In        | 2       |
| 1355 | 2014-04-16 09:29:27 | In        | 2       |
| 2648 | 2014-05-06 09:19:37 | In        | 2       |
| 2770 | 2014-05-07 09:11:38 | In        | 2       |
| 2864 | 2014-05-08 08:47:54 | In        | 2       |
| 2976 | 2014-05-12 09:14:22 | In        | 2       |

может быть можно и в запросе это сделать, но я не нашел.
Спасибо сказали:

Аватара пользователя
Bizdelnick
Модератор
Сообщения: 15818
Статус: grammatikführer
ОС: Debian GNU/Linux

Re: Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение Bizdelnick » 04.07.2014 17:55

nerve писал(а):
04.07.2014 12:16
Хочется получить только первую строку с датой

Не понял, поясните.
Пишите правильно:
в консоли
вкупе (с чем-либо)
в общем
вообще
в течение (часа)
новичок
нюанс
по умолчанию
приемлемо
проблема
пробовать
трафик
Спасибо сказали:

Аватара пользователя
nerve
Сообщения: 267
ОС: OpenBSD

Re: Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение nerve » 07.07.2014 10:37

в таблице записаны события: ID - дата/время - Направление - Юзер.
мне надо получить список: дата/время - Направление - Юзер, при этом основное условие выглядит так: дата/время события должно быть первым за Этот день для каждого юзера. То есть за период времени, допустим неделя, надо получить список во сколько времени каждый юзер пришел на работу.
таблица выглядит следующим образом:

Код: Выделить всё

mysql> describe checkpoint;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(7)   | NO   | PRI | NULL    | auto_increment |
| date      | datetime | NO   | UNI | NULL    |                |
| direction | text     | NO   |     | NULL    |                |
| user_id   | text     | NO   |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
mysql> describe users;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

вообще я думал, что может в моей предыдущей теме, где я парсю csv и заношу в базу, создать в базе дополнительную колонку и назвать ее FIRST_REG, сделать ее дефолт NULL и при чтении файла как-то находить первое событие для юзера за каждую дату и соответственно помечать такие строки NOT NULL, чтоб потом по этому признаку делать запрос к базе и находить сразу все события для всех юзеров: кто и когда зашел на работу. Но это мне кажется еще сложнее чем сделать правильный запрос к базе.
Спасибо сказали:

Аватара пользователя
SLEDopit
Модератор
Сообщения: 4666
Статус: фанат консоли (=
ОС: GNU/Debian, RHEL

Re: Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение SLEDopit » 07.07.2014 11:22

Если я правильно понимаю (нужно оставить по первой записи на каждую дату), то вот.
UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. © Dennis Ritchie
The more you believe you don't do mistakes, the more bugs are in your code.
Спасибо сказали:

Аватара пользователя
nerve
Сообщения: 267
ОС: OpenBSD

Re: Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение nerve » 07.07.2014 13:26

не помогло:(
вот регистрации юзера за два дня

Код: Выделить всё

mysql> SELECT users.name, checkpoint.date
FROM checkpoint
INNER JOIN users ON users.id = checkpoint.user_id
WHERE direction = 'in'
AND users.name = 'ppo'
AND date BETWEEN '2014-07-02' AND '2014-07-04';
+------+---------------------+
| name | date                |
+------+---------------------+
| ppo  | 2014-07-02 09:33:12 |
| ppo  | 2014-07-02 14:08:56 |
| ppo  | 2014-07-02 15:13:54 |
| ppo  | 2014-07-03 09:29:48 |
| ppo  | 2014-07-03 12:46:53 |
+------+---------------------+

вот вывод юзеров, у которых Первая регистрация (то есть первый вход за текущую дату) попадает в нужный интервал

Код: Выделить всё

mysql> SELECT * FROM
(SELECT users.name, checkpoint.date
FROM checkpoint
INNER JOIN users ON users.id = checkpoint.user_id
WHERE direction = 'in'
AND date BETWEEN '2014-07-02' AND '2014-07-03'
GROUP BY users.name) as n
WHERE TIME(n.date) BETWEEN '09:29:59' AND '09:44:59';
+------+---------------------+
| name | date                |
+------+---------------------+
| alt  | 2014-07-02 09:32:04 |
| ato  | 2014-07-02 09:30:48 |
| epr  | 2014-07-02 09:30:08 |
| ppo  | 2014-07-02 09:33:12 |
+------+---------------------+

проблема которую я не могу решить - это задать диапазон дней более чем один.
если я задам в последнем запросе дату с 2 по 4 число, то из-за группировки выведет все равно первую дату и чтоб получить корректный вывод. надо задать интервал для 3 числа отдельно.

Код: Выделить всё

mysql> SELECT * FROM
(SELECT users.name, checkpoint.date
FROM checkpoint
INNER JOIN users ON users.id = checkpoint.user_id
WHERE direction = 'in'
AND date BETWEEN '2014-07-02' AND '2014-07-04'
GROUP BY users.name) as n
WHERE TIME(n.date) BETWEEN '09:29:59' AND '09:44:59';
+------+---------------------+
| name | date                |
+------+---------------------+
| alt  | 2014-07-02 09:32:04 |
| ato  | 2014-07-02 09:30:48 |
| epr  | 2014-07-02 09:30:08 |
| ppo  | 2014-07-02 09:33:12 |
+------+---------------------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM
(SELECT users.name, checkpoint.date
FROM checkpoint
INNER JOIN users ON users.id = checkpoint.user_id
WHERE direction = 'in'
AND date BETWEEN '2014-07-03' AND '2014-07-04'
 GROUP BY users.name) as n
WHERE TIME(n.date) BETWEEN '09:29:59' AND '09:44:59';
+------+---------------------+
| name | date                |
+------+---------------------+
| mts  | 2014-07-03 09:33:26 |
+------+---------------------+
Спасибо сказали:

Аватара пользователя
nerve
Сообщения: 267
ОС: OpenBSD

Re: Solved. Mysql: проходная система, определить во сколько человек пришел на работу

Сообщение nerve » 15.07.2014 12:08

с помощью такого запроса мы получаем 1) первое событие для всех юзеров за интервал в днях и 2) выбираем из них тех, кто попал в определенный часовой интервал, то есть опоздал.

Shell

SELECT users.name, fe.first_event FROM checkpoint INNER JOIN (SELECT user_id, MIN(date) as first_event FROM checkpoint WHERE direction = 'in' AND date BETWEEN '2014-06-01' AND '2014-06-07' GROUP BY user_id, date(date)) fe ON checkpoint.user_id = fe.user_id AND checkpoint.date = fe.first_event AND TIME(fe.first_event) BETWEEN '09:29:59' AND '09:44:59' INNER JOIN users ON users.id = checkpoint.user_id ORDER BY date, users.name LIMIT 0, 30; +------+---------------------+ | name | first_event | +------+---------------------+ | dza | 2014-06-02 09:30:52 | | msn | 2014-06-02 09:36:58 | | ppo | 2014-06-02 09:38:40 | | ami | 2014-06-03 09:39:26 | | ato | 2014-06-04 09:31:47 | | ppo | 2014-06-05 09:31:18 | | mts | 2014-06-05 09:31:32 | | ami | 2014-06-05 09:31:46 | | mts | 2014-06-06 09:31:31 | +------+---------------------+

Спасибо сказали: