MS SQL中如何计算指定日期单台机器各状态时长的占比

你原有代码的问题是窗口函数未指定分区维度，导致分母用了全量数据的总时长，而非单台机器单日的总时长，调整窗口函数的分区规则即可，修改后的SQL如下：

SELECT 
    machine, 
    start_date, 
    SUM(duration) AS status_duration,
    SUM(duration) * 100.0 / SUM(SUM(duration)) OVER (PARTITION BY machine, start_date) AS Percentage,
    st.status_code, 
    st.status_text
FROM er_table er
LEFT JOIN status_table st ON er.status_code = st.status_code
WHERE machine IN ('mach1','mach2','mach3')
GROUP BY machine, start_date, st.status_code, st.status_text
ORDER BY machine, start_date, status_text

如果需要格式化百分比的小数位数，可以在外层包裹ROUND函数，比如保留两位小数就写成：

ROUND(SUM(duration) * 100.0 / SUM(SUM(duration)) OVER (PARTITION BY machine, start_date), 2) AS Percentage

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