表名: tx_live 字段:day 日期;rtime 时间戳;country 国家;os 手机系统;version 版本;uid 用户id;request_live 接收到连麦请求时值为1;start_live 开始连麦时值为1;stop_live 结束连麦时值为1;buid 观众uid;只要出现请求/连麦/结束都会单独上报一条,不是合并上报 例如请求连麦时上报数据为: day = xx, rtime = xx,country =xx,os=xx,version=xx,uid=xx,request_live=1,start_live=null,stop_live=null,buid=xxx 2. 按上面的上报定义,编写hive sql 按天出报表 主播连麦人数,次数,连麦时长
时间: 2024-02-23 15:01:14 浏览: 126
以下是基于上报定义编写的Hive SQL语句:
```sql
SELECT
day,
uid AS anchor_uid,
COUNT(DISTINCT buid) AS connected_users,
COUNT(request_live) AS connection_attempts,
SUM(CASE WHEN start_live = 1 THEN stop_live - rtime ELSE 0 END) AS total_live_duration
FROM tx_live
WHERE request_live = 1 OR start_live = 1 OR stop_live = 1
GROUP BY day, uid
```
该查询语句会按天分组,统计每个主播的连麦人数、连麦次数、以及总连麦时长。其中,`COUNT(DISTINCT buid)`用于计算连麦人数,而`COUNT(request_live)`则用于计算连麦次数。最后,`SUM(CASE WHEN start_live = 1 THEN stop_live - rtime ELSE 0 END)`会根据开始/结束连麦的时间戳计算总连麦时长。
相关问题
表名: tx_avator 字段:day 日期 rtime 时间戳 country 国家 os 手机系统 version 版本 uid 用户id finish 1 更换成功 0未更换成功 style 0 默认风格, 1 自定义风格 编写hive sql 按天出报表 a. 头像功能自定义风格累计使用率及当前使用率 名称定义: 累计使用率: 所有使用过自定义的用户/版本覆盖总用户数 当前使用率: 当前依然保持自定义状态的用户/版本覆盖总用户数
可以使用以下Hive SQL语句来生成所需的报表:
```
SELECT day,
style,
COUNT(DISTINCT uid) / COUNT(DISTINCT version) AS cumulative_usage_rate,
COUNT(DISTINCT CASE WHEN finish = 1 THEN uid END) / COUNT(DISTINCT version) AS current_usage_rate
FROM tx_avator
GROUP BY day, style
```
该语句将按照日期和风格分组,计算累计使用率和当前使用率,并返回结果。其中,累计使用率定义为所有使用过自定义风格的用户数除以总用户数,当前使用率定义为当前仍处于自定义状态的用户数除以总用户数。注意,使用DISTINCT关键字来确保仅计算唯一的用户和版本。
表名:tx_friends 字段:num_cnt 好友数量;day 日期;rtime 时间戳;country 国家;os 手机系统;uid 用户id 1. 按上面的上报定义,编写hive做一个好友中位数及平均数的报表。 一个用户 一天切N次好友列表时 会上报 n个同样的日期,n个不同的时间戳,n个同样的id
可以使用以下Hive SQL语句进行好友中位数和平均数的报表计算:
```
SELECT
day,
country,
os,
AVG(num_cnt) AS avg_num_cnt,
percentile_cont(0.5) WITHIN GROUP (ORDER BY num_cnt) AS median_num_cnt
FROM
tx_friends
GROUP BY
day,
country,
os;
```
这个查询将按天、国家和操作系统对好友数量进行分组,并计算平均值和中位数。其中,`percentile_cont(0.5) WITHIN GROUP (ORDER BY num_cnt)`表示按num_cnt字段升序排序后的中位数。
阅读全文