ใช้ MySQL หาค่าค่าสุดท้ายเมื่อตอนอดีตแต่ตอนนี้คือปัจจุบัน(ซึ่งมันเปลี่ยนไปเยอะแล้ว)

อย่าเพิ่งงงกับชื่อเรื่อง คือมันมีที่มา คือเมื่อสองสามวันก่อนมีคนไปตั้งโพสต์ถามในกลุ่มประมาณ 2-3 กลุ่ม (นี่ก็ตามอยู่)ซึ่งเป็นกลุ่มที่เกี่ยวข้องกับคอมพิวเตอร์เป็นกลุ่มใหญ่เลยทีเดียว คำถามคือ ดูรูปเอานะ

จากที่ดูโจทย์

  • ตาราง drug_catalog เก็บรายการยาและการเปลี่ยนแปลงราคา
  • ตาราง drug_order เก็บรายการสั่งยาของผู้ป่วยแต่ละคน
  • ผลลัพธ์ที่ต้องการคือต้องการราคายา ณ วันที่สั่ง ซึ่งวันที่สั่งคือราคาล่าสุดแต่เดี๋ยวก่อนซึ่งมันดันเป็นอดีตไปแล้วสำหรับราคายาและการสั่งยาแต่ละรายการ ปัจจุบันรายการยามีการปรับปรุงราคามาหลายรอบแล้ว
  • จากในเมนท์ได้รู้ว่า ไม่สามารถเปลี่ยนโครงสร้างให้ดีกว่าได้ ^_^ , มีการบันทึกราคาในตารางสั่งยาผิดนี่คือต้องการแก้ไขเอาราคาที่ถูก ณ เวลาที่สั่งยาเข้าไป ซึ่งก็นั่นแหล่ะเลยพอจะเดาได้ว่านี่แค่ดัมมี่ข้อมูลเพื่อเอามาถามหา Solution แก้ไขเฉย ๆ (ในความเป็นจริงแล้วคนที่อยู่ในฟิลด์โปรแกรมด้านการพยาบาลมันละเอียดกว่านี้เยอะ)
  • แต่ช่างเหอะ มาดูการหาคำตอบกัน ซึ่งแต่ละวิธีมีทั้งผิดและถูก คือถ้าถูกก็ยังอาจไม่จบคือผลลัพธ์ถูกแต่มันมีปัจจัยเรื่อง Performance อะไรอีกเยอะแยะ ข้อมูลจริงอาจมีเป็นหลายล้านเรคคอร์ดซึ่งก็ต้องไปหาสิ่งที่เหมาะสมต่อไป
  • ทำไมถึงเอาโจทย์นี้มาทำ ? ไอ้รายการยา การสั่งยา ผู้ป่วย ไรเทือกนี้คุ้นชินอยู่และไม่ช้าอาจจะเจอเคสแบบนี้ รวมทั้งการแก้โจทย์มันเป็นหนึ่งในการพัฒนาสกิลด้วย ทำบ่อย ๆ ค่อย ๆ ชิน   #ความรักก็เช่นกัน

เริ่มหาคำตอบกันโดยผลลัพธ์แบบชัด ๆ

คือขยายเฉย  ๆ มันใช่เหรอ 5555 มันต้องลองเอามาทำ Timeline ดูเพื่ออธิบายเพิ่มสิ

ลองสร้างตารางข้อมูลตามโจทย์ดู (ในนี้มีคนสร้างไว้ด้วย)

CREATE TABLE `drug_catalog` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_catalog` (`drug_code`, `price`, `date_effect`) VALUES
('Albumin', 15, '2018-01-01'),
('Albumin', 20, '2018-02-15'),
('Albumin', 25, '2019-01-01'),
('Penicillin', 5, '2018-01-02'),
('Penicillin', 6, '2019-01-02'),
('Bromhexin', 11, '2018-02-01'),
('Bromhexin', 13, '2019-03-01'),
('Bromhexin', 15, '2019-04-01'),
('Bupropion', 50, '2019-02-05'),
('Bupropion', 40, '2019-03-06');


CREATE TABLE `drug_order` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `hn` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_order` (`drug_code`, `hn`, `date_effect`) VALUES
('Albumin', '2482', '2018-02-15'),
('Albumin', '1526', '2019-01-01'),
('Penicillin', '2482', '2019-04-01'),
('Albumin', '4458', '2020-01-01'),
('Bromhexin', '8697', '2018-05-01'),
('Bupropion', '2482', '2020-01-01');

เราจะได้วิธีแรก คือการหาราคาสุดท้ายโดยเปรียบเทียบที่ date_effect ของตารางราคายา

SELECT 
    drug_code,
    dr.hn,
    (SELECT price FROM drug_catalog dc WHERE dc.drug_code=dr.drug_code AND dc.date_effect <= dr.date_effect ORDER BY dc.date_effect DESC LIMIT 1) AS 'price', dr.date_effect
FROM drug_order dr

และวิธีที่ 2 คือการหาราคาสุดท้าย ณ วันที่สั่งยาโดยใช้ความสามารถของ MySQL Version 8 เป็นต้นไปเพื่อเอาลำดับแรกสุดของราคายา โดยเปรียบเทียบที่ date_effect ของตารางราคายา

WITH drugprice AS (
    SELECT
  		dr.*,
  		dc.price,
  		ROW_NUMBER() OVER(
        PARTITION BY dc.drug_code, dr.hn ORDER BY dc.date_effect DESC
        ) AS row_num
  	FROM drug_order dr 
  		INNER JOIN drug_catalog dc ON dr.drug_code = dc.drug_code 
  		AND dc.date_effect <= dr.date_effect
)
SELECT * FROM drugprice WHERE row_num = 1;

ผลลัพธ์

ไปลองเล่นกันได้ที่

