在雪花中使用json数据作为源表执行目标表时
merge into cust tgt using (
select parse_json(s.$1):application_num as application num
from prd_json s qualify
row_number() over(partition application
order_by application desc)=1) src
on tgt.application =src.application
when not matched and op_type='I' then
insert(application) values (src.application );
qualize命令忽略所有存在的重复数据,只给出唯一的记录,但在放入联接时,与正常的select语句相比,它只显示更少的记录。例如:
select distinct application
from prd_json where op_type='I';
--有15000排在放置联接时,它显示目标中并没有匹配的记录。如果不匹配,则应插入所有15000行,但8500行仅插入,即使不是重复记录。是否有任何不使用";合格";我们可以插入记录吗。如果我忽略qualification,我会得到dml错误重复。如果有人知道,请引导我。
使用SELECT DISTINCT怎么样?
演示SQL不会编译。使用$1
意味着也很难猜测列的名称来了解ROW_NUMBER是如何工作的。所以很难确定这个问题。
但是使用以下SQL,您可以用DISTINCT 替换ROW_NUMBER
CREATE TABLE cust(application INT);
CREATE OR REPLACE table prd_json as
SELECT parse_json(column1) as application, column2 as op_type
FROM VALUES
('{"application_num":1,"other":1}', 'I'),
('{"application_num":1,"other":2}', 'I'),
('{"application_num":2,"other":3}', 'I'),
('{"application_num":1,"other":1}', 'U')
;
MERGE INTO cust AS tgt
USING (
SELECT DISTINCT
parse_json(s.$1):application_num::int as application,
s.op_type
FROM prd_json AS s
) AS src
ON tgt.application = src.application
WHEN NOT MATCHED AND src.op_type = 'I' THEN
INSERT(application) VALUES (src.application );
插入的行数 |
---|
2 |