สรุปขั้นตอนการสำรวจข้อมูลเพื่อความเข้าใจในข้อมูล ตัวเลข ข้อความ และ วันเวลา exploratory-data-analysis-in-sql

แวะมาทักทายกันได้

สรุปขั้นตอนการสำรวจข้อมูลเพื่อความเข้าใจในข้อมูลที่(อาจจะ) ซ่อนอยู่

1️⃣ ขั้นตอนที่จำเป็นก่อนจะเริ่มงานเขียนโปรแกรม มีขั้นตอนหลักๆ อยู่ 5 ขั้นตอน

• การเก็บ requirment
• การออกแบบระบบ
• การพัฒนาระบบ
• การทดสอบระบบ
• การติดตั้งระบบ

ขั้นตอนเหล่านี้ จะเกิดข้อมูลหลังจากที่ได้พัฒนาเสร็จแล้ว ตามระบบที่วางและออกแบบเอาไว้

2️⃣ แต่ถ้าเป็นงานทางด้านข้อมูลล่ะ? มักเป็นการโยนข้อมูลมาให้ทำในทันทีพร้อมให้โจทย์ว่าอยากได้อะไร ขั้นตอนแรกที่จะต้องทำเลยก็คือ การสำรวจข้อมูล หรือ Exploration Data Analysis (EDA)

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

3️⃣ ลองมายกตัวอย่างดูกันบ้าง

สมมติว่า บริษัทแห่งหนึ่งต้องการทำการวิเคราะห์ข้อมูลลูกค้าเพื่อหาแนวทางปรับปรุงผลิตภัณฑ์และบริการ และสมมว่า บริษัทนี้มาว่าจ้างคุณให้วิเคราะห์ข้อมูลบางอย่าง ขั้นตอนต่างๆอะไรบ้างที่คุณจะต้องทำ

4️⃣ มาดูกระบวนการต่างๆที่จะต้องจัดการข้อมูลเหล่านั้น ให้เหมาะกับการนำไปใช้งานต่อไป มีดังต่อไปนี้

• ตรวจสอบว่าฐานข้อมูลของบริษัทนี้ เป็นประเภทไหน มีอะไรบ้าง เช่น PostgresSQL, MySQL, MongoDB หรือ เป็นแบบ Excel, CSV ไฟล์ เป็นต้น
• มี ER Diagram หรือ มี แผนภาพต่างๆที่เราสามารถเข้าใจความสัมพันธ์ต่างๆ หรือไม่

ถ้าไม่มีสิ่งเหล่านี้จะต้องทำการ EDA ข้อมูลขึ้นมาเพื่อดูลักษณะของข้อมูล และ ความสัมพันธ์ของข้อมูล

5️⃣ในกระบวนการ EDA มักจะมีขั้นตอนซ้ำๆ ให้จัดการข้อมูลอยู่เสมอ ได้แก่

• การเช็คจำนวนแถวของข้อมูลใน Database ไม่ว่าจะมีกี่ตารางก็ตามลองสำรวจดูว่ามีเท่าไรเพื่อทำในขั้นตอนถัดไป
• การตรวจสอบว่ามีค่า Null หรือ ค่าว่างหรือไม่
• การตรวจสอบ ประเภทของข้อมูล หรือ ดูว่าแต่ละ column หน้าตาเป็นอย่างไร ไม่ว่าจะเป็น Numeric, Character, DateTime, Boolean บ้างครั้ง อาจจะมี Object แบบ Unstructure Data เช่น JSON, Base64 ก็ได้
• การดูความสัมพันธ์ในตารางว่าเป็นอย่างไร มี column ไหนเป็น primary key, foreign key บ้าง

6️⃣ขั้นตอนเหล่านี้ สามารถใช้คำสั่ง SQL ในการจัดการข้อมูล หรือ สำรวจข้อมูลได้ในทันที ไม่ว่าจะเป็น COUNT, INNER JOIN, IS NULL, Coalesce, GROUP BY, LIMIT, ORDER BY เป็นต้น

7️⃣ โดยสรุปแล้ว ไม่ว่าจะเป็นงานทั้งด้านเขียนโปรแกรม หรือ งานด้านวิเคราะห์ข้อมูล จะต้องใช้ความรู้ EDA เสมอ

