# **Hive综合应用案例：用户搜索日志分析**

## **1. 案例背景**
假设我们有一个电商平台的用户搜索日志数据，包含：
- **用户ID**（uid）
- **搜索关键词**（keyword）
- **搜索时间**（search_time）
- **点击的商品ID**（click_item，可能为NULL）
- **搜索来源**（source，如APP、PC、H5）

**目标**：
1. 统计热门搜索词（Top 10）
2. 分析不同时间段的搜索量（按小时）
3. 计算搜索-点击转化率（CTR）
4. 分析不同来源的搜索行为差异

---

## **2. 数据准备**
### **(1) 创建Hive表**
```sql
CREATE TABLE user_search_logs (
    uid STRING,
    keyword STRING,
    search_time TIMESTAMP,
    click_item STRING,
    source STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
```

### **(2) 加载数据**
```sql
LOAD DATA LOCAL INPATH '/path/to/search_logs.csv' INTO TABLE user_search_logs;
```

---

## **3. 数据分析**
### **(1) 热门搜索词（Top 10）**
```sql
SELECT 
    keyword,
    COUNT(*) AS search_count
FROM 
    user_search_logs
GROUP BY 
    keyword
ORDER BY 
    search_count DESC
LIMIT 10;
```

### **(2) 按小时统计搜索量**
```sql
SELECT 
    HOUR(search_time) AS hour_of_day,
    COUNT(*) AS search_count
FROM 
    user_search_logs
GROUP BY 
    HOUR(search_time)
ORDER BY 
    hour_of_day;
```

### **(3) 搜索-点击转化率（CTR）**
```sql
SELECT 
    keyword,
    COUNT(*) AS total_searches,
    SUM(CASE WHEN click_item IS NOT NULL THEN 1 ELSE 0 END) AS clicks,
    ROUND(SUM(CASE WHEN click_item IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS ctr_percentage
FROM 
    user_search_logs
GROUP BY 
    keyword
ORDER BY 
    ctr_percentage DESC
LIMIT 10;
```

### **(4) 不同来源的搜索行为分析**
```sql
SELECT 
    source,
    COUNT(*) AS total_searches,
    SUM(CASE WHEN click_item IS NOT NULL THEN 1 ELSE 0 END) AS clicks,
    ROUND(AVG(LENGTH(keyword)), 2) AS avg_keyword_length
FROM 
    user_search_logs
GROUP BY 
    source
ORDER BY 
    total_searches DESC;
```

---

## **4. 进阶分析**
### **(1) 用户搜索关键词词频统计**
```sql
-- 使用LATERAL VIEW + EXPLODE分词（假设关键词用空格分隔）
SELECT 
    word,
    COUNT(*) AS frequency
FROM 
    user_search_logs
LATERAL VIEW 
    EXPLODE(SPLIT(keyword, ' ')) words AS word
GROUP BY 
    word
ORDER BY 
    frequency DESC
LIMIT 20;
```

### **(2) 用户搜索关键词共现分析**
```sql
-- 找出经常一起搜索的关键词组合
SELECT 
    a.keyword AS keyword1,
    b.keyword AS keyword2,
    COUNT(*) AS co_occurrence_count
FROM 
    user_search_logs a
JOIN 
    user_search_logs b
ON 
    a.uid = b.uid
    AND a.search_time < b.search_time
    AND DATEDIFF(b.search_time, a.search_time) <= 1  -- 1天内搜索的关键词
WHERE 
    a.keyword != b.keyword
GROUP BY 
    a.keyword, b.keyword
ORDER BY 
    co_occurrence_count DESC
LIMIT 10;
```

---

## **5. 可视化（可选）**
可以使用 **Hive + Superset / Tableau** 进行可视化：
- **柱状图**：热门搜索词排名
- **折线图**：24小时搜索趋势
- **饼图**：不同来源的搜索占比
- **漏斗图**：搜索→点击转化率

---

## **6. 优化建议**
1. **分区表优化**：如果数据量大，可以按 `dt（日期）` 分区：
   ```sql
   CREATE TABLE user_search_logs_partitioned (
       uid STRING,
       keyword STRING,
       search_time TIMESTAMP,
       click_item STRING,
       source STRING
   )
   PARTITIONED BY (dt STRING)
   STORED AS ORC;
   ```
2. **使用ORC/Parquet** 存储格式提高查询性能。
3. **建立索引** 在 `keyword` 和 `source` 字段上加速查询。

---

## **总结**
通过Hive SQL，我们可以轻松分析用户搜索日志，挖掘：
✅ **热门搜索词** → 优化搜索推荐  
✅ **搜索时段分布** → 调整服务器资源  
✅ **搜索-点击转化率** → 改进搜索结果排序  
✅ **不同来源用户行为** → 优化多端体验  

你可以根据业务需求扩展更多分析维度！ 🚀