python - 使用 SQL 提取唯一值

Question

关闭。这个问题需要多问 focused 。目前不接受答案。

---

想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post .

已关闭 3 年前。

Improve this question

我是 SQL 新手，非常感谢您帮助我从 Hive 表中提取数据。该表包含两个相关列:host和url 。 url列有很多重复项和类似的 url 重定向到同一页面，格式如下:

https://www.cnn.com/2019/09/20/politics/
https://www.cnn.com/2019/09/20/politics
http://www.cnn.com/2019/09/20/politics/
http://www.cnn.com/2019/09/20/politics

主机格式示例:

https://www.cnn.com/
http://www.cnn.com/

我需要一个查询来提取唯一的网址，并优先选择 https版本超过http以及带有尾部斜杠的 url 覆盖不带尾部斜杠的 url(如果可用)。因此，对于上面的示例，结果应该是:

https://www.cnn.com/2019/09/20/politics/

一个简单的 bash 或 python 脚本对本地文件执行相同的操作也会非常有帮助。

Answer 1

Hive 的解决方案。使用 row_number() 删除重复项。查看protocol_key和path_key是如何计算的，它们在row_number()partition by子句中使用:

with your_data as (--use your table instead of this
select stack( 4,
'https://www.cnn.com/2019/09/20/politics/',
'https://www.cnn.com/2019/09/20/politics',
'http://www.cnn.com/2019/09/20/politics/',
'http://www.cnn.com/2019/09/20/politics') as url
) --your table

select url from
(
select s.url, --s.protocol, s.protocol_key, s.host, s.path, s.path_key, --columns for debugging
       row_number() over(partition by s.protocol_key, s.host, s.path_key order by s.protocol desc, s.path desc) rn --https and path with/ are preferred
from       
(--parse url
select t.url, s.protocol, s.host, s.path, regexp_replace(s.path,'/$','') as path_key, regexp_replace(s.protocol,'s$','') as protocol_key
  from your_data t
       lateral view parse_url_tuple(url, 'PROTOCOL','HOST', 'PATH')s as protocol, host, path
)s)s where rn = 1
;

结果:

https://www.cnn.com/2019/09/20/politics/