สรุปขั้นตอนการสำรวจข้อมูลเพื่อความเข้าใจในข้อมูลที่(อาจจะ) ซ่อนอยู่ด้วย SQL ตอนที่ 2 - ข้อมูลประเภทตัวเลข

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

2️⃣  ข้อมูลมีอยู่ 2 ประเภท หลัก ได้แก่ ประเภทหมวดหมู่ (categories) และ ประเภทตัวเลข (numeric) ซึ่งแตกแยกย่อยไปตามการใช้งาน ไม่ว่าจะเป็นการใช้ binary เลขฐานสอง หรือ numeric จำนวนเต็ม และ อื่นๆอีก หลายชนิด แต่จะกล่าวถึงในโพสต์นี้เฉพาะ เลขจำนวนเต็มและเลขจำนวนหลักทศนิยม สำหรับในการวิเคราะห์ข้อมูลชนิดที่เป็นตัวเลขเท่านั้น จะลงรายละเอียดในหัวข้อถัดไป

3️⃣ ในระบบคอมพิวเตอร์มีอยู่หลายชนิด เวลาที่เขียนโปรแกรมจะมีการกำกับประเภทเอาไว้ตามรหัสภาษา ซึ่ง ภาษา SQL จะเอาไว้ติดต่อกับฐานข้อมูลโดยเฉพาะ มีหลักๆ อยู่ 2 กลุ่ม เลขจำนวนเต็มและเลขจำนวนหลักทศนิยม ดังนี้

4️⃣ เลขจำนวนเต็ม

จำนวนหลักข้อมูลจะถูกกำหนดโดยการจองพื้นที่ ตามชนิดของข้อมูล เอาไว้ใน harddisk หรือเรียกว่า storage โดยแบ่งได้ดังนี้

• integer ตัวเลขจำนวนเต็มที่มีขนาดในการจองพื้นที่เก็บข้อมูลตั้งแต่ -2,147,483,648 ถึง 2,147,483,647
• smallint ตัวเลขจำนวนเต็มที่มีขนาดในการจองพื้นที่เก็บน้อยกว่า integer ตัวเลขประเภทนี้สามารถเก็บตัวเลขเต็มจำนวนได้ตั้งแต่ -32,768 ถึง 32,767
• bigint ตัวเลขจำนวนเต็มที่มีขนาดในการจองพื้นที่เก็บที่มากกว่า integer ตัวเลขประเภทนี้สามารถเก็บตัวเลขเต็มจำนวนได้ตั้งแต่ -9,223,372,036,854,775,808 ถึง 9,223,372,036,854,775,807

5️⃣ เลขจำนวนหลักทศนิยม

เช่นเดียวกันกับจำนวนเต็มที่จะถูกกำหนดโดยการจองพื้นที่ ตามชนิดของข้อมูลโดยแบ่งได้ดังนี้

• decimal เป็นประเภทตัวเลขทศนิยมที่สามารถเก็บตัวเลขทศนิยมได้ มีจำนวนหลักทศนิยมได้ตั้งแต่ 1 ถึง 38
• real เป็นประเภทตัวเลขทศนิยมที่ขนาดเล็กกว่า decimal สามารถมีจำนวนหลักทศนิยมได้ตั้งแต่ 1 ถึง 7
• double เป็นประเภทตัวเลขทศนิยมที่ใหญ่กว่า decimal สามารถมีจำนวนหลักทศนิยมได้ตั้งแต่ 1 ถึง 15

6️⃣ แน่นอนว่า การจะเข้าใจข้อมูลตัวเลขได้นั้น จะต้องนำศาสตร์การคำนวนและสถิติเข้ามาเป็นเครื่องมือในการทำงาน

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

8️⃣ การใช้เครื่องมือเพื่อทำความเข้าใจ ไม่ว่าจะเป็น min, max, mean, mode, median, average, standdard deviation และอื่นๆ เพื่อเข้าใจลักษณะของข้อมูล ว่ามีพฤติกรรม มีความผิดปกติหรือไม่ และ นำไปทำการทดลองและทดสอบสิ่งที่อยากรู้ได้อีกด้วย โดยการเปรียบเทียบด้วยวิธีต่างๆ

