Must Story

ได้รับ Requirement กลับมาเกี่ยวกับ SQL ตอนที่ 1 ว่าถ้าหากต้องการดึงเอาวันที่มารับบริการ และอายุออกมาด้วยล่ะ จะทำได้อย่างไร ก็พยายามไปค้นคว้ามารับใช้ให้จนได้แม้ว่าจะหินโคตรๆ

ขอย้อนกลับไปที่ SQL ชุดนั้นอีกครั้งแล้วกันเผื่อคนที่พึ่งเข้ามาอ่านครั้งแรกจะได้ปะติดปะต่อเรื่องราวได้

SELECT

 t_patient.patient_hn AS hn

 ,pat_address.patient_name AS name

 ,pat_address.address AS address

 ,t_patient.patient_pid AS pid

FROM

 t_patient,pat_address,t_visit,t_diag_icd10

WHERE

 t_patient.patient_hn = t_visit.visit_hn

 AND t_patient.patient_hn = pat_address.patient_hn

 AND t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn

 AND t_visit.f_visit_type_id='0'

 AND t_visit.f_visit_status_id='3'

 AND (t_diag_icd10.diag_icd10_number LIKE 'E10%' 

    OR t_diag_icd10.diag_icd10_number LIKE 'E11%'

    OR t_diag_icd10.diag_icd10_number LIKE 'E14%'

    OR t_diag_icd10.diag_icd10_number LIKE 'E15%')
 AND (SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) between '2548-01-01' and '2548-03-31')          

 AND t_patient.patient_hn NOT IN

   ( /* เริ่มต้น */
   SELECT

    DISTINCT(t_patient.patient_hn)

   FROM

    t_patient

    ,t_visit

    ,t_diag_icd10

   WHERE

    t_patient.patient_hn = t_visit.visit_hn

    AND t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn

    AND t_visit.f_visit_type_id='0'

    AND t_visit.f_visit_status_id='3'

    AND (t_diag_icd10.diag_icd10_number LIKE 'E10%' 

      OR t_diag_icd10.diag_icd10_number LIKE 'E11%'

      OR t_diag_icd10.diag_icd10_number LIKE 'E14%'

      OR t_diag_icd10.diag_icd10_number LIKE 'E15%')

    AND (SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) between '2548-01-01' and '2548-02-28')

   ) /* สิ้นสุด */

GROUP BY

 hn

 ,pat_address.patient_name

 ,pat_address.address

 ,t_patient.patient_pid

ORDER BY hn

อยากได้วันที่ที่มารับบริการก็เพิ่มเข้าไปได้ครับ เอาเป็นก่อนหน้า HN ดีไหม

• เพิ่ม SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) AS date_visit


• เพิ่มอายุเข้าไปอีก อายุเนี่ยจะเอาอายุยังงัยล่ะ เอาอายุตอนไหน เอาอายุ ณ วันนี้เหรอ หรือว่าเป็นอายุตอนที่เข้ารับบริการ
  
  
  
  1. ถ้าเป็นอายุ ณ วันปัจจุบัน ก็เอาวันที่ปัจจุบันตั้ง เอาวันเกิดของผู้ป่วยมาลบออก ก็เป๊ะ
  
  
  2. ถ้าเป็นอายุ ตอนที่มารับบริการ ก็เอาวันที่จำหน่ายทางการเงินตั้ง เอาวันเกิดผู้ป่วยมาลบออก แต่คิดว่าคอนเซ็ปต์นี้น่าจะเวอร์คกว่า ไอเดียนี่ไม่ยาก แต่จะทำอย่างไรล่ะทีนี้ ที่ว่ายากประการแรกคือ ในฐานข้อมูลเก็บค่าวันที่อยู่ในรูปของ varchar (Character Varying) + เก็บเป็นพุทธศักราชอีกด้วย ต้องทำการแปลงให้อยู่ในรูปของวันที่เสียก่อน จึงจะเอาไปทำการคำนวณ บวก ลบ คูณ หาร ได้สะดวก คงไม่มีใครเอาตัวแปรแบบ varchar ตั้งแล้วเอา ตัวแปรแบบ varchar ไปลบออกเป็นแน่แท้ ถึงอยากทำก็คงทำไม่ได้
     
  
  
  
  


• เราลองเอาค่าของวันที่มาแตกย่อยดูก่อนเป็นไร ค่าวันที่ที่เก็บในฐานข้อมูลเป็นอย่างนี้ 2548-07-01,11:44:45 (ปี-เดือน-วัน , ชั่วโมง : นาที : วินาที)


