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

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

Модераторы: /dev/random, Модераторы разделов

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

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

Сообщение nerve »

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

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

# 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
Модератор
Сообщения: 20752
Статус: nulla salus bello
ОС: Debian GNU/Linux

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

Сообщение Bizdelnick »

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

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

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

Сообщение nerve »

в таблице записаны события: 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
Модератор
Сообщения: 4823
Статус: фанат консоли (=
ОС: GNU/Debian, RHEL

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

Сообщение SLEDopit »

Если я правильно понимаю (нужно оставить по первой записи на каждую дату), то вот.
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
Сообщения: 280
ОС: OpenBSD

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

Сообщение nerve »

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

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

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
Сообщения: 280
ОС: OpenBSD

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

Сообщение nerve »

с помощью такого запроса мы получаем 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 | +------+---------------------+

Спасибо сказали:
Ответить