9️⃣ เครื่องมือสำหรับการสำรวจข้อมูล สามารถใช้ได้หมดในเรื่องของการคำนวนที่สามารถทำได้ ไม่ว่าจะ Excel หรือ ภาษาที่ใช้ในการเขียนโปรแกรมต่างๆ แต่สำหรับเครื่องมือที่จำเป็นต้องใช้ SQL เป็นหลักซึ่งเป็นพื้นฐานของภาษาอื่นๆที่จะต้องติดต่อกับฐานข้อมูล ก็ควรจะรู้วิธีการเขียนหรือ pattern เอาไว้ ก็จะยกตัวอย่างสั้นๆในการละคำสั่ง

ตัวอย่าง คำสั่ง min, max, avg, stddev

SELECT min(profits),
       avg(profits),
       max(profits),
       stddev(profits)
  FROM fortune500;

ตัวอย่างคำสั่ง การจัดกลุ่ม และ นับจำนวน เพื่อดูการกระจายตัวของข้อมูล

-- Truncate employees
SELECT trunc(employees, -4) AS employee_bin,
       -- Count number of companies with each truncated value
       count(*)
  FROM fortune500
 -- Limit to which companies?
 WHERE employees < 100000
 -- Use alias to group
 GROUP BY employee_bin
 -- Use alias to order
 ORDER BY employee_bin;

ตัวอย่างการจัดกลุ่มข้อมูล หรือ data binning

-- Bins created in Step 2
WITH bins AS (
      SELECT generate_series(2200, 3050, 50) AS lower,
             generate_series(2250, 3100, 50) AS upper),
     -- Subset stackoverflow to just tag dropbox (Step 1)
     dropbox AS (
      SELECT question_count 
        FROM stackoverflow
       WHERE tag='dropbox') 
-- Select columns for result
-- What column are you counting to summarize?
SELECT lower, upper, count(question_count) 
  FROM bins  -- Created above
       -- Join to dropbox (created above), 
       -- keeping all rows from the bins table in the join
       LEFT JOIN dropbox
       -- Compare question_count to lower and upper
         ON question_count >= lower 
        AND question_count < upper
 -- Group by lower and upper to count values in each bin
 GROUP BY lower, upper
 -- Order by lower to put bins in order
 ORDER BY lower;

ตัวอย่างการหาอัตราส่วนของความสัมพันธ์ระหว่างข้อมูล correlation

DROP TABLE IF EXISTS correlations;

CREATE TEMP TABLE correlations AS
SELECT 'profits'::varchar AS measure,
       corr(profits, profits) AS profits,
       corr(profits, profits_change) AS profits_change,
       corr(profits, revenues_change) AS revenues_change
  FROM fortune500;

INSERT INTO correlations
SELECT 'profits_change'::varchar AS measure,
       corr(profits_change, profits) AS profits,
       corr(profits_change, profits_change) AS profits_change,
       corr(profits_change, revenues_change) AS revenues_change
  FROM fortune500;

INSERT INTO correlations
SELECT 'revenues_change'::varchar AS measure,
       corr(revenues_change, profits) AS profits,
       corr(revenues_change, profits_change) AS profits_change,
       corr(revenues_change, revenues_change) AS revenues_change
  FROM fortune500;

-- Select each column, rounding the correlations
SELECT measure, 
       round(profits::numeric, 2) AS profits,
       round(profits_change::numeric, 2) AS profits_change,
       round(revenues_change::numeric, 2) AS revenues_change
  FROM correlations;

???? โดยสรุปแล้ว ในการสำรวจข้อมูล จะช่วยให้เราเข้าใจเข้าใจข้อมูลมากขึ้น ด้วยการใช้เทคนิคต่างๆ ด้วยคำสั่ง SQL และ ในการสำรวจข้อมูลประเภทตัวเลขยังมีอีกมากมาย สิ่งสำคัญคือต้องลองเล่น และ หาตัวอย่างมาหัดทำกันนะครับ

สรุปขั้นตอนการสำรวจข้อมูลเพื่อความเข้าใจในข้อมูลตัวอักษรหรือข้อความที่ซ่อนอยู่ ด้วย SQL ตอนที่ 3

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

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

3️⃣ ปกติการเก็บข้อมูลจะต้องมีการกำหนดประเภทให้กับข้อมูลเพื่อให้คอมพิวเตอร์รู้ว่าข้อมูลที่เก็บเอาไว้นี้เป็นข้อมูลประเภทอะไร เพื่อจองพื้นที่ในการเก็บข้อมูล โดยมีผลต่อพื้นที่ Hardware หรือ harddisk ของคอมพิวเตอร์ ซึ่งได้กล่าวไปในโพสต์ผ่านบ้างแล้ว

4️⃣ ข้อมูลที่เป็นประเภทข้อความหรือตัวอักษรในภาษา SQL สามารถเก็บข้อมูลประเภทนี้ได้ 3 ประเภท ได้แก่

• character หรือ ตัวย่อ CHAR :: เป็นประเภทตัวอักษรความยาวคงที่ เช่น character(20) หมายความว่า จะสามารถเก็บข้อมูลเป็นประเภทตัวอักษรความยาวสูงสุด 20 ตัวอักษร กรณีที่ป้อนไม่ถึง 20 ตัวระบบจะทำการเพิ่มช่องว่างเข้าไปให้จนครบ 20 ตัวอักษร
• character varying หรือ VARCHAR :: เป็นประเภทตัวอักษรความยาวเปลี่ยนแปลงได้ เช่น VARCHAR(50) หมายความว่า สามารถระบุความยาวของตัวอักษรได้ 50 ตัว กรณีที่มีการเก็บตัวอักษรมากกว่าที่กำหนดไว้ จะทำการเก็บไว้ให้เฉพาะ 50 ตัวอักษรแรกเท่านั้น
• text :: เป็นประเภทตัวอักษรความยาวไม่จำกัด สามารถเก็บข้อความที่มีความยาวเท่าไรก็ได้

5️⃣ นอกจากนี้ ชนิดของ text ยังแบ่งออกเป็น ชนิดข้อความแบบหมวดหมู่ และ ชนิดข้อความแบบไม่มีโครงสร้าง ชนิดแบบหมวดหมู่เป็นอย่างไร ก็คือ กลุ่มคำ ในประเภทเดียวกัน เช่น

• Tues, Tuesday, MON, TH
• shirts, shoes, hats, pants
• 081-123-4567

6️⃣ ชนิดแบบไม่โครงสร้างเป็นอย่างไร ก็คือ ข้อความต่างๆ เช่น

• I really like SQL Programming. I use it everyday.
• Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.

7️⃣ วิธีที่จะสำรวจข้อมูลประเภทนี้ทำอย่างไรได้บ้าง เช่นเดียวกับการสำรวจข้อมูลตัวเลข เช่น การตรวจดูข้อมูลว่ามีค่าว่างหรือไม่ (NULL) การจัดกลุ่มข้อมูล (GROUP BY) การนับจำนวน (COUNT) การนับจำนวนพร้อมจัดกลุ่มแบบ UNIQUE ใน SQL ใช้คำสั่ง (DISTINCT)

8️⃣ ยกตัวอย่างเช่น ดูจำนวนของรหัสไปรษณีย์ พร้อมจัดกลุ่มด้วยค่า unique

-- Find values of zip that appear in at least 100 rows
-- Also get the count of each value
SELECT DISTINCT zip, COUNT(*)
  FROM evanston311
 GROUP BY zip
HAVING COUNT(*) >= 100;

9️⃣ ปัญหาที่พบบ่อยในการจัดการข้อมูลประเภทข้อความหรือตัวอักษร รวมถึงการสำรวจข้อมูลด้วย อย่างเช่น

• ตัวอักษร ตัวพิมพ์เล็ก เช่น bangkok (lower) ตัวพิมพ์ใหญ่ เช่น BANGKOK (upper) capital เฉพาะตัวแรก เช่น Bangkok ที่อยู่ในคอลัมน์เดียวกัน แต่ลักษณะไม่เป็นอันหนึ่งอันเดียวกัน ทำให้ยากต่อการค้นหา
• ช่องว่าง (whitespace) หรือ space ในข้อความหรือคำ ช่องว่างมักจะเกิดจาก การเคาะแป้นพิมพ์ spacebar เพื่อเว้นวรรค ในบางครั้งอาจจะเกิดจากความผิดพลาดต่างๆ จากผู้ให้ข้อมูล หรือ ตัวระบบสามารถเกิดขึ้นได้ คอมพิวเตอร์มอง ช่องว่าง เป็นเสมือนหนึ่งตัวอักษร เช่นเดียวถ้าเกิดช่องว่างเวลาที่ค้นหา อาจจะทำให้ค้นหาไม่เจอ

???? วิธีแก้ไข สามารถใช้คำสั่ง SQL จัดการปัญหาเหล่านี้ได้

