Решено с window function. Как сделать сложный запрос для MySql?

Question

[Игорь Фамилия]

Есть таблица:

RecordID Date            Time   EventID UserID ClientID
1             |2019-02-01 |15:00  |2           |100      |5
2             |2019-02-01 |17:00  |2           |150      |5
3             |2019-02-05 |12:00  |2           |200      |5
4             |2019-02-05 |15:00  |3           |100      |5
5             |2019-02-06 |15:00  |2           |200      |5

Нужно написать запрос к базе данных MySQL, формирующий последовательные интервалы работы разных пользователей с указанным клиентом и кол-во дней работы.
Должно выйти что то вроде:

UserID DateStart      DateEnd         Days
100     |2019-02-01  |2019-02-01  |1
150     |2019-02-01  |2019-02-05  |5
200     |2019-02-05  |2019-02-06  |2

Я обновился до MySql 8 и использовал window function, вот что вышло:

SELECT 
    Date, Time, EventId, UserId, 
    IF (LEAD(Date) OVER W, LEAD(Date) OVER W - Date + 1, NOW() - Date) AS 'diff' 
FROM first 
WINDOW W AS (ORDER BY Date);

Но эта штука еще не учитывает что EventId бывает разный.

Можна сделать так :

SET sql_mode = ''; SELECT
a.UserId, MIN(a.`Date`) AS DateStart, IFNULL(b.`Date`, CURDATE()) AS DateEnd,
DATEDIFF(b.`Date`, a.`Date`) + 1 AS Days, a.EventId, a.ClientId
FROM first a LEFT JOIN first b
ON b.RecordId = ( SELECT RecordId FROM first aa WHERE aa.EventId = a.EventId AND aa.`Date` >= a.`Date` AND aa.RecordId > a.RecordId ORDER BY aa.`Date` ASC LIMIT 1 )
GROUP BY a.UserId, a.EventId, a.ClientId ORDER BY a.`RecordId` ASC;

Конечный вариан для MySql 8:

SELECT      
  RecordId AS RecId, Date, Time, EventId AS EvID, UserId,        
  CASE          
    WHEN (LEAD(EventId) OVER W = EventId)            
    THEN (LEAD(Date) OVER W - Date + 1)          
    WHEN (LEAD(EventId) OVER W != EventId)            
    THEN IF ((SELECT Date FROM first WHERE (RecordId > RecId AND EvID = EventId) LIMIT 1),                   DATEDIFF( (SELECT Date FROM first WHERE (RecordId > RecId AND EvID = EventId) LIMIT 1), NOW()), NOW() - Date)           ELSE (NOW() - DATE)             
    END         
    AS 'diff'  
    FROM first WINDOW W AS (ORDER BY Date);

Answer 1

[Vitsliputsli]

Примерно так:

SELECT 
    t1.UserID,
    t1.Date DateStart,
    min(CASE 
        WHEN t2.Event='end' AND t2.Date>t1.Date THEN t2.Date 
        ELSE null END) DateEnd
FROM table t1
CROSS JOIN table t2 
WHERE t1.Event='start' 
GROUP BY UserID

хотя, возможно лучше это сделать аналитикой.
Дальше сами. С EventID у вас чтото напутано, поэтому я взял для примера простой понятный вариант. Date переделайте в timestamp: либо в запросе, либо лучше это сделать в БД, чтобы исключить подобные проблемы в будущем.

Если EventId идентификтор одного интервала времени, т.е. в таблице хранится до 2 EventId (начало и конец, или только начало, или ничего), то:

SELECT 
    t1.UserID,
    t1.Date DateStart,
    CASE 
        WHEN t2.Date is null THEN t1.Date 
        ELSE t2.Date END DateEnd
FROM table t1
LEFT JOIN table t2 ON t1.EventID=t2.EventID AND t1.Date<t2.Date

как-то так. С case выглядит не очень, но так лучше чем cross join с distinct. Ну и вместо date берите timestamp.

Comments

[Игорь Фамилия]

Не совсем так.
Берем первую запись RecordId 1->смотрим EventId(2)->смотрим Date->
используем DATEDIFF() для Date из текущего ряда и Date следуйщий EventId = 2, если больше EventId 2 нет то до текущей даты.
Я только не могу понять как построить такой длинный запрос

[Vitsliputsli]

обновил ответ, по уточненным условиям, если, конечно, я тебя правильно понял.

[Игорь Фамилия]

Vitsliputsli Я добавил конечный результат, если вам интересно.

[Vitsliputsli]

Игорь Фамилия, посмотрел, ничего не понял. По производительности он очень тяжелый, но это ладно. Я абсолютно не понимаю, что он считает, это никак не вяжется с условием. В вашем результате, разница высчитывается относительно текущего времени, что никак несоответствует условию, причем это только если EventID идут не друг за другом. А если друг за другом, то разница высчитывается вроде как в соответствии с условием (если не считать добавление единицы).