Sql snowflake交叉连接+横向压平

发布于10月25日

我有一个半 struct 的列，我想在交叉连接后进行左侧连接.

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)

, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)

select *
from t
cross join cartesian
left join lateral flatten(input => t.col) as js
  on js.key::int = cartesian.a::int

在上面的例子中，我预计原始的交叉联接会将结果计数从1扩展到3；而左侧联接不应该减少返回的行数.

我得到的结果是出乎意料的:

COL	A	SEQ	KEY	PATH	INDEX	VALUE	THIS
{ "1": 1, "2": 2 }	1	1	1	['1']	NULL	1	{ "1": 1, "2": 2 }
{ "1": 1, "2": 2 }	2	2	2	['2']	NULL	2	{ "1": 1, "2": 2 }

是我疯了，还是left关键字在这里没有发挥应有的作用？

推荐答案

如果我对问题的理解正确的话，您的目标是:

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)
, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)
select *
from cartesian 
LEFT JOIN (SELECT * FROM t, TABLE(flatten(input => t.col))) as js
  ON js.key::int = cartesian.a::int;

输出:

在这里，JSON的展平是在内联视图中执行的，结果是使用LEFT JOIN to Tally表连接的.

Its a small difference, but in the above is t truly cross joined to cartesian?my intention was to have col populated in all rows above; by cross joining t to `cartesian个

它可以进行调整:

with t as (
    select parse_json('{"1": 1, "2": 2}') as col UNION ALL
     select parse_json('{"3": 3}') as col
)

, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)

select *
from cartesian 
CROSS JOIN t
LEFT JOIN (SELECT * FROM t, TABLE(flatten(input => t.col))) as js
  ON js.key::int = cartesian.a::int
 AND t.col = js.this
ORDER BY t.col, A;

输出:

Sql snowflake交叉连接+横向压平

推荐答案

Sql相关问答推荐

Snowflake SQL比较克隆x原始计数

SQL(PostgreSQL)从条件创建点表

基于前面行的值：当x&>2时重复1，当连续3行x=0时则重复0

转换表中的数据

SQL -滞后于上一个非重复值

按分类标准检索记录

使用generate_series()时，LEFT联接缺少日期/间隔

如何将insert语句重复n次使一个值递增？

Haystack针相交-在元素最多的Haystack中查找集合

从输出中删除 jsonb_build_object

YEAR 函数仍然不可SARGable 吗？

将最近的结束日期与开始日期相匹配

在 SQL Server 中查找重复项

Postgresql：在链接表中判断相关表中插入值的条件

SQL Server中使用min()和max()从选定的特定值id表中删除不必要的时间

如何使用Informix创建一个临时表，将数据从根表导入并使用筛选条件

根据行号将列转置为没有任何id或键列的行

在多个表上递归查找

如何在 PL/SQL 中区分返回的 XML 值？

如何根据另一列对行值进行分组？