如何用单条Hive SQL查询获取总数、条件计数及通过率？

原数据与期望结果 #

原数据表 #

期望结果表 #

其中pass_rate计算公式为：pass_rate = fail_count / total_count

你的尝试与问题 #

你尝试的SQL语句：

SELECT 
    COUNT(*) AS total_count, 
    COUNT(CASE WHEN LENGTH(id) = 3 AND (value IS NULL OR value <= 0) THEN 1 ELSE NULL END) AS fail_count 
FROM my_table

执行后得到结果：

问题原因 #

你在fail_count的判断条件里错误加入了LENGTH(id) = 3，而原数据中满足value <= 0的记录是id=0001（长度为4），这条记录被你的条件排除，导致fail_count为0，和期望结果不符。

正确的单条Hive SQL语句 #

方法一：使用SUM计算计数 #

SELECT 
    COUNT(*) AS total_count,
    SUM(CASE WHEN value IS NULL OR value <= 0 THEN 1 ELSE 0 END) AS fail_count,
    ROUND(SUM(CASE WHEN value IS NULL OR value <= 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_rate
FROM my_table;

方法二：使用COUNT计算计数 #

SELECT 
    COUNT(*) AS total_count,
    COUNT(CASE WHEN value IS NULL OR value <= 0 THEN 1 END) AS fail_count,
    ROUND(COUNT(CASE WHEN value IS NULL OR value <= 0 THEN 1 END) / COUNT(*), 1) AS pass_rate
FROM my_table;

说明 #

• fail_count统计value为空或小于等于0的记录数，对应原数据中的id=0001，结果为1。
• pass_rate通过fail_count/total_count计算，用ROUND函数保留1位小数得到0.5，若不需要保留小数可去掉ROUND函数。

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