Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析（二）

1.访问次数最多的5个用户

输出每个app 下访问次数最多的5个用户返回字段app_id, top_5_user_id
app_iduser_iddatetime
A

u1

2020-01-01 00:00:00

B

u2

2020-01-01 00:00:01

A

u1

2020-01-01 00:00:02

C

u2

2020-01-01 01:00:00

WITH ranked_users AS (
  SELECT 
    app_id,
    user_id,
    COUNT(*) AS visit_count,
    ROW_NUMBER() OVER (PARTITION BY app_id ORDER BY COUNT(*) DESC) AS rank
  FROM your_table
  GROUP BY app_id, user_id
)
SELECT 
  app_id,
  STRING_AGG(user_id, ', ' ORDER BY visit_count DESC) AS top_5_user_id
FROM ranked_users
WHERE rank <= 5
GROUP BY app_id;

2.近

天的每日活跃用户数

表：

Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
该表没有包含重复数据。
activity_type 列是 ENUM(category) 类型， 从 ('open_session'， 'end_session'， 'scroll_down'， 'send_message') 取值。
该表记录社交媒体网站的用户活动。
注意，每个会话只属于一个用户。

编写解决方案，统计截至

2019-07-27

（包含2019-07-27），近** **

天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

SELECT 
    activity_date AS day,
    COUNT(DISTINCT user_id) AS active_users
FROM 
    Activity
WHERE 
    activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY 
    activity_date
ORDER BY 
    activity_date;

3.获取1-180天注册活跃留存表

现有一个用户活跃表user_active、用户注册表user_regist，

用户字段均为user_id；

设计一张1-180天注册活跃留存表包括以下字段

regist_date -- 注册日期
date_diff -- 日期差
rate -- 留存率（从注册日期起往后180天内注册用户里的活跃用户数/注册日期当天注册的用户总数）

**用户注册表 (
user_regist
):**
user_idregist_date12021-01-0122021-01-0232021-01-03
**用户活跃表 (
user_active
):**
user_idactive_date12021-01-0212021-01-1022021-01-0332021-01-0332021-01-0432021-02-02

-- 定义包含总分和用户排名的CTE（公共表表达式）
WITH TotalScore AS (
    -- 计算每个用户的注册日期及注册总数
    SELECT 
        user_id,  -- 用户ID
        regist_date,  -- 注册日期
        COUNT(*) OVER (PARTITION BY regist_date) AS regist_count  -- 计算每个注册日期的用户数
    FROM 
        user_regist  -- 注册表
    WHERE 
        dt >= DATE_SUB(current_date(), 180)  -- 只考虑过去180天内的数据
), 
RandTotalScore AS (
    -- 对每个用户的活跃日期和注册日期计算日期差并统计用户数
    SELECT 
        t1.regist_date,  -- 注册日期
        t1.regist_count,  --  当前日期的总注册用户
        DATEDIFF(t2.active_date, t1.regist_date) AS date_diff,  -- 计算活跃日期与注册日期的日期差
        COUNT(*) AS user_count  -- 统计每个日期差的用户总数
    FROM 
        TotalScore t1  -- 引用先前计算的注册信息
    LEFT JOIN 
        (   -- 子查询：选择活跃用户及其活跃日期
            SELECT 
                user_id,  -- 用户ID
                to_date(active_date) as active_date  -- 活跃日期转换为日期格式
            FROM 
                user_active  -- 活跃表
            GROUP BY 
                user_id, to_date(active_date)  -- 按用户和活跃日期分组以避免重复
        ) t2 ON t1.user_id = t2.user_id  -- 将注册表与活跃表连接
    WHERE 
        DATEDIFF(t2.active_date, t1.regist_date) BETWEEN 1 AND 180  -- 筛选日期差在1到180天之间的数据
    GROUP BY 
        t1.regist_date, DATEDIFF(t2.active_date, t1.regist_date)  -- 按注册日期和日期差分组
)

