指定下标组合取数
select
sale_id
,str_to_map(regexp_replace(concat_ws(‘,’, collect_set(concat_ws(‘:’, type_name, value_list[idx]))),‘\"’,‘’)) as profile_json
from
(
select
sale_id
,split(substr(get_json_object(profile_json, ‘
.
s
e
c
C
a
t
e
g
o
r
y
T
e
m
p
l
a
t
e
.
t
y
p
e
N
a
m
e
′
)
,
2
,
l
e
n
g
t
h
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
p
r
o
f
i
l
e
j
s
o
n
,
′
.secCategoryTemplate.typeName'), 2, length(get_json_object(profile_json, '
.secCategoryTemplate.typeName′),2,length(getjsonobject(profilejson,′.secCategoryTemplate.typeName’)) - 2), ‘,’) as type_name_list
,split(substr(get_json_object(profile_json, ‘
.
s
e
c
C
a
t
e
g
o
r
y
T
e
m
p
l
a
t
e
.
v
a
l
u
e
′
)
,
2
,
l
e
n
g
t
h
(
g
e
t
j
s
o
n
o
b
j
e
c
t
(
p
r
o
f
i
l
e
j
s
o
n
,
′
.secCategoryTemplate.value'), 2, length(get_json_object(profile_json, '
.secCategoryTemplate.value′),2,length(getjsonobject(profilejson,′.secCategoryTemplate.value’)) - 2), ‘,’) as value_list
from tmp.tmp_001
where dt >= date_sub(‘
d
t
′
,
30
)
a
n
d
d
t
<
=
′
{dt}',30) and dt <= '
dt′,30)anddt<=′{dt}’
) tmp
lateral view posexplode(type_name_list) t1 as idx, type_name
group by sale_id
扫一扫
7911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
