Вопрос по корректировке скрипта Postgresql

Question

Пишу скрипт, который бы выводил список пользователей, у которых есть дубликаты учетных записей (с одного ip и устройства авторизация). Мне нужно, чтобы в выборку попадал только список тех пользователей, у которых дубликаты свежие (т.е. созданы недавно, за последний месяц). Никак не могу понять, как встроить.

select 
distinct user_id, 
  status, created_at 

from ...
  
where 
  users.created_at >= (current_date - 2)
  
and (ip, device) 
IN

(
select 
ip, device

from 
...

inner join ... on ...

inner join ... on ...

group by ip, device

having COUNT(distinct user_id) >=3

and /вот здесь не могу никак сообразить, как добавить условие про дату создания таких дубликатов/)

Пробовал добавить and created_at >= (current_date - 30) - не помогает, список не выгружается совсем.

Answer 1

Основная ошибка в ряде случаев - излишняя сложность. На мой взгляд, вам надо иметь во WHERE комбинированную проверку на период и на уникальность пользователя (её - вложенным запросом). И всё.

У вас же идея верная, но много лишнего (или это не описано в условии).
Кроме того, вы условие проверки единичных записей (должно быть во WHERE) пытаетесь поместить в проверку агрегированных записей (HAVING), что принципиально не должно работать.
И не могу судить о работе в postgresql синтаксиса created_at >= (current_date - 30) - в mySQL это работать не будет точно.

Синтаксис postgresql я не знаю, так что проверил работу кода ниже на mySQL. Основной принцип - рабочий, можете подправить, что надо, и проверить у себя. Сам код - последние 7 строк, все остальное - создание тестового примера.

CREATE TABLE calls(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR (25),
    status_1 VARCHAR (25),
    ip VARCHAR (25), 
    device VARCHAR (25),
    created_at DATETIME    
);
INSERT INTO calls (user_id, status_1, ip, device,created_at)
    VALUES
    ("Петя", "нет разницы", '192.168.0.1', 'модем', '2018-05-25 21:25:54'),
    ("Маша", "нет разницы", '192.168.0.2', 'синхрофазатрон', '2018-05-25 22:22:22'),
    ("Вася", "нет разницы", '192.168.0.3', 'табурет', '2018-05-25 21:25:54'),
    ("Петя", "нет разницы", '192.168.0.1', 'модем', '2018-05-26 22:25:54'),
    ("Маша", "нет разницы", '192.168.0.2', 'синхрофазатрон', '2022-08-03 01:01:01'),
    ("Гена", "нет разницы", '192.168.0.4', 'ванна', '2022-08-04 01:01:01')
;
SELECT * FROM calls;
SELECT user_id, status_1, ip, device,created_at
FROM calls
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(created_at) and (ip, device) IN (
    SELECT ip, device 
    FROM calls
    GROUP BY ip, device
    HAVING COUNT(*) > 1);

Результат (отброшены все 4 записи старых, а также отброшен Гена, который появился первый раз. Зато Маша уличена во вторичной регистрации и включена в вывод):

Affected rows: 0

Affected rows: 6

Query result:
+----+---------+-------------+-------------+----------------+---------------------+
| id | user_id | status_1    | ip          | device         | created_at          |
+----+---------+-------------+-------------+----------------+---------------------+
| 1  | Петя    | нет разницы | 192.168.0.1 | модем          | 2018-05-25 21:25:54 |
| 2  | Маша    | нет разницы | 192.168.0.2 | синхрофазатрон | 2018-05-25 22:22:22 |
| 3  | Вася    | нет разницы | 192.168.0.3 | табурет        | 2018-05-25 21:25:54 |
| 4  | Петя    | нет разницы | 192.168.0.1 | модем          | 2018-05-26 22:25:54 |
| 5  | Маша    | нет разницы | 192.168.0.2 | синхрофазатрон | 2022-08-03 01:01:01 |
| 6  | Гена    | нет разницы | 192.168.0.4 | ванна          | 2022-08-04 01:01:01 |
+----+---------+-------------+-------------+----------------+---------------------+
Affected rows: 6

Query result:
+---------+-------------+-------------+----------------+---------------------+
| user_id | status_1    | ip          | device         | created_at          |
+---------+-------------+-------------+----------------+---------------------+
| Маша    | нет разницы | 192.168.0.2 | синхрофазатрон | 2022-08-03 01:01:01 |
+---------+-------------+-------------+----------------+---------------------+
Affected rows: 1

БЛОГ НА HUSL

Вопрос по корректировке скрипта Postgresql

Ответы (1 шт):