跳到主要内容

华为大数据面试SQL-基站信号覆盖范围统计

一、题目​

已知有两张表：

  • t1_base_station：基站信息表，记录基站ID、基站名称、经度、纬度、覆盖半径（单位km）
  • t1_user_location：用户位置信息表，记录用户ID、所在经度、纬度、上报时间

请统计每个基站覆盖范围内的用户数。如果一个用户处于多个基站的覆盖范围内，该用户应被计入所有覆盖它的基站。最后输出每个基站的名称、覆盖半径、覆盖用户数，按覆盖用户数降序排列。

距离计算使用简化的欧氏距离公式：distance(km) = sqrt((lng2 - lng1)^2 + (lat2 - lat1)^2) * 111。

样例数据

t1_base_station：

+----------+-----------+--------+--------+-----------------+
| site_id  | site_name |  lng   |  lat   | coverage_radius |
+----------+-----------+--------+--------+-----------------+
| 1        | 站点A     | 116.40 | 39.90  | 5.0             |
| 2        | 站点B     | 116.45 | 39.92  | 3.0             |
| 3        | 站点C     | 121.47 | 31.23  | 10.0            |
+----------+-----------+--------+--------+-----------------+

t1_user_location：

+---------+--------+--------+----------------------+
| user_id |  lng   |  lat   |     report_time      |
+---------+--------+--------+----------------------+
| u001    | 116.41 | 39.91  | 2024-06-01 08:00:00  |
| u002    | 116.46 | 39.93  | 2024-06-01 08:05:00  |
| u003    | 116.50 | 39.95  | 2024-06-01 08:10:00  |
| u004    | 121.48 | 31.24  | 2024-06-01 08:15:00  |
| u005    | 121.47 | 31.30  | 2024-06-01 08:20:00  |
| u006    | 116.42 | 39.89  | 2024-06-01 08:25:00  |
+---------+--------+--------+----------------------+

三、思路分析​

本题考察空间距离计算和笛卡尔积的应用。核心思路：

  • 笛卡尔积：将基站表和用户位置表进行无条件的CROSS JOIN，计算每个基站与每个用户的距离。在实际大表场景中，这样的笛卡尔积开销很大，但面试中考察的就是这个基本逻辑。

  • 距离过滤：计算距离后，用WHERE条件过滤出距离 ≤ coverage_radius的记录，保留该基站-用户对。

  • 聚合统计：按基站分组，COUNT DISTINCT user_id得到覆盖用户数。

如果用户位置表存在多条上报记录（同一用户多次上报），需要考虑去重逻辑：本题假设每个用户取最新的一条位置记录参与计算。

维度评分
题目难度⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️

四、逐步推导​

1.每个用户取最新上报位置​

为了确保每个用户只被统计一次，先用ROW_NUMBER按用户分组、按上报时间倒序，取每个用户最新的一条位置记录。

执行SQL

select
    user_id,
    lng,
    lat
from (
    select
        user_id,
        lng,
        lat,
        row_number() over (partition by user_id order by report_time desc) as rn
    from t1_user_location
) t
where rn = 1

执行结果

+---------+--------+--------+
| user_id |  lng   |  lat   |
+---------+--------+--------+
| u001    | 116.41 | 39.91  |
| u002    | 116.46 | 39.93  |
| u003    | 116.50 | 39.95  |
| u004    | 121.48 | 31.24  |
| u005    | 121.47 | 31.30  |
| u006    | 116.42 | 39.89  |
+---------+--------+--------+

2.计算每个基站与用户之间的距离，统计覆盖用户数​

将基站表与去重后的用户位置做CROSS JOIN，计算距离，过滤距离≤覆盖半径的记录，按基站分组统计覆盖用户数。

执行SQL

with user_latest_loc as (
    select
        user_id,
        lng,
        lat
    from (
        select
            user_id,
            lng,
            lat,
            row_number() over (partition by user_id order by report_time desc) as rn
        from t1_user_location
    ) t
    where rn = 1
),
distance_calc as (
    select
        b.site_id,
        b.site_name,
        b.coverage_radius,
        u.user_id,
        round(
            sqrt(power(b.lng - u.lng, 2) + power(b.lat - u.lat, 2)) * 111,
            2
        ) as distance_km
    from t1_base_station b
    cross join user_latest_loc u
)
select
    site_name,
    coverage_radius,
    count(distinct user_id) as covered_user_cnt
from distance_calc
where distance_km <= coverage_radius
group by site_id, site_name, coverage_radius
order by covered_user_cnt desc

执行结果

+-----------+-----------------+--------------------+
| site_name | coverage_radius | covered_user_cnt   |
+-----------+-----------------+--------------------+
| 站点A     | 5.0             | 3                  |
| 站点B     | 3.0             | 2                  |
| 站点C     | 10.0            | 2                  |
+-----------+-----------------+--------------------+

