Date id output
11/26/2018 12:24 A 1
12/10/2018 23:34 A 0
12/11/2018 16:03 A 0
12/11/2018 16:34 A 0
12/12/2018 9:58 A 0
12/13/2018 9:27 A 1
12/6/2018 8:48 B 1
12/26/2018 8:49 B 1
我正在尝试创建一个新列,该列的值为1或0,逻辑。
- 对于ID的每个唯一值,如果日期的最大值和日期最小值的差为15天 - 输出为1 else 0
- 否则日期差为<15天,ID具有计数(ID)> 1-输出为0 else 1
- 每个唯一ID的最后一行 - 输出应始终为1
我正在尝试在SQL查询中实现逻辑,以使输出列为1或0基础逻辑。任何人都可以帮助我。
这应该处理您的条件:
select t.*,
( (seqnum = 1) or
(max_date > min_date + interval '15 day')
)::int as flag
from (select t.*,
row_number() over (partition by id order by date desc) as seqnum,
max(date) over (partition by id) as max_date,
min(date) over (partition by id) as min_date
from t
) t;
您的第二个条件在最后一个条件下是多余的。如果计数为1,则记录是最后一个记录。
此查询应该这样做:
with
x as (
select
id,
min(date) as mind,
max(date) as maxd,
count(*) as cnt
from my_table
group by id
),
select
t.date,
t.id,
case
when t.date = x.maxd then 1
when datediff('day', x.mind, x.maxd) > 15 then 1
when x.cnt <= 1 then 1
else 0 end as output
from my_table t
join x on x.id = t.id