Sql-拉链法_拉链表sql语句写法

目录

一、什么是拉链表

二、拉链表的使用场景

三、实现

一、什么是拉链表

拉链表：维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。（也可以叫---缓慢变化维，解释如下） 

拉链通常full join得出，计算复杂度更高，存储比全表要多。拉链除了终态还有中间态 和全表的单个分区比要更大 和整个全表比更小。

总的来说就是：对历史记录不做物理删除，而是用一个标识（起止时间 或者 有效标识），这种删记录的方式称之为：软删除。拉链表设计初衷就是节约空间，是时代的产物，那个时候存储成本太高。

自我感觉，费力不讨好，不如version方便以及便于维护

二、拉链表的使用场景

1.有一些表的数据量很大，比如一张用户表，大约1亿条记录，50个字段，这种表
2.表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
3.需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
4.表中的记录变化的比例和频率不是很大，比如，总共有1亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

e.g. 

（图片引用自 857-hub 2群 陆酒哥哥）

三、实现

实现1:

（图片引用自 857-hub 2群 陆酒哥哥）

实现2: 使用存储过程，对比插入的原信息是否存在，不存在插入，存在合并

create or replace procedure sp_tab_history 
as     
--先获取到源表的数据
cursor c_tab_init is
    select  t.empno,t.ename,t.job,t.udt
    from tab_init t;
  
  v_ct number;
  v_ct2 number;
begin
  for x in c_tab_init loop
      select count(*)
    into v_ct
    from tab_history t
    where t.empno = x.empno;    
    if v_ct != 0 then     
      --获取该数据在目标表（拉链表）中的有效的那一条信息是否跟源表中是一致的
      select count(*)
      into v_ct2
      from tab_history t1
      where t1.empno = x.empno 
      and t1.job = x.job and t1.ename = x.ename and t1.end_date = date'9999-12-31';     
      if v_ct2 = 0 then
         --先将目标表中该数据的原先的有效记录给失效掉
        update tab_history t
        set t.end_date = x.udt - 1
        where t.empno = x.empno 
        and t.end_date = date'9999-12-31';      
        --再将该数据插入到目标表
        insert into tab_history values(x.empno,x.ename,x.job,x.udt,date'9999-12-31');       
      else
        dbms_output.put_line('没有变动，无需');       
      end if ; 
    else --等同于 elsif v_ct = 0 then
      insert into tab_history
      values (x.empno,x.ename,x.job,x.udt,date'9999-12-31'); 
    end if;
  end loop;
  commit;
end;

四、名词解释

 lag() over() 与 lead() over() 

https://blog.csdn.net/sinat_26811377/article/details/107188400

      lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

        over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b 表示以 a 字段进行分组，再 以 b 字段进行排序，对数据进行查询。

　例如：lead(field, num, defaultvalue) field 需要查找的字段，num 往后查找的 num 行的数据，defaultvalue 没有符合条件的默认值

另外，注意Over这个函数，Over 聚合定义（支持 Batch\Streaming）：可以理解为是一种特殊的滑动窗口聚合函数。那这里我们拿 Over 聚合 与 窗口聚合 做一个对比，其之间的最大不同之处在于：窗口聚合：不在 group by 中的字段，不能直接在 select 中拿到;Over 聚合：能够保留原始字段.在生产环境中，Over 聚合的使用场景还是比较少的。在 Hive 中也有相同的聚合。