7. วันและเวลา

วัตถุประสงค์การเรียนรู้

เมื่อจบบทนี้ ผู้เรียนจะสามารถ

1. ดึงองค์ประกอบวันที่ (ปี เดือน วัน ชั่วโมง วันในสัปดาห์) ด้วย EXTRACT, DATE_PART และ DATE_TRUNC
2. คำนวณช่วงห่างระหว่างวันที่ด้วย DATE_DIFF
3. ดึงองค์ประกอบวันที่ด้วยฟังก์ชัน strftime

บทนำ

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

ตัวอย่างเช่น คำถามอย่าง “สัปดาห์ใดมียอดขายดีที่สุด และเหตุใดจึงเป็นเช่นนั้น” หรือ “ลูกค้าที่สั่งซื้อบ่อยที่สุดกลับมาซื้อซ้ำทุกกี่วัน” ล้วนเป็นคำถามที่ต้องอาศัยมุมมองด้านเวลาเข้ามาช่วยตีความ ไม่สามารถตอบได้อย่างครบถ้วนด้วยการสรุปยอดขายรวมเพียงอย่างเดียว

บทนี้จึงแนะนำฟังก์ชันสำคัญสำหรับการวิเคราะห์ข้อมูลวันเวลา ได้แก่ EXTRACT, DATE_PART, DATE_TRUNC, DATE_DIFF, strftime และ LAG ซึ่งช่วยเปลี่ยนข้อมูล timestamp ให้กลายเป็นเครื่องมือสำหรับตอบคำถามเชิงธุรกิจได้อย่างเป็นระบบ เช่น ช่วงเวลาใดคือ peak hour ของร้าน รายได้เติบโตจากสัปดาห์หนึ่งไปสู่อีกสัปดาห์หนึ่งหรือไม่ และลูกค้ากลุ่มใดเริ่มมีแนวโน้มกลับมาซื้อซ้ำน้อยลง

EXTRACT และ DATE_PART เพื่ออ่านองค์ประกอบวันที่

EXTRACT และ DATE_PART ทำงานเหมือนกันทุกประการ คือดึงส่วนหนึ่งออกมาจากค่าวันที่หรือเวลา ต่างกันเพียงรูปแบบการเขียน ทั้งสองฟังก์ชันเป็นพื้นฐานของการสร้างมิติเวลาที่จะถูกนำไปใช้ใน GROUP BY, CASE และการสร้างตารางสรุปในส่วนถัดไป

เริ่มจากการดึงส่วนประกอบพื้นฐานของวันที่

SELECT
 order_datetime,
 EXTRACT(year    FROM order_datetime) AS ปี,
 EXTRACT(month   FROM order_datetime) AS เดือน,
 EXTRACT(day     FROM order_datetime) AS วัน,
 EXTRACT(hour    FROM order_datetime) AS ชั่วโมง,
 EXTRACT(minute  FROM order_datetime) AS นาที,
 EXTRACT(quarter FROM order_datetime) AS ไตรมาส,
 EXTRACT(week    FROM order_datetime) AS สัปดาห์ที่,
 EXTRACT(dow     FROM order_datetime) AS วันในสัปดาห์  -- 0=อาทิตย์, 6=เสาร์
FROM transactions
LIMIT 5;

เขียนโดยใช้ DATE_PART

SELECT
 order_datetime,
 DATE_PART('year',    order_datetime) AS ปี,
 DATE_PART('month',   order_datetime) AS เดือน,
 DATE_PART('day',     order_datetime) AS วัน,
 DATE_PART('hour',    order_datetime) AS ชั่วโมง,
 DATE_PART('minute',  order_datetime) AS นาที,
 DATE_PART('quarter', order_datetime) AS ไตรมาส,
 DATE_PART('week',    order_datetime) AS สัปดาห์ที่,
 DATE_PART('dow',     order_datetime) AS วันในสัปดาห์ -- 0=อาทิตย์, 6=เสาร์
FROM transactions
LIMIT 5;

order_datetime	ปี	เดือน	วัน	ชั่วโมง	นาที	ไตรมาส	สัปดาห์ที่	วันในสัปดาห์
2026-02-01 09:01:32	2026	2	1	9	1	1	5	0
2026-02-01 09:04:22	2026	2	1	9	4	1	5	0
2026-02-01 09:07:36	2026	2	1	9	7	1	5	0
2026-02-01 09:12:45	2026	2	1	9	12	1	5	0
2026-02-01 09:13:04	2026	2	1	9	13	1	5	0

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

ข้อแตกต่างด้านรูปแบบ

• EXTRACT(month FROM col) เป็น SQL มาตรฐาน
  
• DATE_PART('month', col) ใส่ field name ในเครื่องหมาย single quote

ก่อนเริ่มวิเคราะห์ temporal pattern ต้องรู้ก่อนว่าข้อมูลครอบคลุมช่วงเวลาเท่าไหร่ และมีวันที่ขาดหายไปหรือไม่ ถ้าไม่ตรวจจุดนี้ก่อน การตีความ trend อาจผิดได้ง่าย โดยเฉพาะเมื่อข้อมูลช่วงต้นหรือท้ายไม่เต็มสัปดาห์หรือไม่เต็มเดือน

-- สรุปช่วงเวลาและจำนวนวันที่มีธุรกรรมในฐานข้อมูล
SELECT
 MIN(order_date) AS first_date, -- วันที่มีคำสั่งซื้อแรกสุด
 MAX(order_date) AS last_date, -- วันที่มีคำสั่งซื้อล่าสุด
 MAX(order_date) - MIN(order_date) AS date_span_days, -- จำนวนวันทั้งหมดของช่วงข้อมูล
 COUNT(DISTINCT order_date) AS num_active_days -- จำนวนวันที่มีคำสั่งซื้อจริง
FROM transactions; -- ตารางหลักที่เก็บข้อมูลธุรกรรม

first_date	last_date	date_span_days	num_active_days
2026-02-01	2026-04-01	59	60

ข้อมูลครอบคลุม 60 วัน (1 ก.พ. ถึง 1 เม.ย. 2026) และมียอดขายทุกวันไม่ขาด ค่า date_span_days = 59 เป็นผลต่างของวันต้นทางและปลายทาง ขณะที่ num_active_days = 60 นับจำนวนวันจริงแบบรวมปลายทั้งสองด้าน จึงยืนยันได้ว่าทุกวันมีออเดอร์

เมื่อเข้าใจการดึง year, month และ day มาดูการจัดกลุ่มยอดขายรายเดือน เพื่อดูแนวโน้มภาพใหญ่ของธุรกิจได้