• trim(’ message ’) : การตัดช่องว่างของจากคำ ใน sql มีหลายคำสั่ง
  • trim ซ้าย (ltrim) ตัวอักษรตัวแรก
  • trim ขวา (rtrim) ตัวอักษรตัวสุดท้าย
  • trim ทั้งซ้ายและขวา
• upper(’message’) : เป็นการจัดการข้อความให้เป็นตัวใหญ่ทั้งหมด MESSAGE
• lower(’MESSAGE’) : เป็นการจัดการข้อความให้เป็นตัวเล็กทั้งหมด message

1️⃣1️⃣ นอกจากจะจัดการกับคำเดี่ยวๆกันแล้ว มาจัดการและสำรวจกับข้อความยาวๆบ้าง ไม่ว่าจะเป็นการนับจำนวนคำที่อยู่ในข้อความ การตัดช่องว่าง หรือการทำให้คำเป็นตัวเล็ก ตัวใหญ่ จะทำเช่นเดียวกันกับ แบบคำเดี่ยวๆ ทั้งสิ้นแต่คำสั่งที่ใช้บ่อยๆกับข้อความยาวๆ ก็คือ LIKE เป็นคำสั่งที่ช่วยในการหาคำต่างๆที่อยู่ในประโยคยาว ใช้ร่วมกับ WHERE Cluase

1️⃣2️⃣ ตัวอย่างเช่น ค้นหาคำว่า Trash ในประโยคยาวๆ

-- Select categories containing Trash or Garbage
SELECT category
  FROM evanston311
 -- Use LIKE
 WHERE category LIKE '%Trash%';

1️⃣3️⃣ นอกจากนี้ บางครั้ง ข้อความยาวๆ ก็ไม่ได้ตรงตามที่เราต้องการเสมอไป เราสามารถจัดการข้อความด้วยการ “ตัด” หรือ “ต่อ” ข้อความก็ได้ เพื่อสร้างเป็น กลุ่มคำ หรือ ข้อความที่เราต้องการ เทคนิคในการทำ จะมีอยู่หลายวิธี ขึ้นอยู่กับปัญหาต่างๆ เช่น

1️⃣4️⃣ การตัด (split)

• substring ด้วยคำสั่ง กับข้อความ ‘abcd’
  • left(‘abcd’, 2) หมายถึง ตัดทางซ้าย 2 ตำแหน่ง ผลลัพท์ คือ ab
  • right(‘abcd’, 2) หมายถึง ตัดทางขวา 2 ตำแหน่ง ผลลัพท์ คือ cd
• split_part(’a,b,cd’, ‘,’ , 2) หมายถึง แยกข้อความที่คั่นด้วย comma แล้วเลือกเฉพาะ คำในลำดับที่ 2 ผลลัพท์ คือ b

1️⃣5️⃣ การต่อ (concat) การต่อมีหลายวิธีเช่นเดียวกันกับการตัด

• การใช้เครื่องหมาย || เพื่อต่อข้อความเช่น

-- Concatenate house_num, a space, and street
-- and trim spaces from the start of the result
SELECT concat(house_num,' ', street) AS address
  FROM evanston311;

1️⃣6️⃣ โดยทั้งหมดทั้งมวลนี้ เราสามารถที่จะนำ เทคนิคต่างๆ เพื่อมาสำรวจข้อมูลที่เป็นข้อความยาวๆ แล้ว นำมาหาค่าทางสถิติได้เพื่อจะได้รู้ความหมายในข้อมูล ซึ่งบางครั้งเราไม่สามารถดูค่าทางสถิติจากข้อความยาวๆได้โดยตรง ยกตัวอย่างเช่น การหาค่าเฉลี่ย กับ ข้อความที่เป็น email หรือ เบอร์โทร ที่อยู่ใน ประโยคยาวๆได้

-- To clear table if it already exists
DROP TABLE IF EXISTS indicators;

-- Create the temp table
CREATE TEMP TABLE indicators AS
  SELECT id, 
         CAST (description LIKE '%@%' AS integer) AS email,
         CAST (description LIKE '%___-___-____%' AS integer) AS phone 
    FROM evanston311;
  
