in บันทึก |

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 เศร้าาาวันทำงาน

มินิรีวิว Mi Notebook Pro กับหนึ่งเดือนที่ผ่านมา

เมื่อช่วงเดือนพฤศจิกายนปีที่แล้ว (2017) Xiaomi ได้จัดงาน Mi Product Launch และเปิดตัว Mi Notebook Pro แล็บท็อปสุดบางสเปคระดับบน ซึ่งเป็นรุ่นต่อจาก Mi Notebook Air โดยเปิดตัวรุ่นย่อยออกมาแยกตามซีพียูและความจุ ดังนี้

  • Core i5 + RAM 8GB ราคา 5599 หยวน (28,xxx บาท)
  • Core i7 + RAM 8GB ราคา 6399 หยวน (32,xxx บาท)
  • Core i7 + RAM 16GB ราคา 6999 หยวน (35,xxx บาท)

เราเองเพิ่งได้เครื่องตัว Core i7 RAM 16GB มาเดือนที่แล้ว สนนราคาที่ขายในไทยก็อยู่ที่ 37,900 บาท (ก็ยังถือเป็นเครื่องหิ้วอยู่ ศูนย์ Xiaomi ในไทยยังไม่มีวี่แววว่าจะนำเข้ามาจำหน่าย)

ดีไซน์และตัวเครื่อง

ดีไซน์โดยรวมของตัวเครื่องบอกตรง ๆ ว่าเอามาชนกับ Macbook Pro ของ Apple แน่นอนอย่างไม่ต้องสงสัย โดยดีไซน์ภายนอกของ Mi Notebook Pro สีเทาเข้ม(Dark Gray) บอดี้ทำจากแม็กนีเซียมอัลลอย หนัก 1.95 กก. หน้าจอขนาด 15.6 นิ้ว Full-HD (1920×1080) 16:9 IPS display ขอบจอบาง ครอบทับด้วย Gorilla Glass 3 ซีพียูเป็น Intel Core i7 Gen 8 มี 4 แกน 8 เธรด ความถี่ 4.0 GHz การ์ดจอ NVIDIA GeForce MX150 สำหรับอัลตร้าบุ๊ค แรม DRR4 ขนาด 16 GB SSD PCIe NVMe ขนาด 256GB
คีย์บอร์ดขนาดฟูลไซส์ มีไฟ backlit สำหรับใช้งานในที่มืดและบริเวณ Touchpad มีเซนเซอร์สแกนลายนิ้วมือมาให้ด้วย ใช้ในการล็อกอินผ่าน Windows จากการใช้งานถือว่าทำงานได้เร็วและแม่นยำมาก ส่วนพอร์ทสำหรับเชื่อมต่อมี

  • USB Type C จำนวน 2 พอร์ท โดยแยกเป็น 1 พอร์ทที่รองรับเฉพาะถ่ายโอนข้อมูล อีกพอร์ทเป็น Full Functional ทั้งชาร์จไฟและถ่ายโอนข้อมูล
  • Full HDMI
  • USB 3.0 จำนวน 2 พอร์ท
  • พอร์ท 3-in-1 SD Card
  • ช่องหูฟัง 3.5 มม.

ส่วนสเปคอุปกรณ์อื่นก็มี

  • ลำโพง Harman Infinity มาพร้อมระบบเสียง Dolby Atmos
  • SSD 256GB PCIe (พร้อมช่อง NVMe อีก 4 ช่องสำหรับเพิ่มหน่วยความจำ)
  • Wi-Fi AC Dual-Band

การใช้งานและแบตเตอรี่

จากการใช้งานมาได้สักระยะ ปัญหาที่พบคือปัญหาเรื่องพอร์ทเชื่อมต่อ ที่จริงก็ไม่ใช่ปัญหาของ Mi Notebook Pro หรอก โดยลักษณะใช้งานของตัวเองที่ใช้งานพอร์ทอีเธอร์เนตเป็นหลักรวมทั้งพอร์ท VGA ซึ่งก็หลีกหนีไม่พ้นต้องหาอุปกรณ์เพิ่ม ส่วนการใช้งานอื่นที่เครื่องมีมาให้สามารถรองรับได้ในระดับที่น่าพอใจ

ส่วนแบตเตอรี่ (สเปคเป็น 8000mAh lithium-polymer battery) จากการใช้งานเฉลี่ยก็ราว ๆ 8 ชั่วโมงทั้งนี้ทั้งนั้นก็ขึ้นกับลักษณะการใช้งานด้วย ระยะเวลาชาร์ทอยู่ที่ 1 ชั่วโมงกว่า รองรับ Quick charge ราว ๆ 35-40 นาทีก็ 50% อันนี้เราว่าผ่านกรณีสำหรับการเป็นอัลตร้าบุ๊ค

สรุป

Mi Notebook Pro นับเป็นอัลตราบุ๊คที่ประสิทธิภาพต่อราคาถือว่าคุ้มค่า ด้วยการออกแบบและวัสดุเองที่เน้นความสวยงามและมินิมอลิลต์ตามเทรนด์นิยม การแสดงผลของหน้าจอ IPSขนาด 15.6 นิ้วก็ทำได้ดี การใช้งานกรณีอยู่กลางแจ้งก็ไม่ได้แย่อะไร

