컬럼 구조 (Columns Definition)
10개 컬럼| 컬럼명 | 데이터 타입 | Null 허용 | Key | 기본값 (Default) | 추가 속성 (Extra) | 코멘트 / 설명 |
|---|---|---|---|---|---|---|
|
keyword_id
|
int(11) | NO | - | 0 | - | - |
|
keyword
|
varchar(100) | NO | - | NULL | - | - |
|
category
|
varchar(50) | YES | - | '일반' | - | - |
|
total_articles
|
bigint(21) | NO | - | 0 | - | - |
|
articles_24h
|
decimal(23,0) | YES | - | NULL | - | - |
|
articles_7d
|
decimal(23,0) | YES | - | NULL | - | - |
|
avg_importance
|
decimal(6,2) | YES | - | NULL | - | - |
|
max_importance
|
tinyint(4) | YES | - | NULL | - | - |
|
total_reads
|
decimal(32,0) | YES | - | NULL | - | - |
|
last_collected_at
|
timestamp | YES | - | current_timestamp() | - | - |
CREATE TABLE DDL
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_keyword_stats` AS select `k`.`id` AS `keyword_id`,`k`.`keyword` AS `keyword`,`k`.`category` AS `category`,count(`a`.`id`) AS `total_articles`,sum(case when `a`.`published_at` >= current_timestamp() - interval 24 hour then 1 else 0 end) AS `articles_24h`,sum(case when `a`.`published_at` >= current_timestamp() - interval 7 day then 1 else 0 end) AS `articles_7d`,round(avg(`a`.`importance_score`),2) AS `avg_importance`,max(`a`.`importance_score`) AS `max_importance`,sum(`a`.`read_count`) AS `total_reads`,max(`a`.`created_at`) AS `last_collected_at` from (`keywords` `k` left join `articles` `a` on(`a`.`keyword_id` = `k`.`id`)) where `k`.`is_active` = 1 group by `k`.`id`,`k`.`keyword`,`k`.`category`