查询语句
select
*
from
(
select
MD5(
concat(
byslxz,
'|',
u_b_ysz
)
) as rvid,
MD5(
concat(
ayslxz,
'|',
u_a_ysz
)
) as targetId,
concat(
ayslxz,
'-',
u_a_ysz,
byslxz,
'-',
u_b_ysz,
'-',
relationship
) as deduplicationID,
ayslxz as yslxz,
u_a_ysz as ysz,
relationship,
gxlxms,
first_time as firstTime,
last_time as lastTime,
cs,
kxd,
data_source as source,
zcjts,
ROW_NUMBER() OVER (
PARTITION BY first_relationship_desc,
second_relationship_desc
ORDER BY
kxd DESC,
last_time DESC
) as row_num
from
res001.relational
WHERE
byslxz = 'A030001'
and u_b_ysz in (
'781441316',
'w091688'
)
and first_relationship_desc in ('021')
and second_relationship_desc in (
'021-003',
'021-002',
'021-001'
)
and kxd >= 0.6999
) as c
where
c.row_num <= 3
UNION ALL
select
*
from
(
select
MD5(
concat(
byslxz,
'|',
u_b_ysz
)
) as rvid,
MD5(
concat(
ayslxz,
'|',
u_a_ysz
)
) as targetId,
concat(
ayslxz,
'-',
u_a_ysz,
byslxz,
'-',
u_b_ysz,
'-',
relationship
) as deduplicationID,
ayslxz as yslxz,
u_a_ysz as ysz,
relationship,
gxlxms,
first_time as firstTime,
last_time as lastTime,
cs,
kxd,
data_source as source,
zcjts,
ROW_NUMBER() OVER (
PARTITION BY first_relationship_desc,
second_relationship_desc
ORDER BY
kxd DESC,
last_time DESC
) as row_num
from
res001.relational
WHERE
byslxz = 'A031425'
and u_b_ysz in ('dazhun052366')
and first_relationship_desc in ('021')
and second_relationship_desc in (
'021-003',
'021-002',
'021-001'
)
and kxd >= 0.6999
) as c
where
c.row_num <= 3;
数据量比较大的时候查询报错