PostgreSQL Administration & SQL Query for Reporting 2026

Section 1: PostgreSQL Architecture และวงจรเวอร์ชัน

• โครงสร้าง Cluster, Database, Schema และ Table และความสัมพันธ์ระหว่างกัน
• Process หลัก Shared Buffers และ WAL โดยสรุป และ MVCC กับเหตุผลที่ต้องมี Vacuum
• นโยบายสนับสนุน 5 ปี PostgreSQL 18 รุ่นเสถียรปัจจุบัน และ PostgreSQL 19 ที่อยู่ระหว่าง Beta
• ความเสี่ยงของการใช้เวอร์ชันที่หมดอายุการสนับสนุน

Section 2: Lab: ติดตั้ง PostgreSQL 18 และเครื่องมือ

• รัน PostgreSQL 18 ด้วย Docker พร้อม Volume เก็บข้อมูลถาวร
• ใช้ psql อย่างมืออาชีพ: l, dn, dt, d, x และการตั้งค่าการแสดงผล
• ใช้ pgAdmin 4: ลงทะเบียนเซิร์ฟเวอร์ Query Tool และ ERD Tool สำหรับอ่านโครงสร้างตาราง
• โหลดฐานข้อมูลตัวอย่าง NovaMart และสำรวจโครงสร้างตาราง

Section 3: Configuration ที่ส่งผลต่องานรายงาน

• ไฟล์ postgresql.conf, ALTER SYSTEM และความต่างของ Reload กับ Restart
• work_mem กับการเรียงลำดับและการสรุปข้อมูลขนาดใหญ่ใน Query รายงาน
• statement_timeout สำหรับป้องกัน Query รายงานที่ทำงานนานเกินไป
• ตั้งค่า Log เพื่อเก็บ Query ที่ช้าไว้วิเคราะห์

Section 4: Roles, Privileges และ Schemas สำหรับทีมรายงาน

• Role, User และ Group ใน PostgreSQL และการสืบทอดสิทธิ์
• GRANT และ REVOKE ระดับ Database, Schema, Table และ Column
• Role Read-only สำหรับรายงาน และ ALTER DEFAULT PRIVILEGES สำหรับตารางในอนาคต
• Predefined Role เช่น pg_read_all_data และ pg_monitor
• ตั้งค่า pg_hba.conf และ SCRAM-SHA-256 ให้เครื่องผู้ใช้เชื่อมต่อจากระยะไกล

Section 5: Lab: สำรองและกู้คืนข้อมูลพื้นฐาน

• pg_dump และ pg_restore: รูปแบบไฟล์ และการกู้คืนเฉพาะบางตาราง
• pg_dumpall สำหรับสำรอง Role และ Global Object
• แนวทางทำสำเนาฐานข้อมูลสำหรับงานรายงาน แยกจากฐานข้อมูลหลักของระบบงาน
• ANALYZE และสถิติที่ Query Planner ใช้ หลังโหลดข้อมูลชุดใหญ่

Section 6: Workshop Day 1: NovaMart Sales Reporting

• ติดตั้ง PostgreSQL 18 ด้วย Docker และโหลดฐานข้อมูล NovaMart
• สร้าง Schema sales และ report
• สร้าง Role report_reader ที่อ่านข้อมูลได้อย่างเดียว แล้วทดสอบเชื่อมต่อจาก pgAdmin 4
• Workshop: สำรองและกู้คืนฐานข้อมูลเพื่อเตรียมใช้ในวันถัดไป

Section 7: ทำความเข้าใจโครงสร้างข้อมูลก่อนเขียน Query

• อ่าน ERD: Primary Key, Foreign Key และความสัมพันธ์แบบ One-to-Many
• ชนิดข้อมูลที่พบบ่อยในรายงาน: integer, numeric, text, date, timestamp และ timestamptz
• ความหมายของ NULL และผลต่อการเปรียบเทียบและการคำนวณ
• ทบทวน SELECT, WHERE, ORDER BY, LIMIT และ DISTINCT

Section 8: การกรองและจัดรูปแบบข้อมูล

• ตัวดำเนินการ IN, BETWEEN, LIKE/ILIKE และ IS NULL
• CASE WHEN สำหรับจัดกลุ่มข้อมูล เช่น ช่วงยอดซื้อ หรือระดับลูกค้า
• ฟังก์ชันวันที่: date_trunc, extract, interval, to_char และ AT TIME ZONE
• ฟังก์ชันข้อความและตัวเลข: concat, trim, round และการแปลงชนิดข้อมูลด้วย CAST
• COALESCE และ NULLIF สำหรับจัดการค่าว่างและป้องกันการหารด้วยศูนย์

Section 9: การเชื่อมข้อมูลหลายตารางด้วย JOIN

• INNER JOIN, LEFT JOIN, RIGHT JOIN และ FULL JOIN
• Self Join และการเชื่อมตารางมากกว่าสองตาราง
• ข้อผิดพลาดที่พบบ่อย: ข้อมูลซ้ำจากการ JOIN ผิดระดับ และยอดรวมที่สูงเกินจริง
• หาข้อมูลที่ไม่มีคู่ เช่น ลูกค้าที่ไม่เคยสั่งซื้อ หรือสินค้าที่ไม่มียอดขาย

Section 10: การสรุปข้อมูลด้วย Aggregate

• COUNT, SUM, AVG, MIN, MAX และ COUNT(DISTINCT)
• GROUP BY หลายคอลัมน์ และ HAVING สำหรับกรองผลสรุป
• FILTER clause สำหรับนับหรือรวมหลายเงื่อนไขใน Query เดียว
• string_agg สำหรับรวมรายการเป็นข้อความเดียว

Section 11: Subquery และ CTE

• Subquery ใน WHERE, FROM และ SELECT
• EXISTS และ NOT EXISTS
• WITH (CTE) แยก Query ซับซ้อนเป็นขั้นตอนที่อ่านและตรวจสอบง่าย
• generate_series สร้างตารางปฏิทิน เติมเดือนที่ไม่มียอดขายให้รายงานครบทุกช่วง

Section 12: Workshop Day 2: ชุด Query รายงานยอดขาย

• ยอดขายรายเดือนแยกสาขาและหมวดสินค้า
• สินค้าขายดี 10 อันดับ และลูกค้าที่ซื้อเพียงครั้งเดียว
• ยอดขายรายเดือนที่แสดงครบทุกเดือนแม้เดือนนั้นไม่มียอดขาย
• Workshop: ตรวจสอบความถูกต้องของยอดรวมในทุกรายงาน

Section 13: Window Functions สำหรับรายงานเชิงวิเคราะห์

• แนวคิด OVER, PARTITION BY และ ORDER BY และความต่างจาก GROUP BY
• ROW_NUMBER, RANK และ DENSE_RANK สำหรับจัดอันดับภายในกลุ่ม
• ยอดสะสม (Running Total) และค่าเฉลี่ยเคลื่อนที่ (Moving Average)
• LAG และ LEAD สำหรับเปรียบเทียบเดือนต่อเดือนและปีต่อปี
• NTILE สำหรับแบ่งกลุ่มลูกค้าตามยอดซื้อ

Section 14: รายงานสรุปหลายระดับและตารางแบบ Pivot

• ROLLUP สำหรับยอดรวมย่อยและยอดรวมทั้งหมด
• CUBE และ GROUPING SETS สำหรับสรุปหลายมิติใน Query เดียว
• ฟังก์ชัน GROUPING เพื่อแยกแถวยอดรวมออกจากแถวข้อมูล
• ทำตารางแบบ Pivot ด้วย FILTER และด้วย crosstab จาก Extension tablefunc

Section 15: View และ Materialized View สำหรับชั้นข้อมูลรายงาน

• สร้าง View ใน Schema report และให้สิทธิ์เฉพาะ View แก่ทีมรายงาน
• Materialized View สำหรับรายงานที่คำนวณหนัก
• REFRESH MATERIALIZED VIEW และ CONCURRENTLY เพื่อไม่ให้รบกวนผู้ใช้งาน
• แนวทางตั้งชื่อและจัดระเบียบ View สำหรับรายงาน

Section 16: ประสิทธิภาพของ Query รายงาน

• อ่านแผนการทำงานด้วย EXPLAIN และ EXPLAIN ANALYZE
• Index ที่ช่วยรายงาน: คอลัมน์วันที่ คอลัมน์ที่ใช้ JOIN และ Composite Index
• เขียนเงื่อนไขให้ใช้ Index ได้ และเลี่ยงการครอบคอลัมน์ด้วยฟังก์ชันโดยไม่จำเป็น
• หา Query ที่ใช้ทรัพยากรมากด้วย pg_stat_statements และดู Session ด้วย pg_stat_activity

Section 17: Lab: ส่งออกข้อมูลและเชื่อมต่อ Excel

• ส่งออก CSV ด้วย copy และ COPY และความต่างระหว่างสองคำสั่ง
• จัดการภาษาไทยให้แสดงถูกต้องเมื่อเปิด CSV ด้วย Excel (UTF-8 Encoding)
• ส่งออกผลลัพธ์จาก Query Tool ของ pgAdmin 4
• ติดตั้ง psqlODBC และสร้าง Data Source สำหรับ Role report_reader
• ดึงข้อมูลจาก View เข้า Excel ด้วย Power Query และตั้งค่ารีเฟรชข้อมูล

Section 18: Master Workshop: NovaMart Executive Report

• สร้าง Schema report: View ยอดขาย MoM อันดับสินค้าในแต่ละหมวด และยอดสะสมรายสาขา
• สร้าง Materialized View รายงานสรุปหลายระดับ
• ตรวจ EXPLAIN ANALYZE และเพิ่ม Index ให้ Query ที่ช้า
• Workshop: ส่งออก CSV และสร้างไฟล์ Excel รายงานผู้บริหารที่รีเฟรชผ่าน Power Query ได้เอง