จบปิ๊ง !!!

MySQL ว่าด้วยลำดับและการจัดอันดับข้อมูล

เดิมทีแล้ว MySQL ไม่มีฟังก์ชั่น (Row Number) สำหรับเรียกลำดับที่ (1, 2, 3 …n) ของแถว/เร็คคอร์ดในฐานข้อมูลเหมือนดั่งเช่นเจ้าอื่น ๆ เพราะฉนั้นเหล่านักรบส่วนใหญ่ก็มักสร้างฟังก์ชั่นใส่ลำดับที่แถวเอง

สมมุติเรามีชุดข้อมูลนึงแสดงคะแนนของแต่ละอำเภอ (เอ๊ะ !! คุ้น ๆ ) ตามนี้

CREATE TABLE IF NOT EXISTS `rstanding` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `rstanding` (`id`, `name`, `score`) VALUES
  ('1', 'A', 98.52),
  ('2', 'B', 97.24),
  ('3', 'C', 96.33),
  ('4', 'E', 96.33),  
  ('5', 'F', 95.02),
  ('6', 'G', 94.88),
  ('7', 'H', 94.88);

การใส่ลำดับที่ของแถวก็จะสร้างฟังก์ชั่นเพื่อนับจำนวนของแถวไปจนสิ้นสุดชุดข้อมูล

SELECT 
    @rownum := @rownum + 1 AS row_number,
    t1.*
FROM rstanding t1, 
(SELECT @rownum := 0) r

จะเห็นได้ว่าคอลัมภ์ row_number เพิ่มขึ้นมาโดยเป็นลำดับที่แถว/เรคคอร์ดนั้น ๆ ในที่นี้เราไม่ได้จัดเรียง (ORDER BY Field [ASC/DESC]) ชุดข้อมูลด้วยคอลัมภ์ใด ๆ

ต่อเนื่องในเรื่องของอันดับ

อันดับ เป็นชื่อของตำแหน่ง ที่มีคำบอกระดับของ ขั้น,ชั้น ที่บ่งบอกถึงความต่างระดับ ซึ่งเป็นผลพวงมาจากผลงานที่มีการประกวดหรือแข่งขัน

การใส่ลำดับที่โดยการจัดเรียงตามคะแนน (คอลัมภ์ score) จากคะแนนสูงสุดไปหาน้อยสุด

ตัวอย่างวิธีที่ 1 ใช้ฟังก์ชั่นเดิมแต่เพิ่มเติมด้วยการใส่เงื่อนไขเพื่อให้จัดลำดับได้

SELECT 
    @rownum := @rownum + 1 AS row_number,
    @ranknum := IF(t1.score < @score, @ranknum + 1, @ranknum) AS row_ranking,    
    t1.*,
@score := t1.score AS dummy
FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r
ORDER BY t1.score DESC

ผลลัพธ์การจัดเรียงก็จะได้ตามนี้

จากผลลัพธ์ที่ได้ในวิธีที่ 1 เราสามารถกรองได้ว่าเราสนใจลำดับที่ได้ลำดับอะไรบ้าง เช่นต้องการทราบว่าคนที่ได้ลำดับที่ 3 มีใครบ้างก็สามารถกรองได้จากคอลัมภ์ WHERE row_ranking = ?

SELECT
    t2.*
FROM (
  SELECT 
      @rownum := @rownum + 1 AS row_number,
      @ranknum := IF(t1.score < @score, 		
      @ranknum + 1, @ranknum) AS row_ranking,    
      t1.*,
      @score := t1.score AS dummy
  FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r
  ORDER BY t1.score DESC
) t2 
WHERE t2.row_ranking = 3

ตัวอย่างวิธีที่ 2 กรณีที่สนใจลำดับที่นั้น ๆ ก็นับจำนวน (แบบไม่ซ้ำ) ในลำดับก่อนหน้า เช่น สนใจลำดับที่ 3 ลำดับก่อนหน้าคือ 2 ลำดับ

SELECT
    t1.*
FROM rstanding t1
WHERE 2 = (SELECT COUNT(DISTINCT t2.score) FROM rstanding t2 WHERE t1.score < t2.score)

ตัวอย่างวิธีที่ 3 หาจำนวน LIMIT LIMIT offset, count;

SELECT
    t1.*
FROM rstanding t1
WHERE t1.score = (SELECT t2.score FROM rstanding t2 ORDER BY t2.score DESC LIMIT 3,1);

ตัวอย่างวิธีที่ 4 ทุกอย่างง่ายขึ้น MySQL เห็นคงเห็นว่าจะทำให้ยุ่งยากไปใยเล่า เรามีฟังก์ชั่นให้ใช้นะ แต่ช้าก่อน มันมีในเวอร์ชั่น 8 เป็นต้นไปนะจ๊ะ เวอร์ชั่นก่อนหน้าก็อด

ROW_NUMBER() Number of current row within its partition
DENSE_RANK() Rank of current row within its partition, without gaps
RANK() Rank of current row within its partition, with gaps

ดูวิธีการใช้งานกันเลย

WITH ranked_score AS (
  SELECT 
      ROW_NUMBER() OVER (ORDER BY score DESC) rownum,
      RANK() OVER (ORDER BY score DESC) row_ranking,
      DENSE_RANK() OVER (ORDER BY score DESC) drow_ranking,
      id,
      name,
      score
  FROM rstanding
)
SELECT * FROM ranked_score; -- WHERE drow_ranking = 3;

ผลลัพธ์กรณีที่เราไม่กรองใด ๆ ก็จะแสดงผลดังนี้

จบปิ๊ง ^_^
#แด่ตัวสำรองอันดับหนึ่ง

ป.ล.

 

MySQL แสดงผลวันที่แบบไทย (แบบเต็มและแบบย่อ)

