产品数据分析中的常见SQL模式
微信扫一扫，分享给好友
关闭
产品经理不需要成为SQL专家，但需要能独立完成基础的数据查询。
模式一：用户留存查询
```sql
SELECT
date_trunc('week', registration_date) as cohort_week,
COUNT(DISTINCT user_id) as total_users,
COUNT(DISTINCT CASE WHEN last_active_date >= registration_date + 7 THEN user_id END) as d7_retained
FROM users
GROUP BY 1
```
模式二：漏斗分析
```sql
SELECT
step,
COUNT(DISTINCT user_id) as users,
COUNT(DISTINCT user_id) * 1.0 / LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY step) as conversion_rate
FROM funnel_events
GROUP BY step
ORDER BY step
```
模式三：用户分层
```sql
SELECT
CASE
WHEN days_since_last_active <= 7 THEN '高活跃'
WHEN days_since_last_active <= 30 THEN '中活跃'
ELSE '低活跃'
END as user_segment,
COUNT(*) as user_count
FROM user_activity
GROUP BY 1
```
模式四：同期群分析
```sql
SELECT
date_trunc('month', first_active_date) as cohort_month,
period_month,
COUNT(DISTINCT user_id) as active_users
FROM user_cohorts
GROUP BY 1, 2
ORDER BY 1, 2
```
写在最后
SQL是产品经理与数据对话的语言。掌握基础SQL，能让你更独立、更高效地获取数据洞察。
建议：从简单的查询开始，逐步学习JOIN、子查询、窗口函数等进阶用法。