-- สรุปจำนวนออเดอร์และยอดขายแยกรายเดือน
SELECT
 EXTRACT(year FROM order_datetime)   AS ปี,            -- ดึงเลขเดือนจากวันที่-เวลาสั่งซื้อ
 EXTRACT(month FROM order_datetime)  AS เดือน,          -- ดึงเลขเดือนจากวันที่-เวลาสั่งซื้อ
 COUNT(*)                            AS จำนวนออเดอร์,    -- จำนวนคำสั่งซื้อในแต่ละเดือน
 ROUND(SUM(net_sales_thb), 0)        AS ยอดขายรวม_บาท,  -- ยอดขายรวมของแต่ละเดือน (บาท)
 ROUND(AVG(net_sales_thb), 0)        AS ยอดขายเฉลี่ย_บาท -- มูลค่าคำสั่งซื้อเฉลี่ยของแต่ละเดือน (บาท)
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
GROUP BY EXTRACT(year FROM order_datetime), EXTRACT(month FROM order_datetime) -- จัดกลุ่มตามเดือน
ORDER BY เดือน; -- เรียงตามลำดับเดือน

ปี	เดือน	จำนวนออเดอร์	ยอดขายรวม_บาท	ยอดขายเฉลี่ย_บาท
2026	2	24,420	4,905,954.0	201.0
2026	3	27,805	5,660,917.0	204.0
2026	4	1,098	220,531.0	201.0

DATE_DIFF

ในกรณีที่ต้องการดูว่าลูกค้า gold, silver, member หายไปนานแค่ไหนแล้ว Recency เป็นตัวชี้วัดที่สำคัญกว่าจำนวนออเดอร์ เพราะลูกค้าที่เคยสั่งบ่อยแต่หายไปนาน มีค่าควรดึงกลับมากกว่าลูกค้าที่ยังสั่งอยู่เป็นประจำ

-- วิเคราะห์ความถี่และมูลค่าการซื้อของลูกค้าแยกตามระดับสมาชิก (Customer Recency Analysis)
WITH customer_recency AS (
 SELECT
  t.customer_id, -- รหัสลูกค้า
  c.loyalty_tier, -- ระดับสมาชิก
  c.age_group, -- กลุ่มอายุ
  MAX(t.order_date) AS last_order_date, -- วันที่สั่งซื้อล่าสุด
  MIN(t.order_date) AS first_order_date, -- วันที่สั่งซื้อครั้งแรก
  COUNT(*) AS total_orders, -- จำนวนคำสั่งซื้อรวมของลูกค้า
  ROUND(SUM(t.net_sales_thb), 0) AS total_spend, -- ยอดใช้จ่ายรวมของลูกค้า (บาท)
  DATE_DIFF('day', MAX(t.order_date), DATE '2026-04-01') AS days_since_last, -- จำนวนวันนับจากสั่งซื้อล่าสุดถึง 1 เม.ย. 2026
  DATE_DIFF('day', MIN(t.order_date), MAX(t.order_date)) AS customer_lifespan_days -- ระยะเวลาตั้งแต่ซื้อครั้งแรกถึงครั้งล่าสุด (วัน)
 FROM transactions t
 JOIN customers c ON t.customer_id = c.customer_id -- เชื่อมตารางลูกค้าเพื่อดึงระดับสมาชิกและกลุ่มอายุ
 GROUP BY t.customer_id, c.loyalty_tier, c.age_group -- จัดกลุ่มตามลูกค้าแต่ละราย
)
SELECT
 loyalty_tier, -- ระดับสมาชิก
 COUNT(*) AS num_customers, -- จำนวนลูกค้าในแต่ละระดับ
 ROUND(AVG(days_since_last), 1) AS avg_recency_days, -- จำนวนวันเฉลี่ยนับจากสั่งซื้อล่าสุด (ยิ่งน้อยยิ่งดี)
 ROUND(AVG(customer_lifespan_days), 1) AS avg_lifespan_days, -- อายุลูกค้าเฉลี่ย (วัน)
 ROUND(AVG(total_orders), 1) AS avg_orders, -- จำนวนคำสั่งซื้อเฉลี่ยต่อลูกค้า
 ROUND(AVG(total_spend), 0) AS avg_spend -- ยอดใช้จ่ายเฉลี่ยต่อลูกค้า (บาท)
FROM customer_recency
GROUP BY loyalty_tier -- จัดกลุ่มตามระดับสมาชิก
ORDER BY avg_recency_days; -- เรียงจากระดับที่มีลูกค้า active มากสุด (recency น้อยสุด) ไปมากสุด

loyalty_tier	num_customers	avg_recency_days	avg_lifespan_days	avg_orders	avg_spend
member	704	8	25.7	6.7	1,376
none	456	8.1	25	6.6	1,336
gold	344	8.6	24.3	6.2	1,250
silver	607	9	24.8	6.4	1,279

ลูกค้าทุก tier สั่งล่าสุดเฉลี่ย 8–9 วันก่อนวันอ้างอิง (1 เม.ย.) member สดสุดที่ 8.0 วัน gold 8.6 วัน silver ช้าสุดที่ 9.0 วัน อายุการใช้งานเฉลี่ย 24–26 วัน ซึ่งเกือบเท่ากับระยะข้อมูล 60 วัน

ทุก tier มี recency ใกล้เคียงกัน loyalty tier ไม่ได้ทำให้ลูกค้ากลับมาบ่อยกว่าอย่างมีนัยสำคัญ member ดีกว่า gold เพียง 0.6 วัน ซึ่งอาจเป็นเพราะโปรแกรม loyalty เพิ่งเริ่มต้น (ข้อมูลเพียง 2 เดือน) ยังไม่มีเวลาสร้างความแตกต่าง

recency เฉลี่ย 8 วันเป็น benchmark ที่ใช้ตั้ง threshold ได้ ถ้าลูกค้าไม่สั่งเกิน 14 วัน (2 เท่าของค่าเฉลี่ย) ควรถือว่า “at risk” และส่ง re-engagement campaign ทั้งนี้ ต้องระวังว่า avg_recency ตัวเลขนี้ bias ไปทางลูกค้าที่ยังอยู่ในระบบ ลูกค้าที่หายไปตั้งแต่เดือน ก.พ. จะดึง avg ขึ้น ควรดู distribution (median, percentile) ร่วมด้วย