การแสดงผลวัน เดือน ปี ของ MySQL จะแสดงผลเป็นวันที่ในรูปแบบสากลคือในรูปแบบภาษาอังกฤษจะแบบเต็มหรือแบบย่อก็ยังเป็นภาษาอังกฤษอยู่ดี ซึ่งในภาษาอื่นก็ยังไม่รองรับการแสดงผลในภาษาท้องถิ่นนั้น ๆ (รวมทั้งฟังก์ชั่นที่สามารถใช้งานด้วย)

ในกรณีที่ต้องการแสดงผล วัน เดือน ปี เป็นภาษาไทยจึงจำเป็นต้องสร้างฟังก์ชั่นขึ้นมาใช้งานเองซึ่งเราจะมาเขียนฟังก์ชั่นนี้กันโดยอาศัยฟังก์ชั่นของ MySQL ที่ชื่อว่า SUBSTRING_INDEX()

Return a substring of a string before a specified number of delimiter occurs
Syntax
SUBSTRING_INDEX(string, delimiter, number)
Parameter Values

string	Required. The original string
delimiter	Required. The delimiter to search for
number	Required. The number of times to search for the delimiter. Can be both a positive or negative number. If it is a positive number, this function returns all to the left of the delimiter. If it is a negative number, this function returns all to the right of the delimiter.

ตัวอย่างการใช้งาน

การใช่งานฟังก์ชั่นจาก w3resource.com

การใช่งานฟังก์ชั่นจาก w3resource.com

จะเห็นได้ว่าเราสามารถนำมาสร้างชุดคำสั่งใน MySQL สำหรับการแสดงผลวันที่แบบไทยทั้งแบบเต็มและแบบย่อ (วัน x ที่ x เดือน xxxx พ.ศ. xxxx) โดย

กำหนดค่าคงที่รายชื่อเดือนและชื่อวันในภาษาไทย

-- กำหนดค่าคงที่รายชื่อเดือนและชื่อวันในภาษาไทย
SET @monthname_long_th = 'มกราคม,กุมภาพันธ์,มีนาคม,เมษายน,พฤษภาคม,มิถุนายน,กรกฎาคม,สิงหาคม,กันยายน,ตุลาคม,พฤศจิกายน,ธันวาคม';
SET @dayname_long_th = 'อาทิตย์,จันทร์,อังคาร,พุธ,พฤหัสบดี,ศุกร์,เสาร์';

ใช้ฟังก์ชั่นใน MySQL เพื่อดึงค่าวันที่ วันในสัปดาห์ เดือน และปี

-- ใช้ฟังก์ชั่นใน MySQL เพื่อดึงค่าวันที่ วันในสัปดาห์ เดือน และปี 
SET @d = DAY(CURDATE()); 
SET @dddd = SUBSTRING_INDEX(SUBSTRING_INDEX(@dayname_long_th, ',', DAYOFWEEK(CURDATE())), ',' , -1); 
SET @mmmm = SUBSTRING_INDEX(SUBSTRING_INDEX(@monthname_long_th, ',', MONTH(CURDATE())), ',' , -1); 
SET @yyyy = YEAR(CURDATE()) + 543;

ทดสอบผลลัพธ์ที่ได้

SELECT CONCAT_WS(' ', 'วัน', @dddd, 'ที่', @d, 'เดือน', @mmmm, 'พ.ศ.', @yyyy);
-- วัน เสาร์ ที่ 27 เดือน กรกฎาคม พ.ศ. 2562

จบปิ๊ง ^__^ (ลองไปปรับเป็นฟังก์ชั่นรวมทั้งแบบย่อกันดู)
ตามไปทดลองกันได้ที่

MySQL Get First value or Last value in each group

ใน MySQL ตั้งแต่เวอร์ชั่น 8.0 เป็นต้นไป ถ้าต้องการดึงค่าแรกและค่าสุดท้ายในกรุ๊ปนั้น ๆ ออกมา สามารถใช้ Function

  • FIRST_VALUE()
    FIRST_VALUE(expr) [null_treatment] over_clause
    Returns the value of expr from the first row of the window frame.
  • LAST_VALUE()
    LAST_VALUE(expr) [null_treatment] over_clause
    Returns the value of expr from the last row of the window frame.

ตัวอย่างการใช้งาน

โจทย์ มีข้อมูล CKD Stage (สมมุติ) เรียงตามรายปีงบประมาณ ถ้าต้องการทราบ Stage แรกและสุดท้ายที่ตรวจพบในรายนั้น ๆ หาได้จาก

เริ่มต้นคือสร้างตารางทดสอบ

CREATE TABLE ckdstage(vn INT(11) AUTO_INCREMENT,
    hn VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    stage INT(1) NOT NULL,
    PRIMARY KEY(vn)
);
 
INSERT INTO ckdstage(vn,hn,fiscal_year,stage)
VALUES
(NULL,'1945',2016, 4),
(NULL,'1945',2017, 3),
(NULL,'1945',2018, 5),
(NULL,'1945',2019, 3),
(NULL,'2311',2016, 4),
(NULL,'2311',2017, 5),
(NULL,'2311',2018, 3),
(NULL,'2311',2019, 2);

ข้อมูลที่ได้จะเป็นตามนี้ ถ้ามองด้วยสายตาค่าที่เราต้องการจะตามที่ลูกศรชี้

หาผลลัพธ์ตามโจทย์

SELECT 
    hn,
    fiscal_year,
    FIRST_VALUE(stage) OVER (
      	PARTITION BY hn
        ORDER BY fiscal_year
    ) first_stage,	
    LAST_VALUE(stage) OVER (
        PARTITION BY hn      
        ORDER BY fiscal_year
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    ) last_stage
FROM ckdstage;

