Presto将行对除以公共名称

Question

说我有个预告声明回来了

|Name|Type    |Count|
|A   | total  |   2 |
|A   | count  |   4 |
|B   | total  |   3 |
|B   | count  |   9 |

我如何实现这样的目标

|Name|Avg |
|A   |  2 |
|B   |  3 |

基本上

A.avg = A.count/A.total
B.avg = B.count/B.total

谢谢

PS:不是很精通SQL

Answer 1

你可以这样做，通过一个自我连接

select a.Name, 
Total/Count as Avg 
from (select Name, Count as total From tbl where Type = 'total')a
inner join (select Name, Count From tbl where Type = 'count') b on a.Name = b.Name

Answer 2

我只想简单地使用有条件的农业：

select name,
       ( max(case when type = 'count' then count end) /
         max(case when type = 'total' then count end)
       ) as average
from t
group by name;

实际上，如果我们假设total >= count，那么这就更简单了：

select name, min(count) / max(count) as average
from t
group by name;

如果你真的更喜欢join而不是group by

select t.name, tc.count / tt.count
from t tc join
     t tt
     on tc.name = tt.name and
        tc.type = 'count' and
        tt.type = 'total';