zenn
ef9efd7af5
통계 추이 차트 공통화, 유입 스택 막대, 집계 시작일 고정, traffic API 오류 수정, deploy 문서 정리를 포함한다. Co-authored-by: Cursor <cursoragent@cursor.com>
38 lines
1.2 KiB
SQL
38 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS post_analytics_traffic_daily (
|
|
day DATE NOT NULL,
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
source_group TEXT NOT NULL,
|
|
source_name TEXT NOT NULL,
|
|
device_type TEXT NOT NULL,
|
|
os_name TEXT NOT NULL,
|
|
keyword TEXT NOT NULL DEFAULT '',
|
|
page_views INTEGER NOT NULL DEFAULT 0,
|
|
visitors INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (day, post_id, source_group, source_name, device_type, os_name, keyword)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS post_analytics_traffic_daily_post_day_idx
|
|
ON post_analytics_traffic_daily (post_id, day DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS post_analytics_traffic_daily_source_idx
|
|
ON post_analytics_traffic_daily (source_group, source_name);
|
|
|
|
ALTER TABLE analytics_daily_visitors
|
|
DROP CONSTRAINT IF EXISTS analytics_daily_visitors_scope_check;
|
|
|
|
ALTER TABLE analytics_daily_visitors
|
|
ADD CONSTRAINT analytics_daily_visitors_scope_check CHECK (scope IN ('site', 'post', 'page', 'traffic', 'post_traffic'));
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS analytics_daily_visitors_post_traffic_uidx
|
|
ON analytics_daily_visitors (
|
|
day,
|
|
post_id,
|
|
visitor_hash,
|
|
source_group,
|
|
source_name,
|
|
device_type,
|
|
os_name,
|
|
keyword
|
|
)
|
|
WHERE scope = 'post_traffic';
|