เมื่อรู้ค่า recency เฉลี่ยของแต่ละ tier แล้ว ขั้นต่อไปคือแบ่งลูกค้าออกเป็น bucket ตามช่วงวันที่หายไป เพื่อให้ทีมการตลาดออกแคมเปญได้ตรงกลุ่มมากขึ้น

ต้องการแบ่งลูกค้าเป็น Active, Recent, At Risk และ Churned เพื่อให้ทีมการตลาดออก campaign ที่เหมาะสมกับแต่ละกลุ่ม โดยใช้ CASE expression สร้าง recency bucket

-- จัดกลุ่มลูกค้าตามความถี่การกลับมาซื้อล่าสุด (Recency Segmentation) แยกตามระดับสมาชิก
WITH customer_last AS (
 SELECT
  t.customer_id, -- รหัสลูกค้า
  c.loyalty_tier, -- ระดับสมาชิก
  MAX(t.order_date) AS last_order_date, -- วันที่สั่งซื้อล่าสุด
  DATE_DIFF('day', MAX(t.order_date), DATE '2026-04-01') AS days_since_last -- จำนวนวันนับจากสั่งซื้อล่าสุดถึง 1 เม.ย. 2026
 FROM transactions t
 JOIN customers c ON t.customer_id = c.customer_id -- เชื่อมตารางลูกค้าเพื่อดึงระดับสมาชิก
 GROUP BY t.customer_id, c.loyalty_tier -- จัดกลุ่มตามลูกค้าแต่ละราย
)
SELECT
 loyalty_tier, -- ระดับสมาชิก
 CASE
  WHEN days_since_last <= 7  THEN 'Active (0-7 days)'    -- ลูกค้า Active: ซื้อภายใน 7 วัน
  WHEN days_since_last <= 14 THEN 'Recent (8-14 days)'   -- ลูกค้า Recent: ซื้อภายใน 8-14 วัน
  WHEN days_since_last <= 30 THEN 'At Risk (15-30 days)' -- ลูกค้าเสี่ยงหาย: ซื้อภายใน 15-30 วัน
  ELSE 'Churned (31+ days)'                              -- ลูกค้าหาย: ไม่ซื้อมากกว่า 30 วัน
 END AS recency_bucket,
 COUNT(*) AS num_customers, -- จำนวนลูกค้าในแต่ละกลุ่ม
 ROUND(AVG(days_since_last), 1) AS avg_days -- จำนวนวันเฉลี่ยนับจากสั่งซื้อล่าสุด
FROM customer_last
GROUP BY loyalty_tier, recency_bucket -- จัดกลุ่มตามระดับสมาชิกและกลุ่ม Recency
ORDER BY loyalty_tier, avg_days; -- เรียงตามระดับสมาชิก จากนั้นเรียงตาม Recency จากใหม่สุด

loyalty_tier	recency_bucket	num_customers	avg_days
gold	Active (0-7 days)	219	2.5
gold	Recent (8-14 days)	52	11
gold	At Risk (15-30 days)	56	21.8
gold	Churned (31+ days)	17	36.7
member	Active (0-7 days)	481	2.5
member	Recent (8-14 days)	91	10.9
member	At Risk (15-30 days)	93	21.7
member	Churned (31+ days)	39	36.7
none	Active (0-7 days)	303	2.3
none	Recent (8-14 days)	60	10.7
none	At Risk (15-30 days)	66	20.4
none	Churned (31+ days)	27	36.8
silver	Active (0-7 days)	368	2.5
silver	Recent (8-14 days)	109	10.6
silver	At Risk (15-30 days)	91	21.5
silver	Churned (31+ days)	39	36.9

ลูกค้า Active (0–7 วัน) มีสัดส่วนมากที่สุดทุก tier โดยมี gold 219 คน (64%), member 481 คน (68%), silver 368 คน (61%), none 303 คน (66%) Churned (31+ วัน) มีสัดส่วนต่ำ 4–6% ทุก tier

ทุก tier มี distribution คล้ายกัน โดยมี Active ~65%, Recent ~14%, At Risk ~14%, Churned ~5% ซึ่งหมายความว่า loyalty tier ไม่ได้ช่วยรักษาลูกค้าให้ active มากกว่ากลุ่มอื่น นี่อาจเป็นเพราะโปรแกรม loyalty ยังใหม่เกินไป หรือ benefits ยังไม่ differentiating พอ

ต้องมีกลไกอื่นนอกจาก loyalty tier เพื่อดึงลูกค้ากลับ เช่น personalized timing-based offers ถ้าลูกค้าคนหนึ่งปกติสั่งทุก 4 วัน แต่หายไป 10 วัน ระบบควรส่ง offer อัตโนมัติ ทั้งนี้ ก่อนสรุปว่า loyalty tier “ไม่ช่วย” ควรรอข้อมูลอย่างน้อย 6 เดือนเพื่อให้ tier มีเวลาสร้างผล กลุ่ม Churned ของ gold (17 คน) มีจำนวนน้อยมาก ควรวิเคราะห์เป็นรายบุคคลว่าทำไมถึงหายไป

DATE_TRUNC

DATE_TRUNC() ใช้ “ปัด” วัน–เวลาให้เหลือระดับความละเอียดตามหน่วยที่ระบุ (year, quarter, month, week, day, hour) โดยเริ่มจากวันที่เดียวกันคือ 14 มีนาคม 2026

ตัวอย่างต่อไปนี้แสดงการใช้ฟังก์ชัน DATE_TRUNC() เพื่อตัดค่าวันที่–เวลาให้ตรงกับ “จุดเริ่มต้น” ของช่วงเวลาแต่ละระดับ โดยเริ่มจากวันที่เดียวกัน คือ 14 มีนาคม 2026 เวลา 14:30:15

                            2026
      January               February               March
Mo Tu We Th Fr Sa Su  Mo Tu We Th Fr Sa Su  Mo Tu We Th Fr Sa Su
          1  2  3  4                     1                     1
 5  6  7  8  9 10 11   2  3  4  5  6  7  8   2  3  4  5  6  7  8
12 13 14 15 16 17 18   9 10 11 12 13 14 15   9 10 11 12 13 14 15
19 20 21 22 23 24 25  16 17 18 19 20 21 22  16 17 18 19 20 21 22
26 27 28 29 30 31     23 24 25 26 27 28     23 24 25 26 27 28 29
                                            30 31

