如何过滤雪花内的一对行

发布于 2025-02-13 21:37:02 字数 1116 浏览 1 评论 0原文

我有一张桌子，就像：

ID	宠物	国家
45	狗	我们
72	狗	CA
15	猫	CA
36	猫	美国
21	蛇	fr
12	蛇	在
20	猪	中
14	猪	14猪
33	马	iq
90	马	ID

行配对/分组基于匹配宠物。我只想选择包括美国在内的对。基本上，每对国家的价值都将使美国和另一个国家/一个价值之一是美国。例如，结果表将是

ID	宠物	国家
45	狗	US
72	狗	CA
15	猫	CA
36	猫	我们
20	猪	我们
14	Pig	Rs

这可以通过某种分区来实现吗？我不确定如何在分组中示例选择语句...谢谢！

原文

I have a table A that is like:

ID	PET	COUNTRY
45	DOG	US
72	DOG	CA
15	CAT	CA
36	CAT	US
21	SNAKE	FR
12	SNAKE	IN
20	PIG	US
14	PIG	RS
33	HORSE	IQ
90	HORSE	ID

The rows are paired/grouped based on matching PET. I would like to only select the pairs that include the US in the country. Basically each pair for the country value would have the US and another country/one of the values has to be the US. So for example, the resulting table would be

ID	PET	COUNTRY
45	DOG	US
72	DOG	CA
15	CAT	CA
36	CAT	US
20	PIG	US
14	PIG	RS

Would this be achieved by a partition of some sort? I am not sure how to case the select statement within a grouping... Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

与往事干杯 2025-02-20 21:37:02

您可以根据PET加入，首先过滤出country = US。

with data_cte (ID,PET,COUNTRY) as (
select * from values 
(45,'DOG','US'),
(72,'DOG','CA'),
(15,'CAT','CA'),
(36,'CAT','US'),
(21,'SNAKE','FR'),
(12,'SNAKE','IN'),
(20,'PIG','US'),
(14,'PIG','RS'),
(33,'HORSE','IQ'),
(90,'HORSE','ID')
), cte_1 as(
select * from data_cte
where country = 'US')
select d.* from data_cte d join
cte_1 c
where d.pet = c.pet;

给出以下结果 -

You can just join based on PET, first filtering out for country = US.

with data_cte (ID,PET,COUNTRY) as (
select * from values 
(45,'DOG','US'),
(72,'DOG','CA'),
(15,'CAT','CA'),
(36,'CAT','US'),
(21,'SNAKE','FR'),
(12,'SNAKE','IN'),
(20,'PIG','US'),
(14,'PIG','RS'),
(33,'HORSE','IQ'),
(90,'HORSE','ID')
), cte_1 as(
select * from data_cte
where country = 'US')
select d.* from data_cte d join
cte_1 c
where d.pet = c.pet;

Gives following result -

回复收藏 0 原文

笑咖 2025-02-20 21:37:02

使用符合条件和条件聚合：

SELECT *
FROM tab
QUALIFY COUNT_IF(COUNTRY = 'US') OVER(PARTITION BY PET) > 0
   AND COUNT(*) OVER(PARTITION BY PET) > 1;

count_if（country ='us'）（pet分区）＆gt; 0 - 组中至少必须是我们的

计数（*）（pet）＆gt; 1 - 每行PET

样本数据：

CREATE OR REPLACE TABLE tab(ID INT,PET TEXT,COUNTRY TEXT)
AS
SELECT * FROM VALUES (45,'DOG','US'),    (72,'DOG','CA'),
(15,'CAT','CA'),     (36,'CAT','US'),    (21,'SNAKE','FR'),
(12,'SNAKE','IN'),   (20,'PIG','US'),    (14,'PIG','RS'),
(33,'HORSE','IQ'),   (90,'HORSE','ID');

输出：

Using QUALIFY and conditional aggregation:

SELECT *
FROM tab
QUALIFY COUNT_IF(COUNTRY = 'US') OVER(PARTITION BY PET) > 0
   AND COUNT(*) OVER(PARTITION BY PET) > 1;

COUNT_IF(COUNTRY = 'US') OVER(PARTITION BY PET) > 0 - at least one row in the group has to be US

COUNT(*) OVER(PARTITION BY PET) > 1 - more than one row per pet

Sample data:

CREATE OR REPLACE TABLE tab(ID INT,PET TEXT,COUNTRY TEXT)
AS
SELECT * FROM VALUES (45,'DOG','US'),    (72,'DOG','CA'),
(15,'CAT','CA'),     (36,'CAT','US'),    (21,'SNAKE','FR'),
(12,'SNAKE','IN'),   (20,'PIG','US'),    (14,'PIG','RS'),
(33,'HORSE','IQ'),   (90,'HORSE','ID');

Output: