记一次oracle 连续记录合并处理 (LAG,LEAD,row_number())
2022/6/6 2:21:07
本文主要是介绍记一次oracle 连续记录合并处理 (LAG,LEAD,row_number()),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
需求
下图中的数据,要求 取出 seq 1,2 ;合并3~6,取最早生效时间,和 最晚失效时间 ;合并7~10,取最早生效时间,和 最晚失效时间。
以上需求理解为: 连续的cust_id 需要合并
测试数据
create table TMP_MIG_ACCOUNT ( acct_id NUMBER(18) not null, seq_nbr NUMBER(5) not null, cust_id NUMBER(16), eff_date DATE not null, exp_date DATE not null ); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 1, 700752288, to_date('01-01-2000', 'dd-mm-yyyy'), to_date('07-03-2013 17:39:55', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 2, 701059792, to_date('07-03-2013 17:39:56', 'dd-mm-yyyy hh24:mi:ss'), to_date('07-03-2013 17:40:13', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 3, 701059793, to_date('07-03-2013 17:40:14', 'dd-mm-yyyy hh24:mi:ss'), to_date('09-03-2013 13:02:04', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 4, 701059793, to_date('09-03-2013 13:02:05', 'dd-mm-yyyy hh24:mi:ss'), to_date('09-03-2013 13:35:01', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 5, 701059793, to_date('09-03-2013 13:35:02', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-08-2014 12:14:22', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 6, 701059793, to_date('02-08-2014 12:14:23', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-12-2014 15:05:08', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 7, 700752288, to_date('15-12-2014 15:05:09', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-12-2014 15:06:08', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 8, 700752288, to_date('15-12-2014 15:06:09', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-2019 21:59:23', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 9, 700752288, to_date('01-01-2019 21:59:24', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-02-2019 19:04:08', 'dd-mm-yyyy hh24:mi:ss')); insert into TMP_MIG_ACCOUNT (ACCT_ID, SEQ_NBR, CUST_ID, EFF_DATE, EXP_DATE) values (90174285, 10, 700752288, to_date('01-02-2019 19:04:09', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-01-9999', 'dd-mm-yyyy'));
处理过程
构建level 1 临时表
DROP TABLEMIG_ACCOUNT_2; CREATE TABLE MIG_ACCOUNT_2 AS SELECTA.ACCT_ID, A.SEQ_NBR, A.CUST_ID, A.EFF_DATE, A.EXP_DATE, LAG(A.CUST_ID)OVER(PARTITIONBYA.ACCT_IDORDERBYA.SEQ_NBR)PREV_CUST_ID, LEAD(A.CUST_ID)OVER(PARTITIONBYA.ACCT_IDORDERBYA.SEQ_NBR)NEXT_CUST_ID FROM TMP_MIG_ACCOUNT A ORDERBYA.ACCT_ID,A.SEQ_NBRASC; SELECT*FROM MIG_ACCOUNT_2 ;
构建level2 临时表
DROP TABLETMP_MIG_ACCOUNT_1; CREATE TABLE TMP_MIG_ACCOUNT_1 AS SELECTA.*,row_number()OVER(PARTITIONBYa.acct_idORDERBYa.seq_nbrASC)RN FROMMIG_ACCOUNT_2 A WHEREA.CUST_ID!=NVL(A.PREV_CUST_ID,0); DROP TABLETMP_MIG_ACCOUNT_2; CREATE TABLE TMP_MIG_ACCOUNT_2 AS SELECT A.*,row_number()OVER(PARTITIONBYa.acct_idORDERBYa.seq_nbrASC)RN FROM MIG_ACCOUNT_2 A WHEREA.CUST_ID!=NVL(A.NEXT_CUST_ID,0); SELECT*FROMTMP_MIG_ACCOUNT_1; SELECT*FROMTMP_MIG_ACCOUNT_2;
组装数据
SELECTT1.ACCT_ID, T1.SEQ_NBR, T1.CUST_ID, T1.EFF_DATE, T1.EXP_DATE, T1.PREV_CUST_ID, T1.NEXT_CUST_ID, T1.RN, T2.EXP_DATE LAST_EXP_DATE FROMTMP_MIG_ACCOUNT_1 T1,TMP_MIG_ACCOUNT_2 T2 WHERET1.ACCT_ID=T2.ACCT_ID ANDT1.CUST_ID=T2.CUST_ID ANDT1.RN=T2.RN;
结果集为
![](/upload/202206/06/202206060221072804.png)
这篇关于记一次oracle 连续记录合并处理 (LAG,LEAD,row_number())的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-23AI大模型企业应用实战(14)-langchain的Embedding
- 2024-06-23AI大模型企业应用实战(15)-langchain核心组件
- 2024-06-23AI大模型企业应用实战(16)-langchain核心组件
- 2024-06-23AI 大模型企业应用实战(06)-初识LangChain
- 2024-06-19EntBot.ai: AI Website Chatbot for Product Guides and Development Doc
- 2024-06-17zero-shot-learning-definition-examples-comparison
- 2024-06-06Package Easy(基于 NSIS 的打包exe安装包工具)使用方法-icode9专业技术文章分享
- 2024-06-06基于 casdoor 的 ELK 开源登录认证解决方案: elk-auth-casdoor-icode9专业技术文章分享
- 2024-05-29Elasticsearch慢查询日志配置
- 2024-05-29揭秘华为如此多成功项目的产品关键——Charter模板