-- Select the column you'll group by
SELECT priority,
       -- Compute the proportion of rows with each indicator
       SUM(email)/COUNT(*)::numeric AS email_prop, 
       SUM(phone)/COUNT(*)::numeric AS phone_prop
  -- Tables to select from
  FROM evanston311
       LEFT JOIN indicators
       -- Joining condition
       ON evanston311.id=indicators.id
 -- What are you grouping by?
 GROUP BY priority;

1️⃣7️⃣ โดยสรุปแล้ว ในการสำรวจข้อมูล จะช่วยให้เราเข้าใจเข้าใจข้อมูลมากขึ้น ด้วยการใช้เทคนิคต่างๆ ด้วยคำสั่ง SQL และ ในการสำรวจข้อมูลประเภทตัวอักษรยังมีอีกมากมาย สิ่งสำคัญคือต้องลองเล่น และ หาตัวอย่างมาหัดทำกันนะครับ

สรุปขั้นตอนการสำรวจข้อมูลเพื่อความเข้าใจในข้อมูลวันที่และเวลาที่ซ่อนอยู่ ด้วย SQL ตอนจบ

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

2️⃣ สำหรับโพสต์นี้จะเป็นการสำรวจข้อมูลที่เป็นวันที่และเวลา ซึ่งข้อมูลเหล่านี้ เป้าหมายในการสำรวจ จะนำไปหาความหมายด้วยหลักการทางสถิติด้วยวันที่ ส่วนใหญ่จะนำไปใช้กับเรื่อง Time series analysis จะทำวิธีการทางสถิติ อย่างเช่น การนับจำนวนวัน การหาระยะห่างของวันที่ต้องการสังเกตหรือสงสัย ปริมาณของข้อมูลในแต่ละวัน ตัวอย่างเช่น

-- Count requests created on February 29, 2016
SELECT count(*)
  FROM evanston311 
 WHERE date_created >= '2016-02-29' 
   AND date_created < '2016-03-01';

3️⃣ แต่ก่อนที่จะไปการดูค่าทางสถิติ ก่อนอื่นไปจะต้องไปทำความเข้าใจในการจัดการข้อมูลวันที่และเวลาด้วย SQL กันก่อน

4️⃣ สิ่งที่จะต้องพิจารณาก่อน คือ เรื่องของ รูปแบบในการแสดงผลของ datetime จะมีอยู่ 3 กลุ่มใหญ่ ได้แก่

1. Date
   • YYYY-MM-DD เช่น 2023-07-21
2. Timestamp
   • YYYY-MM-DD HH:MM:SS เช่น 2023-07-21 12:00:00
3. Interval
   • ลักษณะการแสดงผลเป็นช่วงเวลา เช่น 1 day 01:24:00

5️⃣ รวมถึง format ที่มีความหลากหลาย ไม่ว่าจะเป็น ISO, UTC เป็นเรื่องของ format ที่จะต้องรู้เพื่อกำหนดวันและเวลาที่ถูกต้อง รวมถึง timezone ต่างๆด้วย อย่างประเทศไทย timezone จะต้องเป็น GMT+7 เป็นต้น

6️⃣ ข้อมูลประเภทของวันที่และเวลา ก็ไม่ต่างจาก ข้อมูลที่เป็นประเภทตัวเลขและตัวอักษรหรือข้อความ ซึ่งจะต้องจัดการข้อมูล อย่างเช่น การตัดคำ แยกองค์ประกอบของวันที่ และ เวลา เพื่อรวบรวมข้อมูลให้อยู่ในรูปแบบที่เหมาะสม คำสั่งในการแยก field วัน - เดือน - ปี ต่างๆ มักจะใช้คำสั่ง date_part or extract ในภาษา SQL ตัวอย่างเช่น

7️⃣ แยกวันที่ด้วยคำสั่ง EXTRACT

-- Extract the month from date_created and count requests
SELECT EXTRACT(MONTH FROM date_created) AS month, 
       date_created
  FROM evanston311
 -- Limit the date range
 WHERE date_created >= '2016-01-01'::date
   AND date_created < '2017-01-01'::date
 -- Group by what to get monthly counts?
 GROUP BY date_created;

8️⃣แยกวันที่ด้วยคำสั่ง date_part

-- Extract the month from date_created and count requests
SELECT date_part('month',date_created) AS month, 
       date_created
  FROM evanston311
 -- Limit the date range
 WHERE date_created >= '2016-01-01'
   AND date_created < '2018-01-01'
 -- Group by what to get monthly counts?
 GROUP BY month;

9️⃣ ได้คำตอบแบบเดียวกัน การแยก ตัวเลขของเดือนออกมาจาก date_created

???? บางครั้งข้อมูลประเภทวันที่ของชุดข้อมูล ก็ไม่มีข้อมูลวันที่บางวันทำให้ข้อมูลไม่ต่อเนื่อง จึงมีฟังก์ชันที่ทำขึ้นสำหรับสร้างขึ้นเอาไว้เทียบกับชุดข้อมูล ยกตัวอย่างเช่น ข้อมูลยอดขาย ช่วงเวลา 10.00 - 12.00 ไม่มี จึงทำให้ข้อมูลช่วงนี้หายไป ฟังก์ชันที่ใช้สำหรับแก้ปัญหานี้ ก็คือ generate_series ยกตัวอย่างเช่น สร้างช่วงวันเวลาขึ้นเพื่อ ดูจำนวนที่อยู่ในชุดข้อมูลมีจำนวนอยู่เท่าไร

-- Count number of requests made per day
SELECT day, COUNT(day IS NOT NULL) AS count
-- Use a daily series from 2016-01-01 to 2018-06-30 
-- to include days with no requests
  FROM (SELECT generate_series('2016-01-01',  -- series start date
                               '2018-06-30',  -- series end date
                               '1 days'::interval)::date AS day) AS daily_series
       LEFT JOIN evanston311
       -- match day from above (which is a date) to date_created
       ON day = date_created::date
 GROUP BY day;

1️⃣1️⃣ รวมถึงการใช้คำสั่ง Lead และ Lag functions ที่ช่วยให้คุณสามารถเลื่อนค่าของวันที่ ให้เรียงลำดับในคอลัมน์ได้ 1 แถว เพื่อค้นหาว่าเวลาผ่านไประหว่างช่วงเวลานั้นว่าเป็นอย่างไร ใช้คำนวณการเปลี่ยนแปลงในชุดเวลา เพื่อเปรียบเทียบค่าปัจจุบันกับค่าก่อนหน้าหรือในอนาคต ยกตัวอย่างเช่น

-- Compute the gaps
WITH request_gaps AS (
        SELECT date_created,
               -- lead or lag
               LAG(date_created) OVER (ORDER BY date_created) AS previous,
               -- compute gap as date_created minus lead or lag
               date_created - LAG(date_created) OVER (ORDER BY date_created) AS gap
          FROM evanston311)
-- Select the row with the maximum gap
SELECT *
  FROM request_gaps
-- Subquery to select maximum gap from request_gaps
 WHERE gap = (SELECT max(gap)
                FROM request_gaps);

1️⃣2️⃣ โดยสรุปแล้ว ในการสำรวจข้อมูล จะช่วยให้เราเข้าใจเข้าใจข้อมูลมากขึ้น ด้วยการใช้เทคนิคต่างๆ ด้วยคำสั่ง SQL และ ในการสำรวจข้อมูลประเภทวันที่และเวลายังมีอีกมากมาย สิ่งสำคัญคือต้องลองเล่น และ หาตัวอย่างมาหัดทำกันนะครับ

1️⃣3️⃣ จบไปแล้วสำหรับ การสำรวจข้อมูลด้วย SQL (exploratory-data-analysis-in-sql) หากใครที่ทำงานเกี่ยวกับ data คำแนะนำที่จำเป็นต่อการวิเคราะห์ข้อมูลในอนาคต ได้แก่

• ความกระหายใคร่รู้ สงสัย และ ตั้งคำถาม
• แบ่งเวลามาสำรวจลักษณะของข้อมูล
• ดูการกระจายของข้อมูลด้วยหลักการทางสถิติก่อนเริ่มทำการสำรวจ
• อ่าน document ของแต่ละ platform

หวังว่าจะเป็นประโยชน์

???? สุดท้ายนี้ เป็นอย่างไรกันบ้างครับ กับ บทความทั้ง 4 บทในเรื่องของการสำรวจข้อมูล exploratory-data-analysis-in-sql อยากขอความคิดเห็นว่า เขียนดีมั้ย ตัวอย่างเป็นอย่างไร อยากให้เพิ่มเติมอะไรหรือไม่ อยากอ่านเรื่องอะไรกันบ้าง comment บอกกันได้เลยครับ

แวะมาทักทายกันได้