SQL Tips : Select data with ” Case When “
ในการจัดทำรายงานจากข้อมูลในตาราง บางครั้งเราต้องการที่จะเปลี่ยนมุมมองในการนำเสนอข้อมูลจากแนวตั้งรายบรรทัดให้เปลี่ยนเป็นแนวนอน หรือบางทีเราต้องการแปลงข้อมูลด้วยเงื่อนไข
เช่น ถ้าข้อมูล > 0 ให้แสดงค่านั้น นอกนั้นให้แสดงค่าเป็น 0 เป็นต้น ซึ่งใน MySQL, Oracle , SQL Servers หรือ SQL Statement ทั่วไปจะมีฟังก์ชันพื้นฐานให้ใช้นั่นคือ Case … When เป็นการแสดงข้อมูลโดยผูกเงื่อนไขเข้าไป ซึ่งมีรูปแบบการเรียกใช้ดังนี้
| ( CASE WHEN < Condition > THEN < Action > WHEN < Condition > THEN < Action > ELSE < Action > END ) |
เพื่อให้เห็นภาพและเข้าใจได้ง่ายๆ เรามาลองกับข้อมูลจริงๆกันครับ สมมติว่าเรามีตารางข้อมูล TST_RECORDS ซึ่งมีข้อมูลดังนี้
| date | amount |
| 1/30/2014 15:29 | 52355 |
| 1/29/2014 3:44 | 50910 |
| 1/28/2014 3:46 | 59522 |
| 1/27/2014 3:54 | 37177 |
| 1/26/2014 3:54 | 37482 |
| 1/25/2014 4:58 | 58191 |
| 1/24/2014 6:10 | 45270 |
| 1/23/2014 6:12 | 52671 |
| 1/22/2014 6:13 | 55528 |
| 1/21/2014 6:17 | 48936 |
| 1/20/2014 9:37 | 44911 |
| 1/19/2014 19:38 | 47394 |
| 1/18/2014 9:38 | 49442 |
| 1/17/2014 9:39 | 51839 |
ตัวอย่าง 1 สมมติว่าเราต้องการใส่สัญลักษณ์ข้อความแทนวันในรอบสัปดาห์ เช่น วันอาทิตย์ แทนด้วย ‘A’ วันจันทร์ แทนด้วย ‘B’ ไปจนถึงวันเสาร์แทนด้วย ‘G’ และถ้าเกิดมี error ให้แทนด้วย ‘Z’
- อย่างแรก ต้องแปลงข้อมูลวันที่ (date) ในตารางเป็นวันในสัปดาห์ โดยใช้ date_format(date,’%W’)
- จากนั้น ใช้ case when ตรวจสอบเงื่อนไขว่า date_format(date,’%W’) = ‘วันที่ต้องการ’ แล้วกำหนดค่า
ดังนี้
| SELECT date,date_format(date,’%W’) day, (case when date_format(date,’%W’) = ‘Sunday’ then ‘A’ when date_format(date,’%W’) = ‘Monday’ then ‘B’ when date_format(date,’%W’) = ‘Tuesday’ then ‘C’ when date_format(date,’%W’) = ‘Wednesday’ then ‘D’ when date_format(date,’%W’) = ‘Thursday’ then ‘E’ when date_format(date,’%W’) = ‘Friday’ then ‘F’ when date_format(date,’%W’) = ‘Saturday’ then ‘G’ else ‘Z’ end ) Flag FROM TST_RECORDS |
ผลลัพธ์ที่ได้ คือ
| date | day | Flag |
|---|---|---|
| 2014-01-30 15:29:52 | Thursday | E |
| 2014-01-29 03:44:20 | Wednesday | D |
| 2014-01-28 03:46:22 | Tuesday | C |
| 2014-01-27 03:54:21 | Monday | B |
| 2014-01-26 03:54:25 | Sunday | A |
| 2014-01-25 04:58:48 | Saturday | G |
| 2014-01-24 06:10:57 | Friday | F |
| 2014-01-23 06:12:34 | Thursday | E |
| 2014-01-22 06:13:10 | Wednesday | D |
| 2014-01-21 06:17:15 | Tuesday | C |
| 2014-01-20 09:37:58 | Monday | B |
| 2014-01-19 19:38:24 | Sunday | A |
| 2014-01-18 09:38:43 | Saturday | G |
| 2014-01-17 09:39:04 | Friday | F |
ตัวอย่างที่ 2 ถ้าเราต้องการหาผลรวมของ amount โดยให้แสดงผลในบรรทัดเดียวตามวันอาทิตย์ – วันเสาร์
เราสามารถใช้ CASE WHEN แยกเป็น Column ตามวัน แล้วใช้ฟังก์ชัน sum() เข้าช่วยในการรวมค่า amount ได้ดังนี้
| SELECT sum(case when date_format(date,’%W’) = ‘Sunday’ then amount else 0 end ) Sun, sum(case when date_format(date,’%W’) = ‘Monday’ then amount else 0 end ) Mon, sum(case when date_format(date,’%W’) = ‘Tuesday’ then amount else 0 end ) Tue, sum(case when date_format(date,’%W’) = ‘Wednesday’ then amount else 0 end ) Wed, sum(case when date_format(date,’%W’) = ‘Thursday’ then amount else 0 end ) Thu, sum(case when date_format(date,’%W’) = ‘Friday’ then amount else 0 end ) Fri, sum(case when date_format(date,’%W’) = ‘Saturday’ then amount else 0 end ) Sat FROM `tst_records` |
ผลลัพธ์ที่ได้คือ
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|---|---|---|---|---|---|---|
| 84876 | 82088 | 108458 | 106438 | 105026 | 97109 | 107633 |
ฟังก์ชัน CASE WHEN มีประโยชน์มากครับ ในการจัดทำรายงานในมุมมองใหม่ๆ และยังสามารถใส่เงื่อนไขในการกรองข้อมูลที่จะนำเสนอได้อีกด้วยครับ
