Блог

это моя скрипка https://dbfiddle.uk/?rdbms=mysql_5.7amp;fiddle=1dbde0d6227268a4eee4dee539a5d930

предположим, у меня есть эта таблица

 CREATE TABLE test (
  ID INT,
  user_id INT,
  createdAt DATE,
  status_id INT
);

INSERT INTO test VALUES
  (1, 13, '2020-01-01', 8),
  (2, 13, '2020-01-03', 8),
  (3, 13, '2020-01-06', 8),
  (4, 13, '2020-01-02', 7),
  (5, 13, '2020-01-03', 7),
  (6, 14, '2020-03-03', 8),
  (7, 13, '2020-03-04', 4),
  (8, 15, '2020-04-04', 7),
  (9, 14, '2020-03-02', 6),
  (10, 14, '2020-03-10', 5),
  (11, 13, '2020-04-10', 8);
  

я хочу подсчитывать время по-разному для каждого пользователя в диапазоне даты и времени, который мне нужен для этого запроса

 SELECT t1.user_id, 
       t1.createdAt cretecompare1, 
       t2.createdAt cretecompare2,
       DATEDIFF(t2.createdAt, t1.createdAt) diff
-- table for a transaction
FROM test t1
-- table for prev. transaction
JOIN test t2 ON t1.user_id = t2.user_id 
            AND t1.createdAt < t2.createdAt
            AND 7 NOT IN (t1.status_id, t2.status_id)
JOIN (SELECT t3.user_id
      FROM test t3
      WHERE t3.status_id != 7
      GROUP BY t3.user_id
      HAVING SUM(t3.createdAt < '2020-04-01') > 1
         AND SUM(t3.createdAt BETWEEN '2020-02-01' AND '2020-04-01')) t4 ON t1.user_id = t4.user_id
WHERE NOT EXISTS (SELECT NULL
                   FROM test t5
                   WHERE t1.user_id = t5.user_id
                     AND t5.status_id != 7
                     AND t1.createdAt < t5.createdAt
                     AND t5.createdAt < t2.createdAt) 
HAViNG cretecompare2  BETWEEN '2020-02-01' AND '2020-04-01';
  

после этого результаты были такими

  ---------- --------------- --------------- ------ 
|  user_id | cretecompare1 | cretecompare2 | diff |
 ---------- --------------- --------------- ------ 
|       14 | 2020-03-02    | 2020-03-03    |    1 |
|       13 | 2020-01-06    | 2020-03-04    |   58 |
|       14 | 2020-03-03    | 2020-03-10    |    7 |
 ---------- --------------- --------------- ------ 
  

я хочу посчитать максимальное различие, минимальное различие и среднее значение для diff с помощью этого запроса

 SELECT t1.user_id, 
       t1.createdAt cretecompare1, 
       t2.createdAt cretecompare2,
       DATEDIFF(t2.createdAt, t1.createdAt) diff,
       MIN(DATEDIFF(t2.createdAt, t1.createdAt)) min_diff, 
       MAX(DATEDIFF(t2.createdAt, t1.createdAt)) max_diff, 
       AVG(DATEDIFF(t2.createdAt, t1.createdAt)) avg_diff
-- table for a transaction
FROM test t1
-- table for prev. transaction
JOIN test t2 ON t1.user_id = t2.user_id 
            AND t1.createdAt < t2.createdAt
            AND 7 NOT IN (t1.status_id, t2.status_id)
JOIN (SELECT t3.user_id
      FROM test t3
      WHERE t3.status_id != 7
      GROUP BY t3.user_id
      HAVING SUM(t3.createdAt < '2020-04-01') > 1
         AND SUM(t3.createdAt BETWEEN '2020-02-01' AND '2020-04-01')) t4 ON t1.user_id = t4.user_id
WHERE NOT EXISTS (SELECT NULL
                   FROM test t5
                   WHERE t1.user_id = t5.user_id
                     AND t5.status_id != 7
                     AND t1.createdAt < t5.createdAt
                     AND t5.createdAt < t2.createdAt) 
HAViNG cretecompare2  BETWEEN '2020-02-01' AND '2020-04-01'
  

и в нем говорится

 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db_2015623402.t1.user_id'; this is incompatible with sql_mode=only_full_group_by
  

ожидаемые результаты

  ----- ----- --------- 
| MIN | MAX | AVERAGE |
 ----- ----- --------- 
|   1 |  58 |      22 |
 ----- ----- --------- 
  

Это поможет. Вы ищете сводку данных внутри имеющихся у вас данных, чтобы вы могли обернуть их в SELECT () , присвоить всему этому псевдоним в конце ( t ) и использовать такие функции, как min() , max() и avg() , как обычно.

 SELECT min(diff) as min, max(diff) as max, format(avg(diff),0) as avg  FROM (SELECT t1.user_id, 
       t1.createdAt cretecompare1, 
       t2.createdAt cretecompare2,
       DATEDIFF(t2.createdAt, t1.createdAt) diff
-- table for a transaction
FROM test t1
-- table for prev. transaction
JOIN test t2 ON t1.user_id = t2.user_id 
            AND t1.createdAt < t2.createdAt
            AND 7 NOT IN (t1.status_id, t2.status_id)
JOIN (SELECT t3.user_id
      FROM test t3
      WHERE t3.status_id != 7
      GROUP BY t3.user_id
      HAVING SUM(t3.createdAt < '2020-04-01') > 1
         AND SUM(t3.createdAt BETWEEN '2020-02-01' AND '2020-04-01')) t4 ON t1.user_id = t4.user_id
WHERE NOT EXISTS (SELECT NULL
                   FROM test t5
                   WHERE t1.user_id = t5.user_id
                     AND t5.status_id != 7
                     AND t1.createdAt < t5.createdAt
                     AND t5.createdAt < t2.createdAt) 
HAViNG cretecompare2  BETWEEN '2020-02-01' AND '2020-04-01') t