35 lines
1.2 KiB
SQL
Executable File
35 lines
1.2 KiB
SQL
Executable File
-- 방문자 마스터 테이블: clientId 단위 집계 정보 보관
|
|
create table if not exists visitor_clients (
|
|
id serial primary key,
|
|
client_id varchar(120) not null unique,
|
|
nickname varchar(80) not null,
|
|
first_visited_at timestamptz not null default now(),
|
|
last_visited_at timestamptz not null default now(),
|
|
visit_count integer not null default 1,
|
|
last_visited_url varchar(2000),
|
|
last_user_agent varchar(1000),
|
|
last_ip varchar(120),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_visitor_clients_last_visited_at
|
|
on visitor_clients (last_visited_at desc);
|
|
|
|
-- 방문 상세 이력 테이블: 중복 방문도 모두 적재
|
|
create table if not exists visitor_visit_histories (
|
|
id serial primary key,
|
|
client_id varchar(120) not null,
|
|
visited_at timestamptz not null default now(),
|
|
url varchar(2000) not null,
|
|
event_type varchar(80) not null default 'page_view',
|
|
user_agent varchar(1000),
|
|
ip varchar(120)
|
|
);
|
|
|
|
create index if not exists idx_visitor_visit_histories_client_id
|
|
on visitor_visit_histories (client_id);
|
|
|
|
create index if not exists idx_visitor_visit_histories_visited_at
|
|
on visitor_visit_histories (visited_at desc);
|