1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
| with punish_money AS (
select id, cid,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(regexp_replace(regexp_replace(regexp_replace(
translate(punish_moneny, '零〇一二三四五六七八九壹弍叁肆伍陆柒捌玖', '00123456789123456789'),
'(?<=[0-9])((千万)|(千亿))(?=[0-9])','000'),
'(?<=[0-9])((百万)|(百亿))(?=[0-9])','00'),
'(?<=[0-9])((十万)|(十亿))(?=[0-9])','0'),
'(?<=[0-9])[十|百|千|万|亿](?=[0-9])', ''),
'(?<![0-9])十(?![0-9])', '10'),
'(?<![0-9])十(?=[0-9])', '1'),
'(?<=[0-9])十(?![0-9])', '0')
as pm,
punish_moneny,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(regexp_replace(regexp_replace(regexp_replace(
translate(total_pm, '零〇一二三四五六七八九壹弍叁肆伍陆柒捌玖', '00123456789123456789'),
'(?<=[0-9])((千万)|(千亿))(?=[0-9])','000'),
'(?<=[0-9])((百万)|(百亿))(?=[0-9])','00'),
'(?<=[0-9])((十万)|(十亿))(?=[0-9])','0'),
'(?<=[0-9])[十|百|千|万|亿](?=[0-9])', ''),
'(?<![0-9])十(?![0-9])', '10'),
'(?<![0-9])十(?=[0-9])', '1'),
'(?<=[0-9])十(?![0-9])', '0')
as total_pm
from (
select cid, id,
regexp_replace(
regexp_extract(
punish_content,
'(((?<=((共计|合并)处).{0,100})([〇一二二三四五六七八九十百千万0-9\.,，]+)(?=元))|((?<=([处罚|罚款|罚])?)([〇一二二三四五六七八九十百千万0-9\.,，]+)(?=元))|((?<=处.{0,100})([〇一二二三四五六七八九十百千万0-9\.,，]+)(?=罚款)))', 1),
',|，', '')as punish_moneny,
regexp_extract(punish_content, '((?<=((共计|合并)处).{0,100})([〇一二二三四五六七八九十百千万0-9\.,，]+)(?=元))', 1) as total_pm, punish_content
from (
SELECT id, cid, translate(punish_content, '零壹弍贰叁肆伍陆柒捌玖拾佰仟萬', '〇一二二三四五六七八九十百千万') as punish_content
from dwd_creditrisk_xy_company_jyfx.company_jyfx_hbcf
where punish_content rlike '罚'
and dayno = '2023-05-13'
) t
) tt
where tt.punish_moneny !=''
) ,
cal_total_pm as(
SELECT id,cid, pm, punish_moneny, total_pm,
case
when pm rlike '千亿' then cast(money * 100000000000 as decimal(16,2))
when pm rlike '百亿' then cast(money * 10000000000 as decimal(16,2))
when pm rlike '十亿' then cast(money * 1000000000 as decimal(16,2))
when pm rlike '亿' then cast(money * 100000000 as decimal(16,2))
when pm rlike '千万' then cast(money * 10000000 as decimal(16,2))
when pm rlike '百万' then cast(money * 1000000 as decimal(16,2))
when pm rlike '十万' then cast(money * 100000 as decimal(16,2))
when pm rlike '万' then cast(money * 10000 as decimal(16,2))
when pm rlike '千' then cast(money * 1000 as decimal(16,2))
when pm rlike '百' then cast(money * 100 as decimal(16,2))
when pm rlike '十' then cast(money * 10 as decimal(16,2))
else cast(money as decimal(16,2))
end as money,
case
when total_pm rlike '千亿' then cast(total_money * 100000000000 as decimal(16,2))
when total_pm rlike '百亿' then cast(total_money * 10000000000 as decimal(16,2))
when total_pm rlike '十亿' then cast(total_money * 1000000000 as decimal(16,2))
when total_pm rlike '亿' then cast(total_money * 100000000 as decimal(16,2))
when total_pm rlike '千万' then cast(total_money * 10000000 as decimal(16,2))
when total_pm rlike '百万' then cast(total_money * 1000000 as decimal(16,2))
when total_pm rlike '十万' then cast(total_money * 100000 as decimal(16,2))
when total_pm rlike '万' then cast(total_money * 10000 as decimal(16,2))
when total_pm rlike '千' then cast(total_money * 1000 as decimal(16,2))
when total_pm rlike '百' then cast(total_money * 100 as decimal(16,2))
when total_pm rlike '十' then cast(total_money * 10 as decimal(16,2))
else cast(total_money as decimal(16,2))
end as total_money
from (
SELECT id,
cid,
pm,
punish_moneny,
total_pm,
cast(regexp_extract(pm, '([0-9\.]+)', 1) as decimal(16,4)) as money,
cast(regexp_extract(total_pm, '([0-9\.]+)', 1) as decimal(16,4)) as total_money
from punish_money
) t
)
SELECT id,cid, punish_moneny as pm, if(total_money is not null and total_money > money, total_money, money) as punish_amt
FROM cal_total_pm
|
wechat
alipay