SELECT
    DATE_TRUNC('year',    DATE '2026-03-14 14:30:15') AS year_start,
    DATE_TRUNC('quarter', DATE '2026-03-14 14:30:15') AS quarter_start,
    DATE_TRUNC('month',   DATE '2026-03-14 14:30:15') AS month_start,
    DATE_TRUNC('week',    DATE '2026-03-14 14:30:15') AS week_start

ผลลัพธ์ที่ได้มีลักษณะดังนี้

year_start	quarter_start	month_start	week_start
2026-01-01 00:00:00	2026-01-01 00:00:00	2026-03-01 00:00:00	2026-03-09 00:00:00

สามารถอธิบายความหมายของแต่ละคอลัมน์ได้ดังนี้

• year_start
  ใช้ DATE_TRUNC('year', …) เพื่อตัดวันที่และเวลาให้ตรงกับ “วันแรกของปี” ดังนั้นค่าจากวันที่ 14 มีนาคม 2026 จะถูกปรับให้เป็น 1 มกราคม 2026 เวลา 00:00:00

• quarter_start
  ใช้ DATE_TRUNC('quarter', …) เพื่อตัดให้ตรงกับ “วันแรกของไตรมาส” ของปีนั้น วันที่ 14 มีนาคม 2026 อยู่ในไตรมาสที่ 1 (มกราคม–มีนาคม) จึงถูกปรับให้เป็น 1 มกราคม 2026 เวลา 00:00:00 ซึ่งตรงกับวันเริ่มต้นของไตรมาสที่ 1

• month_start
  ใช้ DATE_TRUNC('month', …) เพื่อตัดให้ตรงกับ “วันแรกของเดือน” ดังนั้น 14 มีนาคม 2026 จะถูกปรับให้เป็น 1 มีนาคม 2026 เวลา 00:00:00

• week_start
  ใช้ DATE_TRUNC('week', …) เพื่อตัดให้ตรงกับ “วันแรกของสัปดาห์” ตามนิยามของระบบ (เช่น เริ่มต้นวันจันทร์) สำหรับตัวอย่างนี้ วันที่ 14 มีนาคม 2026 (วันเสาร์) จะถูกปรับย้อนกลับไปเป็นวันจันทร์ในสัปดาห์เดียวกันคือวันที่ 9 มีนาคม 2026 เวลา 00:00:00

การดูรายได้รายวันช่วยให้เห็นจังหวะสั้น ๆ ของธุรกิจ แต่ถ้าต้องการดูแนวโน้มที่ชัดขึ้น เรามักต้องสรุปข้อมูลเป็นรายสัปดาห์ DATE_TRUNC('week', ...) ช่วยให้ทุกธุรกรรมในสัปดาห์เดียวกันถูกจัดไปรวมอยู่ใต้วันเริ่มต้นสัปดาห์เดียวกัน

-- สรุปยอดขายและอัตรากำไรส่วนเกินรายสัปดาห์
SELECT
 DATE_TRUNC('week', order_date) AS week_start, -- วันเริ่มต้นของสัปดาห์ (เริ่มนับจากวันจันทร์)
 COUNT(*) AS order_count, -- จำนวนคำสั่งซื้อในสัปดาห์
 ROUND(SUM(net_sales_thb), 0) AS total_revenue, -- ยอดขายรวมของสัปดาห์ (บาท)
 ROUND(AVG(net_sales_thb), 0) AS avg_order_value, -- มูลค่าคำสั่งซื้อเฉลี่ย (บาท)
 ROUND(100.0 * SUM(contribution_margin_thb) / SUM(net_sales_thb), 1) AS cm_pct -- อัตรากำไรส่วนเกิน (%)
FROM transactions -- ตารางหลักที่เก็บข้อมูลธุรกรรม
GROUP BY week_start -- จัดกลุ่มตามสัปดาห์
ORDER BY week_start; -- เรียงตามลำดับสัปดาห์จากเก่าสุดไปใหม่สุด

week_start	order_count	total_revenue	avg_order_value	cm_pct
2026-01-26	721	147,428	204	53.4%
2026-02-02	4,762	946,918	199	53.8%
2026-02-09	6,541	1,319,122	202	53.7%
2026-02-16	7,447	1,505,723	202	54.0%
2026-02-23	5,773	1,158,533	201	54.1%
2026-03-02	5,401	1,094,839	203	54.4%
2026-03-09	6,357	1,285,490	202	54.4%
2026-03-16	6,270	1,273,984	203	54.3%
2026-03-23	7,037	1,446,546	206	54.3%
2026-03-30	3,014	608,819	202	54.7%

สัปดาห์แรกและสุดท้ายเป็นข้อมูลไม่เต็มสัปดาห์

สัปดาห์ 2026-01-26 มีเพียง 721 orders (รายได้ 147,428 บาท) และสัปดาห์ 2026-03-30 มีเพียง 3,014 orders เนื่องจากข้อมูลครอบคลุมเฉพาะ 1 ก.พ. – 1 เม.ย. 2026 จึงควรตัดสองสัปดาห์นี้ออกเมื่อเปรียบเทียบ trend เพื่อไม่ให้เกิด bias

Peak week คือสัปดาห์ 16 ก.พ. รายได้สูงสุดที่ 1.5 ล้านบาท

สัปดาห์ 2026-02-16 มีทั้ง order count (7,447) และ total revenue (1,505,723 บาท) สูงสุดในช่วงข้อมูล ตรงกับช่วงวาเลนไทน์ (14 ก.พ.) ซึ่งน่าจะมีผลดึงดูดลูกค้าเป็นพิเศษ และยังมีสัปดาห์ 2026-03-23 ที่ใกล้เคียง (7,037 orders, 1,446,546 บาท)

รายได้ดิปลงในช่วงปลาย ก.พ. – ต้น มี.ค.

หลัง Peak week รายได้ลดลงชัดเจนในสัปดาห์ 23 ก.พ. (1,158,533 บาท, –23%) และ 2 มี.ค. (1,094,839 บาท) ก่อนจะฟื้นตัวขึ้นอีกครั้งในเดือนมีนาคม แนวโน้มนี้อาจสะท้อนภาวะปกติหลังเทศกาล หรือลูกค้า loyalty ที่ใช้ points ไปแล้วในช่วงวาเลนไทน์

Contribution Margin ค่อย ๆ ดีขึ้นอย่างต่อเนื่อง

cm_pct เพิ่มจาก 53.4% ในสัปดาห์แรกเป็น 54.7% ในสัปดาห์สุดท้าย แม้จะดูเป็นตัวเลขเล็กน้อย แต่การที่ margin ขยับขึ้นทุกสัปดาห์บ่งชี้ว่า PiCha ควบคุม COGS ได้ดีขึ้น หรือ product mix เปลี่ยนไปในทางที่มี margin สูงขึ้น (เช่น สินค้า premium หรือ topping add-on เพิ่มขึ้น)