ข้อดี

  • ตัวเครื่องและวัสดุให้ความรู้สึกพรีเมี่ยม
  • ประสิทธิภาพดี (i7-8550U รวมกับ RAM 16GB นี่สบาย)
  • เสียงจากลำโพงดีเลย
  • Touchpad ตอบสนองได้ดี
  • แสกนลายนิ้วมือทำงานได้เร็วและแม่นยำ
  • คุ้มค่า

ข้อเสีย

  • การระบายความร้อนยังทำได้ไม่ดี
  • Windows 10 เป็นเวอร์ชั่นภาษาจีน
  • แป้นคีย์บอร์ดไม่มีภาษาไทย

ป.ล.

  • เพื่อให้รองรับกับพฤติกรรมใช้งานตัวเองเราสั่ง USB Type C To VGA Ethernet with USB 3.o x 2 Adapter เพิ่ม
  • เนื่องจาก Mi Notebook Pro รองรับการเพิ่ม SSD อยู่แล้ว ก็คงได้เพิ่มจริง ๆ 256 สำหรับเรานี่ไม่พอ
  • เครื่องเราทำ  Dual Boot (Windows 10 + Ubuntu 18.04)

Convert pdf to image – แปลงไฟล์ PDF เป็นรูปภาพด้วย Linux บน Windows กัน

จั่วหัวมาแบบนี้ก็อย่าเพิ่งสับสน ณ วันนี้ (พฤษภาคม 2561) Linux กับ Windows เป็นมิตรกันมากกกกกกกกกซึ่งก็ถือเป็นเรื่องที่ดีจริง ๆ สำหรับคนในวงการคอมพิวเตอร์และทั่ว ๆ ไปถือว่าดีกับทุกฝ่ายหล่ะกัน (ลองหาข่าวช่วงก่อนหน้านี้ดูคร่าว ๆ ได้ว่าสังคมสรรเสริญเรื่องนี้กันขนาดไหน) ถ้าใครอยากลองใช้งาน Linux บน Windows ก็ทำตามคร่าว ๆ ประมาณนี้ (แบบละเอียดก็ตามเอกสารตัวนี้ได้เลย)

  1. เปิด PowerShell รันคำสั่งนี้

    รอสักครู่เพื่อให้ระบบเปิดใช้งานฟีเจอร์ “Windows Subsystem for Linux”
  2. เปิด Microsoft Store เพื่อติดตั้ง Ubuntu ต่อได้เลย

    ขนาดไฟล์ก็ไม่ใหญ่มาก
  3. พอติดตั้งเสร็จเราก็เริ่มใช้งานกันได้เลย
  4. จะเห็นได้ว่าเวอร์ชั่นที่ติดตั้งยังเป็น 16.04 (Xenial Xerus) อยู่ ณ ปัจจุบันก็ 18.04 ไปเรียบร้อยแล้ว แต่แค่นี้ก็เพียงพอสำหรับการใช้งานละ ^_^

พอระบบพร้อมสำหรับใช้งานก็มาเริ่มแปลงไฟล์ PDF เป็นรูปภาพกัน (มันมีที่มานิดนึงว่าทำไมต้องใช้วิธีแบบนี้ เนื่องจากเรามีไฟล์ PDF จำนวนมากก จำนวนหนึ่งที่ต้องแปลงเป็นไฟล์รูปภาพแบบด่วน ๆ ลองค้นเครื่องมือบนวินโดวส์ที่เป็นเครื่องมือฟรีรวมถึงออนไลน์ด้วยก็พบว่ามีจำนวนน้อยเกินไป ตัวดี ๆ ก็ Commercial ซะ เศร้าาา และบวกกับความขี้เกียจและเคยชินว่า Linux แปลงไฟล์ตามโจทย์นี้แบบสะดวกเลย แค่นั้นแหล่ะ) เริ่มเลยหล่ะกัน

  1. สร้างไฟล์ Bash Script มา 1 ไฟล์ 
    #!/bin/bash
for file in *.pdf ; do
  convert -verbose -density 300 -quality 100 "${file}" "${file%.*}".png
done

    โดยเราใช้โปรแกรมที่ชื่อว่า convert ที่อยู่ใน imagemagick package ก็ให้ติดตั้งโดยใช้คำสั่ง

    sudo apt-get install imagemagick

    ซึ่ง Bash script ข้างบนจะทำการแปลงทุกไฟล์ที่นามสกุล *.pdf ใน Directory ปัจจุบัน และบันทึกเป็นไฟล์รูปภาพนามสกุล *.png ส่วน พารามิเตอร์ที่ใช้ ก็ตามไปอ่านต่อที่นี่ได้

  2. ลองทดสอบกัน สมมุติเรามีไฟล์ pdf จำนวนหนึ่ง ตัวอย่างมีไฟล์ 2 ไฟล์ ไฟล์ละ 2 หน้า (ไฟล์เดียวกันนั่นแหล่ะ)

  3. รัน Bash Script เพื่อแปลงไฟล์เป็นรูปภาพซะ

    เราจะได้ไฟล์รูปภาพ 1 ไฟล์ ต่อ 1 หน้ารันลำดับชื่อไฟล์
  4. จบปิ๊ง

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