จะเห็นได้ว่าผลลัพธ์ทีได้จะดึงค่าแรกสุดและค่าสุดท้ายตามกรุ๊ปของคอลัมภ์ hn แต่ข่าวร้ายก็คือฟังก์ชั่นนี้ไม่มีให้ใช้ใน MySQL เวอร์ชั่นเก่า 55555 ซึ่งก็สามารถแก้ปัญหาโดยการจำลองฟังก์ชั่น First(), Last()  ขึ้นมาใช้งานเอง ตัวอย่าง

SELECT 
    t1.hn,
    GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC) AS stagelist_asc,
    GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC) AS stagelist_desc,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn ASC), ',', 1) , ',' , -1) AS first_stage,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(t1.stage ORDER BY t1.vn DESC), ',', 1) , ',' , -1) AS last_stage
FROM ckdstage AS t1
GROUP BY t1.hn

จบปิ๊ง !! ^__^

ป.ล.

 

MySQL generates a sequence of numeric values

หลังจากได้อ่านและลอง Sequence ใน MariaDB 10.3 แล้ว มันช่างง่ายเหลือเกินให้ตายเถอะโรบิน (แนะนำให้ไปอ่านยังไงก็ได้ใช้แน่ ๆ ) แต่บล๊อกนี้เราจะไม่ได้เขียนเรื่องนี้ 5555 เราจะใช้วิธีที่เรียกว่า CTE (Common Table Expression) สำหรับการสร้างชุดข้อมูล CTE ซึ่งเริ่มใช้ได้ใน MySQL 8.0, MariaDB 10.2 เป็นต้นมาซึ่งเอาจริง ๆ ก็ง่ายพอ ๆ กันแหล่ะ เราเคยเขียนในบทความเก่า ๆ ลองไปหาอ่านกันได้ หรือของ อ.ประเสริฐ

ลักษณของ CTE ใช้การเรียกโดยวิธี Recursive รูปแบบใน MySQL จะเป็นแบบนี้

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

ลักษณะของคำสั่งนี้คือ

สมมุติเรามีโจทย์ตามข้างต้น คือการสร้างตารางชั่วคราวที่เก็บ sequence number จาก 1 ถึง 100 เราก็เขียนได้ดังนี้

WITH RECURSIVE numbers AS (
    SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM numbers
    WHERE numbers.value < 100
)
SELECT *
FROM numbers;

ผลลัพธ์ก็ตามนี้ ดูได้ใน dbfiddle

สั้น ๆ และง่ายใช่ไหม อ่านจบตามไปอ่าน  sequence ต่อ แนะนำเลย
หลับฝันดีมีแฮง ^__^

Split comma separated string to multiple rows

สำหรับใครที่เคยใช้ฟังก์ชั่น GROUP_CONCAT() ใน  MySQL มาก่อนก็พอจะเดาผลลัพธ์จากฟังก์ชั่นนี้ได้ว่า ผลลัพธ์จะเป็นค่าตามคอลัมภ์ที่ถูกกรุ๊ป (GROUP BY) และนำมาต่อกันด้วยเครื่องหมายที่ระบุ ปกติค่าดีฟอลต์จะเป็น comma ‘,’ รูปแบบคำสั่งก็จะประมาณนี้

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

ตัวอย่าง


**ภาพจาก mysqltutorial

ข้างบนนี่คือต้นเหตุ มักจะมีกรณีที่เราได้ผลลัพธ์มาแล้ว นั่นคือ “A,B,C” และเราต้องการแยกข้อความที่ได้มาออกเป็นแต่ละแถว (ตาราง t ก่อนที่จะผ่านฟังก์ชั่น GROUP_CONCAT() นั่นแหล่ะ) ดูตัวอย่างกัน

CREATE TABLE DEMO (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    PID VARCHAR(20) NOT NULL,
    ICD10LIST VARCHAR(255) DEFAULT NULL
);
 
INSERT INTO DEMO(ID, PID, ICD10LIST) VALUES(NULL, '101', 'E119,E112,I10'), (NULL, '102', 'E119,E112'), (NULL, '103', 'E119,I10');

ข้อมูลในตารางเป็นแบบนี้ (คุ้น ๆ กันไหม 5555)

**แนวคิดของคำสั่งก็คือทำการสร้างตารางค่า Index ที่อยู่ในชุด/เซตข้อความเพื่อแยกแต่ละไอเท็มออกมา

SELECT
  DEMO.ID,
  DEMO.PID,
  SUBSTRING_INDEX(SUBSTRING_INDEX(DEMO.ICD10LIST, ',', numbers.n), ',', -1) AS ICD10
FROM
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN DEMO
  ON CHAR_LENGTH(DEMO.ICD10LIST)-CHAR_LENGTH(REPLACE(DEMO.ICD10LIST, ',', '')) >= numbers.n-1
ORDER BY
  PID, n

ผลลัพธ์ที่ได้

จบปิ๊ง !!!

ป.ล.

  • ตาราง numbers จะสร้างไว้ก่อนก็ได้ ปกติก็ใช้บ่อย ๆ นะครับจะด้วย RECURSIVE CTE ก็ได้หรือตัวนี้
  • ตัว Split String จะทำเป็นฟังก์ชั่นไว้ก็ได้นะ ^_^
  • ข้อมูลเป็นข้อมูลสมมุติจาก Data Exchange สมมุติแห่งหนึ่ง

 

 

Hierarchical and recursive queries in MySQL

Hierarchical query คืออะไร ?

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