AOV เสถียรมากตลอดช่วงเวลา

avg_order_value อยู่ระหว่าง 199–206 บาทตลอด 10 สัปดาห์ แสดงว่า revenue ที่ขึ้นลงมาจาก volume (จำนวน order) ไม่ใช่ราคาต่อ order PiCha จึงควรโฟกัสกลยุทธ์ที่เพิ่ม frequency และ traffic มากกว่าการขึ้นราคา

ตัวอย่างการนำข้อมูลเหล่านี้ไปใช้ในการวางแผนด้านต่าง ๆ

• วางแคมเปญรับเทศกาล ในช่วงกุมภาพันธ์อย่างต่อเนื่อง เนื่องจากวาเลนไทน์พิสูจน์แล้วว่าดึง traffic ได้จริง
• กระตุ้น demand ช่วง dip (ปลาย ก.พ. – ต้น มี.ค.) ด้วยโปรโมชั่น limited-time เช่น seasonal menu หรือ double stamps
• ติดตาม CM% ต่อเนื่อง หากขยับผ่าน 55% ได้ แสดงว่าการ optimize product mix เริ่มเห็นผลชัดเจน

strftime ดึงและจัดรูปแบบวันเวลา

หลังจากใช้ฟังก์ชันเกี่ยวกับวันเวลาเพื่อสรุปและวิเคราะห์ข้อมูลแล้ว อีกขั้นที่สำคัญคือการแสดงผลให้อ่านง่ายและเหมาะกับงานมากขึ้น ฟังก์ชัน strftime(column, format_string) ใช้แปลงค่า DATE หรือ TIMESTAMP ให้เป็นข้อความตามรูปแบบที่เรากำหนดเอง ตัวอย่างเช่น เราอาจใช้ฟังก์ชันนี้เพื่อแสดงวันที่ในรูปแบบ 2026-03-14 ให้เป็นรูปแบบ 14 March 2026

Specifier	Description	Example
%a	ชื่อวันแบบย่อ	Sun, Mon, …
%A	ชื่อวันแบบเต็ม	Sunday, Monday, …
%b	ชื่อเดือนแบบย่อ	Jan, Feb, …, Dec
%B	ชื่อเดือนแบบเต็ม	January, February, …
%c	วันที่และเวลาแบบ ISO	1992-03-02 10:30:20
%d	วันที่ของเดือน เติม 0 นำหน้า	01, 02, …, 31
%-d	วันที่ของเดือน ไม่เติม 0 นำหน้า	1, 2, …, 30
%f	ไมโครวินาที เติม 0 นำหน้าทางซ้าย	000000 - 999999
%g	มิลลิวินาที เติม 0 นำหน้าทางซ้าย	000 - 999
%G	ปี ISO 8601 แบบ 4 หลัก แทนปีที่มีสัปดาห์ ISO ส่วนใหญ่อยู่ (ใช้คู่กับ %V)	0001, 0002, …, 9999
%H	ชั่วโมง (นาฬิกา 24 ชั่วโมง) เติม 0 นำหน้า	00, 01, …, 23
%-H	ชั่วโมง (นาฬิกา 24 ชั่วโมง) ไม่เติม 0 นำหน้า	0, 1, …, 23
%I	ชั่วโมง (นาฬิกา 12 ชั่วโมง) เติม 0 นำหน้า	01, 02, …, 12
%-I	ชั่วโมง (นาฬิกา 12 ชั่วโมง) ไม่เติม 0 นำหน้า	1, 2, … 12
%j	วันที่ของปี เติม 0 นำหน้า	001, 002, …, 366
%-j	วันที่ของปี ไม่เติม 0 นำหน้า	1, 2, …, 366
%m	เดือน เติม 0 นำหน้า	01, 02, …, 12
%-m	เดือน ไม่เติม 0 นำหน้า	1, 2, …, 12
%M	นาที เติม 0 นำหน้า	00, 01, …, 59
%-M	นาที ไม่เติม 0 นำหน้า	0, 1, …, 59
%n	นาโนวินาที เติม 0 นำหน้าทางซ้าย	000000000 - 999999999
%p	AM หรือ PM	AM, PM
%S	วินาที เติม 0 นำหน้า	00, 01, …, 59
%-S	วินาที ไม่เติม 0 นำหน้า	0, 1, …, 59
%u	วันในสัปดาห์แบบ ISO 8601 โดย 1 คือวันจันทร์	1, 2, …, 7
%U	ลำดับสัปดาห์ของปี โดยสัปดาห์ที่ 01 เริ่มต้นวันอาทิตย์แรกของปี (อาจมีสัปดาห์ที่ 00) ไม่ใช่มาตรฐาน ISO 8601	00, 01, …, 53
%V	สัปดาห์แบบ ISO 8601 โดยวันจันทร์เป็นวันแรกของสัปดาห์ สัปดาห์ที่ 01 คือสัปดาห์ที่มีวันที่ 4 ม.ค. ใช้คู่กับ %G ไม่ใช่ %Y	01, …, 53
%w	วันในสัปดาห์ โดย 0 คือวันอาทิตย์	0, 1, …, 6
%W	ลำดับสัปดาห์ของปี โดยสัปดาห์ที่ 01 เริ่มต้นวันจันทร์แรกของปี (อาจมีสัปดาห์ที่ 00) ไม่ใช่มาตรฐาน ISO 8601	00, 01, …, 53
%x	วันที่แบบ ISO	1992-03-02
%X	เวลาแบบ ISO	10:30:20
%y	ปีแบบ 2 หลัก เติม 0 นำหน้า โดย 00–68 แทน ค.ศ. 2000–2068 และ 69–99 แทน ค.ศ. 1969–1999	00, 01, …, 99
%-y	ปีแบบ 2 หลัก ไม่เติม 0 นำหน้า โดย 0–68 แทน ค.ศ. 2000–2068 และ 69–99 แทน ค.ศ. 1969–1999	0, 1, …, 99
%Y	ปีแบบ 4 หลัก	2013, 2019 etc.
%z	ส่วนต่างของเขตเวลาจาก UTC ในรูปแบบ ±HH:MM, ±HHMM หรือ ±HH	-0700
%Z	ชื่อเขตเวลา	Europe/Amsterdam
%%	แสดงเครื่องหมาย % ตามตัวอักษร	%

