星座，生肖，年龄，几零后，性别
select
identifynumber,
case
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0101' and '0119' then '摩羯座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0120' and '0218' then '水瓶座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0219' and '0320' then '双鱼座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0321' and '0419' then '白羊座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0420' and '0520' then '金牛座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0521' and '0621' then '双子座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0622' and '0722' then '巨蟹座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0723' and '0822' then '狮子座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0823' and '0922' then '处女座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '0923' and '1023' then '天秤座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '1012' and '1122' then '天蝎座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '1123' and '1221' then '射手座'
when case
when a.id_type = '15' then SUBSTR(
a.IDENTIFYNUMBER,
9,
4
)
when a.id_type = '18' then SUBSTR(
a.IDENTIFYNUMBER,
11,
4
)
end between '1222' and '1231' then '摩羯座'
end as conste
from
bdpmart.L_IDENTIFY A
;
select
identifynumber,
case
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '0' then '生肖——猴'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '1' then '生肖——鸡'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '2' then '生肖——狗'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '3' then '生肖——猪'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '4' then '生肖——鼠'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '5' then '生肖——牛'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '6' then '生肖——虎'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '7' then '生肖——兔'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '8' then '生肖——龙'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '9' then '生肖——蛇'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '10' then '生肖——马'
when cast(
(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- age
) as decimal
) mod 12 = '11' then '生肖——羊'
end as Animal
from
bdpmart.GET_AGE
;
select
identifynumber,
case
when a.ID_TYPE = '15' then(
case
when cast(
substr(
a.IDENTIFYNUMBER,
7,
2
) as decimal(2)
)> cast(
substr(
to_char(
current_timestamp( 0 )
),
3,
2
) as decimal(2)
) then cast(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
) as decimal(4)
)- cast(
'19' || substr(
a.IDENTIFYNUMBER,
7,
2
) as decimal(4)
)
else cast(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
) as decimal(4)
)- cast(
'20' || substr(
a.IDENTIFYNUMBER,
7,
2
) as decimal(4)
)
end
)
when a.ID_TYPE = '18' then cast(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
) as decimal(4)
)- cast(
SUBSTR(
a.IDENTIFYNUMBER,
7,
4
) as decimal(4)
)
end as age
from
bdpmart.L_IDENTIFY A
;
select
identifynumber,
AGE,
-- 得到身份证，年龄，生命周期，几零后标签数据
case
when AGE between 1 and 6 then '童年'
when AGE between 7 and 17 then '少年'
when AGE between 18 and 40 then '青年'
when AGE between 41 and 65 then '中年'
when AGE >= 66 then '老年'
end as agetype,
-- 取生命周期
case
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '19'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '50' and '59' then '50后'
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '19'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '60' and '69' then '60后'
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '19'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '70' and '79' then '70后'
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '19'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '80' and '89' then '80后'
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '19'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '90' and '99' then '90后'
when substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
1,
2
)= '20'
and substr(
to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
),
3,
4
) between '00' and '09' then '00后'
when to_char(
substr(
to_char(
current_timestamp( 0 )
),
1,
4
)- AGE
)>= '2010' then '10后'
end as AGELABEL -- 取几零后
from
bdpmart.GET_AGE
;
select
identifynumber,
case
when a.id_type = 15
and substr(
a.IDENTIFYNUMBER,
15,
1
) mod 2 = 1 then '男'
when a.id_type = 15
and substr(
a.IDENTIFYNUMBER,
15,
1
) mod 2 = 0 then '女'
when a.id_type = 18
and substr(
a.IDENTIFYNUMBER,
17,
1
) mod 2 = 1 then '男'
when a.id_type = 18
and substr(
a.IDENTIFYNUMBER,
17,
1
) mod 2 = 0 then '女'
end as SEX
from
bdpmart.L_IDENTIFY A
;