用户行为:用户ID、商品ID、商品类目ID、行为类型和时间戳组成,并以逗号分隔。 行为类型:点击pv、购买buy、加购cart、fav收藏中使用sql统计每天的用户量、点击量、收藏量、加购量、购买量
时间: 2023-07-10 11:40:23 浏览: 34
假设数据表名为user_behavior,包含列名为user_id、item_id、category_id、behavior_type和timestamp。
统计每天的用户量:
```
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') AS day, COUNT(DISTINCT user_id) AS user_count
FROM user_behavior
GROUP BY day;
```
统计每天的点击量:
```
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') AS day, COUNT(*) AS click_count
FROM user_behavior
WHERE behavior_type = 'pv'
GROUP BY day;
```
统计每天的收藏量:
```
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') AS day, COUNT(*) AS fav_count
FROM user_behavior
WHERE behavior_type = 'fav'
GROUP BY day;
```
统计每天的加购量:
```
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') AS day, COUNT(*) AS cart_count
FROM user_behavior
WHERE behavior_type = 'cart'
GROUP BY day;
```
统计每天的购买量:
```
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') AS day, COUNT(*) AS buy_count
FROM user_behavior
WHERE behavior_type = 'buy'
GROUP BY day;
```
以上 SQL 语句可以按照日期分组,分别统计每天的用户量、点击量、收藏量、加购量和购买量。