-- 选择最终的结果集，包括日期差、用户数和留存率
SELECT 
    regist_date,  -- 注册日期
    date_diff,  -- 日期差
    user_count / regist_count AS rate  -- 计算留存率
FROM 
    RandTotalScore;  -- 引用计算好的活跃信息

4.学生名次统计

1）找出语数英每门课前三名的学生

2）单科分数有低于80分的学生的总分排名

1). 每门课前三名的学生

SELECT name, lesson, goal
FROM (
    SELECT name, lesson, goal,
           RANK() OVER (PARTITION BY lesson ORDER BY goal DESC) as rank
    FROM scores
) ranked
WHERE rank <= 3;

2). 单科分数有低于80分的学生的总分排名

-- 首先，创建一个子查询或公共表表达式 (CTE)，以获取得分低于80分的学生名单
WITH LowScorers AS (
    SELECT DISTINCT name
    FROM scores
    WHERE goal < 80
)

-- 接下来，使用此子查询来计算这些学生的总分，并对其进行排名
SELECT 
    name,
    SUM(goal) AS total_score,
    RANK() OVER (ORDER BY SUM(goal) DESC) as rank
FROM 
    scores
WHERE 
    name IN (SELECT name FROM LowScorers)
GROUP BY 
    name
ORDER BY 
    total_score DESC;

5.最近连续3周连续活跃用户数

源表dws_uv_detail示例数据
mid_iddtwk_dt10012019-02-062019-02-04_2019-02-1010012019-02-072019-02-04_2019-02-1010012019-02-082019-02-04_2019-02-1010012019-02-092019-02-04_2019-02-1010012019-02-102019-02-04_2019-02-1010012019-02-112019-02-11_2019-02-1710022019-02-122019-02-11_2019-02-1710032019-02-102019-02-04_2019-02-1010042019-02-092019-02-04_2019-02-1010042019-02-102019-02-04_2019-02-1010042019-02-112019-02-11_2019-02-1710042019-02-122019-02-11_2019-02-1710052019-02-082019-02-04_2019-02-1010052019-02-092019-02-04_2019-02-1010052019-02-102019-02-04_2019-02-10

目标表ads_continuity_wk_count示例数据