分析：站点A覆盖半径5km，覆盖了u001（距离1.57km）、u002（距离7.46km需要验证）、u006（距离2.47km）实际上我们来看详细距离。如果需要调试，可以在distance_calc CTE中直接查看所有基站-用户距离。

3.（可选）查看所有基站用户距离明细​

执行SQL

with user_latest_loc as (
    select user_id, lng, lat
    from (
        select user_id, lng, lat,
            row_number() over (partition by user_id order by report_time desc) as rn
        from t1_user_location
    ) t where rn = 1
)
select
    b.site_name,
    b.coverage_radius,
    u.user_id,
    round(sqrt(power(b.lng - u.lng, 2) + power(b.lat - u.lat, 2)) * 111, 2) as distance_km,
    case when sqrt(power(b.lng - u.lng, 2) + power(b.lat - u.lat, 2)) * 111 <= b.coverage_radius
         then '覆盖' else '未覆盖' end as coverage_status
from t1_base_station b
cross join user_latest_loc u
order by b.site_name, distance_km

执行结果

+-----------+-----------------+---------+-------------+----------------+
| site_name | coverage_radius | user_id | distance_km | coverage_status|
+-----------+-----------------+---------+-------------+----------------+
| 站点A     | 5.0             | u006    | 2.47        | 覆盖           |
| 站点A     | 5.0             | u001    | 1.57        | 覆盖           |
| 站点A     | 5.0             | u002    | 7.46        | 未覆盖         |
| 站点A     | 5.0             | u003    | 12.42       | 未覆盖         |
| 站点A     | 5.0             | u004    | 1102.14     | 未覆盖         |
| 站点A     | 5.0             | u005    | 1074.31     | 未覆盖         |
| 站点B     | 3.0             | u002    | 1.57        | 覆盖           |
| 站点B     | 3.0             | u003    | 6.37        | 未覆盖         |
| 站点B     | 3.0             | u001    | 3.51        | 未覆盖         |
| 站点B     | 3.0             | u006    | 4.69        | 未覆盖         |
| 站点B     | 3.0             | u004    | 1096.86    | 未覆盖         |
| 站点B     | 3.0             | u005    | 1068.80    | 未覆盖         |
| 站点C     | 10.0            | u004    | 1.57        | 覆盖           |
| 站点C     | 10.0            | u005    | 7.77        | 覆盖           |
| 站点C     | 10.0            | u003    | 1109.30    | 未覆盖         |
| 站点C     | 10.0            | u001    | 1108.55    | 未覆盖         |
| 站点C     | 10.0            | u002    | 1101.84    | 未覆盖         |
| 站点C     | 10.0            | u006    | 1104.39    | 未覆盖         |
+-----------+-----------------+---------+-------------+----------------+

五、常见坑点​

坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。

坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。

六、举一反三​

  1. 动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略

  2. 累计分布CDF：SUM(cnt) OVER (ORDER BY bucket) 算累计占比，画帕累托图

  3. 按时间维度对比：按天/周/月分区，观察分布的时序变化

七、知识点总结​

考点说明
ROW_NUMBER + 差值法连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
CASE WHEN 条件聚合灵活实现分段统计、条件计数、标签化处理
多表JOINLEFT JOIN保留主表所有数据，COALESCE处理NULL
COUNT DISTINCT 去重统计唯一用户/事件数，避免重复记录干扰聚合结果

八、建表语句和数据插入​

点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_base_station (
    site_id BIGINT COMMENT '基站ID',
    site_name STRING COMMENT '基站名称',
    lng DOUBLE COMMENT '经度',
    lat DOUBLE COMMENT '纬度',
    coverage_radius DOUBLE COMMENT '覆盖半径(km)'
);

CREATE TABLE IF NOT EXISTS t1_user_location (
    user_id STRING COMMENT '用户ID',
    lng DOUBLE COMMENT '经度',
    lat DOUBLE COMMENT '纬度',
    report_time STRING COMMENT '上报时间'
);

--数据插入
INSERT INTO t1_base_station(site_id, site_name, lng, lat, coverage_radius) VALUES
(1, '站点A', 116.40, 39.90, 5.0),
(2, '站点B', 116.45, 39.92, 3.0),
(3, '站点C', 121.47, 31.23, 10.0);

INSERT INTO t1_user_location(user_id, lng, lat, report_time) VALUES
('u001', 116.41, 39.91, '2024-06-01 08:00:00'),
('u002', 116.46, 39.93, '2024-06-01 08:05:00'),
('u003', 116.50, 39.95, '2024-06-01 08:10:00'),
('u004', 121.48, 31.24, '2024-06-01 08:15:00'),
('u005', 121.47, 31.30, '2024-06-01 08:20:00'),
('u006', 116.42, 39.89, '2024-06-01 08:25:00');
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
01. 合并日期重叠的活动02. 设备告警日志分析04. ODS层数据清洗去重