• สิ่งที่เราต้องการคือ ปี เดือน วัน เท่านั้น ก็เอาแต่ 2548-07-01 มาเล่น วิธีตัดออกมาเป็นบางช่วงที่เราต้องการคือใช้ฟังก์ชั่น SUBSTRING ช่วย ฟอร์แมตของฟังก์ชั่น SUBSTRING นี้คือ SUBSTRING(text,n1,n2) หรือจะเขียนอีกแบบก็ได้คือ SUBSTRING(text FROM n1 FOR n2) เมื่อ text คือข้อความที่เราต้องการจะตัด , n1 คือตำแหน่งเริ่มต้นที่เราต้องการจะตัดเริ่มจากซ้ายมือ , n2 คือ จำนวนสตริงที่เราต้องการจะตัด (n1 และ n2 ต้องเป็นจำนวนเต็มนะครับ ทศนิยมใช้ไม่ได้)
  
  For Example
  
  
  
  
  • SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 4) จะได้เท่ากับ 2548 ซึ่งจะได้ค่าออกมาเท่ากับ SUBSTRING(t_visit.visit_financial_discharge_time,1,4) หรือ SUBSTRING(t_visit.visit_financial_discharge_time from 0 for 5)
  
  
  • SUBSTRING(t_visit.visit_financial_discharge_time,2,2) ได้เท่ากับ 54 เป็นต้น
  
  
  • ลองตัดกันจริงแล้วนะ ฟอร์แมตวันที่ของ PostgreSQL คือ เดือน/วัน/ปี นะครับ ต้องทำให้ถูกต้องด้วย
  
  
  
  
  • เอาเดือนออกมาก่อน substring(t_visit.visit_financial_discharge_time from 6 for 2) จะได้เท่ากับ 07 (ยังเป็น text อยู่)
  
  
  • ที่นี้ก็เอาวันออกมา substring(t_visit.visit_financial_discharge_time from 9 for 2) จะได้เท่ากับ 01 (ยังเป็น text อยู่)
  
  
  • ตามด้วยปี substring(t_visit.visit_financial_discharge_time from 1 for 4) (ยังเป็น text อยู่) เราก็ทำต่อให้เป็นตัวเลขซะเพื่อที่จะแปลงค่าเป็น ค.ศ. ได้ เนื่องจากยังเป็น พ.ศ. อยู่โดย ใช้พังก์ชั่น to_number ครอบ ได้เป็น -->to_number(substring(t_visit.visit_financial_discharge_time) ,9999)-543 ก็จะได้เป็น 2005 (2548-543) ตอนนี้เป็นตัวเลขแล้ว
  
  
  • ลองเอาสิ่งที่ได้จากที่เราได้ลองตัดสตริงมาต่อกันดูซิด้วยตัวไปป์คู่ (||) ได้ดังนี้
    
    substring(t_visit.visit_financial_discharge_time from 6 for 2)||'/'||
    
    substring(t_visit.visit_financial_discharge_time from 9 for 2)||'/'||
    
    CAST(to_number(substring(t_visit.visit_financial_discharge_time from 1 for 4) ,9999) - 543 AS text) เหตุที่เราต้อง CAST อีกทีก็เพราะว่า 2005 เป็น Numeric ในขณะที่ชาวบ้านเค้าเป็น text กัน ทำให้เป็น text เหมือนกันทั้งสามชุด สมมติว่าชุดนี้คือ เดือน/วัน/ปี ที่เราตัดสตริงมาเนี่ยมีค่าเป็น X (X อีกแล้วผมว่าพยัญชนะตัวนี้เหนื่อยที่สุดในโลกเลยก็ว่าได้)
    
  
  
  • จากค่า X ที่เราได้มาแล้วก็ต้องมาทำการแปลงให้เป็น varchar อีกครั้งเพื่อสะดวกในการแปลงให้เป็น Date ก็เอา CAST ครอบ X ไปอีกที ได้เป็น
    
    CAST(x AS varchar) แต่ยังไม่จบได้เป็น varchar แล้ว สมมติให้เป็น Y ต้องแปลงให้เป็น date อีกโดยการใช้ฟังก์ชั่น to_date ครอบ Y อีกที ได้เป็น
    
    to_date(y,'MM-DD-YYYY') ในวงเล็บระบุด้วยว่าต้องแปลงให้เป็น เดือน-วัน-ปี ด้วยนะ ยุ่งมากมายเลยเนี่ย
  
  
  • เมื่อแทนค่า X และ Y ในสมการแล้วก็จะได้เป็นดังนี้
    
    

    
    to_date(CAST(substring(t_visit.visit_financial_discharge_time from 6 for 2)
    ||'/'||substring(t_visit.visit_financial_discharge_time from 9 for 2)
    ||'/'||CAST(to_number(substring(t_visit.visit_financial_discharge_time from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY')     
         

  
  
  • ฉันใดก็ฉันนั้นเมื่อเราได้วันที่มารับบริการแล้วก็น่าจะได้วันเกิดของผู้ป่วยด้วย โดยใช้การแทนค่าด้วยตัวแปรเหมือนข้างบน
    
    

    
    to_date(CAST(substring(t_patient.patient_birthday from 6 for 2)
    ||'/'||substring(t_patient.patient_birthday from 9 for 2)
    ||'/'||CAST(to_number(substring(t_patient.patient_birthday from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY')     
         

  
  
  • ผมอยากจะบอกว่า ไอ้ที่พูดมาข้างบนน่ะ ผมไม่ได้ทำด้วยตัวเอง ได้รับความอนุเคราะห์จากน้องๆ ที่น่ารัก (โอ๋ , เหน่ง) ทำให้ต่างหาก
  
  
  • ความยากลำบากในการที่ต้องแปลงนั่นเป็นนี่ แปลงนี่ให้เป็นนั่น เนื่องมาจากค่าของวันที่นั้น ผมได้ Request ไปในเวอร์ชั่นสามแล้ว แต่ไม่รู้ว่าผลการ Request จะออกมาอย่างไรบ้าง
  
  
  
  
  
  


• เมื่อได้วันที่ที่มารับบริการและวันเกิดที่สามารถจะเอาทำการคำนวณได้แล้ว ก็เอาวันที่ที่มารับบริการตั้งแล้วลบด้วยวันเกิด แต่การที่จะทำอย่างนั้นแล้วได้ผลลัพท์ออกมาเลยน่ะ ค่อนข้างยาก และซับซ้อน โชคดีที่มีตัวช่วยอยู่ คือเมื่อก่อนเหน่ง (สุรชัย ต่อวงศ์) ได้ทำฟังก์ชันเกี่ยวกับวันที่ ไว้ให้ผมใช้สองสามอัน ก็เลยเอามาโมดิฟายเล่น เพื่อลดภาระในการเขียน SQL และช่วยในการคำนวณ ฟังก์ชันนี้ผมเรียกว่า ฟังก์ชันแสดงอายุแบบสั้น สร้างด้วยภาษา plpgsql หน้าตาเป็นอย่างนี้ครับ
  

  
  CREATE OR REPLACE FUNCTION calage3(date,date)
    RETURNS text AS
  'DECLARE
    d INTEGER;
    BEGIN
    d:= $1-$2;
    IF d > 365 THEN  RETURN d/365 || \' ป\';
    ELSE
     IF d = 365 THEN  RETURN \'1 ป\';
     ELSE
      IF d > 30 THEN  RETURN d/30 || \' ด\';
      ELSE
       IF d = 30 THEN  RETURN \'1 ด\';
       ELSE
        IF d > 1 THEN  RETURN d || \' ว\';
        ELSE
         IF d = 1 THEN  RETURN \'1 ว\';
         ELSE
          IF d <= 0 THEN  RETURN \'0 ว\';
          END IF;
         END IF;
        END IF;
       END IF;
      END IF;
     END IF;
    END IF;
    RETURN \' \';
    END;'
    LANGUAGE 'plpgsql' VOLATILE; 
   

  วิธีการก็คือเอาไปฝังไว้ในฐานข้อมูล
  

  postgres@debian: ~ $ psql your_database < calage3.fnc.sql

  
  ฟังก์ชั่นนี้จะสามารถทำงานได้ก็ต่อเมื่อ มีการส่งค่าพารามิเตอร์มาสองค่า จะมากหรือน้อยกั่วนี้ย่อมเป็นไปมิได้เด็ดขาด ค่าแรกที่ต้องส่งมาคือ t_visit.visit_financial_discharge_time และค่าที่สองคือ t_patient.patient_birthday ทั้งนี้ทั้งสองค่าต้องผ่านการแปลงค่าด้วยวิธีการข้างบนเรียบร้อยแล้วด้วยนะ รูปแบบการใช้ฟังก์ชั่นนี้ก็คือ
  
  

  calage3(visit_date,birth_date)

  มาดูค่าจริงๆ ที่เราต้องเขียนเป็น SQL ก็คือ
  

  calage3(to_date(CAST(substring(t_visit.visit_financial_discharge_time from 6 for 2)
  ||'/'||substring(t_visit.visit_financial_discharge_time from 9 for 2)
  ||'/'||CAST(to_number(substring(t_visit.visit_financial_discharge_time from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY') 
   ,
   to_date(CAST(substring(t_patient.patient_birthday from 6 for 2)
  ||'/'||substring(t_patient.patient_birthday from 9 for 2)
  ||'/'||CAST(to_number(substring(t_patient.patient_birthday from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY')
   ) AS age

  
  


• เอาล่ะนะ ลองเอามาทำเป็น SQL เต็มๆ ดูบ้าง
  

  
  SELECT
   SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) AS date_visit
   ,t_patient.patient_hn AS hn
   ,pat_address.patient_name AS name
   ,calage3(to_date(CAST(substring(t_visit.visit_financial_discharge_time from 6 for 2)
  ||'/'||substring(t_visit.visit_financial_discharge_time from 9 for 2)
  ||'/'||CAST(to_number(substring(t_visit.visit_financial_discharge_time from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY') 
   ,
   to_date(CAST(substring(t_patient.patient_birthday from 6 for 2)
  ||'/'||substring(t_patient.patient_birthday from 9 for 2)
  ||'/'||CAST(to_number(substring(t_patient.patient_birthday from 1 for 4) ,9999) - 543 AS text) AS varchar),'MM-DD-YYYY')
   ) AS age
   ,pat_address.address AS address
   ,t_patient.patient_pid AS pid
  FROM
   t_patient,pat_address,t_visit,t_diag_icd10
  WHERE
   t_patient.patient_hn = t_visit.visit_hn
   AND t_patient.patient_hn = pat_address.patient_hn
   AND t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn
   AND t_visit.f_visit_type_id='0'
   AND t_visit.f_visit_status_id='3'
   AND (t_diag_icd10.diag_icd10_number LIKE 'E10%' 
      OR t_diag_icd10.diag_icd10_number LIKE 'E11%'
      OR t_diag_icd10.diag_icd10_number LIKE 'E14%'
      OR t_diag_icd10.diag_icd10_number LIKE 'E15%')
   AND (SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) between '2548-01-01' and '2548-03-31')          
   AND t_patient.patient_hn NOT IN
     ( /* เริ่มต้น */
     SELECT
      DISTINCT(t_patient.patient_hn)
     FROM
      t_patient
      ,t_visit
      ,t_diag_icd10
     WHERE
      t_patient.patient_hn = t_visit.visit_hn
      AND t_visit.t_visit_id = t_diag_icd10.diag_icd10_vn
      AND t_visit.f_visit_type_id='0'
      AND t_visit.f_visit_status_id='3'
      AND (t_diag_icd10.diag_icd10_number LIKE 'E10%' 
        OR t_diag_icd10.diag_icd10_number LIKE 'E11%'
        OR t_diag_icd10.diag_icd10_number LIKE 'E14%'
        OR t_diag_icd10.diag_icd10_number LIKE 'E15%')
      AND (SUBSTRING(t_visit.visit_financial_discharge_time from 1 for 10) between '2548-01-01' and '2548-02-28')
     ) /* สิ้นสุด */
  GROUP BY
   hn
   ,pat_address.patient_name
   ,pat_address.address
   ,t_patient.patient_tambon
   ,t_visit.visit_financial_discharge_time
   ,t_patient.patient_birthday
   ,t_patient.patient_pid
  ORDER BY date,hn,t_patient.patient_tambon

  
  ประโยคที่ท่านเห็นเป็นสีแดงคือสิ่งที่เพิ่มเข้าไปใหม่ และต้องเพิ่ม Group By เข้าไปอีกสองสามบรรทัดนะครับ SQL จึงจะทำงานได้สมบูรณ์ ไม่มีเอ๋อเหล๋อ ได้ผลลัพท์อย่างนี้ครับ
  

  
  2548-03-08    000000435    นางศรี  อินปุ๊ด    53 ป    42  ม.05 ต.วอแก้ว อ.ห้างฉัตร จ.ลำปาง    3510500027099
  2548-03-30    000001530    นายแก้ว  วงศ์จันทร์    73 ป    33  ม.07 ต.บ้านปวง อ.ทุ่งหัวช้าง จ.ลำพูน    3510500074780
  

  ก็จบเรื่องราวของ SUBSTRING , การแปลงค่าด้วยฟังก์ชั่นต่างๆ รวมถึงการใช้งานฟังก์ชั่นที่สร้างขึ้นมาเอง ภาษาฝรั่งตามที่ได้ยินมาเค้าเรียกว่า User Define Function (UDF) ก็คงจะได้ไอเดียเอาเรื่องราวเหล่านี้ไปประยุกต์ใช้ให้เหมาะสมได้ทั่วหน้ากันนะครับ
  

แหล่งความรู้และอ้างอิง

• John C. Worsley and Joshua D. Drake, Practical PostgreSQL
  
  O'Reilly & Associates, Inc., CA ,USA
  

แสดงความคิดเห็น