如何从多个列中查找不同值的平均值

ryevplcw 于 2021-06-25 发布在 Hive

关注(0)|答案(1)|浏览(312)

下面是200000行的输入数据。

我用下面的命令来求平均值，期望o/p为：m 50%f 50%

select avg(sum(case when col1='M' then 1 end)+
       sum(case when col2='M' then 1 end)+
       sum(case when col3='M' then 1 end)+
       sum(case when col4='M' then 1 end)+
       sum(case when col5='M' then 1 end)) as M,

   avg(sum(case when col1='F' then 1 end)+
       sum(case when col2='F' then 1 end)+
       sum(case when col3='F' then 1 end)+
       sum(case when col4='F' then 1 end)+
       sum(case when col5='F' then 1 end)) as F
       from household;

但显示了一个错误：

Hive

来源：https://stackoverflow.com/questions/58830934/how-to-find-the-average-of-distinct-values-from-multiple-columns

1条答案

按热度按时间

i34xakig1#

在配置单元中尝试此查询。那就行了。

SELECT 
    y.M1/(y.M1 + y.F1) * 100 AS M,
    y.F1/(y.M1 + y.F1) * 100 AS F
FROM (
    SELECT 
        (x.SumMCol1 + x.SumMCol2 + x.SumMCol3 + x.SumMCol4 + x.SumMCol5) AS M1,
        (x.SumFCol1 + x.SumFCol2 + x.SumFCol3 + x.SumFCol4 + x.SumFCol5) AS F1
    FROM (
        SELECT 
            SUM(IF(col1 = 'M', 1, 0)) AS SumMCol1,
            SUM(IF(col2 = 'M', 1, 0)) AS SumMCol2,
            SUM(IF(col3 = 'M', 1, 0)) AS SumMCol3,
            SUM(IF(col4 = 'M', 1, 0)) AS SumMCol4,
            SUM(IF(col5 = 'M', 1, 0)) AS SumMCol5,
            SUM(IF(col1 = 'F', 1, 0)) AS SumFCol1,
            SUM(IF(col2 = 'F', 1, 0)) AS SumFCol2,
            SUM(IF(col3 = 'F', 1, 0)) AS SumFCol3,
            SUM(IF(col4 = 'F', 1, 0)) AS SumFCol4,
            SUM(IF(col5 = 'F', 1, 0)) AS SumFCol5,
            COUNT(*) AS TotalRows
        FROM 
            household
    ) x
) y;

下面是要试用的sql fiddle链接：http://sqlfiddle.com/#!9/e9cf85/2型

赞(0）回复(0）举报 2021-06-26

我来回答

如何从多个列中查找不同值的平均值

1条答案

相关问题

热门标签

最新问答