5月1日向ods_order_info插入3条数据：
CREATE TABLE ods_order_info(
`dt` string,
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额'
)
PRIMARY KEY(`dt`, `id`)
PARTITION BY (`dt`)
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1"
);
insert into ods_order_info
select
'2025-05-01' as dt
,9527 as id
,2000 as total_amount
;
insert into ods_order_info
select
'2025-05-01' as dt
,9528 as id
,3000 as total_amount
;
insert into ods_order_info
select
'2025-05-01' as dt
,9529 as id
,4000 as total_amount
;查询结果：
MySQL [tmp]> select * from ods_order_info;
+------------+------+--------------+
| dt | id | total_amount |
+------------+------+--------------+
| 2025-05-01 | 9529 | 4000.00 |
| 2025-05-01 | 9528 | 3000.00 |
| 2025-05-01 | 9527 | 2000.00 |
+------------+------+--------------+
3 rows in set (0.01 sec)
制作拉链表dwd_order_info_his：
drop table if exists dwd_order_info_his;
create table dwd_order_info_his(
`end_date` string COMMENT '有效结束日期',
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`start_date` string COMMENT '有效开始日期',
`record_status` string COMMENT '是否有效'
)
PRIMARY KEY(`end_date`, `id`)
PARTITION BY (`end_date`)
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1"
);初始化拉链表：
insert overwrite dwd_order_info_his
select
'9999-99-99',
id,
total_amount,
'2025-05-01',
'active'
from ods_order_info di
where di.dt='2025-05-01';查询结果：
MySQL [tmp]> select * from dwd_order_info_his;
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9527 | 2000.00 | 2025-05-01 | active |
| 9999-99-99 | 9529 | 4000.00 | 2025-05-01 | active |
| 9999-99-99 | 9528 | 3000.00 | 2025-05-01 | active |
+------------+------+--------------+------------+---------------+
3 rows in set (0.01 sec)
创建临时表用于导数据：
drop table if exists dwd_order_info_his_tmp;
create table dwd_order_info_his_tmp(
`end_date` string COMMENT '有效结束日期',
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`start_date` string COMMENT '有效开始日期'
)
PRIMARY KEY(`end_date`, `id`)
PARTITION BY (`end_date`)
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1"
);5月2日ODS表发生改变：
insert into ods_order_info
select
'2025-05-02' as dt
,9527 as id
,2222 as total_amount
;
insert into ods_order_info
select
'2025-05-02' as dt
,9540 as id
,7000 as total_amount
;导入数据：
insert overwrite dwd_order_info_his_tmp
select * from
(
select
'9999-99-99' end_date,
id,
total_amount,
'2025-05-02' start_date
from ods_order_info where dt='2025-05-02'
union all
select
if(oi.id is null, oh.end_date, date_add(oi.dt, -1)) end_date,
oh.id,
oh.total_amount,
oh.start_date
from dwd_order_info_his oh left join
(
select
*
from ods_order_info
where dt='2025-05-02'
) oi
on oh.id=oi.id and oh.end_date='9999-99-99'
)his
order by his.id, start_date;
insert overwrite dwd_order_info_his
select
end_date,
id,
total_amount,
start_date,
case when end_date = '9999-99-99' then 'active' else 'expire' end as record_status
from dwd_order_info_his_tmp;查询结果：
MySQL [tmp]> select * from dwd_order_info_his where start_date = '2025-05-01' and record_status = 'active';
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9529 | 4000.00 | 2025-05-01 | active |
| 9999-99-99 | 9528 | 3000.00 | 2025-05-01 | active |
+------------+------+--------------+------------+---------------+
2 rows in set (0.01 sec)
MySQL [tmp]> select * from dwd_order_info_his where start_date = '2025-05-02' and record_status = 'active';
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9527 | 2222.00 | 2025-05-02 | active |
| 9999-99-99 | 9540 | 7000.00 | 2025-05-02 | active |
+------------+------+--------------+------------+---------------+
2 rows in set (0.01 sec)
5月3日ODS层数据再次改变：
insert into ods_order_info
select
'2025-05-03' as dt
,9528 as id
,3333 as total_amount
;
insert into ods_order_info
select
'2025-05-03' as dt
,9541 as id
,8000 as total_amount
;导入数据：
insert overwrite dwd_order_info_his_tmp
select * from
(
select
'9999-99-99' end_date,
id,
total_amount,
'2025-05-03' start_date
from ods_order_info where dt='2025-05-03'
union all
select
if(oi.id is null, oh.end_date, date_add(oi.dt, -1)) end_date,
oh.id,
oh.total_amount,
oh.start_date
from dwd_order_info_his oh left join
(
select
*
from ods_order_info
where dt='2025-05-03'
) oi
on oh.id=oi.id and oh.end_date='9999-99-99'
)his
order by his.id, start_date;查询数据：
MySQL [tmp]> select * from dwd_order_info_his;
+---------------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+---------------------+------+--------------+------------+---------------+
| 9999-99-99 | 9529 | 4000.00 | 2025-05-01 | active |
| 9999-99-99 | 9541 | 8000.00 | 2025-05-03 | active |
| 2025-05-01 00:00:00 | 9527 | 2000.00 | 2025-05-01 | expire |
| 9999-99-99 | 9528 | 3333.00 | 2025-05-03 | active |
| 9999-99-99 | 9540 | 7000.00 | 2025-05-02 | active |
| 9999-99-99 | 9527 | 2222.00 | 2025-05-02 | active |
| 2025-05-02 00:00:00 | 9528 | 3000.00 | 2025-05-01 | expire |
+---------------------+------+--------------+------------+---------------+
7 rows in set (0.01 sec)
MySQL [tmp]> select * from dwd_order_info_his where start_date = '2025-05-01' and record_status = 'active';
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9529 | 4000.00 | 2025-05-01 | active |
+------------+------+--------------+------------+---------------+
1 row in set (0.03 sec)
MySQL [tmp]> select * from dwd_order_info_his where start_date = '2025-05-02' and record_status = 'active';
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9540 | 7000.00 | 2025-05-02 | active |
| 9999-99-99 | 9527 | 2222.00 | 2025-05-02 | active |
+------------+------+--------------+------------+---------------+
2 rows in set (0.01 sec)
MySQL [tmp]> select * from dwd_order_info_his where start_date = '2025-05-03' and record_status = 'active';
+------------+------+--------------+------------+---------------+
| end_date | id | total_amount | start_date | record_status |
+------------+------+--------------+------------+---------------+
| 9999-99-99 | 9541 | 8000.00 | 2025-05-03 | active |
| 9999-99-99 | 9528 | 3333.00 | 2025-05-03 | active |
+------------+------+--------------+------------+---------------+
2 rows in set (0.01 sec)