PostgreSQL中SQL查询无法返回预期欺诈行为记录问题排查

表结构 #

CREATE TABLE IF NOT EXISTS auth_user
(
    id SERIAL PRIMARY KEY,
    username VARCHAR(30),
    first_name VARCHAR(30),
    last_name VARCHAR(30)
);

CREATE TABLE IF NOT EXISTS statistics_restaccesslog
(
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    url VARCHAR(200),
    course_id INTEGER,
    date_visited TIMESTAMP,
    ip_address VARCHAR(16)
);

测试数据 #

INSERT INTO auth_user (username, first_name, last_name)
VALUES
    ('user1', 'John', 'Doe'),
    ('user2', 'Jane', 'Smith'),
    ('user3', 'Michael', 'Johnson'),
    ('user4', 'Emily', 'Brown'),
    ('user5', 'David', 'Miller'),
    ('user6', 'Olivia', 'Davis'),
    ('user7', 'Daniel', 'Wilson'),
    ('user8', 'Sophia', 'Anderson'),
    ('user9', 'Andrew', 'Taylor'),
    ('user10', 'Emma', 'Thomas');

INSERT INTO statistics_restaccesslog (user_id, url, course_id, date_visited, ip_address)
VALUES
    (1, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.1'),
    (1, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.2'),
    (3, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.2'),
    (4, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.3'),
    (5, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.4'),
    (6, 'https://example.com/page', 2, CURRENT_TIMESTAMP, '192.168.0.5'),
    (7, 'https://example.com/page', 2, CURRENT_TIMESTAMP, '192.168.0.6'),
    (8, 'https://example.com/page', 3, CURRENT_TIMESTAMP, '192.168.0.7'),
    (9, 'https://example.com/page', 4, CURRENT_TIMESTAMP, '192.168.0.8'),
    (10, 'https://example.com/page', 5, CURRENT_TIMESTAMP, '192.168.0.9');

需排查的两类异常记录 #

• 异常类型1：同一(user_id, url)组合对应不同ip_address的记录

  (1, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.1')
  (1, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.2')
  

• 异常类型2：同一ip_address对应不同(user_id, url)组合的记录

  (1, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.2')
  (3, 'https://example.com/page', 1, CURRENT_TIMESTAMP, '192.168.0.2')
  

遇到的问题 #

编写的SQL查询在db-fiddle中能正确返回预期记录，但在真实的PostgreSQL 11数据库中，仅返回部分符合条件的记录，后续新增的4条符合两类异常的记录也未被查询返回。使用JOIN写法的等效查询同样存在该问题。

内容的提问来源于stack exchange，提问作者MsA