การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติ
June 24, 2019
15:01
การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติของ column ที่ระบุลำดับเป็นตัวเลข
การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติของ column ที่ระบุลำดับเป็นตัวเลข
ต่อจา ELK #07 – Logstash คราวนี้ มาใช้งานจริง โดยใช้ ELK เพื่อเก็บ Log ของ Oracle Audit Trail Oracle Audit Trail บน Database Server เก็บ Log ในรูปแบบ XML โดยแต่ละ Event จะมี tag <AuditRecord> … </AuditRecord> คุมอยู่ ที่แตกต่างจาก Log ทั่วไปคือ ในแต่ละ Event จะมีเครื่องหมาย CRLF (การขึ้นบรรทัดใหม่) เป็นระยะ ๆ ออกแบบให้ Logstash รับข้อมูล (Input Plugin) จาก TCP Port 5515 ซึ่งต้องใช้ Codec ในการรวบ Multiline ในแต่ละ Event เข้าด้วยกัน โดยหา pattern “<AuditRecord>” เป็นจุดเริ่มต้น ส่วนบรรทัดที่ไม่เจอ Pattern ดังกล่าวนั้นการตั้งค่า negate => “true” เป็นการบอกว่า “ให้ดำเนินการต่อไป” โดยจะเอาบรรทัดที่ตามมาจากนี้ ต่อท้าย ด้วยการตั้งค่า what=> “previous” ในส่วนของ Filter Plugin จะอ่านค่าจาก “message” และ ส่งสิ่งที่ถอดจาก XML ได้ ไปยัง “doc” ในส่วของ Output Plugin จะส่งออกไปยัง ElasticSearch ที่ TCP port 9200 ดัง Configuration ต่อไปนี้ จากนั้น ทาง Oracle Database Server ทำการเปิด Audit Trail แล้วเขียน Log ลงไฟล์ แล้วเขียน Cron เพื่อ Netcat ไฟล์ส่งมาให้ Lostash ที่เปิด Port TCP 5515 ไว้รอรับ ผลที่ได้คือ โดยวิธีนี้ จะเป็นการนำ Log ซึ่งจากเดิมเป็น Text Format นำมาเป็น NoSQL ได้ ซึ่งจะสามารถ Query ข้อมูลได้ง่ายยิ่งขึ้น หวังว่าจะเป็นประโยชน์ครับ
Oracle Label Security (OLS) เป็นส่วนขยายของเทคโนโลยี Virtual Private Database (VPD) ซึ่งเริ่มนำมาใช้ตั้งแต่ Oracle 8i OLS อนุญาตให้มีการควบคุมการเข้าถึงลงในแถวต่างๆ ตามป้ายกำกับที่ระบุ ฟังก์ชันการทำงานที่คล้ายคลึงกันสามารถทำซ้ำโดยใช้ Fine Grained Access Control (FGAC) แต่ OLS ให้โซลูชันที่ง่ายกว่าสำหรับการรักษาความปลอดภัยระดับแถว (row-level security) ในบทความนี้ฉันจะนำเสนอตัวอย่างง่ายๆของการกำหนดค่า OLS ตั้งค่าฐานข้อมูล หากไม่ได้เลือกตัวเลือก Label Security เมื่อครั้งติดตั้ง Oracle Database สามารถเรียกตัวติดตั้งและเลือกตัวเลือก Label Security เพิ่มเติมได้ภายหลัง เข้าระบบด้วยบัญชีผู้ใช้ Oracle เปิด terminal พิมพ์ dbca (Database Server เป็น Linux) เมื่อได้ Welcome Screen คลิก Next ที่หน้า Operation คลิก Configure Database Options แล้วคลิก Next หน้า Database เลือกฐานข้อมูลที่ต้องการ คลิก Next ที่หน้า Database Features คลิกเลือก Label Security คลิก Next คลิก Finish เมื่อจบการตั้งค่าให้ restart database สร้างผู้ใช้ทดสอบ คำสั่งทั้งหมดทำภายใน sqlplus สร้างผู้ใช้ ols_test มีรหัสผ่านว่า password โดยให้สิทธิ์ CONNECT, RESOURCE, SELECT_CATALOG_ROLE sqlplus, / as sysdba CONN / AS SYSDBA CREATE USER ols_test IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ols_test; ต่อไปเป็นการให้สิทธิ์ในแพ็คเกจ OLS แก่ผู้ใช้ ols_test จำเป็นต้อง unlock ผู้ใช้ lbacsys และตั้งรหัสผ่านว่า lbacsys เพื่อใช้เป็นคนกำหนดสิทธิ์ต่างๆ ให้ ols_test ได้แก่สิทธิ์ execute บน sa_components, sa_user_admin, sa_label_admin, sa_policy_admin, sa_audit_admin, sa_sysdba, to_lbac_data_label และให้ ols_test เป็น lbac_dba sqlplus, / as sysdba, lbacsys/lbacsys ALTER USER lbacsys IDENTIFIED BY lbacsys ACCOUNT UNLOCK; CONN lbacsys/lbacsys GRANT EXECUTE ON sa_components TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_label_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_policy_admin TO
ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน SUM และ DECODE กันมาแล้ววันนี้ลองมาใช้อีกวิธีในการแสดงผลดังกล่าวด้วย PIVOT ก่อนอื่นมาดูในส่วน syntax ของ PIVOT กันก่อน PIVOT Syntax SELECT * FROM ( SELECT column1, column2 FROM tables WHERE conditions ) PIVOT ( aggregate_function(column2) FOR column2 IN ( expr1, expr2, … expr_n) | subquery ) ORDER BY expression [ ASC | DESC ]; โดยที่ aggregate_function คือ aggregate function เช่น SUM, COUNT, MIN, MAX, หรือ AVG IN ( expr1, expr2, … expr_n ) คือ รายการค่าของ column2 ที่ต้องการจะแสดงในส่วนคอลัมภ์ คราวนี้ลองมาดูตัวอย่างกัน สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ โจทย์ เราต้องการจะแสดงจำนวนของนักศึกษาแยกตามคณะที่สังกัด โดยให้แสดงข้อมูลอยู่ในรูปแบบคอลัมภ์ ดังนั้นจึงเราสามารถเขียนคิวรีโดยใช้ PIVOT เพื่อแก้ปัญหาข้างต้นได้ดังนี้ SELECT * FROM (SELECT ‘TotalStudent’ fac_summary, fac_id FROM test_new_student) PIVOT (COUNT (fac_id) FOR fac_id IN (’06’ Engineering, ’08’ Science, ’12’ Natural_Resources)) ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์
วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน SUM และ ฟังก์ชัน DECODE ก่อนอื่นจะขออธิบายในส่วนของฟังก์ชัน SUM และ DECODE กันก่อน ฟังก์ชัน SUM SUMจัดเป็นฟังก์ชันประเภท Aggregate Function ทำหน้าที่ในการคำนวณผลรวมของค่าในคอลัมภ์ มีรูปแบบการใช้งานดังตัวอย่างข้างล่างนี้ SELECT SUM(aggregate_expression) FROM tables [WHERE conditions]; โดยที่ aggregate_expression คือ คอลัมภ์ที่ต้องการค่าผลรวม โดยที่ข้อมูลในคอลัมภ์จะต้องเป็นชนิดตัวเลขเท่านั้น มาดูตัวอย่างการใช้งานกัน SELECT fac_id, SUM (eng_score) sum_eng_score FROM test_new_student GROUP BY fac_id; ผลลัพธ์ที่ได้ : แสดงผลรวมของคะแนนภาษาอังกฤษของแต่ละคณะ ฟังก์ชัน DECODE DECODE เป็นการเขียนเงื่อนไขบนชุดคำสั่ง select โดยมีลักษณะการทำงานเหมือน IF-THEN-ELSE โดยถ้าเข้าเงื่อนไขที่กำหนด ต้องการให้ทำอะไรก็ให้ระบุลงไป Syntax: DECODE( expression , search , result [, search , result]… [, default] ) โดยที่ expression คือ ค่าที่จะเอามาใช้เปรียบเทียบเงื่อนไข compare_value คือ ค่าที่สามารถจะมาเปรียบเทียบได้กับ expression return_value คือค่าที่จะแสดงออกมาเมื่อ ค่าของ expression = compare_value default_return_value คือค่าที่จะแสดง เมื่อ expression <> compare_value ลองมาดูตัวอย่างการใช้งานกัน สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ ตัวอย่างคิวรีในการใช้ฟังก์ชัน DECODE SELECT fac_id, DECODE ( fac_id, ’06’, ‘คณะวิศวกรรมศาสตร์’, ’08’, ‘คณะวิทยาศาสตร์’, ’12’, ‘คณะทรัพยากรธรรมชาติ’,’null’) fac_name,stud_name_thai,stud_sname_thai,sn_code,eng_score FROM test_new_student; ผลลัพธ์ที่ได้ โดยความหมายของด้านบนคือ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’06’ ก็ให้แสดง คำว่า ‘คณะวิศวกรรมศาสตร์’ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’08’ ก็ให้แสดง คำว่า ‘คณะคณะวิทยาศาสตร์’ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’12’ ก็ให้แสดง คำว่า ‘คณะทรัพยากรธรรมชาติ’ โดยปกติถ้าเราเขียนคิวรีเพื่อนับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา เราสามารถเขียนคิวรีได้ดังนี้ SELECT fac_id, COUNT (*) fac_summary FROM test_new_student GROUP BY fac_id; ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด การแสดงผลปกติจะแสดงในรูปแบบแถว คราวนี้ลองมาตั้งโจทย์ใหม่โดยยังมีความต้องการเหมือนเดิมคือ นับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา แต่เปลี่ยนจากการแสดงในรูปแบบแถวให้แสดงในรูปแบบคอลัมภ์แทน จากโจทย์เราสามารถประยุกต์ใช้งานฟังก์ชัน sum และ decode เพื่อจะแปลงข้อมูลในรูปแบบแถวให้อยู่ในรูปแบบ คอลัมภ์ได้ โดยเราสามารถเขียนคิวรีได้ดังนี้ SELECT ‘TotalStudent’ faculty, SUM (DECODE (fac_id, ’06’, 1)) Engineering, SUM (DECODE (fac_id, ’08’, 1)) Science, SUM (DECODE (fac_id, ’12’, 1)) Natural_Resources FROM test_new_student ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์