จัด format วันที่

ในงานวิเคราะห์ข้อมูล เรามักไม่ได้ต้องการดูวันที่ในรูปแบบดิบเพียงอย่างเดียว แต่ต้องการแยกส่วนของวันที่ออกมาใช้งาน เช่น วันของเดือน ชื่อวัน ชื่อเดือน หรือแปลงวันที่ให้อยู่ในรูปแบบที่อ่านง่ายขึ้น ฟังก์ชัน strftime() จึงมีบทบาทสำคัญ เพราะช่วยแปลงค่า DATE ให้เป็นข้อความตามรูปแบบที่กำหนดได้

ตัวอย่างคำสั่งนี้ใช้ strftime() เพื่อดึงองค์ประกอบต่าง ๆ จากคอลัมน์ order_date แล้วแสดงออกมาในหลายรูปแบบพร้อมกัน ทำให้เห็นชัดเจนว่าวันที่เดียวกันสามารถนำเสนอได้หลายแบบตามวัตถุประสงค์ของการใช้งาน เช่น ใช้เป็นตัวเลขสำหรับการจัดกลุ่ม ใช้ชื่อวันเพื่อวิเคราะห์พฤติกรรมตามวันในสัปดาห์ หรือใช้รูปแบบมาตรฐานเพื่อเตรียมข้อมูลสำหรับการแลกเปลี่ยนกับระบบอื่น

-- แสดงรูปแบบวันที่ต่าง ๆ จากคอลัมน์ order_date โดยใช้ฟังก์ชัน strftime
SELECT DISTINCT
 order_date, -- วันที่สั่งซื้อต้นฉบับ
 strftime(order_date, '%d')       AS day_num, -- วันที่ในรูปแบบตัวเลข (01–31)
 strftime(order_date, '%a')       AS day_name, -- ชื่อย่อวันในสัปดาห์ภาษาอังกฤษ (Mon, Tue, ...)
 strftime(order_date, '%m')       AS month_num, -- เดือนในรูปแบบตัวเลข (01–12)
 strftime(order_date, '%b')       AS month_name, -- ชื่อย่อเดือนภาษาอังกฤษ (Jan, Feb, ...)
 strftime(order_date, '%Y')       AS year_4digit, -- ปีแบบ 4 หลัก (เช่น 2024)
 strftime(order_date, '%d/%m/%Y') AS thai_format, -- รูปแบบวันที่แบบไทย (dd/mm/yyyy)
 strftime(order_date, '%Y-%m-%d') AS iso_format -- รูปแบบวันที่มาตรฐาน ISO 8601 (ปปปป-ดด-วว)
FROM transactions -- ตารางธุรกรรมทั้งหมด
ORDER BY order_date -- เรียงตามวันที่จากเก่าสุดไปใหม่สุด
LIMIT 5; -- แสดงเพียง 5 แถวแรกเพื่อตรวจสอบผลลัพธ์

order_date	day_num	day_name	month_num	month_name	year_4digit	thai_format	iso_format
2026-02-01	01	Sun	02	Feb	2026	01/02/2026	2026-02-01
2026-02-02	02	Mon	02	Feb	2026	02/02/2026	2026-02-02
2026-02-03	03	Tue	02	Feb	2026	03/02/2026	2026-02-03
2026-02-04	04	Wed	02	Feb	2026	04/02/2026	2026-02-04
2026-02-05	05	Thu	02	Feb	2026	05/02/2026	2026-02-05

ดึงและจัดรูปแบบเวลา

นอกจากการจัดรูปแบบวันที่แล้ว ในหลายกรณีเรายังต้องการดึง “เวลา” ออกมาใช้งานด้วย เช่น ชั่วโมง เวลารูปแบบย่อ หรือช่วงเวลา AM/PM ฟังก์ชัน strftime() สามารถใช้แยกและจัดรูปแบบส่วนของเวลาจากคอลัมน์ชนิด TIMESTAMP ได้อย่างสะดวก

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

-- แสดงรูปแบบเวลาต่าง ๆ จากคอลัมน์ order_datetime โดยใช้ฟังก์ชัน strftime
SELECT DISTINCT
 order_datetime, -- วันที่และเวลาสั่งซื้อต้นฉบับ
 strftime(order_datetime, '%H')              AS hour_24, -- ชั่วโมงในรูปแบบ 24 ชั่วโมง (00–23)
 strftime(order_datetime, '%H:%M')           AS time_hhmm, -- เวลาในรูปแบบ ชช:นน
 strftime(order_datetime, '%H:%M:%S')        AS time_full, -- เวลาเต็มในรูปแบบ ชช:นน:วว
 strftime(order_datetime, '%p')              AS am_pm, -- ช่วงเวลา AM หรือ PM
 strftime(order_datetime, '%d %b %Y %H:%M')  AS full_datetime_fmt -- วันที่และเวลาแบบอ่านง่าย (เช่น 01 Jan 2024 13:30)
FROM transactions -- ตารางธุรกรรมทั้งหมด
ORDER BY order_datetime -- เรียงตามวันที่และเวลาจากเก่าสุดไปใหม่สุด
LIMIT 5; -- แสดงเพียง 5 แถวแรกเพื่อตรวจสอบผลลัพธ์

order_datetime	hour_24	time_hhmm	time_full	am_pm	full_datetime_fmt
2026-02-01 09:01:32	09	09:01	09:01:32	AM	01 Feb 2026 09:01
2026-02-01 09:04:22	09	09:04	09:04:22	AM	01 Feb 2026 09:04

การเขียนแบบนี้มีประโยชน์มากในงานวิเคราะห์ข้อมูล เช่น ใช้ดูว่าลูกค้ามักสั่งซื้อในช่วงเวลาใดของวัน ใช้สร้างตัวแปรชั่วโมงสำหรับการสรุปยอดขายรายช่วงเวลา หรือใช้จัดรูปแบบวันและเวลาให้เหมาะกับการแสดงในรายงานและแดชบอร์ด

มาดูตัวอย่างการประยุกต์ใช้ strftime() เพื่อสรุปจำนวนออเดอร์และรายได้รวมในแต่ละเดือน โดยจัดรูปแบบเดือนให้มีทั้งแบบที่อ่านง่ายสำหรับแสดงผล และแบบที่เหมาะสำหรับใช้เรียงลำดับข้อมูล

