Like this:

หัวข้อในวันนี้ได้รับการอนุเคราะห์จากคำถามของคุณ Artelrooy ที่เว็บบอร์ด pantip

ทางผมต้องการ นับ ข้อมูล เฉพาะ down โดยแต่ละรอบจะมีข้อมูลเข้ามาทุก 5 นาที ครับ เช่น รอ 12.00  ได้ down ก็จะนับ 1  แต่ต่อมา รอบ 12.05 ส่ง down มาอีกจะไม่นับ  จนกว่า ข้อมูล จะเป็น up  แล้ว ส่ง down มาให้อีกทีจะนับ เป็น 2 ครับ เงื่อนไขคือ ถ้ามี down ส่งมาติดต่อ กัน จะนับ เพียง 1 ครั้ง ครับ  ไม่ทราบว่าต้องเขียน query อย่างไรครับ

เห็นว่าคำถามนี้น่าสนใจดี สามารถใช้เป็นตัวอย่างให้เห็นประโยชน์ที่บางคนคิดไม่ถึงของ analytic functions ได้

สำหรับผู้อ่านที่คุ้นเคยกับ analytic functions ของ Oracle ดีอยู่แล้วอ่านคำถามครั้งแรกก็น่าจะนึกถึง lead, lag functions ซึ่งสามารถใช้ในการเปรียบเทียบข้อมูลในแถวปัจจุบันกับข้อมูลในแถวก่อนหน้าได้ อย่างไรก็ดีสำหรับ database ยี่ห้ออื่น เท่าที่ผมรู้ยังไม่มี function 2 ตัวนี้ ดังนั้น วันนี้ผมจะแสดงตัวอย่างของการนำ function row_number มาประยุกต์ใช้ในกรณีนี้ครับ

SQL> select tt1, status
  2  from down_time
  3  /
TT1      STAT
-------- ----
00:00:00 UP
...
09:05:00 UP
09:10:00 DOWN
09:15:00 UP
...
19:40:00 UP
19:45:00 DOWN
...
20:30:00 DOWN
20:35:00 UP
...
21:55:00 UP
22:00:00 DOWN
...
22:15:00 DOWN
22:20:00 UP
...
23:55:00 UP
288 rows selected.

SQL> select count(distinct (rn1-rn2)) cnt_down
  2  from (
  3  select status
  4    , row_number () over (order by tt1) rn1
  5    , row_number () over (partition by status order by tt1) rn2
  6  from down_time
  7  )
  8  where status = 'DOWN'
  9  /
  CNT_DOWN
----------
         3

tips นี้ผมได้ต้นแบบมาจาก otn forum โดยคุณ Rod West และ Vadim Tropashkoโดย query ต้นฉบับนั้นใช้ตอบคำถามว่าเราจะยุบแถวหลาย ๆ แถว ให้เหลือเป็นแต่ละช่วงที่ต่อเนื่องกันได้อย่างไร เช่น 1,2,3,5,6,8,9,10 ก็เหลือเพียง 3 แถว คือ 1-3, 5-6 และ 8-10 เป็นต้น

สำหรับคำถามที่ต้องการยุบข้อมูลมากกว่าหนึ่งแถวให้เหลือเพียงหนึ่งแถวนั้น ส่วนใหญ่เราต้องตอบด้วย group by และ aggregate functions ปัญหาคือเราจะ group by ด้วยอะไร ผมจะแสดงวิธีแก้ปัญหาของเขาโดยใช้ rownum

SQL> select rownum, n1, n1-rownum diff
  2  from t1
  3  order by n1 ;
    ROWNUM         N1       DIFF
---------- ---------- ----------
         1          1          0
         2          2          0
         3          3          0
         4          5          1
         5          6          1
         6          8          2
         7          9          2
         8         10          2
8 rows selected.

ดูจากตัวอย่างข้างบนจะเห็นได้ว่าทุกค่าที่อยู่ติดกัน จะมีผลต่างระหว่างค่ากับ rownum ที่เท่ากัน ผลต่างนี้คือสิ่งที่เราจะใช้ในการ group by

SQL> select min(n1) n_from, max(n1) n_to
  2  from ( select rownum rn, n1 from t1 order by n1 )
  3  group by n1-rn
  4  order by n_from
  5  /
    N_FROM       N_TO
---------- ----------
         1          3
         5          6
         8         10

กลับมาที่คำถามของเราจะเห็นว่ายากขึ้นอีกนิด แต่ก็เป็นคำถามที่ไม่ต่างจากคำถามนี้ในแง่ที่ว่าต้องการยุบ status ที่มีค่า ‘DOWN’ ที่อยู่ติดกันให้เหลือเพียงหนึ่ง สิ่งที่ผมต้องทำเพิ่มก็คือสร้าง running number ขึ้นมาสองตัว ตัวหนึ่งนับเฉพาะค่าที่ เป็น ’DOWN’ (ซึ่งคือ row_number ที่มี partition by clause ) อีกตัวนับทั้งหมด (ซึ่งคือ row_number ที่มีแต่ order by clause)  จากนั้นเมื่อกรองข้อมูลมาดูเฉพาะที่เราสนใจคือ ‘DOWN’ เราก็จะได้ตัวเลขสองชุด ชุดที่หนึ่งเรียงกันแบบต่อเนื่อง ส่วนชุดที่สองเป็นตัวเลขที่มี gap ซึ่งเกิดจากข้อมูลที่ไม่ใช่ down เพียงเท่านี้ เราก็จะได้คำถามในลักษณะเดียวกันแล้ว แต่ไม่ได้ต้องการค่าสูงสุด ต่ำสุดในแต่ละช่วง แต่ต้องการนับว่ามีกี่ช่วง ซึ่งสามารถตอบได้ด้วย function count (distinct ..)

หลาย ๆ ท่านอาจตั้งคำถามว่า เขียน query แบบนี้ถ้าไม่อธิบายเพิ่มเติม ใครจะไปดูเข้าใจ ถูกต้องที่สุดเลยครับ อย่างไรก็ดีผมเห็นว่าที่ดูไม่เข้าใจนั้น ไม่ใช่เพราะมันยากเกินไป แต่เพราะคนรู้จักเทคนิคนี้กันน้อยเกินไปต่างหาก ถ้ามีคนนำไปใช้กันอย่างแพร่หลาย มันก็จะกลายเป็น query ที่ธรรมดา ๆ query หนึ่งเท่านั้น สำหรับผมแล้วมันเป็น query ที่มีประโยชน์มาก ผมนำมาประยุกต์ใช้นับครั้งไม่ถ้วนแล้ว จึงอยากเผยแพร่ให้คนอื่น ๆ รู้จักด้วย ดังนั้นหากท่านเห็นว่า query นี้มีประโยชน์ ก็เรียนเชิญให้นำไปเผยแพร่กันต่อได้เลยครับ

SQL is fun!