hive presto sql从身份证号提取年龄和性别

hive presto sql如何从身份证号提取年龄和性别
原理：采用substr把身份证18位的数字拆分得到想要的，并通过case when、cast as赋值为想要的结果

select 2020-cast(substr(id,7,4)as int))*1.0 as age
平均年龄avg(2020-cast(substr(id,7,4)as int))*1.0 as age) as avg_age

select distinct case when cast(substr(id,17,1)as int) in (1,3,5,7,9) then male end
男性比例：
count (select distinct case when cast(substr(id,17,1)as int) in (1,3,5,7,9) then id end)*1.0000/count (distinct id) as male_rate