sql—在窗口分区中仅查找具有非重复值的行

zsohkypk 于 2021-07-27 发布在 Java

关注(0)|答案(4)|浏览(200)

我想看看为什么 descriptions 对同一个人来说是不同的 permit id.这是table（我用的是雪花）：

create or replace table permits (permit varchar(255), description varchar(255));

// dupe permits, dupe descriptions, throw out
INSERT INTO permits VALUES ('1', 'abc'); 
INSERT INTO permits VALUES ('1', 'abc');

// dupe permits, unique descriptions, keep
INSERT INTO permits VALUES ('2', 'def1'); 
INSERT INTO permits VALUES ('2', 'def2');
INSERT INTO permits VALUES ('2', 'def3');

// dupe permits, unique descriptions, keep
INSERT INTO permits VALUES ('3', NULL);   
INSERT INTO permits VALUES ('3', 'ghi1');

// unique permit, throw out
INSERT INTO permits VALUES ('5', 'xyz');

我想要的是查询这个表，只得到具有重复的许可证ID但描述不同的行集。
我想要的输出是：

+---------+-------------+
| PERMIT  | DESCRIPTION |
+---------+-------------+
|       2 | def1        |
|       2 | def2        |
|       2 | def3        |
|       3 |             |
|       3 | ghi1        |
+---------+-------------+

我试过这个：

with with_dupe_counts as (
    select
        count(permit) over (partition by permit order by permit) as permit_dupecount,
        count(description) over (partition by permit order by permit) as description_dupecount,
        permit,
        description
    from permits
)
select *
from with_dupe_counts
where permit_dupecount > 1 
and description_dupecount > 1

这给了我许可证1和2，并计算了它们是否唯一的描述：

+------------------+-----------------------+--------+-------------+
| PERMIT_DUPECOUNT | DESCRIPTION_DUPECOUNT | PERMIT | DESCRIPTION |
+------------------+-----------------------+--------+-------------+
|                2 |                     2 |      1 | abc         |
|                2 |                     2 |      1 | abc         |
|                3 |                     3 |      2 | def1        |
|                3 |                     3 |      2 | def2        |
|                3 |                     3 |      2 | def3        |
+------------------+-----------------------+--------+-------------+

我认为可行的是

count(unique description) over (partition by permit order by permit) as description_dupecount

但我意识到有很多东西在窗口函数中不起作用。这个问题不一定是“如何让count（unique x）在窗口函数中工作”，因为我不知道这是否是解决这个问题的最佳方法。
一个简单的 group by 我不认为它会起作用，因为我想恢复原来的行。

sql window-functions count Database snowflake-cloud-data-platform

来源：https://stackoverflow.com/questions/63181132/finding-only-rows-with-non-duplicated-values-within-a-window-partition

4条答案

按热度按时间

juzqafwq1#

一种方法使用 min() 以及 max() 以及 count() :

select *
from (select p.*,
             min(description) over (partition by permit) as min_d,
             max(description) over (partition by permit) as max_d,
             count(description) over (partition by permit) as cnt_d,
             count(*) over (partition by permit) as cnt,
            count(permit) over (partition by permit order by permit) as permit_dupecount
      from permits
     )
where min_d <> max_d or cnt_d <> cnt;

赞(0）回复(0）举报 2021-07-27

oxiaedzo2#

我只想用 exists :

select p.*
from permits p
where exists (
    select 1 
    from permits p1 
    where p1.permit = p.permit and p1.description <> p.description
)

处理 null 值，我们可以使用标准的空安全相等运算符 IS DISTINCT FROM ，snowlake支持：

select p.*
from permits p
where exists (
    select 1 
    from permits p1 
    where 
        p1.permit = p.permit 
        and p1.description is distinct from p.description
)

赞(0）回复(0）举报 2021-07-27

sigwle7e3#

应该有用

SELECT DISTINCT p1.permit, p1.description
FROM permits p1
JOIN permits p2 ON p1.permit = p2.permit
WHERE p1.description != p2.description OR p1.description IS NULL AND p2.description IS NOT NULL

赞(0）回复(0）举报 2021-07-27

f2uvfpb94#

这是我的目标：

with x as (
    select permit, count(distinct description) cnt
    from permits p1 
    group by permit
    having cnt > 1
    )
select p.*
from x
join permits p
  on x.permit = p.permit;

赞(0）回复(0）举报 2021-07-27

我来回答

sql—在窗口分区中仅查找具有非重复值的行

4条答案

相关问题

热门标签

最新问答