使用Starrocks制作拉鏈表

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_infowhere dt='2025-05-02'
) oion 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_infowhere dt='2025-05-03'
) oion 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)

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/80833.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/80833.shtml
英文地址，請注明出處：http://en.pswp.cn/web/80833.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！