sql-在一个时间间隔内搜索分区的动态最小日期

bfrts1fy 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(400)

我目前正陷入这个sql问题中，试图使用sql生成“starting date”列。我在下表中得到了“id”、“class”和“date”列。
要生成'starting date'列，我需要获取'id'和'class'分区上的最小日期。此外，我还需要检查日期是否少于110天的最低日期。如果满足110天的条件，那么我可以使用最小日期来计算最后一列（“与开始日期的天数差”）。否则，我将需要使用该行的日期作为开始日期，后续行也将使用新的开始日期，直到不满足110天标准。我已经强调了根据110天标准使用的最短日期。
我从下面的案例陈述开始，但一直坚持如何完成它。

CASE
    WHEN Date <= date(min(Date) OVER (PARTITION BY ID, Class) + interval '110 day') THEN Date
    ELSE --This is where I am stuck
END as 'Starting date'

sql netezza

来源：https://stackoverflow.com/questions/62567791/sql-searching-for-a-dynamic-minimum-date-of-a-partition-over-a-time-interval

1条答案

按热度按时间

0s0u357o1#

我认为这是一个会话问题。
我看到的一个症结是2019-09-06到2019-05-20。这是109天，这将属于你的110天的标准。
如果希望基于最小日期设置静态110天间隔，则应该能够使用日期维度表来建立符合110天限制的最小日期的开始和停止。
我在下面为您准备了一个纯sql的解决方案。如果存在将这些会话连接在一起的事件，则可以对此进行另一次传递，并将这些会话菊花链连接在一起，以创建一个超过110天的会话。
希望这有帮助！

select
event_data.id,
event_data.class,
event_data.date_dt,
min(event_data_2.date_dt) as starting_dt,
event_data.date_dt - min(event_data_2.date_dt) as days_between

from
(
select
'1234' as id,
'XYZ' as class,
'2019-02-04'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-05-20'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-06'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-11'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-03-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-05-05'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-10-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
) event_data
inner join
(
select
'1234' as id,
'XYZ' as class,
'2019-02-04'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-05-20'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-06'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-11'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-03-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-05-05'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-10-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
) event_data_2 on (event_data.id = event_data_2.id and event_data.class = event_data_2.class and ((event_data.date_dt - event_data_2.date_dt) <= 110))
group by
event_data.id,
event_data.class,
event_data.date_dt

赞(0）回复(0）举报 2021-07-24

我来回答

sql-在一个时间间隔内搜索分区的动态最小日期

1条答案

相关问题

热门标签

最新问答