in บันทึก |

หาจำนวนรายใหม่โดยใช้การเปรียบเทียบระหว่างแถวใน MySQL

วันนี้บล็อกสั้น ๆ จากหัวข้อไม่งงกันใช่ไหม ^__^  อธิบายคร่าว ๆ คือกรณีเรามีตารางที่เก็บจำนวนสะสมไว้ในแต่ละวัน เวลาเราต้องการทราบว่าแล้ววันนี้มีเพิ่มเข้ามาใหม่เท่าไหร่ โดยทั่วไปทำมือก็อาจจะ

  • นับจำนวนเฉพาะรายที่มาใหม่ไง
  • หรือเอาจำนวนสะสมของวันนี้/ตอนนี้ ไปลบกับวันที่แล้ว

ใช่นั่นแหล่ะทำด้วยมือ แล้วในภาษา SQL เค้าทำยังไงกัน ให้เห็นภาพมากขึ้นเราจะเอาข้อมูลรายงานเคสโควิด 19 ของประเทศไทยในแต่ละวันมาเป็นข้อมูลดัมมี่หล่ะกัน (ข้อมูลจริงนะ นี่  ศบค. รายงานทุกวัน อยากได้ข้อมูลก็ไปที่กรมควบคุมโรค ที่นี่เลย https://covid19.ddc.moph.go.th/th/api)

*เราตัดเฉพาะยอดสะสมของแต่ละวันในเดือนพฤษภาคมหล่ะกัน เพื่อจะได้ง่ายขึ้น

CREATE TABLE `covid19timeline` (
  `ConfirmedDate` date NOT NULL,
  `Confirmed` int(11) NOT NULL,
  `Recovered` int(11) NOT NULL,
  `Hospitalized` int(11) NOT NULL,
  `Deaths` int(11) NOT NULL,
  PRIMARY KEY (`ConfirmedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-01',2960,2719,187,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-02',2966,2732,180,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-03',2969,2739,176,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-04',2987,2740,193,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-05',2988,2747,187,54);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-06',2989,2761,173,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-07',2992,2772,165,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-08',3000,2784,161,55);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-09',3004,2787,161,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-10',3009,2794,159,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-11',3015,2796,163,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-12',3017,2798,163,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-13',3017,2844,117,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-14',3018,2850,112,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-15',3025,2854,115,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-16',3025,2855,114,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-17',3028,2856,116,56);
INSERT INTO `covid19timeline` (`ConfirmedDate`,`Confirmed`,`Recovered`,`Hospitalized`,`Deaths`) VALUES ('2020-05-18',3031,2857,118,56);

ข้อมูลก็ตามนี้

ทีนี้เราก็หาจำนวนรายใหม่กันว่ามีจำนวนเท่าไหร่โดยการเปรียบเทียบกับวันที่แล้ว

SELECT 
	c1.*,
    COALESCE(c1.Confirmed - c2.Confirmed, 0) AS NewConfirmed
FROM covid19timeline c1 
	LEFT JOIN covid19timeline c2 ON c2.ConfirmedDate = DATE_SUB(c1.ConfirmedDate, INTERVAL 1 DAY)
ORDER BY c1.ConfirmedDate;

ผลลัพธ์ที่ได้ตามนี้ โดย NewConfirmed คือจำนวนรายใหม่ที่เพิ่มเข้ามา

จบปิ๊ง !! ^__^

ป.ล.

  • แล้วเราจะเอามาใช้ตอนไหน โดยปกติส่วนใหญ่ก็มักอยู่ในรายงานที่ต้องการดูความเปลี่ยนแปลงเป็นหลักแต่ข้อมูลที่เรามีมักเป็นข้อมูลสะสมอย่างเดียว อย่างเคสการระบาดของโควิด 19 เป็นต้น (แต่ api เค้ามีแจ้งรายใหม่นะตัดออกเฉย ๆ)
  • ไปลองเล่นกันได้ที่ https://www.db-fiddle.com/f/n3Zbf8FmThZxPGDgSGhmVN/0

rsync ห้ามหยุด ห้ามหายและนายต้องไปต่อ

บล็อกต้อนรับต้นเดือนและวันแรงงาน ด้วยจำเป็นต้องย้ายไฟล์จำนวนน้อยนิดมหาศาล แบบเร่งด่วน จำนวนหนึ่งไปไว้อีกทีและงานนี้ก็ห้ามหยุดจนกว่างานจะเสร็จ (นี่มันสะท้อนชีวิตใครบ้างไหม ^__^) ใครมีงานแบบนี้ต้องทำแบบด่วน ๆ และไม่มีเวลาให้ต้องมามอนิเตอร์และรันใหม่อีกรอบ ก็ใช้สคริปท์นี้ไปปรับได้

อธิบายคร่าว ๆ คือ

  • ใช้ rsync ในการโอนย้ายไฟล์ไปอ่านรายละเอียดต่อได้ที่นี่
  • อาจมีบางช่วงหรือ error ขัดจังหวะช่วงโปรแกรม rsync ทำงานแล้วโปรแกรมปิดตัวลงก็ให้โปรแกรม rsync กลับมาทำงานต่อ (Loop) จนกว่าจะสำเร็จ (ดูบังคับมากมาย)
  • เสร็จแล้วก็ Line notify นิดนึง
#!/usr/bin/env bash

# Declare an array of Token
declare -a ACCESS_TOKENS=("token1" "token2")
START=$(date +"%d-%m-%Y %H:%M")
RC=1

while [[ $RC -ne 0 ]]
do
    NOW=$(date +"%d-%m-%Y %H:%M")

    rsync -arv --partial --progress --ignore-errors [source] [destination]
    RC=$?
    
    if [[ $RC -ne 0 ]] ; then
        echo "$NOW Rsync failure. Backing off and retrying..."
        sleep 180
    fi
done

# Iterate the token array
for token in "${ACCESS_TOKENS[@]}"; do
    echo $token
    curl -X POST https://notify-api.line.me/api/notify -H "Authorization: Bearer $token" -F "Rsync start $START completed normally" > /dev/null 2>&1   
done

echo "Rsync start $START completed normally"

จบปิ๊งงง (ไม่ได้ดื้อก็แค่พยายาม)
ป.ล.
เผื่อเคสต้องรีสตาร์ทก็เอาไปไว้ใน cron job ด้วยนะ ^__^

 

Screenshot หน้าเธอแล้วส่งเข้า Line Notify กัน

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

รวมทั้งข้อมูลรายงานผลโรค COVID-19 ที่ตอนนี้หลาย ๆ สื่อหลาย ๆ ที่ ได้ทำย่อยข้อมูลเหล่านี้ออกมาเป็น Infographic บ้าง เว็บไซค์แสดง Dashboard บ้าง งั้นจะช้าอยู่ไย เราเซฟรูปเหล่านั้นแล้วส่งเข้าไลน์เลยดีกว่า

Shell Script ไม่ได้มีอะไรซับซ้อนใช้ Headless Chrome ให้ Screenshot หน้าจอตอนนั้นแล้วส่งเข้าไลน์ก็เป็นอันเสร็จสิ้น

#!/bin/bash
  
PICTURE_PATH="./screenshot.png"
NOW=$(date +"%d-%m-%Y %H:%M")
IP=$(hostname -I | awk '{ print $1 }')
DASHBOARD="IP Address"

# Declare an array of Token PHP-CC PSH
declare -a ACCESS_TOKENS=("token1" "token2")

command_exists()
{
  command -v "$1" >/dev/null 2>&1
}

# Take pictures using Chrome Headless
if command_exists google-chrome ; then
    google-chrome --headless --no-sandbox --screenshot --hide-scrollbars --virtual-time-budget=10000 --disable-gpu --window-size=680,380 http://$IP:88/covid
    convert -resize 1280x screenshot.png screenshot-01.png

    google-chrome --headless --no-sandbox --screenshot --hide-scrollbars --virtual-time-budget=10000 --disable-gpu --window-size=1280,1410 http://$DASHBOARD
    mv screenshot.png screenshot-02.png
else
    if command_exists docker ; then 
        sudo docker container run -it --rm -v $(pwd):/usr/src/app zenika/alpine-chrome --no-sandbox --screenshot --hide-scrollbars --virtual-time-budget=10000 --disable-gpu --window-size=680,380 http://$IP:88/covid
        convert -resize 1280x screenshot.png screenshot-01.png

        sudo docker container run -it --rm -v $(pwd):/usr/src/app zenika/alpine-chrome --no-sandbox --screenshot --hide-scrollbars --virtual-time-budget=10000 --disable-gpu --window-size=1280,1410 http://$DASHBOARD
        mv screenshot.png screenshot-02.png
    else
        echo 'Not found docker'
    fi  
fi

convert -append screenshot-01.png screenshot-02.png screenshot.png

# Iterate the token array
for token in "${ACCESS_TOKENS[@]}"; do
    echo $token
    # curl -X POST https://notify-api.line.me/api/notify -H "Authorization: Bearer $token" -F "message=รายงานสถานการณ์ COVID-19 ณ $NOW" -F "imageFile=@$PICTURE_PATH" > /dev/null 2>&1   
done

ผลลัพธ์ที่ได้ก็ตามนี้

จบปิ๊ง บล๊อกเดือนนี้ ^__^
ป.ล.

  • เราใช้ Dashboard ของ อ.เอ แห่ง Developer Mate
  • มี Dashboard 2 ตัว ก็เลย Merge ไฟล์รวมกันซะจะทิ้งไปก็เสียดาย
  • Token ของ Line Notify น่าจะมีกันแล้วนะ อันนี้ข้ามเรื่องการสร้าง Line Notify Token หล่ะกัน
  • ตัวเลขผู้ติดเชื้อวันนี้เห็นแล้วมีแรงทำงานสู้กันต่อ

ลบแถวที่ซ้ำกันใน MySQL

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

เริ่มต้นเราดูตัวอย่างข้อมูลที่ซ้ำกันก่อน

DROP TABLE IF EXISTS contacts;
 
CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL, 
    email VARCHAR(255) NOT NULL
);
 
INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

มาดูชุดข้อมูลที่ซ้ำกันบ้างว่ามีแถวใดบ้างที่ซ้ำกัน

SELECT 
    email, 
    COUNT(email),
    GROUP_CONCAT(id) AS id_list
FROM contacts
GROUP BY email
HAVING COUNT(email) > 1;


ในแถวที่ซ้ำกันเรา และอยากเก็บไว้แค่ 1 แถว
วิธีที่ 1
ทำการลบด้วยคำสั่งดังนี้

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE c1.id > c2.id AND c1.email = c2.email;

จากคำสั่งข้างบนหมายความว่าเมื่อมีแถวที่ซ้ำกันให้เก็บไว้เฉพาะแถวแรกที่พบ (id ที่ค่าน้อยกว่า) ซึ่งผลลัพธ์ได้ก็ประมาณนี้ (id 6, 12, 13, 14 จะถูกลบไป)

วิธีที่ 2

ALTER IGNORE TABLE contacts 
ADD UNIQUE INDEX idx1(email);

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

วิธีที่ 3

เนื่องจากหมายเหตุในวิธีที่ 2 ใน MySQL เวอร์ชั่นใหม่ ๆ ก็เลยใช้วิธีแบบนี้แทน

CREATE TABLE contacts_copy LIKE contacts;
ALTER TABLE contacts_copy ADD UNIQUE INDEX idx1(email);

INSERT IGNORE INTO contacts_copy
SELECT * FROM contacts;

DROP TABLE contacts;
ALTER TABLE contacts_copy RENAME TO contacts;

จบปิ๊ง ๆ
ไปลองทดสอบกันได้ที่ https://www.db-fiddle.com/f/sXB3sBDQEdrbdEDFi98Y3j/0
**ส่วนความเจ็บปวดซ้ำ ๆ หน่ะมันลบไม่ได้หรอกนะ

ใช้ 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;

ผลลัพธ์

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

จบปิ๊ง !!!

ทดสอบส่ง Line Notify ด้วย curl ใน PowerShell

PowerShell คืออะไร ?

PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language. Initially a Windows component only, known as Windows PowerShell, it was made open-source and cross-platform on 18 August 2016 with the introduction of PowerShell Core.[5] The former is built on .NET Framework while the latter on .NET Core.

ที่มา : https://en.wikipedia.org/wiki/PowerShell

ถ้าพูดให้เข้าใจง่าย ๆ คือ เป็น Command Shell รูปใหม่ของ Microsoft ที่คล้ายกับ command line แต่สามารถทำอะไรได้มากกว่า ทำงานบน .NET Framework สามารถจัดการบริหาร Windows ได้ผ่านคำสั่ง cmdlets ทีนี้ถ้าใครคุ้นชินกับ Shell อื่น ๆ ใน Linux หรือ Unix Base ก็จะใช้งานผ่าน Shell อาทิ

  • Bourne shell (sh)
  • Debian Almquist shell (dash)
  • Bourne-Again shell (bash)
  • C shell (csh)

ซึ่ง Shell เหล่านี้มันมี curl ให้ใช้ใน Distro นั้น ๆ อยู่แล้ว (คือติดตั้งมาก่อน ถ้าไม่มีก็ติดตั้งเพิ่มตามแต่ละ Distro ไป) แล้ว curl คืออะไร

curl is a tool to transfer data from or to a server, using one of the supported
protocols (DICT, FILE, FTP, FTPS, GOPHER, HTTP, HTTPS,IMAP, IMAPS, LDAP, LDAPS, POP3,POP3S, RTMP, RTSP, SCP, SFTP, SMB, SMBS, SMTP, SMTPS, TELNET and TFTP).The command is designed to work without user interaction.

ไปตามอ่านวิธีใช้งานกันได้ที่ https://devahoy.com/blog/2016/11/getting-started-with-curl

โดย curl ไม่ได้มีมาพร้อมกับ PowerShell แต่ถ้าเราต้องการใช้คำสั่งนี้ใน PowerShell ที่ให้ผลลัพธ์เช่นเดียวกับ curl หล่ะทำไง ^__^  (มีวิธีติดตั้ง curl ที่เป็นเวอร์ชั่น Windows อยู่แต่ก็นั่นหล่ะ : https://curl.haxx.se/ )

ซึ่ง PowerShell ก็มีคำสั่งที่ใช้งานได้ในลักษณะเดียวกันกับ curl ชื่อว่า Invoke-WebRequest รายละเอียดลึก ๆ ตามไปอ่านที่นี่นะครับ ซึ่ง PowerShell ตั้งแต่เวอร์ชั่น 5.0 เป็นต้นมาสามารถใช้ Alias curl แทน Invoke-WebRequest ได้ ทดสอบก่อนว่าเราใช้ PowerShell เวอร์ชั่นอะไร

$PSversiontable

เกริ่นมาเยอะขิง ๆ อยากแค่ทดสอบ Line Notify เอง ตัดจบหล่ะกันนะ ^___^ มีงานงอก ตามตัวอย่างเอกสารของทางไลน์เวลาเราจะทดสอบส่ง Line Notify สามารถใช้คำสั่งตามนี้

curl -X POST -H 'Authorization: Bearer [access_token]' -F 'message=foobar' https://notify-api.line.me/api/notify

ส่วนใน PowerShell เราทดสอบก่อนว่าสามารถใช้ curl ได้หรือไม่จากคำสั่งนี้

Get-Alias -Definition Invoke-WebRequest | Format-Table -AutoSize

ถ้าขึ้นแบบนี้เป็นอันว่าเราสามารถใช้ curl แทน(Alias) Invoke-WebRequest ได้เลย งั้นก็ทดสอบกันเลย ส่ง Line Notify กัน

curl -Uri 'https://notify-api.line.me/api/notify' -Method Post -Headers @{Authorization = 'Bearer [access_token]'} -Body @{message = 'PowerShell Notification'}

ส่งสำเร็จก็จะขึ้นผลลัพธ์ตามนี้

จบปิ๊ง ^__^ (เราตัดเรื่องการขอ Access Token ไปนะ เชื่อว่าคงหาอ่านได้ไม่ยากก)
ป.ล.1  มือใหม่ PowerShell
ป.ล.2 มือใหม่หัดรัก(หัดเจ็บด้วย)

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 ต่อ แนะนำเลย
หลับฝันดีมีแฮง ^__^