in บันทึก |

transfer.sh – Easy file sharing from the command line

transfer.sh เป็นบริการ file sharing แบบต้องใช้ command line ตอนนี้ก็รองรับทั้งการฝากไฟล์ที่อยู่ในเครื่อง (local file) s3 (Amazon S3) และ gdrive (Google Drive) โดยฟีเจอร์หลัก ๆ ก็มี

  • ผ่าน shell command ได้ (Powershell ด้วย)
  • ขนาดไฟล์สูงสุดได้ 10 GB
  • ฝากไฟล์ได้ 14 วัน
  • เข้ารหัสไฟล์ได้
  • กำหนดเงื่อนไข (จำนวน) การดาวน์โหลด
  • ฟรี 👏👏👏

ตัวอย่างแบบเบสิคเลย

# Uploading is easy using curl
$ curl --upload-file ./hello.txt https://transfer.sh/hello.txt
https://transfer.sh/66nb8/hello.txt

$ curl -H "Max-Downloads: 1" -H "Max-Days: 5" --upload-file ./hello.txt https://transfer.sh/hello.txt
https://transfer.sh/66nb8/hello.txt
# Download the file
$ curl https://transfer.sh/66nb8/hello.txt -o hello.txt

หรือจะเป็นการ Backup MySQL ก็ทำได้เหมือนกัน

# Backup, encrypt and transfer
$ mysqldump --all-databases|gzip|gpg -ac -o-|curl -X PUT --upload-file "-" https://transfer.sh/test.txt

* ถ้าใครสนใจลองเข้าไปใช้งานได้ที่เว็บ transfer.sh ส่วนตัวแล้วเป็นบริการที่ชอบเลย ด้วยความที่เป็นอะไรที่ใช้งานง่ายและมีประโยชน์จริง ๆ

** โปรเจคอยู่บน github (https://github.com/dutchcoders/transfer.sh) จะเอามาทำใช้เองก็ได้มี Docker ให้ด้วย

สร้าง docker container สำหรับทำ REST API กัน – Full fake data

ในวันที่ต้อง Mock API แบบด่วน ๆ สำหรับทดสอบ Front-end (แต่ Back-end จริง ๆ มันยังอยู่แค่ในร่างออกแบบ) งั้นก็เริ่มกันเลย

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

npm install -g json-server

ส่วนการสร้างชุดข้อมูลก็ใช้ faker.js (เราใช้ lodash เพิ่ม ก็ติดตั้งเข้าไปพร้อมกันเลย)

npm install faker lodash

สร้างชุดข้อมูลแบบ random ด้วยไฟล์ชื่อ generator.js เข้าไป

// generator.js
module.exports = function () {
    var faker = require("faker");
    var _ = require("lodash");
    return {
        customers: _.times(Math.floor((Math.random() * 1000) + 1), function (n) {
            return {
                id: n+1,
                firstname: faker.name.firstName(),
                lastname: faker.name.lastName(),
                birthdate: faker.date.past(50, new Date("Sat Sep 20 1992 21:35:02 GMT+0700 (ICT)")),
                address: faker.address.streetAddress(),
                city: faker.address.city(),
                country: faker.address.country(),
                email: faker.internet.email(),
                avatar: faker.internet.avatar()
            }
        })
    }
}

หลังจากที่เรา Start Server ด้วยคำสั่ง json-server generator.js ข้อมูลที่เรา generate ขึ้นหน้าตาก็ประมาณนี้

ท้ายสุดเราก็แพ็คลง Container เลย โดยสร้าง Dockerfile ไว้ใช้งานยาว ๆ  (เป็นลางว่า Back-end จะยังไม่เสร็จในเร็ววันใช่ไหม 55555)

FROM node:10-alpine

LABEL author="mf"
LABEL version="latest"

ENV NPM_CONFIG_LOGLEVEL info
ENV TZ Asia/Bangkok

RUN apk add --no-cache tzdata \
    && cp /usr/share/zoneinfo/Asia/Bangkok /etc/localtime \
    && echo "Asia/Bangkok" >  /etc/timezone \
    && rm -rf /var/cache/apk/*

# Create application directory
RUN mkdir -p /data \
    && npm install -g json-server lodash faker
WORKDIR /data

VOLUME [ "/data" ]
EXPOSE 3000

# Default command
ENTRYPOINT ["json-server"]
CMD ["--help"]

หลังจาก build Dockerfile แล้วก็ลองรันกันเลย ถ้าไม่ทำอะไรผิดพลาดผลลัพธ์มันก็ควรจะเป็นดังรูป

docker run -d -p 3000:3000 -v `pwd`/app:/data mf/mockup-api --watch generator.js --host 0.0.0.0

จบปิ๊งง ^_^

#ยังคิดถึงเสมอ

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

 

 

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

ป.ล.