无论如何，是否可以在Hive中查询查询一些指标并验证它们在加入两个表之后是否满足某些公式？

发布于 2025-02-13 19:53:56 字数 562 浏览 0 评论 0原文

我在Hive中有两张桌子：

A.Hive：

| rid | cid | a | b | c |

a.samples

| rid | cid | a | b | c |

这两个表具有相同的字段名称。大于0.00001（查询结果中输出true/false），并输出这三个字段中值差异小于0.00001的百分比。我目前在第一部分编写了代码，但它会引发错误，但我不知道如何输出百分比。我应该怎么做？

select s.rid, 
       s.cid
from a.samples s
join a.hive h
if(s.a - h.a < 0.00001, 'True', 'False') as result_a,
if(s.b - h.b < 0.00001, 'True', 'False') as result_b,
if(s.c - h.c < 0.00001, 'True', 'False') as result_c
on (s.rid = h.rid and s.cid = h.cid)

原文

I got two tables in Hive:

a.hive:

|rid|cid|a|b|c|

a.samples

|rid|cid|a|b|c|

The two tables have the same field names.Now I want to take out the parts with the same rid and cid of these two tables, and then verify whether the difference between the values in the three fields of a, b, and c is less than 0.00001 (output true/false in the query result), and output the percentage that the difference between the values is less than 0.00001 in these three fields. I currently wrote the code for the first part but it throws an error and I don't know how to output the percentage. How should I do?

select s.rid, 
       s.cid
from a.samples s
join a.hive h
if(s.a - h.a < 0.00001, 'True', 'False') as result_a,
if(s.b - h.b < 0.00001, 'True', 'False') as result_b,
if(s.c - h.c < 0.00001, 'True', 'False') as result_c
on (s.rid = h.rid and s.cid = h.cid)

分享到QQ

分享到微博

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

发布评论

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

独﹏钓一江月 2025-02-20 19:53:56

SELECT s.rid
     , s.cid
     , IF(s.a - h.a < 0.00001, 'True', 'False') AS result_a
     , s.a / h.a - 1                            AS percentage_a
     , IF(s.b - h.b < 0.00001, 'True', 'False') AS result_b
     , s.b / h.b - 1                            AS percentage_b
     , IF(s.c - h.c < 0.00001, 'True', 'False') AS result_c
     , s.c / h.c - 1                            AS percentage_c
FROM a.samples    s
INNER JOIN a.hive h ON s.rid = h.rid AND s.cid = h.cid

SELECT s.rid
     , s.cid
     , IF(s.a - h.a < 0.00001, 'True', 'False') AS result_a
     , s.a / h.a - 1                            AS percentage_a
     , IF(s.b - h.b < 0.00001, 'True', 'False') AS result_b
     , s.b / h.b - 1                            AS percentage_b
     , IF(s.c - h.c < 0.00001, 'True', 'False') AS result_c
     , s.c / h.c - 1                            AS percentage_c
FROM a.samples    s
INNER JOIN a.hive h ON s.rid = h.rid AND s.cid = h.cid

回复收藏 0 原文

~没有更多了~