อยากบอก(บ่น) ว่าก่อนที่ MySQL 8 จะออกนี่การดึงข้อมูลแบบลำดับชั้นลักษณะต้นไม้ (Tree) แบบนี้โคตรเปลืองพลังงาน แต่ตอนนี้ดีขึ้นมาก ต้องขอบคุณจริง ๆ ที่ MySQL อิมพลีเมนต์ Common Table Expression (CTE) ให้ใช้ได้ซะที เขียนคำสั่งสั้นลง ประสิทธิภาพดีขึ้น (ก็แหงสิเจ้าของเค้าทำออกมาเอง)

ยกตัวอย่างข้อมูลลักษณะแบบลำดับชั้น เช่น โครงสร้างองค์กร เธรดในคอมเมนต์ต่าง ๆ ข้อมูล Categories and sub-categories ที่เห็นภาพชัด ๆ  เลยคือ โครงสร้างองค์กร (Organization charts ) นี่ง่ายสุดละ มีหัวหน้าเป็นรูทและมีลูกน้องในความดูแลและบางทีลูกน้องก็มีเบ้ต่อลงไปอีกที ลักษณะข้อมูลแบบรูปนี้เลย


ว่าแล้วก็ติดตั้ง MySQL 8 สำหรับใช้งานกัน

docker run -d -p 3306:3306 --name mysqltest --restart always -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=demo mysql:8

โดยธรรมเนียมปฏิบัติทั่วไป ของตารางที่เก็บข้อมูลในลักษณะนี้ก็จะออกแบบคล้าย ๆ แบบนี้ โดยให้คอลัมภ์ parent เก็บข้อมูลหัวหน้า