dt wk_dt continuity_count2019-02-12 2019-02-06_2019-02-121562019-02-132019-02-07_2019-02-131622019-02-142019-02-08_2019-02-14170 create table ads_continuity_wk_count(
dt string COMMENT      '统计日期，一般用结束周周日日期，如果每天计算一次，可用当天日期'， 

wk_dtstring COMMENT '持续时间'， 

continuity_count bigint   '最近3周的活跃总次数' 
）

with dws_uv_detail_convert as(
    select mid_id,wk_dt
    from dws_uv_detail
    where wk_dt>=concat(date_add(next_day(CURRENT_DATE, 'MO'), -7*3), '_', date_add(next_day(CURRENT_DATE, 'MO'), -7*2-1))
    and wk_dt<=concat(date_add(next_day(CURRENT_DATE, 'MO'), -7), '_', date_add(next_day(CURRENT_DATE, 'MO'), -1))
    group by mid_id,wk_dt
)

select
  CURRENT_DATE,
    concat(date_add(next_day(CURRENT_DATE, 'MO'), -7*3), '_', date_add(next_day(CURRENT_DATE, 'MO'), -1)),
 wk_dt,
  count(*) continuity_count
from
(
  select mid_id
  from dws_uv_detail_wk_convert
  group by mid_id
  having count(*) = 3
) t1;

where wk_dt>=concat(date_add(next_day(CURRENT_DATE, 'MO'), -73), '_', date_add(next_day(CURRENT_DATE, 'MO'), -72-1))
and wk_dt<=concat(date_add(next_day(CURRENT_DATE, 'MO'), -7), '_', date_add(next_day(CURRENT_DATE, 'MO'), -1))

next_day(CURRENT_DATE, 'MO')
的含义是：

找到从当前日期（CURRENT_DATE）开始的下一个星期一（Monday）的日期。

这个where条件筛选出了连续三周的数据。具体来说：

第一周：从 3 周前的周一到 2 周前的周日

第二周：从 2 周前的周一到 1 周前的周日

第三周：从 1 周前的周一到本周的周日

concat确保生成的日期范围字符串格式与
wk_dt
列的格式一致，便于比较和匹配。

6.最近7天内连续3天活跃用户数

源表dws_uv_detail和目标表ads_continuity_uv_count和题5保持一致

-- 创建目标表ads_continuity_uv_count
CREATE TABLE IF NOT EXISTS ads_continuity_uv_count (
    dt STRING COMMENT '统计日期',
    wk_dt STRING COMMENT '最近7天日期范围',
    continuity_count BIGINT COMMENT '连续3天活跃用户数'
) COMMENT '最近7天连续3天活跃用户数';

-- 插入数据到ads_continuity_uv_count表
INSERT OVERWRITE TABLE ads_continuity_uv_count
SELECT
    CURRENT_DATE AS dt,
    CONCAT(DATE_SUB(CURRENT_DATE, 6), '_', CURRENT_DATE)  wk_dt,
    COUNT(DISTINCT mid_id) AS continuity_count
FROM (
    SELECT
        mid_id
    FROM (
        SELECT
            mid_id,
            dt,
            DATE_SUB(dt, RANK() OVER (PARTITION BY mid_id ORDER BY dt)) AS date_group
        FROM dws_uv_detail
        WHERE dt BETWEEN DATE_SUB(CURRENT_DATE, 6) AND CURRENT_DATE
        -- 筛选最近7天的数据
    ) t1
    GROUP BY mid_id, date_group
    HAVING COUNT(*) >= 3
    -- 找出连续活跃3天及以上的用户
) t2;

-- 解释：
-- 1. 首先在WHERE子句中筛选出最近7天的数据。
-- 2. 使用窗口函数RANK()给每个用户的活跃日期进行排序。
-- 3. 用日期减去排名，得到date_group。连续的日期会得到相同的date_group值。
-- 4. 按mid_id和date_group分组，统计每组的记录数。记录数>=3的就是连续3天及以上活跃的用户。
-- 5. 最后统计符合条件的不同用户数量，即为连续3天活跃的用户数。
-- 6. CURRENT_DATE用于动态获取当前日期，使查询可以自动适应不同的执行日期。
-- 7. CONCAT(DATE_SUB(CURRENT_DATE, 6), '_', CURRENT_DATE) 生成最近7天的日期范围字符串。

7.餐馆营业额变化增长

Customer
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
在 SQL 中，(customer_id, visited_on) 是该表的主键。
该表包含一家餐馆的顾客交易数据。
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。
amount 是一个顾客某一天的消费总额。
你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。
average_amount
要 保留两位小数。

结果按
visited_on
升序排序。

WITH daily_totals AS (
    SELECT visited_on, SUM(amount) as daily_amount
    FROM Customer
    GROUP BY visited_on
),
rolling_window AS (
    SELECT 
        visited_on,
        SUM(daily_amount) OVER (
            ORDER BY visited_on
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as amount,
        AVG(daily_amount) OVER (
            ORDER BY visited_on
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as average_amount,
        ROW_NUMBER() OVER (ORDER BY visited_on) as row_num
    FROM daily_totals
)
SELECT 
    visited_on,
    amount,
    ROUND(average_amount, 2) as average_amount
FROM rolling_window
WHERE row_num >= 7
ORDER BY visited_on;

-- ORDER BY visited_on RANGE BETWEEN 6 PRECEDING AND CURRENT ROW 定义，意味着它将包括当前行的日期和之前6天的数据，从而确保每次计算都覆盖了7天的数据。

-- RDER BY visited_on  RANGE BETWEEN CURRENT ROW AND INTERVAL 6 DAY FOLLOWING当前行的日期和之后6天的数据，从而确保每次计算都覆盖了7天的数据

RANGE BETWEEN: 适用于处理连续的数值或日期范围，如时间序列数据。

这会计算当前行薪水前后1000范围内的薪水总和
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
ROWS BETWEEN: 适用于需要精确指定行数的情况，不考虑数值的连续性

这会计算当前行前后各2行的薪水总和。
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

8.上升的温度

表：

Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的

id

。

返回结果 无顺序要求 。

联表查询求解

select a.ID, a.date
from weather as a cross join weather as b 
     on datediff(a.date, b.date) = 1
where a.temp > b.temp;

LAG求解

SELECT id
FROM (
    SELECT 
        id,
        temperature,
        recordDate,
        LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp,
        LAG(recordDate) OVER (ORDER BY recordDate) AS prev_date
    FROM Weather
) subquery
WHERE temperature > prev_temp
    AND DATEDIFF(recordDate, prev_date) = 1;

9. 找出恶意购买用户

问题：下面是某电商网站的订单数据，包括order_id,user_id,order_status和operate_time四个字段，我们需要找出所有恶意购买的用户。恶意购买的用户定义是：同一个用户，在任意半小时内（含），取消订单次数>=3次的就被视为恶意买家。比如该样例数据中c用户就是恶意买家。

典型的滑动窗口的场景。可能我们平常在Flink中听滑动窗口比较多，其实Hive中也是有滑动窗口的功能的（按数据范围开窗）。

order_id    user_id    order_status     operate_time1101         a         已支付        2023-01-01 10:00:001102         a         已取消        2023-01-01 10:10:001103         a         待支付        2023-01-01 10:20:001104         b         已取消        2023-01-01 10:30:001105         a         待确认        2023-01-01 10:50:001106         a         已取消        2023-01-01 11:00:001107         b         已取消        2023-01-01 11:40:001108         b         已取消        2023-01-01 11:50:001109         b         已支付        2023-01-01 12:00:001110         b         已取消        2023-01-01 12:11:001111         c         已取消        2023-01-01 12:20:001112         c         已取消        2023-01-01 12:30:001113         c         已取消        2023-01-01 12:55:001114         c         已取消        2023-01-01 13:00:00

--这里设定tmp表中放的就是上面提供的样例数据
select
distinct user_id
from
(
    select
    order_id
    ,user_id
    ,order_status
    --通过range between以当前行为锚点，圈定数据范围为operate_time为近30分钟内，然后算该范围内的取消订单数
    ,count(case when order_status='已取消' then order_id end) over(partition by user_id order by operate_time range between 30*60 preceding and current row) as cancel_order_cnt
    from
    (
        select
        order_id
        ,user_id
        ,order_status
        ,unix_timestamp(operate_time) as operate_time  ---由于range...between只能整数比较，这里先转换为秒
        from tmp
    )t1
)t1
where cancel_order_cnt>=3
;

10.奖金瓜分问题

在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为3000元，代表奖金池中的初始额度。用户的分数信息(user_score)如下：
user_idscore10145102401033510430105
25
表中的数据代表每一个用户和其对应的得分，user_id和score都不会有重复值。瓜分奖金的规则如下：按照score从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于500时（不含），则停止瓜分奖金。

现在需要查询出所有分到奖金的user_id和其对应的奖金。


select
user_id
,score
,1/power(2,rn)*3000 as prize
from
(
    select
    user_id
    ,score
    ,row_number() over(order by score desc) as rn
    from  user_score
)t1
where 1/power(2,rn)*3000>=250;

11.同时最大在线人数问题

如下为某直播平台各主播的开播及关播时间明细数据(online_offline_data)，现在需要计算出该平台最高峰期同时在线的主播人数。

user_id     start_date              end_date1001    2021-06-14 12:12:12     2021-06-14 18:12:121003    2021-06-14 13:12:12     2021-06-14 16:12:121004    2021-06-14 13:15:12     2021-06-14 20:12:121002    2021-06-14 15:12:12     2021-06-14 16:12:121005    2021-06-14 15:18:12     2021-06-14 20:12:121001    2021-06-14 20:12:12     2021-06-14 23:12:121006    2021-06-14 21:12:12     2021-06-14 23:15:121007    2021-06-14 22:12:12     2021-06-14 23:10:12

用1代表开播(此时用开播时间)，-1代表关播(此时用关播时间)，然后开窗可以计算出到每个时间点时有多少主播同时在线，最后求最大值即可。

select
max(online_nums) as max_online_nums
from
(
    select
    user_id
    ,dt
    ,sum(flag) over(order by dt) as online_nums
    from
    (
        select
        user_id
        ,start_date as dt
        ,1 as flag  --开播记为1
        from online_offline_data
        union all
        select
        user_id
        ,end_date as dt
        ,-1 as flag --关播记为-1
        from online_offline_data
    )t1
)t1;

12.每年的在校人数

year表示学生入学年度，num表示对应年度录取学生人数，stu_len表示录取学生的学制；说明：例如录取年度2018学制是3年，表示该批学生在校年份为2018~~2019、2019~~2020、2020-2021，在算每年的在校人数时，2018/2019/2020/2021年份都需要算上。

every_year_school_number以下是示例数据：
idyearnumstu_len1201820003220192000332020100043
2020
2000
3
根据以上示例计算出每年的在校人数

首先在 t1 表中生成所有可能的年份序列。具体做法是: - 先找出临时表中的最小年份和最大年份加上最大学制减 1,作为生成年份序列的范围。- 使用 lateral view posexplode 将这个范围内的所有年份生成出来,存入 t1 表。

接下来,将 t1 表与 every_year_school_number 进行内连接,条件是当前年份 t1.year 在 tmp 表的 year 到 year + stu_len - 1 之间。

最后,对连接后的结果按 t1.year 分组,并计算每个年份的学生人数 sum(t2.num)。

select
    t1.year,
    sum(t2.num) as stu_num
from
(
    -- 生成所有可能的年份序列,从最小年份到最大年份+最大学制-1
    select
        t1.min_year + tab.pos as year
    from
    (
        select
            min(year) as min_year,
            max(year + stu_len) as max_year
        from every_year_school_number
    ) t1
    lateral view posexplode(split(repeat(',', max_year - min_year), ',')) tab as pos, val
) t1
inner join every_year_school_number t2
    -- 连接临时表,找出每个年份对应的录取人数
    on t1.year between t2.year and t2.year + t2.stu_len - 1
group by
    t1.year

首先,在 t1 表中生成所有可能的年份序列:- 从临时表 tmp 中找出最小年份 2018 和最大年份加上最大学制 2020 + 4 - 1 = 2023,生成年份范围为 2018 到 2023。- 使用 lateral view posexplode 将这个范围内的所有年份生成出来,存入 t1 表。- 此时 t1 表包含的年份有: 2018, 2019, 2020, 2021, 2022, 2023。

接下来,将 t1 表与临时表 tmp 进行内连接:- 对于 t1 表中的每一个年份,都会在 tmp 表中查找是否有对应的记录。- 条件是当前年份 t1.year 在 tmp 表的 year 到 year + stu_len - 1 之间。- 例如, t1.year = 2018 会与 tmp 表中 year = 2018 且 stu_len = 3 的记录匹配。- 同理, t1.year = 2019 会与 tmp 表中 year = 2019 且 stu_len = 3 的记录匹配。- t1.year = 2020 会与 tmp 表中两条记录 year = 2020 且 stu_len = 4 和 stu_len = 3 的记录都匹配。

最后,对连接后的结果按 t1.year 分组,并计算每个年份的学生人数 sum(t2.num):- 2018 年: 2000 人- 2019 年: 2000 人- 2020 年: 1000 + 2000 = 3000 人- 2021 年: 0 人 (因为临时表中没有 2021 年的记录)- 2022 年: 0 人- 2023 年: 0 人

13.计算部门的平均工资

现在要计算每个部门的平均工资（工资和/员工数），但是要去掉部门的最高工资和最低工资（如果一个部门最高或最低工资有并列的，去掉一个最高的和一个最低的）后，计算部门的平均工资。

with tmp as
(
    select 101 as user_id,"研发部" as dept,50000 as salary
    union all
    select 102 as user_id,"研发部" as dept,50000 as salary
    union all
    select 103 as user_id,"研发部" as dept,10000 as salary
    union all
    select 104 as user_id,"研发部" as dept,20000 as salary
    union all
    select 105 as user_id,"研发部" as dept,30000 as salary
    union all
    select 106 as user_id,"市场部" as dept,20000 as salary
    union all
    select 107 as user_id,"市场部" as dept,30000 as salary
    union all
    select 108 as user_id,"产品部" as dept,20000 as salary
    union all
    select 109 as user_id,"产品部" as dept,30000 as salary
    union all
    select 110 as user_id,"产品部" as dept,25000 as salary
)

select
dept
,dept_total_salary
,dept_employee_cnt
,round(dept_total_salary/dept_employee_cnt,2) as dept_avg_salary
from
(
    select
    dept
    ,sum(if((salary=min_salary and min_salary_rn=1) or (salary=max_salary and max_salary_rn=1),0,salary)) as dept_total_salary --去掉一个最高工资和一个最低工资后剩下的部门总工资
    ,sum(if((salary=min_salary and min_salary_rn=1) or (salary=max_salary and max_salary_rn=1),0,1)) as dept_employee_cnt  --去掉一个最高分和一个最低分后的剩余人数
    from
    (
        select
        user_id
        ,dept
        ,salary
        ,min(salary) over(partition by dept) as min_salary --部门的最高工资
        ,max(salary) over(partition by dept) as max_salary --部门的最低工资
        ,row_number() over(partition by dept order by salary desc) as max_salary_rn  --为了使用编号去掉其中一个最高分
        ,row_number() over(partition by dept order by salary) as min_salary_rn  --为了使用编号去掉其中一个最低分
        from tmp 
    )t1
    group by dept
)t1;

不可以去掉
min_salary_rn=1
和
max_salary_rn=1
。这两个条件是必需的,具体原因如下:

如果只判断 salary=min_salary 或 salary=max_salary,那么会把所有与最低/最高工资相等的员工的工资都设为 0,而题目要求只去掉一个最低工资和一个最高工资。

用 min_salary_rn=1 和 max_salary_rn=1 可以确保只去掉排名第1的最低工资和最高工资,而不影响其他与最低/最高工资相等的员工

Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析（二）

1.访问次数最多的5个用户

2.近

3.获取1-180天注册活跃留存表

4.学生名次统计

5.最近连续3周连续活跃用户数

6.最近7天内连续3天活跃用户数

7.餐馆营业额变化增长

8.上升的温度

9. 找出恶意购买用户

10.奖金瓜分问题

11.同时最大在线人数问题

12.每年的在校人数

13.计算部门的平均工资

相关拓展

发表评论

“Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析（二）”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析（二）

1.访问次数最多的5个用户

2.近** **

3.获取1-180天注册活跃留存表

4.学生名次统计

5.最近连续3周连续活跃用户数

6.最近7天内连续3天活跃用户数

7.餐馆营业额变化增长

8.上升的温度

9. 找出恶意购买用户

10.奖金瓜分问题

11.同时最大在线人数问题

12.每年的在校人数

13.计算部门的平均工资

相关拓展

发表评论

“Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析（二）”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航

2.近