如何從歷史表中跟蹤有效和有效到

我有一個表用戶，我想跟蹤每個組態檔用戶作業的有效起始日期和有效截止日期，以及現有組態檔的最后一個有效日期。

select id,email,ROLE,PROFILE,
    min_last_modified_date as valid_from, lead(min_last_modified_date) over (partition by 
    id order by min_last_modified_date) as valid_to 
    from
    (
      select id,email,ROLE,PROFILE,
      min(last_modified_date) as min_last_modified_date,
      max(last_modified_date) as max_last_modified_date
      from users_table 
      group by 1,2,3,4 
    ) 

[樣品表]

[樣品表2]

[為示例表 1 獲得這樣的輸出]

[為示例表 2 獲得這樣的輸出]

[樣本表 1 的預期輸出]

[樣本表 2 的預期輸出]

uj5u.com熱心網友回復：

其中一種方法可以是將具有相同組態檔的并發行識別到相同的存盤桶（即組）中。

• 首先根據遇到的下一個組態檔和上一個組態檔區分每一行。

• 如果上一個或下一個組態檔為空（即表中的第一個或最后一個條目），則將其默認為當前組態檔。

• 然后你的視窗函式可以做正確的磁區。

  select
  distinct
  ID
  , email
  , role
  , profile
  , min(last_modified_date) over (partition by ID, email, role, nearest_role_ind order by last_modified_date) as valid_from
  , lead(last_modified_date,1,null) over (partition by ID, email, role, attach_role_ind order by last_modified_date) as valid_to
  from
  (
  select
  ID
  , email
  , role
  , profile
  , last_modified_date
  , sum(prev_profile) over (order by last_modified_date) assimilar_role_ind
  from
  (
  select
  ID
  , email
  , role
  , profile
  , last_modified_date
  ,
  當滯后（profile, 1, 0) over (order by last_modified_date) != profile
  then 1
  else 0
  end as prev_profile
  from users
  時的情況
  ；
  

您可以將上述查詢格式化為 CTE，以獲得清晰的代碼塊和最佳執行。

uj5u.com熱心網友回復：

這是根據@shubh_8175 的答案中列出的邏輯改編的查詢。我試圖編輯他們的答案，因為邏輯是合理的，只需要進行小的更改即可獲得正確的輸出，但佇列已滿

1. 檢測每個 id 隨時間推移的組態檔更改
2. id 內的組組態檔更改
3. 獲取這些組態檔組中的有效日期
4. 聚合以洗掉組態檔組中的更改

with cte as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,case when lag([profile]) over (order by [last_modified_date]) = [profile] then 0 else 1 end as [new_profile]
    from users_table 
),

profile_grouping as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,sum([new_profile]) over ( order by [last_modified_date]) as [profile_n]
    from cte 
),

valid_dates as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,[profile_n]
        ,min(last_modified_date) over (partition by [id], [profile_n] order by [last_modified_date]) as [valid_from]
        ,lead(last_modified_date) over (partition by [id] order by [profile_n]) as [valid_to]
    from profile_grouping
)

select
     [id]
    ,[email]
    ,[role]
    ,[profile]
    ,[profile_n]
    ,min([valid_from]) [valid_from]
    ,case when [profile_n] = max([profile_n]) over ( partition by [id] ) then null else max([valid_to]) end [valid_to]
from valid_dates
group by 
     [id]
    ,[email]
    ,[role]
    ,[profile]
    ,[profile_n]
order by [id], [valid_from]

http://sqlfiddle.com/#!18/44b08/1

uj5u.com熱心網友回復：

這適用于不同的角色以及正確的 last_modified_date 軌道

with cte as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,case when lag([profile]) over (partition by [id],[email],[role] order by [last_modified_date]) = [profile] then 0 else 1 end as [new_profile]
    from users_table 
),

profile_grouping as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,sum([new_profile]) over ( partition by [id] order by [last_modified_date]) as [profile_n]
    from cte 
),

valid_dates as (
    select
        [id]
        ,[email]
        ,[role]
        ,[profile]
        ,[last_modified_date]
        ,[profile_n]
        ,min(last_modified_date) over (partition by [id], [profile_n] order by [last_modified_date]) as [valid_from]
        ,lead(last_modified_date) over (partition by [id] order by [last_modified_date]) as [valid_to]
    from profile_grouping
)

select
     [id]
    ,[email]
    ,[role]
    ,[profile]
    ,[profile_n]
    ,min([valid_from]) [valid_from]
    ,case when [profile_n] = max([profile_n]) over ( partition by [id] ) then null else max([valid_to]) end [valid_to]
from valid_dates
group by 
     [id]
    ,[email]
    ,[role]
    ,[profile]
    ,[profile_n]
order by [id], [valid_from]

標籤：sql日期雪花云数据平台追踪历史

上一篇：計算日期滯后

下一篇：為什么我的日期不會根據我的時區而改變？