DROP TABLE IF EXISTS category;
CREATE TABLE IF NOT EXISTS category(
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES
(1,'Root A', NULL),
(2,'Item 1', 1),
(3,'Item 2', 1),
(4,'Item 3', 3),
(5,'Root B', NULL),
(6,'Item 4', 5),
(7,'Item 5', 6),
(8,'Root C', NULL),
(9,'Item 6', 8);

การดึงข้อมูลในลักษณะ Recursive MySQL ได้เตรียมคำสั่งให้แล้ว รูปแบบการใช้งาน

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

ลักษณะของคำสั่งนี้คือ


*ภาพจาก mysqltutorial : A Definitive Guide To MySQL Recursive CTE

เรามาดูผลลัพธ์กัน

WITH RECURSIVE categorypath(id, name, dept, breadcrumbs) AS
(
  SELECT id, name, 0, CAST(name AS CHAR(1000))
  FROM category 
  WHERE parent IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.dept + 1, CONCAT_WS('/', cp.breadcrumbs, c.name) 
  FROM categorypath cp 
    JOIN category c ON cp.id = c.parent
)
SELECT * FROM categorypath ORDER BY breadcrumbs;

ไปอ่านเพิ่มเติมกันได้ที่

  • https://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/
  • https://www.percona.com/live/17/sites/default/files/slides/Recursive%20Query%20Throwdown.pdf
  • https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

จบปิ๊ง ^__^
ป.ล.1 วันหยุด วันฝนตกฉันควรนอนกกใครสักคนอยู่ใต้ผ้าห่มนี่นา
ป.ล.2 เศร้าาาวันทำงาน

Get random 2 rows from each group

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

นี่เป็นความหมายของการสุ่มตัวอย่าง ซึ่งหลายครั้งเราก็หลีกเลี่ยงไม่ได้ที่จะเลือกข้อมูลบางกลุ่มข้อมูลมาทำงาน ปกติก็ใช้หลักจากข้างบนนั่นแหล่ะเลือกมา (จะใช้วิธี 1-5 ก็เอาที่สบายใจ)

ในกลุ่มมีคำถามนึงน่าสนใจ ถามขึ้นในกลุ่มตามนี้

คร่าว ๆ ก็คืออยากได้กลุ่มข้อมูลมาจำนวนนึงแยกตามรหัส/กลุ่มการวินิจฉัยโรค มาดูอีก 1 วิธีกัน

  1. สร้างตารางทดสอบกัน (เอาตารางเกี่ยวกับผลไม้เนี่ยแหล่ะ เมื่อเช้าจัดมา //แพคเกจแบบนี้โคตรตอบโจทย์เอาจริง ๆ คือกินวันนึงก็ 1-2 ลูกเอง เยอะหน่อยก็ช่วงเล่นกีฬา แล้วกล้วยหอมหวีนึงนี่ก็ใช่ว่าหวีเล็ก ซื้อมาเป็นหวีทีก็กินไม่หมด T_T)

    CREATE TABLE fruits
        (`type` varchar(50), `variety` varchar(50), `price` float)
    ;
        
    INSERT INTO fruits
        (`type`, `variety`, `price`)
    VALUES
     ('apple', 'gala', 2.79),
     ('apple', 'fuji', 0.24),
     ('apple', 'limbertwig', 2.87),
     ('orange', 'valencia', 3.59),
     ('orange', 'navel', 9.36),
     ('pear', 'bradford', 6.05),
     ('pear', 'bartlett', 2.14),
     ('cherry', 'bing', 2.55),
     ('cherry', 'chelan', 6.33);

     

  2. ใช้ฟังก์ชั่น RAND() ใน MySQL เพื่อให้ได้ค่าตัวเลขที่ได้จากการสุ่มขึ้นมาและทำการจัดเรียงตามกลุ่ม (กำหนดเงื่อนไข) ในที่นี้เรียงตาม `type`

    RAND() – Returns a random floating-point value v in the range 0 <= v < 1.0

    SELECT
        type,
        variety,
        price,
        RAND()
    FROM fruits
    ORDER BY type, 4

  3. ใส่ลำดับที่ของแต่ละกลุ่มไว้
    SET @num:=0, @type:='';
    SELECT
        @num:=IF(@type = A.type, @num + 1, 1) as number,
        @type:=A.type as dummy,
        A.*
    FROM (
      SELECT
        type,
        variety,
        price,
        RAND()
      FROM fruits
      ORDER BY type, 4
    ) A

  4. เลือกจำนวนกลุ่มตัวอย่างตามที่ต้องการ (ในคำถามเลือก 2 ตัวอย่าง)
    ...
    WHERE number <= 2

    ผลลัพธ์ที่ได้คือรายการที่ได้จากการสุ่มกลุ่มละ 2 ตัวอย่าง

  5. ขอให้สนุกกับเดือนแห่ง QOF จร้าาา

ป.ล.
ลองรันทดสอบได้ที่ http://sqlfiddle.com/#!9/350bc7/6

Dynamic pivoting in MySQL

คิดถึงรายงานคิดถึง Excel 555 … เรื่องความตั้งใจในการเรียน Microsoft Excel นี่มีมาสักพักแล้ว เริ่มด้วยการซื้อหนังสือมาก่อนแต่น่าจะหลายเดือนละ (จนลืมไปเลยว่าซื้อมาตอนไหน บาปมาก) แต่ก็นะความขี้เกียจครอบงำ ช่วงนี้ก็เลยเป็นช่วงอ่านหนังสือ Microsoft Excel แบบจริงจัง (แอบตั้งใจ)
เปิดดูคร่าว ๆ มีบทนึงพูดถึงเรื่องการทำ Pivot Table ในโปรแกรมตระกูลตารางคำนวณเรื่องนี้ทุกตัวในตลาดก็สามารถทำได้ ถือเป็นหนึ่งใน Killer Feature แต่เรื่องนี้แค่ประเด็นนิดหน่อย ^_^

สำหรับคนที่เขียนคำสั่งเรียกดูข้อมูล (SQL) ก็ทำเรื่องนี้อยู่เนือง ๆ หลายครั้งก็ทำแบบฮาร์ดคอร์ ณ ตอนนั้นเลย ตัวอย่างคร่าว ๆ สมมุติเรามีข้อมูลค่าใช้จ่ายภายในบ้านสรุปประมาณนี้ ในฐานข้อมูล (ด้านซ้าย) และต้องการแสดงผลเป็นอีกรูปด้านขวา

SQL แสดงรายงานแบบนี้ อนุมานจากข้อมูลดิบที่เก็บในฐานข้อมูล ก่อนที่เราจะสรุปได้ก็น่าจะมีประมาณนี้ (เราใช้บริการของ generatedata.com) เอาตัวอย่างข้อมูลสัก 100 รายการหล่ะกัน

DROP TABLE IF EXISTS `ExpenseCategory`;
CREATE TABLE `ExpenseCategory` (
  `CategoryId` varchar(255) NOT NULL,
  `CategoryName` varchar(255) default NULL,
  PRIMARY KEY (`CategoryId`)
);

INSERT INTO `ExpenseCategory` (`CategoryId`,`CategoryName`) VALUES ("01","ความบันเทิง"),("02","ของชำ"),("03","ครอบครัว"),("04","การคมนาคมขนส่ง");

DROP TABLE IF EXISTS `Expense`;
CREATE TABLE `Expense` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `CategoryId` varchar(255) default NULL,
  `ExpenseDate` varchar(255),
  `Amount` varchar(100) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `Expense` VALUES (1,'04','2018-07-19 02:55:09','83369'),(2,'01','2018-01-17 19:18:21','28001'),(3,'04','2018-06-29 03:32:27','17058'),(4,'04','2018-07-04 17:44:03','64619'),(5,'04','2018-07-22 17:38:56','24050'),(6,'04','2018-08-15 03:43:50','80585'),(7,'03','2018-06-11 23:52:59','79427'),(8,'02','2018-04-29 04:03:38','63177'),(9,'01','2018-09-03 04:02:46','77975'),(10,'04','2018-07-04 21:49:15','61752'),(11,'01','2018-02-21 11:39:34','59245'),(12,'04','2018-01-05 05:42:14','15695'),(13,'02','2018-09-13 09:07:51','22132'),(14,'02','2018-08-12 13:24:09','58156'),(15,'01','2018-08-14 18:34:16','7802'),(16,'01','2018-04-04 04:26:02','44733'),(17,'03','2018-11-03 02:59:01','73158'),(18,'04','2018-01-23 09:59:45','69724'),(19,'02','2018-11-16 07:39:50','98963'),(20,'03','2018-01-04 11:20:59','93659'),(21,'02','2018-08-25 22:01:10','13945'),(22,'04','2018-04-08 22:46:07','90735'),(23,'01','2018-09-13 10:20:59','76856'),(24,'01','2018-09-02 08:45:17','54668'),(25,'03','2018-12-06 12:19:30','45571'),(26,'02','2018-05-27 09:48:40','24694'),(27,'01','2018-04-15 03:01:29','73961'),(28,'01','2018-03-11 06:05:34','53546'),(29,'02','2018-12-27 06:57:17','91375'),(30,'04','2018-02-05 05:14:19','44997'),(31,'02','2018-12-15 02:35:52','54144'),(32,'02','2018-08-01 02:06:37','95898'),(33,'02','2018-02-18 08:50:43','79249'),(34,'02','2018-03-26 04:39:35','66184'),(35,'01','2018-07-26 13:17:03','44670'),(36,'02','2018-12-26 13:50:39','88500'),(37,'04','2018-04-19 20:26:25','31414'),(38,'01','2018-07-28 13:53:24','56802'),(39,'02','2018-04-27 06:18:15','67682'),(40,'01','2018-10-11 12:25:37','98233'),(41,'03','2018-09-28 12:36:05','43118'),(42,'02','2018-08-17 16:06:34','94942'),(43,'02','2018-12-16 16:28:16','25039'),(44,'04','2018-04-14 15:47:06','87368'),(45,'03','2018-11-21 16:53:01','49355'),(46,'04','2018-01-06 17:08:54','63354'),(47,'03','2018-02-02 11:06:33','26540'),(48,'01','2018-10-19 03:50:49','32534'),(49,'01','2018-05-15 23:40:11','58038'),(50,'03','2018-04-28 23:53:24','2915'),(51,'01','2018-03-30 18:57:12','10229'),(52,'03','2018-04-22 07:47:45','20004'),(53,'03','2018-08-16 23:42:08','69185'),(54,'02','2018-03-09 20:38:52','92533'),(55,'02','2018-12-25 23:51:10','66589'),(56,'04','2018-09-22 05:40:21','94759'),(57,'01','2018-06-03 13:24:32','76588'),(58,'03','2018-04-21 07:27:01','66573'),(59,'02','2018-09-25 03:49:02','76607'),(60,'01','2018-08-11 10:48:24','47755'),(61,'04','2018-11-02 02:37:54','69570'),(62,'02','2018-08-31 14:44:15','25560'),(63,'02','2018-03-23 07:24:59','78550'),(64,'04','2018-06-07 17:39:12','18455'),(65,'02','2018-10-31 11:14:30','71227'),(66,'01','2018-10-31 14:23:16','2454'),(67,'04','2018-08-14 21:16:17','54700'),(68,'04','2018-06-05 01:04:59','59554'),(69,'01','2018-01-23 10:33:50','37798'),(70,'04','2018-06-09 12:00:56','59983'),(71,'04','2018-02-19 15:56:33','56754'),(72,'04','2018-12-24 13:14:33','54684'),(73,'02','2018-05-15 23:08:16','66531'),(74,'02','2018-06-25 19:08:32','25407'),(75,'02','2018-11-25 18:53:14','96119'),(76,'01','2018-05-28 02:43:26','30991'),(77,'04','2018-03-18 02:03:29','81845'),(78,'02','2018-02-18 20:23:03','1057'),(79,'01','2018-11-15 16:32:26','52151'),(80,'01','2018-07-25 19:54:37','91069'),(81,'04','2018-04-18 14:02:13','51134'),(82,'02','2018-06-04 07:27:50','4609'),(83,'04','2018-07-07 20:22:38','62669'),(84,'02','2018-08-13 10:40:36','93161'),(85,'03','2018-03-29 12:51:47','75717'),(86,'04','2018-04-03 15:35:12','67327'),(87,'04','2018-02-06 15:56:58','87785'),(88,'01','2018-12-16 04:38:52','34503'),(89,'03','2018-07-25 02:52:09','87144'),(90,'04','2018-08-21 21:48:58','37756'),(91,'01','2018-07-25 20:38:00','5070'),(92,'01','2018-05-23 17:56:31','40138'),(93,'01','2018-01-10 22:47:36','35626'),(94,'01','2018-03-20 22:01:19','94446'),(95,'01','2018-05-20 23:55:32','65411'),(96,'02','2018-08-06 07:52:03','48145'),(97,'02','2018-05-26 22:38:07','17564'),(98,'01','2018-05-31 21:40:52','59694'),(99,'03','2018-03-03 16:47:55','18672'),(100,'01','2018-04-29 06:15:51','52622');

ปกติ(เรา)เขียน SQL เพื่อสรุปค่าใช้จ่ายแยกตามประเภทและรายเดือนตามตารางรูปขวา

SELECT
    CategoryName, 
    SUM(IF(M = 1, TotalAmount, 0 )) AS '1',
    SUM(IF(M = 2, TotalAmount, 0 )) AS '2',
    SUM(IF(M = 3, TotalAmount, 0 )) AS '3',
    SUM(IF(M = 4, TotalAmount, 0 )) AS '4',
    SUM(IF(M = 5, TotalAmount, 0 )) AS '5',
    SUM(IF(M = 6, TotalAmount, 0 )) AS '6',
    SUM(IF(M = 7, TotalAmount, 0 )) AS '7',
    SUM(IF(M = 8, TotalAmount, 0 )) AS '8',
    SUM(IF(M = 9, TotalAmount, 0 )) AS '9',
    SUM(IF(M = 10, TotalAmount, 0 )) AS '10',
    SUM(IF(M = 11, TotalAmount, 0 )) AS '11',
    SUM(IF(M = 12, TotalAmount, 0 )) AS '12',
    SUM(TotalAmount) AS Total
FROM (
    SELECT
        MONTH(Expense.ExpenseDate) AS M,
        MONTHNAME(Expense.ExpenseDate) AS MN,
        Expense.CategoryId, 
        ExpenseCategory.CategoryName, 
        SUM(Amount) AS TotalAmount
    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId
    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)
) T 
GROUP BY T.CategoryId 
ORDER BY T.CategoryId ASC;

ผลลัพธ์ก็จะประมาณนี้

  • แถวเป็นประเภทค่าใช้จ่าย
  • คอลัมภ์เป็นยอดรวมรายเดือน

แบบนี้บ่อย ๆ ก็เหนื่อยเหมือนกันนะ  (อาจมีคนเขียน SQL สั้นและง่ายกว่านี้แหล่ะ  ก็ช่วยแนะนำด้วยหล่ะกันครับ) มันต้องมีคนเคยเหนื่อยเหมือนเรานะ และก็มีจริงด้วย คุณ Rick James เค้าก็ทำ Function เพื่อทำ Pivot Table ไว้เหมือนกัน (Blog นี้แนะนำให้อ่านเลย)

DELIMITER //
DROP   PROCEDURE IF EXISTS PIVOTTABLE //
CREATE PROCEDURE PIVOTTABLE(
    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)
    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas
    IN pivot_col VARCHAR(64),      -- name of column to put across the top
    IN tally_col VARCHAR(64),      -- name of column to SUM up
    IN where_clause VARCHAR(99),   -- empty string or "WHERE ..."
    IN order_by VARCHAR(99)        -- empty string or "ORDER BY ..."; usually the base_cols
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    -- Find the distinct values
    -- Build the SUM()s
    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
    -- select @subq;

    SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    -- select @cc2, @cc3;
    SET @qval = CONCAT("'\"', val, '\"'");
    -- select @qval;
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    -- select @cc4;

    SET SESSION group_concat_max_len = 10000;   -- just in case
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     select @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      -- Intermediate step: build SQL for columns
    DEALLOCATE PREPARE _sql;
    -- Construct the query and perform it
    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',\n',
                @sums,
                ',\n SUM(', tally_col, ') AS Total'
            '\n FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            '\n WITH ROLLUP',
            '\n', order_by
        );
    select @stmt2;                    -- The statement that generates the result
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     -- The resulting pivot table ouput
    DEALLOCATE PREPARE _sql;
    -- For debugging / tweaking, SELECT the various @variables after CALLing.
