如何根据时间戳列选择过去3个月内只登录过一次的ID ?

Question

我在Oracle SQL中有如下表:

ID   date
------------
11  | 2021-07-01 12:55:13.278
11  | 2021-06-01 12:55:13.278
22  | 2021-06-01 12:55:13.278
33  | 2021-05-01 12:55:13.278

并且我只需要选择从当前日期(2021-09-28)在过去3个月内仅登录一次的客户端。

因此我需要

ID   
----
11  

因为只有ID = 11的客户端在过去3个月内只登录了一次(2021-07-01 12:55:13.278)。

我想也许表格的样本不是很大，但是描述是清楚的。请注意，带数据的列的格式可能是带日期和时间的时间戳，可能需要特殊的选择?

Answer 1

我会过滤最近三个月的记录，然后再计数

select id
from test_data
where date_col > add_months(to_date('2021-09-28', 'yyyy-mm-dd'), -3)
group by id
having count(1) = 1

Answer 2

select ID from tbl_name
WHERE 
DATE > add_months( sysdate, -3 )
group by ID having count(*) = 1 

注意:像date这样的保留字不能用作列名

Answer 3

DATA列看起来像VARCHAR，直接比较

select t1.id from tab1 t1 
group by t1.id 
having count(case when t1.data > to_char(add_months(to_date('2021-09-28', 'yyyy-mm-dd'), -3), 'yyyy-mm-dd')
then 1 else null end) = 1

Answer 4

另一个选项

with x ( id , date_logon ) 
as 
(
select 11 , to_timestamp('2021-07-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 11 , to_timestamp('2021-06-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 22 , to_timestamp('2021-06-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 33 , to_timestamp('2021-05-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual
), 
z as 
(
select id , min(date_logon) min_val, max(date_logon) max_val
from x group by id 
) 
select * from z where add_months(sysdate,-3) between min_val and max_val;
ID MIN_VAL                                                                     MAX_VAL
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
11 01-JUN-21 12.55.13.278000000 PM                                             01-JUL-21 12.55.13.278000000 PM