VS Code font rendering looks ugly on Linux

source : https://github.com/Microsoft/vscode/issues/35675

  1. Create fontconfig.conf and put this file in /usr/share/code/fontconfig.conf 
    <?xml version='1.0'?>
<!DOCTYPE fontconfig SYSTEM 'fonts.dtd'>
<fontconfig>
  <!-- Force RGBA subpixel aliasing to «none» in code and code-insiders -->
  <match target="pattern">
    <or>
      <test name="prgname">
        <string>code</string>
      </test>
      <test name="prgname">
        <string>code-insiders</string>
      </test>
    </or>
    <edit name="rgba" mode="assign">
      <const>none</const>
    </edit>
  </match>
</fontconfig>
  2. Symlink file to /etc/fonts/conf.d/99-vscode.conf

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');

ป.ล.

HDC V4.0 & Smart Card On Ubuntu

เนื่องจาก HDC ของกระทรวงสาธารณสุขได้ปรับปรุงเวอร์ชั่นใหม่ ทำให้การเข้าใช้งานระบบด้วย Smart Card ด้วย Linux แบบเดิมไม่สามารถทำได้ เคยเขียนวิธีใช้เวอร์ชั่นเดิมไว้ที่นี่ (HDC & Smart Card On Ubuntu)

ถ้าจากข้อมูลการปรับปรุงจะเห็นว่า

  • มีการใช้ HTTPS
  • มีการปรับปรุง Agent

ที่นี้เราจะเข้าใช้งานระบบด้วย Smart Card ด้วย Linux (ในที่นี้ใช้ Ubuntu 18.04)

  1. ทำการดาวน์โหลด Smart Card Agent เวอร์ชั่นปรับปรุง ทำการติดตั้งให้เรียบร้อย
  2. ตรวจสอบ Smart Card ในเครื่อง ถ้าพบเครื่องอ่าน Smart Card จะขึ้นหน้าตาแบบนี้ (กรณีไม่พบให้ทำการติดตั้งแพคเกจ sudo apt install pcscd pcsc-tools
  3. ทำการรัน Smart Card Agent ได้เลย (ข้ามเรื่องติดตั้ง JRE ไปเลยนะ ถ้าไม่มีก็ติดตั้งซะ) 
    java -Dsun.security.smartcardio.library=/lib/x86_64-linux-gnu/libpcsclite.so.1 -jar JSmartCardReader.jar 8084

  4. เปิดหน้า Login ของ HDC ก็เป็นอันสามารถเข้าใช้งานได้ เคลียร์ !!!

ป.ล. ที่เปลี่ยนแปลงจากเวอร์ชั่นเดิม

  • Port ปรับมาใช้ 8084 ในตัว Agent
  • ใช้ HTTPS

 

การคำนวณระยะห่างระหว่างจุดสองจุด (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

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

 

ปรับปรุงข้อมูลวันหยุดราชการ (Holiday) กัน

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

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

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

และอีกเว็บไซต์ก็ myhora ซึ่งมีบริการ iCal บริการ

จะเห็นได้ว่ามีไฟล์ CSV (Comma-separated values) ให้เราได้ใช้ด้วย งั้นเราก็มาปรับปรุงวันหยุดราชการด้วยไฟล์ตัวนี้กัน

  1. เริ่มต้นก็ดาวน์โหลดมาซะ 
    wget -O th_holiday.csv http://www.myhora.com/calendar/ical/holiday.aspx?2561.csv

    หน้าตาข้อมูลไฟล์คร่าว ๆ ก็ประมาณนี้

  2. นำเข้าข้อมูลข้างต้นลงในฐานข้อมูลของเราด้วยคำสั่ง LOAD DATA INFILE (สร้างตารางชั่วคราว th_holiday ไว้รอรับด้วย ) 
    DROP TABLE IF EXISTS th_holiday;
CREATE TABLE IF NOT EXISTS th_holiday(
    Subject       	VARCHAR(255) NULL
    ,Start_Date    	DATE NOT NULL
    ,Start_Time    	TIME NULL
    ,End_Date      	DATE NULL
    ,End_Time      	TIME NULL
    ,All_day_event 	VARCHAR(4) NOT NULL
    ,Description   	VARCHAR(255) NOT NULL
    ,Show_time_as  	INTEGER  NOT NULL
    ,Location      	VARCHAR(30)
);

TRUNCATE th_holiday;
LOAD DATA LOCAL INFILE 'th_holiday.csv' 
INTO TABLE th_holiday
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(`Subject`, @start_date, `Start_Time`, `End_Date`, `End_Time`, `All_day_event`, `Description`, `Show_time_as`, `Location`)
SET start_date = STR_TO_DATE(@start_date, '%d/%m/%Y');
  3. เป็นอันเสร็จสิ้น ทีนี้ก็นำข้อมูลนี้ไปปรับปรุงในตารางของแต่ละ HIS