END;
//
DELIMITER ;

วิธีเรียกใช้งานก็ง่าย ๆ เลยแบบนี้

CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');

ตาราง TEMP ก็สร้างขึ้นมาจากสรุป (ตารางฝั่งซ้ายจากรูปแรก)

DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP (
    SELECT
        MONTH(Expense.ExpenseDate) AS M,
        MONTHNAME(Expense.ExpenseDate) AS MN,
        Expense.CategoryId AS CategoryId, 
        ExpenseCategory.CategoryName AS CategoryName, 
        SUM(Amount) AS TotalAmount
    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId
    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)
);

SELECT * FROM TEMP;

หลังจากเรียกฟังก์ชั่นเสร็จผลลัพธ์ก็เหมือนที่เราเขียนเองนั่นแหล่ะ ^_^ แต่ลดแรงงานได้อีก

CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');

ป.ล.

การคำนวณระยะห่างระหว่างจุดสองจุด (Latitude/Longitude points) ใน MySQL

วันนี้เขียนบันทึกสั้น ๆ หล่ะกัน  ^_^

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

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

อย่ากระนั้นเลยบางทีสิ่งที่เราต้องการก็รอโปรแกรมหรือทูลมาทำงานได้ไม่ทันการณ์ ตัวอย่างเช่น “ขอรายชื่อ/บ้านของชาวบ้านที่อยู่ห่างจากบ้านที่พบผู้ป่วยไข้เลือดออกรัศมี 100 เมตร” แล้วสิ่งที่เรามี (พื้นฐาน) หล่ะมีอะไรบ้าง
– ข้อมูลพิกัดจาก HIS (Latitude/Longitude) มีเก็บ
– ทางเลือก โปรแกรม/ทูลก็ดีไปคลิก ๆ ค้นหา (มีเว็บ gisjhcis ให้ใช้นะครับ ตัวนี้ก็ใช้บริการ/API แผนที่จาก Google อีกต่อนึง) หรือคำนวณจากพิกัดที่เรามี
เรามันสายฮาร์ดคอร์อยู่แล้ว ทำเรื่องง่ายให้เป็นเรื่องยากสิ 5555 งั้นก็เริ่มกันเลย

  1. สร้างฟังก์ชั่นสำหรับการคำนวณระยะห่างจุดสองจุด (Latitude/Longitude points) ใน MySQL โดยใช้ Haversine formula รายละเอียดก็ไปอ่านต่อได้เลย
    DELIMITER $$
    DROP FUNCTION IF EXISTS haversine $$
    CREATE FUNCTION haversine (
            lat1 FLOAT, lon1 FLOAT,
            lat2 FLOAT, lon2 FLOAT
    ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    BEGIN
        DECLARE r FLOAT unsigned DEFAULT 6372.8;
        DECLARE dLat FLOAT unsigned;
        DECLARE dLon FLOAT unsigned;
        DECLARE a FLOAT unsigned;
        DECLARE c FLOAT unsigned;
     
        SET dLat = RADIANS(lat2 - lat1);
        SET dLon = RADIANS(lon2 - lon1);
        SET lat1 = RADIANS(lat1);
        SET lat2 = RADIANS(lat2);
     
        SET a = POW(SIN(dLat / 2), 2) + COS(lat1) * COS(lat2) * POW(SIN(dLon / 2), 2);
        SET c = 2 * ASIN(SQRT(a));
        
        SET r = IF(htype = 'km', r, 3959);
        
        RETURN (r * c);
    END$$
     
    DELIMITER ;
  2. ลองทดสอบเรียกใช้งานฟังก์ชั่นดูจากพิกัดที่เรามี
    -- Ex
    SET @origin_lat0=15.829187;
    SET @origin_long0=105.262752;
    SET @origin_lat=15.828063;
    SET @origin_long=105.380678;
    
    SELECT ROUND(haversine(@origin_lat0, @origin_long0, @origin_lat, @origin_long), 2) AS distance_km;
    +-------------+
    | distance_km |
    +-------------+
    |       12.62 |
    +-------------+
    1 row in set (0.00 sec)
    
  3. ตรวจสอบว่าคลาดเคลื่อนมากน้อยแค่ไหน
  4. ทีนี้เราก็สามารถใช้ข้อมูลพิกัดจากตาราง house (ในที่นี้ใช้ HIS เป็น JHCIS ของกระทรวงฯ นะครับ) ได้เลย
  5. จบปิ๊งงงง ^_^

เพิ่มเติม
Fastest Way to Find Distance Between Two Lat/Long Points
Haversine formula
GISFORJHCIS

ป.ล.
เอาไปคำนวณระยะห่างระหว่างเราน่าจะไม่ได้