记一次Hive SQL的需求实现及优化

需求描述

根据用户的浏览记录表，编写一个SQL输出用户对某个产品的感兴趣程度。

浏览记录表格式

• 用户ID (a)：表示浏览产品的用户的唯一标识符。
• 产品ID (b)：表示被浏览产品的唯一标识符。
• 浏览日期 (v_date)：表示用户浏览产品的日期。

浏览记录表样例

用户ID (a)	产品ID (b)	浏览日期 (v_date)
1	101	2022-07-01
1	101	2022-07-01
1	101	2022-07-02
1	102	2022-07-02
2	101	2022-07-01
2	103	2022-07-03
3	104	2022-07-01
3	101	2022-07-02
4	102	2022-07-03
4	103	2022-07-04
5	104	2022-07-05
5	101	2022-07-05
5	101	2022-07-05

怎么做

只使用浏览表，可以对用户的浏览记录进行加权得分。 距离当天越近，浏览记录权重越高。

具体做法

• 计算每个用户（a）对每个产品（b）的浏览权重得分。
• 权重得分基于浏览日期（v_date），最近的浏览记录权重更高。
• 具体权重计算公式：1 / (datediff(current_date(), v_date) + 0.0001)，以避免除以零。

这里使用HIVE函数datediff和current_date结合计算距今的天数。

假设今天是2022-07-06，针对用户1，

• 计算 datediff：
  • 2022-07-01 到 2022-07-06：datediff = 5
  • 2022-07-02 到 2022-07-06：datediff = 4
• 计算权重得分：
  • 2022-07-01：weight = 1 / (5 + 0.0001) ≈ 0.199996
  • 2022-07-02：weight = 1 / (4 + 0.0001) ≈ 0.249994
• 汇总结果：
  • 用户1对产品101的总权重得分：0.199996 + 0.199996 + 0.249994 = 0.649986
  • 用户1对产品102的总权重得分：0.249994

SQL实现

select
    a,
    b,
    sum(1/(datediff(current_date(), v_date) + 0.0001)) as score
from
    table
GROUP BY
    a,
    b

这个SQL在实际跑的时候性能会稍差点，可以进一步优化。

优化

优化步骤：

1. 子查询： 对每一天的数据进行聚合，计算 count(*) / (datediff(current_date(), v_date) + 0.0001)

2. 外层查询： 对子查询结果按 (a, b) 进行聚合，计算 sum(count_by_day) 得到最终的 score。

优化后的SQL

select
    a,
    b,
    sum(count_by_day) as score
from
(
    select
        a,
        b,
        count(*) / (datediff(current_date(), v_date) + 0.0001) as count_by_day
    from
        table
    where
        xxx
    group by
        a,
        b,
        v_date
) t 
group by
    t.a,
    t.b

为什么优化后的SQL会变快

1. 减少了计算量： 在原始查询中，sum(1/(datediff(current_date(), v_date) + 0.0001)) 需要对每一行数据进行计算，而优化后的查询通过先对每一天的浏览记录进行聚合，减少了需要计算的行数。

具体来说，原始查询中每一行都需要执行一次 datediff 和除法操作，而优化后的查询首先在子查询中对每一天的数据进行聚合，减少了需要进行复杂计算的行数。

2. 更少的聚合操作： 原始查询中，GROUP BY a, b 对所有数据进行聚合，这可能涉及大量的数据行。而优化后的查询通过先在子查询中对 (a, b, v_date) 进行聚合，然后在外层查询中对 (a, b) 进行聚合，减少了需要处理的数据量。

3. 提前过滤和聚合： 优化后的查询在子查询中就已经对数据进行了初步的过滤和聚合，这样可以减少传递到外层查询的数据量，从而提高查询效率。