-- สรุปจำนวนออเดอร์และรายได้รวมแยกตามเดือน
SELECT
 strftime(order_date, '%B %Y') AS month_label,  -- ชื่อเดือนและปีแบบอ่านง่าย (เช่น January 2024)
 strftime(order_date, '%Y-%m') AS month_sort,   -- รูปแบบเดือนสำหรับเรียงลำดับ (ปปปป-ดด)
 COUNT(*)                      AS order_count,  -- จำนวนออเดอร์ในเดือนนั้น
 ROUND(SUM(net_sales_thb), 0)  AS total_revenue -- รายได้สุทธิรวมในเดือนนั้น (บาท)
FROM transactions -- ตารางธุรกรรมทั้งหมด
WHERE order_date BETWEEN '2026-02-01' AND '2026-03-31'
GROUP BY month_label, month_sort -- จัดกลุ่มตามเดือน
ORDER BY month_sort; -- เรียงตามลำดับเดือน (จากเก่าสุดไปใหม่สุด)

month_label	month_sort	order_count	total_revenue
February 2026	2026-02	24,420	4,905,954
March 2026	2026-03	27,805	5,660,917

ใส่ month_sort (%Y-%m) เพิ่มเพื่อใช้ ORDER BY ให้เดือนเรียงถูกต้อง ถ้า ORDER BY month_label จะเรียงตามตัวอักษร (April → February → March) ซึ่งผิด

ตัวอย่างสุดท้ายนี้แสดงการประยุกต์ใช้ strftime() เพื่อสรุปจำนวนออเดอร์และรายได้รวมแยกตามวันในสัปดาห์ ช่วยให้เห็นว่าธุรกิจมีแนวโน้มขายดีในวันใด หรือมีรายได้สูงเป็นพิเศษในช่วงต้นสัปดาห์หรือวันหยุด

-- สรุปจำนวนออเดอร์และรายได้รวมแยกตามวันในสัปดาห์
SELECT
 strftime(order_date, '%w')   AS dow_num, -- หมายเลขวันในสัปดาห์ (0 = อาทิตย์, 6 = เสาร์)
 strftime(order_date, '%A')   AS day_name, -- ชื่อเต็มวันในสัปดาห์ภาษาอังกฤษ (Sunday, Monday, ...)
 COUNT(*)                     AS order_count, -- จำนวนออเดอร์ในวันนั้น
 ROUND(SUM(net_sales_thb), 0) AS total_revenue -- รายได้สุทธิรวมในวันนั้น (บาท)
FROM transactions -- ตารางธุรกรรมทั้งหมด
GROUP BY dow_num, day_name -- จัดกลุ่มตามวันในสัปดาห์
ORDER BY dow_num; -- เรียงตามหมายเลขวัน (0–6)

dow_num	day_name	order_count	total_revenue
0	Sunday	8,507	1,771,639
1	Monday	6,445	1,285,679
2	Tuesday	7,570	1,502,126
3	Wednesday	8,099	1,621,162
4	Thursday	7,358	1,465,462
5	Friday	7,618	1,541,691
6	Saturday	7,726	1,599,642

จากผลลัพธ์ วันอาทิตย์มีออร์เดอร์และรายได้สูงสุด (8,507 ออร์เดอร์ / 1.77 ล้านบาท) สูงกว่าวันจันทร์ซึ่งต่ำสุดถึง 32% สะท้อนว่าลูกค้าของ PiCha มีแนวโน้มออกมาใช้บริการมากขึ้นในวันหยุด ข้อมูลแบบนี้มีประโยชน์โดยตรงต่อการวางแผนกะพนักงานและการออกแบบโปรโมชันตามวันในสัปดาห์

สรุป

ฟังก์ชันวันเวลาในบทนี้แม้จะมีหน้าที่ต่างกัน แต่เมื่อมองรวมกันแล้วจะเห็นว่าเป็นชุดเครื่องมือที่ทำงานเสริมกันอย่างเป็นระบบ EXTRACT และ DATE_PART ใช้ดึงองค์ประกอบของวันเวลาออกมา เช่น ชั่วโมง วัน หรือเดือน DATE_TRUNC ใช้ปรับวันเวลาให้อยู่ในระดับที่ต้องการเพื่อการสรุปข้อมูล เช่น รายวัน รายสัปดาห์ หรือรายเดือน DATE_DIFF ใช้คำนวณระยะห่างระหว่างวันเวลา และ strftime ใช้จัดรูปแบบผลลัพธ์ให้อ่านง่ายและพร้อมนำไปใช้ในรายงานหรือแดชบอร์ด

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

Syntax	ตัวอย่าง	ใช้เมื่อไร
EXTRACT	EXTRACT(hour FROM timestamp)	เมื่อต้องการดึงองค์ประกอบบางส่วนของวันเวลา
DATE_PART	DATE_PART('dow', date)	เมื่อต้องการใช้รูปแบบทางเลือกของ EXTRACT
DATE_TRUNC	DATE_TRUNC('week', date)	เมื่อต้องการจัดกลุ่มข้อมูลตามระดับเวลา
DATE_DIFF	DATE_DIFF('day', d1, d2)	เมื่อต้องการวัดช่วงห่างระหว่างวันเวลาสองค่า
strftime	strftime(date, '%Y-%m')	เมื่อต้องการจัดรูปแบบวันเวลาให้อยู่ในรูปแบบข้อความที่อ่านง่าย

คำถามท้ายบท

1. ใช้ DATE_TRUNC('month', order_date) กับ DATE_DIFF คำนวณว่าแต่ละเดือนมีกี่วัน และรายได้เฉลี่ยต่อวันเท่าไหร่

ผลลัพธ์ที่คาดหวัง

month_start	days_in_month	total_revenue	avg_daily_revenue
2026-02-01	28	4,905,954	175,213
2026-03-01	31	5,660,917	182,610
2026-04-01	30	220,531	7,351

คลิกเพื่อดูเฉลย

-- สรุปรายได้รวมและรายได้เฉลี่ยต่อวันแยกตามเดือน
SELECT
  DATE_TRUNC('month', order_date) AS month_start, -- วันแรกของแต่ละเดือน
  DATEDIFF('day',
    DATE_TRUNC('month', MIN(order_date)),
    DATE_TRUNC('month', MIN(order_date)) + INTERVAL 1 MONTH
  ) AS days_in_month, -- จำนวนวันในเดือนนั้น
  ROUND(SUM(net_sales_thb), 0) AS total_revenue, -- รายได้สุทธิรวมในเดือนนั้น (บาท)
  ROUND(SUM(net_sales_thb) /
    DATEDIFF('day',
      DATE_TRUNC('month', MIN(order_date)),
      DATE_TRUNC('month', MIN(order_date)) + INTERVAL 1 MONTH
    ), 0) AS avg_daily_revenue -- รายได้เฉลี่ยต่อวันในเดือนนั้น (บาท)
