时间戳

首页 > 解决方案 > 在 AWS Athena sql 中进行有条件计数的相关查询的更好方法

sql - 在 AWS Athena sql 中进行有条件计数的相关查询的更好方法

问题描述

有两个表 A 和 B。表 A 与 B 具有一对多的关系。

  1. 我想从 A 中获取记录并从 B 中获取相应的一条记录（如果 B 有一条记录），
  2. 如果表 B 中有多个记录，则首先找到状态 ='Active' 的记录。

下面是在 oracle 中运行的查询，但我们希望在 AWS Athena 中运行相同的功能，但是 AWS athena sql 不支持相关查询。Athena 支持 ANSI Sql。


SELECT b.*
FROM A a ,B b
WHERE a.instruction_id = b.txn_report_instruction_id AND b.txn_report_instruction_id IN
(SELECT b2.txn_report_instruction_id FROM B b2
WHERE b2.txn_report_instruction_id=b.txn_report_instruction_id
GROUP BY b2.txn_report_instruction_id 
HAVING COUNT(b2.txn_report_instruction_id)=1
)
UNION
SELECT * FROM
(SELECT b.*
FROM A a , B b
WHERE a.instruction_id = b.txn_report_instruction_id AND b.txn_report_instruction_id IN
(SELECT b2.txn_report_instruction_id
FROM B b2
WHERE b2.txn_report_instruction_id=b.txn_report_instruction_id
AND b2.status ='ACTIVE'
GROUP BY b2.txn_report_instruction_id
HAVING COUNT(b2.txn_report_instruction_id)> 1
)
)

使用 group by 时，我们需要将所有字段放在 select 或聚合函数中，因此 group by 不是优选的。非常感谢您的帮助。

在此处输入图像描述[ 在此处输入图像描述] 2 输出结果表 在此处输入图像描述

标签： sqloracleamazon-web-servicesamazon-athena

解决方案

可以通过横向连接来连接最佳行。

select *
from a
outer apply
(
  select * 
  from b
  where b.txn_report_instruction_id = a.instruction_id
  order by case when b.status = 'ACTIVE' then 1 else 2 end
  fetch first row only
) bb;

另一种选择是窗口函数：

select *
from a
left join
(
  select 
    b.*, 
    row_number() over (partition by txn_report_instruction_id 
                       order by case when status = 'ACTIVE' then 1 else 2 end) as rn
  from b
) bb on bb.txn_report_instruction_id = a.instruction_id and bb.rn = 1;

我不知道亚马逊雅典娜的 SQL 覆盖率。然而，这都是标准的 SQL，除了OUTER APPLY我认为。如果我没记错的话，SQL 标准需要LEFT OUTER JOIN LATERAL (...) ON ...相反，为此您需要一个虚拟ON子句，例如ON 1 = 1. 因此，如果上述查询失败，您还有另一种选择:-)

推荐阅读