SQL实现设备各位置停留时长的小时级占比计算需求

需求说明 #

现有业务表（假设表名为device_location）包含数千台设备的位置停留数据，需按设备维度计算其在Work、Home、Other三类位置的停留小时占比，输出格式要求将三类占比转为列展示。

解决方案代码 #

SELECT
    device AS deviceid,
    ROUND(
        SUM(CASE WHEN gridtype = 'Home' THEN total_hour_count ELSE 0 END) * 100.0 
        / SUM(total_hour_count) OVER (PARTITION BY device),
        0
    ) AS home_percent,
    ROUND(
        SUM(CASE WHEN gridtype = 'Work' THEN total_hour_count ELSE 0 END) * 100.0 
        / SUM(total_hour_count) OVER (PARTITION BY device),
        0
    ) AS work_percent,
    ROUND(
        SUM(CASE WHEN gridtype = 'Other' THEN total_hour_count ELSE 0 END) * 100.0 
        / SUM(total_hour_count) OVER (PARTITION BY device),
        0
    ) AS other_percent
FROM device_location
GROUP BY device;

代码解释 #

• 计算单设备总停留时长：SUM(total_hour_count) OVER (PARTITION BY device) 按设备分组，计算每台设备的累计总停留小时数，作为占比计算的分母。
• 统计各类型停留时长：通过CASE WHEN筛选对应gridtype的停留小时数，求和后得到该设备在对应位置的总停留时长。
• 占比计算与格式化：将各类型时长除以总时长后乘以100得到百分比，用ROUND(..., 0)将结果取整为整数，匹配示例输出格式。
• 分组输出：按device分组，确保每台设备仅输出一行结果。

特殊情况适配 #

如果某设备缺少某类位置数据（如示例中无Work类记录），CASE WHEN会返回0，计算后对应占比为0，符合需求逻辑。

内容的提问来源于stack exchange，提问作者Hello World