题解 | 统计所有课程参加培训人次

with cultivate_tb as

(

select 101 as info_id,1 as staff_id,'course1,course2' as course

union all

select 102,2,'course2'

union all

select 103,3,'course1,course3'

union all

select 104,4,'course1,course2,course3'

union all

select 105 ,5 ,'course3'

union all

select 106 ,6 ,NULL

union all

select 107 ,7 ,'course1,course2'

)

select

count(*) staff_nums

from (

select

info_id,

staff_id,

course

from

cultivate_tb

lateral view explode(split(course,' ')) t1 as cultivate_tb

)t2