Chiều nay vô
tình vào diễn đàn Oracle Việt Nam xem chơi, tôi thấy một bạn có post bài như
này:
NHỜ GIÚP ĐỠ VIẾT CÂU
LỆNH THỰC HIỆN TRUY VẤN THEO YÊU CẦU SAU ĐÂY
recno______MA_____QUAN________NGAY THAY DOI
1__________AAA_____10__________15/03/2007
2__________AAA_____10__________20/05/2007
3__________AAA_____01__________17/01/2009
4__________AAA_____01__________25/05/2009
5__________AAA_____03__________03/01/2011
6__________AAA_____03__________09/12/2011
7__________AAA_____03__________19/05/2012
8__________AAA_____04__________20/12/2012
9__________AAA_____04__________21/12/2012
10_________AAA_____01__________02/02/2013
11_________AAA_____01__________30/06/2013
12_________AAA_____HM__________30/09/2013
13_________BBB_____11__________12/03/2011
14_________BBB_____11__________20/12/2012
15_________BBB_____12__________01/03/2013
16_________CCC_____GV__________03/01/2010
17_________CCC_____GV__________06/05/2011
18_________CCC_____TB__________01/12/2012
19_________CCC_____TB__________08/08/2013
Mô tả
- Cty có MST AAA bắt đầu kinh doanh ngày 15/03/2007 ở quận 10
(recno = 1)
- Đến 17/01/2009 chuyển đến kinh doanh tại quân 1 (recno = 3)
- Ngày 03/01/2011 chuyển về quận 3 (recno =
5)
- Sau đó chuyển về quận 4 và về lại quận 1 ngày 02/02/2013 (recno =
10)
- Đến 30/09/2013 về Hóc Môn (recno =
12)
- Các cty có MST = BBB; CCC không quan tâm
- Trong dữ liệu thật, có rất nhiều cty có lịch sử thay đổi địa bàn
kinh doanh như AAA
Yêu cầu
- Xác định thời gian cty AAA (và những cty khác tương tự) kinh
doanh tại quận
1
- Thời gian kinh doanh tại quận 1 được xác định như
sau:
+ từ 17/01/2009 đến 03/01/2011
+ từ 02/02/2013 đến 30/09/2013
Nhờ bạn nào viết giúp mình câu truy vấn để thực hiện yêu cầu trên.
Và có rất nhiều
thành viên trong diễn đàn đã tham gia giúp đỡ, đa số viết cho riêng
trường hợp công ty AAA như yêu cầu. Chỉ có một số ít bạn viết thử câu truy vấn
tổng quát cho tất cả các công ty nhưng hoặc không đúng, hoặc câu lệnh rất dài, khó hiểu và cũng chả đúng.
Thực tế yêu cầu
như này gặp trong rất nhiều các hệ thống nghiệp vụ, tôi nhớ đã từng làm ở đâu
đó trước đây rồi. Nghĩ không khó nên tôi bắt tay vào thử luôn. Thế là mất cả buổi
chiều và rồi cũng xong. Quả thực rất vui !!!
Đầu tiên, ta tạo
dữ liệu như yêu cầu:
drop table ccdr;
create table ccdr (
rec_no number,
comp_id varchar2(3), -- mã
công ty
district_id varchar2(2), -- mã quận
move_date date); -- ngày thay đổi
insert into ccdr values (1, 'AAA', '10', to_date('15/03/2007',
'dd/mm/yyyy'));
insert into ccdr values (2, 'AAA', '10', to_date('20/05/2007',
'dd/mm/yyyy'));
insert into ccdr values (3, 'AAA', '01', to_date('17/01/2009',
'dd/mm/yyyy'));
insert into ccdr values (4, 'AAA', '01', to_date('25/05/2009',
'dd/mm/yyyy'));
insert into ccdr values (5, 'AAA', '03', to_date('03/01/2011',
'dd/mm/yyyy'));
insert into ccdr values (6, 'AAA', '03', to_date('09/12/2011',
'dd/mm/yyyy'));
insert into ccdr values (7, 'AAA', '03', to_date('19/05/2012',
'dd/mm/yyyy'));
insert into ccdr values (8, 'AAA', '04', to_date('20/12/2012',
'dd/mm/yyyy'));
insert into ccdr values (9, 'AAA', '04', to_date('21/12/2012',
'dd/mm/yyyy'));
insert into ccdr values (10, 'AAA', '01',
to_date('02/02/2013', 'dd/mm/yyyy'));
insert into ccdr values (11, 'AAA', '01',
to_date('30/06/2013', 'dd/mm/yyyy'));
insert into ccdr values (12, 'AAA', 'HM', to_date('30/09/2013',
'dd/mm/yyyy'));
insert into ccdr values (13, 'BBB', '11',
to_date('12/03/2011', 'dd/mm/yyyy'));
insert into ccdr values (14, 'BBB', '11',
to_date('20/12/2012', 'dd/mm/yyyy'));
insert into ccdr values (15, 'BBB', '12',
to_date('01/03/2013', 'dd/mm/yyyy'));
insert into ccdr values (16, 'CCC', 'GV',
to_date('03/01/2010', 'dd/mm/yyyy'));
insert into ccdr values (17, 'CCC', 'GV',
to_date('06/05/2011', 'dd/mm/yyyy'));
insert into ccdr values (18, 'CCC', 'TB',
to_date('01/12/2012', 'dd/mm/yyyy'));
insert into ccdr values (19, 'CCC', 'TB',
to_date('08/08/2013', 'dd/mm/yyyy'));
commit;
|
Sau đây, để dễ
hình dung, tôi sẽ giải thích từng bước trong câu lệnh của mình.
Trước hết, tôi
nhận thấy với mỗi bản ghi, ta cần phải xác định được thời điểm bắt đầu
(start_date) và kết thúc (end_date). Start_date thì dễ rồi,
chính là trường move_date. Còn end_date sẽ là move_date của bản ghi tiếp theo
(tính theo từng mã công ty). Có một số bản ghi vì nằm ở cuối nên không xác định được bản ghi tiếp theo nên end_date sẽ chính là ngày hiện tại (trunc(sysdate)). Câu lệnh đơn giản như này:
select rec_no, comp_id, district_id, move_date start_date,
nvl(lead(move_date)
over (partition by comp_id order by move_date), trunc(sysdate)) end_date
from ccdr;
|
Tiếp theo, ta cần
phải tìm các bản ghi có start_date bằng với end_date của bản ghi trước đó (lưu
ý là tính theo từng nhóm mã công ty và mã quận). Ta tạo thêm một trường mới
(grp_no) để nhận biết các bản ghi này, ta gán giá trị ‘0’, các bản ghi còn lại là
gán là ‘1’. Sử dụng từ khóa “with” để thực hiện truy vấn trên kết quả của câu lệnh
trước đó.
with
ccdr1 as (
select rec_no, comp_id, district_id,
move_date start_date,
nvl(lead(move_date) over
(partition by comp_id order by move_date), trunc(sysdate)) end_date
from ccdr
)
select comp_id, district_id, start_date, end_date,
from ccdr1;
|
Câu lệnh tiếp
theo sẽ giải thích tại sao ta gán giá trị ‘0’. Tôi sẽ cộng dồn trường gpr_no để
tạo ra một trường mới (grp), khi đó những bản ghi có grp_no là 0 sẽ có cùng
giá trị grp với bản ghi trước đó.
with
ccdr1 as (
select rec_no, comp_id, district_id, move_date
start_date,
nvl(lead(move_date) over
(partition by comp_id order by move_date), trunc(sysdate)) end_date
from ccdr
),
ccdr2 as (
select comp_id, district_id, start_date,
end_date,
from ccdr1
)
select comp_id, district_id, start_date, end_date,
sum(grp_no) over (partition by comp_id, district_id
order by start_date, end_date) grp
from ccdr2;
|
Và đây là câu lệnh
cuối cùng. Dựa trên việc nhóm 3 trường comp_id, district_id, và grp của câu lệnh
trước đó, ta tính giá trị min của start_date và max của end_date.
with
ccdr1 as (
select rec_no, comp_id, district_id,
move_date start_date,
nvl(lead(move_date) over
(partition by comp_id order by move_date), trunc(sysdate)) end_date
from ccdr
),
ccdr2 as (
select comp_id, district_id, start_date,
end_date,
from ccdr1
),
ccdr3 as (
select comp_id, district_id, start_date,
end_date,
sum(grp_no) over (partition by
comp_id, district_id order by start_date, end_date) grp
from ccdr2)
select
comp_id,
district_id,
min(start_date) start_date,
max(end_date) end_date
from ccdr3
group by comp_id, district_id, grp
order by 1, 2, 3, 4;
|
Kết quả nhận được
như này:
COMP_ID
|
DISTRICT_ID
|
START_DATE
|
END_DATE
|
AAA
|
01
|
1/17/2009
|
1/3/2011
|
AAA
|
01
|
2/2/2013
|
9/30/2013
|
AAA
|
03
|
1/3/2011
|
12/20/2012
|
AAA
|
04
|
12/20/2012
|
2/2/2013
|
AAA
|
10
|
3/15/2007
|
1/17/2009
|
AAA
|
HM
|
9/30/2013
|
1/10/2016
|
BBB
|
11
|
3/12/2011
|
3/1/2013
|
BBB
|
12
|
3/1/2013
|
1/10/2016
|
CCC
|
GV
|
1/3/2010
|
12/1/2012
|
CCC
|
TB
|
12/1/2012
|
1/10/2016
|
Hy vọng ngày
mai ít việc thôi J
Không có nhận xét nào:
Đăng nhận xét