sql - HIVE 获取选择任何课程的男性和女性人数

Question

我有两张桌子，学生和培训。学生和培训表如下。

Student
ID      name    age     sex     salary
1213    lavanya 18      Female  8000
1208    reshma  19      Female  14000
1207    bhavya  20      Female  15000
1212    Arshad  28      Male    20000
1209    kranthi 22      Male    22000
1210    Satish  24      Male    25000
1211    Krishna 25      Male    26000
1203    khaleel 34      Male    30000
1204    prasant 30      Male    31000
1206    laxmi   25      Female  35000
1205    kiran   20      Male    40000
1201    gopal   45      Male    50000
1202    manisha 40      Female  51000

Training
1       1201    csharp
2       1205    c
3       1201    c
4       1202    java
5       1205    java
6       1203    shell
7       1204    hadoop
8       1201    hadoop

现在我想计算参加过任何课程的男性和女性。我尝试了以下查询-

hive> select s.sex, count(*) from student join training t on s.id=t.sid group by s.sex;

但是这个查询给出的输出是女性 2 男性 4 虽然预期结果应该是女性 1 男性 2 请注意这是一个样本和正在使用的数据的简短形式。

标签： sqldatabasehive

Answer 1

在这里，我编写了一个获取您数据的代码：-

SELECT 
    final.ct_sex as sex,count(*) as num
FROM 
    (SELECT tb.sex as ct_sex FROM newschema.mytable AS tb JOIN (SELECT tr.ID,GROUP_CONCAT(tr.skill) as skills FROM newschema.train AS tr GROUP BY tr.ID) AS tp ON tb.ID = tp.ID) as final
group by
    final.ct_sex