วันศุกร์, ตุลาคม 14, 2548
SQL ตอนที่ 5
วันสองวันที่ผ่านมา คุณ SP (น่าจะมี & คั่นกลางนะ) ถ้าจำไม่ผิดก็อยู่ที่ กระบุรี ระนอง ถามผ่านเวบบอร์ดมาว่า ทำอย่างไรถึงจะใช้ SQL หารายงาน 505 โดยแยกเป็นกลุ่มอายุต่างๆ ได้อย่างไร ฮื่อไอ้เราก็ดันตอบไปว่าไม่ยาก (ความคิดตอนนั้นคิดว่าไม่ยาก) แต่เมื่อผ่านมาได้ประมาณ 2-3 ชั่วโมงก็รู้ว่ามันหินนะ คือต้องเล่นกับวันที่ ที่เป็น varchar อีกแล้วล่ะ แต่ก็ไม่เป็นไร เราก็ลองมาทำดู ก็พอเห็นแนวอยู่บ้าง ไหนๆก็ลองทำแล้วบันทึกไว้ซะหน่อย เตือนความจำไว้ก็ไม่เสียหายอะไร เลยมีเรื่องมาเล่าสู่กันอีกแล้ว หลังจากหมดมุขไปนาน ฮ่าๆๆๆๆ
โจทย์มีอย่างนี้ครับ ช่วงอายุที่คุณ SP ต้องการแบบนี้
สร้างตารางเก็บช่วงอายุขึ้นมาอีกตารางดีไหม จะได้ไม่ต้องมากำหนดใน SQL ให้มันยุ่งยาก เวลาแก้ไขกลุ่มอายุ ก็มาแก้ไขที่ตารางเลยทีเดียว ลองสร้างกันดู เอาสักสามฟิลด์คือ
แต่ผมว่าเพิ่มเลขกำกับกลุ่มไว้เป็นฟิลด์แรกเหอะ เวลาจัดเรียงจะทำให้มันง่ายอีก SQL สำหรับสร้างตารางก็น่าจะประมาณนี้ ตั้งชื่อตารางว่า r_age_group
ทีนี้เราก็มา INSERT ข้อมูลเข้าตารางกันสองฟิลด์แรกคงไม่ยากเท่าไร แต่สองฟิลด์หลังนี่ ฉุกใจนึกขึ้นได้ว่าจะเอาอายุ เป็นปี หรือเป็นอะไรดีอ่ะ เอาเป็นปีรู้สึกว่ามันจะหยาบไปหน่อย พอดีไปคุยกันกับน้องที่อยู่งานส่งเสริม เค้าก็แนะนำมาว่าทำไม ไม่คิดเป็นเดือนล่ะ จะได้ละเอียดขึ้น เพราะเวลาเค้าทำงานสร้างเสริมภูมิคุ้มกันเด็ก เค้าก็นับอายุกันเป็นเดือน ไม่มีใครนับเป็นปีกันหรอก ว่างั้น อือ ได้ได้ เรคคอร์ดแรกก็น่าจะเป็นแบบนี้
เหอะ เหอะ พอเรคคอร์ดที่สองสังเกตได้ว่า ทำไมต้องเป็น 5-9 ปี ด้วยอ่ะ แทนที่จะเป็น 4-9 ก็เรคคอร์ดแรกมันจบที่อายุ 48 เดือน มันก็ควรจะเริ่มต้นที่ 49 ซึ่งก็น่าจะอายุ 4 ปีอยู่ ก็กลับไปถามอีก เค้า (คนเดิม) ก็บอกมาว่า ที่คุณ SP ให้มาน่ะถูกแล้ว มากกว่า 48 เดือน ก็นับเป็น 5 ปีแล้ว เออ พึ่งรู้นะเนี่ย ขอคอนเฟิอร์มด้วยนะครับ จะได้กลับไปเฉ่งน้องคนนี้ ถ้าเกิดว่าข้อมูลที่ให้มาน่ะไม่ถูกต้อง เอาวะ ว่าแล้วก็นั่ง INSERT เข้าไปอีกประมาณนี้
เผื่ออายุให้ตั้งหมื่นเดือนนะ สำหรับเรคคอร์ดสุดท้าย อิ อิ
แล้วก็ยังมีอีกตารางนึงนะ ที่ต้องสร้างคือตารางที่เก็บกลุ่มของโรคสำหรับรายงาน 505 ผมทำไว้ให้แล้วลองไปตรวจสอบความถูกต้องอีกทีแล้วกัน นะ ทั้งสองตารางมีให้ดาวน์โหลดข้างล่าง ไม่ต้องทำตามหรอก ทำไว้ให้แล้ว ตั้งใจอ่านให้เข้าใจคอนเซ็ปต์ก็พอแว๊ว ทีนี้ก็มาเริ่มสร้าง SQL กันเลย คิดมาได้ขนาดนี้
มีอยู่สองบรรทัดที่อยากอธิบายเพิ่มเติม บรรทัดแรกคือ
ก็ลองเอา SQL ไปรันก็ได้ผลลัพท์ออกมาแบบนี้
หากว่ายังไม่สะใจ อยากจะแยกเพศ ก็เพิ่มอีกสองบรรทัดนี้เข้าไประหว่าง
แล้วลองรัน SQL ที่เราแยกเพศดู
แต่ผลลัพท์ที่ได้นี้ ยังไม่ค่อยสวยงามนักเนื่องจากว่าบางช่วงอายุ ที่ไม่มีผู้ใช้บริการก็ไม่แสดงออกมา คงแสดงออกมาเพียงกลุ่มอายุที่มีผู้ใช้บริการเท่านั้น ถ้าหากว่าอยากจะให้แสดงออกมาหมด ก็ต้องใช้น้องจอย (JOIN) ช่วย ลองกลับไปอ่าน เรื่องเดิมๆ แล้วจะเห็นว่า วิธีการ JOIN ทำอย่างไร
เกือบลืมไป ว่าไม่ได้เอาไฟล์ให้ดาวน์โหลดกันเยย
แหล่งความรู้และอ้างอิง
เผอิญไม่ค่อยได้เขียน sql เท่าไร สงสัยถามนิดครับ ว่าทำไม age_begin กับ age_end ถึงไม่ใช้ int
อืม จริงๆแล้วก็น่าจะเป็น int เหมือนที่คุณ mk สงสัยนั่นแหล่ะครับ คงจะติดกับการใช้ฐานข้อมูลของ Hospital OS มา เห็นทีต้องอัพเดทซะแล้วล่ะ ขอบคุณมากครับสำหรับคำแนะนำ
แสดงความคิดเห็น
Powered for โจทย์มีอย่างนี้ครับ ช่วงอายุที่คุณ SP ต้องการแบบนี้
คนไข้กลุ่ม 505 ช่วงอายุ
0-4
5-9
10-14
15-19
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-64
55-69
70-74
75-79
80-84
85+
สร้างตารางเก็บช่วงอายุขึ้นมาอีกตารางดีไหม จะได้ไม่ต้องมากำหนดใน SQL ให้มันยุ่งยาก เวลาแก้ไขกลุ่มอายุ ก็มาแก้ไขที่ตารางเลยทีเดียว ลองสร้างกันดู เอาสักสามฟิลด์คือ
- กลุ่มอายุ
- อายุเริ่มต้น
- อายุสิ้นสุด
แต่ผมว่าเพิ่มเลขกำกับกลุ่มไว้เป็นฟิลด์แรกเหอะ เวลาจัดเรียงจะทำให้มันง่ายอีก SQL สำหรับสร้างตารางก็น่าจะประมาณนี้ ตั้งชื่อตารางว่า r_age_group
CREATE TABLE r_age_group
(
age_code int4,
age_description varchar,
age_begin varchar,
age_end varchar
)
ทีนี้เราก็มา INSERT ข้อมูลเข้าตารางกันสองฟิลด์แรกคงไม่ยากเท่าไร แต่สองฟิลด์หลังนี่ ฉุกใจนึกขึ้นได้ว่าจะเอาอายุ เป็นปี หรือเป็นอะไรดีอ่ะ เอาเป็นปีรู้สึกว่ามันจะหยาบไปหน่อย พอดีไปคุยกันกับน้องที่อยู่งานส่งเสริม เค้าก็แนะนำมาว่าทำไม ไม่คิดเป็นเดือนล่ะ จะได้ละเอียดขึ้น เพราะเวลาเค้าทำงานสร้างเสริมภูมิคุ้มกันเด็ก เค้าก็นับอายุกันเป็นเดือน ไม่มีใครนับเป็นปีกันหรอก ว่างั้น อือ ได้ได้ เรคคอร์ดแรกก็น่าจะเป็นแบบนี้
INSERT INTO r_age_group VALUES (1,'อายุ 0-4 ปี','0','48');
เหอะ เหอะ พอเรคคอร์ดที่สองสังเกตได้ว่า ทำไมต้องเป็น 5-9 ปี ด้วยอ่ะ แทนที่จะเป็น 4-9 ก็เรคคอร์ดแรกมันจบที่อายุ 48 เดือน มันก็ควรจะเริ่มต้นที่ 49 ซึ่งก็น่าจะอายุ 4 ปีอยู่ ก็กลับไปถามอีก เค้า (คนเดิม) ก็บอกมาว่า ที่คุณ SP ให้มาน่ะถูกแล้ว มากกว่า 48 เดือน ก็นับเป็น 5 ปีแล้ว เออ พึ่งรู้นะเนี่ย ขอคอนเฟิอร์มด้วยนะครับ จะได้กลับไปเฉ่งน้องคนนี้ ถ้าเกิดว่าข้อมูลที่ให้มาน่ะไม่ถูกต้อง เอาวะ ว่าแล้วก็นั่ง INSERT เข้าไปอีกประมาณนี้
INSERT INTO r_age_group VALUES (2, 'อายุ 5 ถึง 9 ปี', '49', '108');
INSERT INTO r_age_group VALUES (3, 'อายุ 10 ถึง 14 ปี', '109', '168');
INSERT INTO r_age_group VALUES (4, 'อายุ 15 ถึง 19 ปี', '169', '228');
INSERT INTO r_age_group VALUES (5, 'อายุ 20 ถึง 24 ปี', '229', '288');
INSERT INTO r_age_group VALUES (6, 'อายุ 25 ถึง 29 ปี', '289', '348');
INSERT INTO r_age_group VALUES (7, 'อายุ 30 ถึง 34 ปี', '349', '408');
INSERT INTO r_age_group VALUES (8, 'อายุ 35 ถึง 39 ปี', '409', '468');
INSERT INTO r_age_group VALUES (9, 'อายุ 40 ถึง 44 ปี', '469', '528');
INSERT INTO r_age_group VALUES (10, 'อายุ 45 ถึง 49 ปี', '529', '588');
INSERT INTO r_age_group VALUES (11, 'อายุ 50 ถึง 54 ปี', '589', '648');
INSERT INTO r_age_group VALUES (12, 'อายุ 55 ถึง 59 ปี', '649', '708');
INSERT INTO r_age_group VALUES (13, 'อายุ 60 ถึง 64 ปี', '709', '768');
INSERT INTO r_age_group VALUES (14, 'อายุ 65 ถึง 69 ปี', '769', '828');
INSERT INTO r_age_group VALUES (15, 'อายุ 70 ถึง 74 ปี', '829', '888');
INSERT INTO r_age_group VALUES (16, 'อายุ 75 ถึง 79 ปี', '889', '948');
INSERT INTO r_age_group VALUES (17, 'อายุ 80 ถึง 84 ปี', '949', '1008');
INSERT INTO r_age_group VALUES (18, 'อายุตั้งแต่ 85 ปีขึ้นไป', '1009', '10000');
เผื่ออายุให้ตั้งหมื่นเดือนนะ สำหรับเรคคอร์ดสุดท้าย อิ อิ
แล้วก็ยังมีอีกตารางนึงนะ ที่ต้องสร้างคือตารางที่เก็บกลุ่มของโรคสำหรับรายงาน 505 ผมทำไว้ให้แล้วลองไปตรวจสอบความถูกต้องอีกทีแล้วกัน นะ ทั้งสองตารางมีให้ดาวน์โหลดข้างล่าง ไม่ต้องทำตามหรอก ทำไว้ให้แล้ว ตั้งใจอ่านให้เข้าใจคอนเซ็ปต์ก็พอแว๊ว ทีนี้ก็มาเริ่มสร้าง SQL กันเลย คิดมาได้ขนาดนี้
SELECT
r_age_group.age_description AS description
,COUNT(t_visit.t_visit_id) AS total
FROM
t_visit
,t_patient
,t_diag_icd10
,disease_505_code
,r_age_group
WHERE
t_visit.t_patient_id=t_patient.t_patient_id
AND t_visit.t_visit_id=t_diag_icd10.diag_icd10_vn
AND t_visit.f_visit_type_id='1'
AND t_visit.f_visit_status_id='3'
AND (t_diag_icd10.diag_icd10_number BETWEEN (disease_505_code.begin_code) and (disease_505_code.end_code))
AND (SUBSTRING(t_visit.visit_financial_discharge_time,0,11) BETWEEN '2548-08-01' and '2548-08-05')
AND ((to_date(substr(t_visit.visit_financial_discharge_time,0,11),'yyyy-mm-dd') -
to_date(substr(t_patient.patient_birthday,0,11),'yyyy-mm-dd'))/30 BETWEEN (r_age_group.age_begin) and (r_age_group.age_end))
GROUP BY
r_age_group.age_description
,r_age_group.age_code
ORDER BY
r_age_group.age_code
มีอยู่สองบรรทัดที่อยากอธิบายเพิ่มเติม บรรทัดแรกคือ
- AND (t_diag_icd10.diag_icd10_number BETWEEN (disease_505_code.begin_code) and (disease_505_code.end_code))
หมายความว่า ให้เลือกเอา รหัส icd10 ที่อยู่ระหว่างค่าเริ่มต้น กับค่าสิ้นสุด ของแต่ละกลุ่มในตาราง disease_505_code เท่านั้นนะ นอกเหนือจากนี้ไม่ต้องทำการเลือกมา - แล้วบรรทัดต่อมาที่สำคัญคือ AND ((to_date(substr(t_visit.visit_financial_discharge_time,0,11),'yyyy-mm-dd') -
to_date(substr(t_patient.patient_birthday,0,11),'yyyy-mm-dd'))/30 BETWEEN (r_age_group.age_begin) and (r_age_group.age_end))
ขอแยกให้ดูชัดๆ t_visit.visit_financial_discharge_time คือเวลาที่จำหน่ายทางการเงิน มีค่าของฟิลด์เป็น varchar ในฟอรแมต แบบนี้ 2548-08-01,15:30:10 เราก็ตัดมาเฉพาะ ปี เดือน วัน โดยใช้ฟังก์ชัน SUBSTRING(t_visit.visit_financial_discharge_time,จากตำแหน่งที่ศูนย์,ความยาวของสตริงที่ต้องการตัด) --->substr(t_patient.patient_birthday,0,11) ก็ได้สตริงแบบนี้ --> 2548-08-01 ทีนี้เราก็มาทำให้เป็นวันที่ซะ โดยการใช้ฟังก์ชั่นมาตรฐานของ PostgreSQL คือ to_date(สตริง,'yyyy-mm-dd') ก็จะได้วันที่ที่จบการรับบริการเป็นตัวตั้ง เช่นเดียวกันกับวันเกิดของผู้ใช้บริการคือ t_patient.patient_birthday ก็ทำเหมือนกัน ทีนี้ก็เอาวันที่มารับบริการตั้ง ลบด้วย วันเกิดของผู้ใช้บริการ ก็ได้ผลต่างคืออายุ แล้ว
แต่ผลต่างที่ได้เนี่ยยังเป็นวันอยู่ ถ้าหากว่าจะทำเป็นเดือน เราก็ต้องเอา 30 หรือ 31 ไปหารอีกต่อนึง ใช่ป่าว แต่ที่เห็นๆ มาเค้าใช้ 30 หารกัน ไม่ค่อยมีใครใช้ 31 อ่ะ ก็แปลกไปอย่าง ใครพอจะอธิบายเรื่องนี้ได้บ้างครับ ก็จะได้อายุออกมาเป็นเดือนตามที่เราต้องการ แล้วก็เอาอายุนี้ไปจัดกลุ่มตาม ค่า age_begin กับ age_end ในตาราง r_age_group นั้นแลลลลลล
ก็ลองเอา SQL ไปรันก็ได้ผลลัพท์ออกมาแบบนี้
description total
อายุ 0 ถึง 4 ปี 27
อายุ 10 ถึง 14 ปี 2
อายุ 15 ถึง 19 ปี 1
อายุ 20 ถึง 24 ปี 3
อายุ 25 ถึง 29 ปี 8
อายุ 30 ถึง 34 ปี 2
อายุ 35 ถึง 39 ปี 2
อายุ 40 ถึง 44 ปี 2
อายุ 45 ถึง 49 ปี 3
อายุ 50 ถึง 54 ปี 1
อายุ 55 ถึง 59 ปี 3
อายุ 60 ถึง 64 ปี 3
อายุ 65 ถึง 69 ปี 5
อายุ 70 ถึง 74 ปี 1
อายุ 75 ถึง 79 ปี 3
หากว่ายังไม่สะใจ อยากจะแยกเพศ ก็เพิ่มอีกสองบรรทัดนี้เข้าไประหว่าง
SELECT
r_age_group.age_description AS description
,SUM(CASE WHEN t_patient.f_sex_id='1' THEN 1 ELSE 0 END) AS male
,SUM(CASE WHEN t_patient.f_sex_id='2' THEN 1 ELSE 0 END) AS female
,COUNT(t_visit.t_visit_id) AS total
แล้วลองรัน SQL ที่เราแยกเพศดู
description male female total
อายุ 0 ถึง 4 ปี 7 20 27
อายุ 10 ถึง 14 ปี 0 2 2
อายุ 15 ถึง 19 ปี 1 0 1
อายุ 20 ถึง 24 ปี 1 2 3
อายุ 25 ถึง 29 ปี 0 8 8
อายุ 30 ถึง 34 ปี 2 0 2
อายุ 35 ถึง 39 ปี 0 2 2
อายุ 40 ถึง 44 ปี 1 1 2
อายุ 45 ถึง 49 ปี 3 0 3
อายุ 50 ถึง 54 ปี 1 0 1
อายุ 55 ถึง 59 ปี 2 1 3
อายุ 60 ถึง 64 ปี 3 0 3
อายุ 65 ถึง 69 ปี 1 4 5
อายุ 70 ถึง 74 ปี 0 1 1
อายุ 75 ถึง 79 ปี 3 0 3
แต่ผลลัพท์ที่ได้นี้ ยังไม่ค่อยสวยงามนักเนื่องจากว่าบางช่วงอายุ ที่ไม่มีผู้ใช้บริการก็ไม่แสดงออกมา คงแสดงออกมาเพียงกลุ่มอายุที่มีผู้ใช้บริการเท่านั้น ถ้าหากว่าอยากจะให้แสดงออกมาหมด ก็ต้องใช้น้องจอย (JOIN) ช่วย ลองกลับไปอ่าน เรื่องเดิมๆ แล้วจะเห็นว่า วิธีการ JOIN ทำอย่างไร
เกือบลืมไป ว่าไม่ได้เอาไฟล์ให้ดาวน์โหลดกันเยย
แหล่งความรู้และอ้างอิง
- John C. Worsley and Joshua D. Drake, Practical PostgreSQL
O'Reilly & Associates, Inc., CA ,USA
เผอิญไม่ค่อยได้เขียน sql เท่าไร สงสัยถามนิดครับ ว่าทำไม age_begin กับ age_end ถึงไม่ใช้ int
อืม จริงๆแล้วก็น่าจะเป็น int เหมือนที่คุณ mk สงสัยนั่นแหล่ะครับ คงจะติดกับการใช้ฐานข้อมูลของ Hospital OS มา เห็นทีต้องอัพเดทซะแล้วล่ะ ขอบคุณมากครับสำหรับคำแนะนำ
แสดงความคิดเห็น


Copyright ? 2008-2009 Uthai Lueadnakrop. All Rights reserved