使用聚合功能根据 MIN 时间戳筛选记录

Question

SELECT * FROM ABC_CUSTOMER_DETAILS abc_detail
INNER JOIN ABC_CUSTOMERS abc_cust
ON abc_detail.ID=abc_cust.CUSTOMER_ID
WHERE abc_detail.COUNTRY_CODE='KE'
AND CREATION_TIMESTAMP=(SELECT MIN (CREATION_TIMESTAMP)
FROM ABC_CUSTOMER_DETAILS abc_detail
INNER JOIN ABC_CUSTOMERS abc_cust
ON abc_detail.ID=abc_cust.CUSTOMER_ID
WHERE abc_detail.COUNTRY_CODE='KE');

上面的脚本查询连接记录从ABC_CUSTOMER_DETAILS到ABC_CUSTOMERS然后选择一个具有最早时间戳的记录。

无论如何，如果我能够在CREATION_TIMESTAMP条件下不重复相同的JOIN和WHERE条款？

Answer 1

有几种方法可以获取最早的记录，并避免必须键入相同的条件两次。

使用 FETCH FIRST ROWS(从 Oracle 12c 开始提供(

select * 
from abc_customer_details cd
join abc_customers c on c.id = cd.customer_id
where cd.country_code = 'KE'
order by creation_timestamp
fetch first row only;

使用 CTE(WITH 子句(

with cte as
(
select * 
from abc_customer_details cd
join abc_customers c on c.id = cd.customer_id
where cd.country_code = 'KE'
)
select *
from cte
where (creation_timestamp) = (select min(creation_timestamp) from cte);

使用窗口函数

select *
from
(
select cd.*, c.*, min(creation_timestamp) over () as min_creation_timestamp
from abc_customer_details cd
join abc_customers c on c.id = cd.customer_id
where cd.country_code = 'KE'
)
where creation_timestamp = min_creation_timestamp;

(顺便说一下，我更改了所有这些查询中的连接条件。你似乎极不可能加入abc_customer_details.id = abc_customers.customer_id。

Answer 2

您可以使用MIN()分析函数。

SELECT
*
FROM
(
SELECT
abc_detail.*,
abc_cust.*,
MIN(creation_timestamp) OVER(
PARTITION BY abc_detail.id
) AS min_timestamp
FROM
abc_customer_details abc_detail
INNER JOIN abc_customers abc_cust
ON abc_detail.id = abc_cust.customer_id
WHERE
abc_detail.country_code = 'KE'
)
WHERE
creation_timestamp = min_timestamp;