FROM transactions -- ตารางธุรกรรมทั้งหมด
GROUP BY DATE_TRUNC('month', order_date) -- จัดกลุ่มตามเดือน
ORDER BY month_start; -- เรียงตามลำดับเดือน (จากเก่าสุดไปใหม่สุด)

2. ใช้ DATE_PART('hour', order_datetime) + CASE จัดชั่วโมงเป็น 4 ช่วง: Morning (9–10), Lunch (11–13), Afternoon (14–16), Evening (17–22) พร้อมแสดงรายได้แต่ละช่วง

ผลลัพธ์ที่คาดหวัง

time_slot	orders	total_revenue	aov
Morning (9–10)	5,615	1,093,356	195
Lunch (11–13)	17,481	3,545,399	203
Afternoon (14–16)	10,156	2,053,268	202
Evening (17–22)	20,071	4,095,378	204

คลิกเพื่อดูเฉลย

-- สรุปจำนวนออเดอร์ รายได้รวม และมูลค่าเฉลี่ยต่อออเดอร์ แยกตามช่วงเวลาของวัน
SELECT
 CASE
  WHEN DATE_PART('hour', order_datetime) BETWEEN 9  AND 10 THEN 'Morning (9–10)' -- ช่วงเช้า
  WHEN DATE_PART('hour', order_datetime) BETWEEN 11 AND 13 THEN 'Lunch (11–13)' -- ช่วงมื้อกลางวัน
  WHEN DATE_PART('hour', order_datetime) BETWEEN 14 AND 16 THEN 'Afternoon (14–16)' -- ช่วงบ่าย
  WHEN DATE_PART('hour', order_datetime) BETWEEN 17 AND 22 THEN 'Evening (17–22)' -- ช่วงเย็นถึงดึก
  ELSE 'Other' -- ช่วงเวลานอกเหนือจากที่กำหนด
 END                          AS time_slot, -- ชื่อช่วงเวลา
 COUNT(*)                     AS orders, -- จำนวนออเดอร์ในช่วงเวลานั้น
 ROUND(SUM(net_sales_thb), 0) AS total_revenue, -- รายได้สุทธิรวม (บาท)
 ROUND(AVG(net_sales_thb), 0) AS aov -- มูลค่าเฉลี่ยต่อออเดอร์ (บาท)
FROM transactions -- ตารางธุรกรรมทั้งหมด
GROUP BY 1 -- จัดกลุ่มตามช่วงเวลา (อ้างอิงคอลัมน์ที่ 1)
ORDER BY MIN(DATE_PART('hour', order_datetime)); -- เรียงตามชั่วโมงเริ่มต้นของแต่ละช่วง

3. ใช้ DATE_DIFF คำนวณอายุลูกค้าแต่ละคน (จาก acquisition_date ถึง order_date แรก) พร้อมวิเคราะห์ว่าลูกค้าที่เข้ามาวันแรก ๆ กับวันหลังมีพฤติกรรมต่างกันไหม

ผลลัพธ์ที่คาดหวัง

activation_cohort	customers	avg_days_to_order	avg_lifetime_orders	avg_aov
Day 0 (same day)	376	0.0	10.6	202
Week 1 (1–7 days)	969	3.1	7.6	202
Later (8+ days)	766	19.5	3.1	198

คลิกเพื่อดูเฉลย

-- วิเคราะห์ความเร็วในการสั่งซื้อครั้งแรกและพฤติกรรมระยะยาวของลูกค้าแยกตาม Activation Cohort
WITH first_order AS ( -- CTE คำนวณข้อมูลออเดอร์แรกของลูกค้าแต่ละราย
 SELECT
  c.customer_id, -- รหัสลูกค้า
  c.acquisition_date, -- วันที่ลูกค้าสมัครสมาชิก
  c.loyalty_tier, -- ระดับสมาชิก
  MIN(t.order_date)                                      AS first_order_date, -- วันที่สั่งซื้อครั้งแรก
  DATEDIFF('day', c.acquisition_date, MIN(t.order_date)) AS days_to_first_order, -- จำนวนวันจากสมัครถึงสั่งซื้อครั้งแรก
  COUNT(t.transaction_id)                                AS total_orders, -- จำนวนออเดอร์ทั้งหมดตลอดช่วงเวลา
  ROUND(AVG(t.net_sales_thb), 0)                         AS avg_order_value -- มูลค่าเฉลี่ยต่อออเดอร์ (บาท)
 FROM customers c -- ตารางข้อมูลลูกค้า
 JOIN transactions t ON c.customer_id = t.customer_id -- เชื่อมตารางธุรกรรมเพื่อดึงประวัติการสั่งซื้อ
 GROUP BY c.customer_id, c.acquisition_date, c.loyalty_tier -- จัดกลุ่มตามรายลูกค้า
)
SELECT
 CASE
   WHEN days_to_first_order = 0  THEN 'Day 0 (same day)' -- สั่งซื้อในวันเดียวกับที่สมัคร
   WHEN days_to_first_order <= 7 THEN 'Week 1 (1–7 days)' -- สั่งซื้อภายใน 1 สัปดาห์แรก
   ELSE 'Later (8+ days)' -- สั่งซื้อหลังจาก 7 วันขึ้นไป
 END                               AS activation_cohort, -- กลุ่ม Cohort ตามความเร็วในการสั่งซื้อครั้งแรก
 COUNT(*)                          AS customers, -- จำนวนลูกค้าในแต่ละ Cohort
 ROUND(AVG(days_to_first_order),1) AS avg_days_to_order, -- จำนวนวันเฉลี่ยจากสมัครถึงสั่งซื้อครั้งแรก
 ROUND(AVG(total_orders), 1)       AS avg_lifetime_orders, -- จำนวนออเดอร์เฉลี่ยตลอดช่วงเวลา
 ROUND(AVG(avg_order_value), 0)    AS avg_aov -- มูลค่าเฉลี่ยต่อออเดอร์ของแต่ละ Cohort (บาท)
FROM first_order -- อ้างอิงจาก CTE first_order
GROUP BY 1 -- จัดกลุ่มตาม Activation Cohort
ORDER BY MIN(days_to_first_order); -- เรียงตามจำนวนวันต่ำสุดของแต่ละกลุ่ม