hive查询语言只返回与另一个表中的值不同的值

cl25kdpy  于 2021-06-03  发布在  Hadoop
关注(0)|答案(2)|浏览(356)

我正在尝试查找我的hosts表中的所有值,其中不包含与我的maildomains表中的值部分匹配的值。

  1. hosts
  2. +-------------------+-------+
  3. | host | score |
  4. +-------------------+-------+
  5. | www.gmail.com | 489 |
  6. | www.hotmail.com | 653 |
  7. | www.google.com | 411 |
  8. | w3.hotmail.ca | 223 |
  9. | stackexchange.com | 950 |
  10. +-------------------+-------+
  11. maildomains
  12. +---------------+
  13. | email |
  14. +---------------+
  15. | gmail |
  16. | hotmail |
  17. | outlook |
  18. | mail |
  19. +---------------+

具体地说,我希望对hosts.host不喜欢“%.maildomains.email%”中任何值的主机执行select*

  1. Desired output:
  2. +-------------------+-------+
  3. | host | score |
  4. +-------------------+-------+
  5. | www.google.com | 411 |
  6. | stackexchange.com | 950 |
  7. +-------------------+-------+

以下是我认为它应该如何逻辑地工作:

  1. SELECT h.*, m.email FROM (SELECT h.* FROM hosts WHERE score > 100 as h)
  2. h LEFT OUTER JOIN maildomains m ON (h.host LIKE CONCAT('%.',m.email,'%'))
  3. WHERE m.email IS NULL

这将导致错误10017:在联接“%”中同时遇到左别名和右别名
我还成功地将类似的查询作为交叉连接运行而不会出错,但它会产生错误的结果:

  1. SELECT h.*, m.email FROM (SELECT h.* FROM hosts WHERE score > 100 as h)
  2. h CROSS JOIN maildomains m
  3. WHERE h.host NOT LIKE CONCAT('%.',m.email,'%')
  4. +-------------------+---------+---------+
  5. | p.host | p.score | m.email |
  6. +-------------------+---------+---------+
  7. | www.gmail.com | 489 | hotmail |
  8. | www.gmail.com | 489 | outlook |
  9. | www.gmail.com | 489 | mail |
  10. | www.hotmail.com | 653 | gmail |
  11. | www.hotmail.com | 653 | outlook |
  12. | www.hotmail.com | 653 | mail |
  13. | www.google.com | 411 | gmail |
  14. | www.google.com | 411 | hotmail |
  15. | www.google.com | 411 | outlook |
  16. | www.google.com | 411 | mail |
  17. | w3.hotmail.ca | 223 | gmail |
  18. | w3.hotmail.ca | 223 | outlook |
  19. | w3.hotmail.ca | 223 | mail |
  20. | stackexchange.com | 950 | gmail |
  21. | stackexchange.com | 950 | hotmail |
  22. | stackexchange.com | 950 | outlook |
  23. | stackexchange.com | 950 | mail |
  24. +-------------------+---------+---------+

我感谢所有的指导。

jtjikinw

jtjikinw1#

如果您的配置单元版本是0.13或更新版本,则可以在 WHERE 子句从 hosts table。以下是一种更通用的方法,它不需要您枚举在数据中可能找到的所有顶级域:

  1. SELECT host, score
  2. FROM hosts
  3. WHERE
  4. regexp_extract(hosts.host, "(?:.*?\\.)?([^.]+)\\.[^.]+", 1) NOT IN
  5. (SELECT email FROM maildomains);

这种方法将主机域的一部分隔离在tld之前 regexp_extract 然后检查该域名是否出现在 maildomains table。

3ks5zfa0

3ks5zfa02#

你可以这样做:

  1. select host from hosts h left outer join maildomains m on (regexp_replace(regexp_replace(regexp_replace(regexp_replace(h.host,'www.',''),'.com',''),'.ca',''),'w3.','') = m.email) where email is NULL;

相关问题