in How To |

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

(My) Wishlist WordPress Starter Themes

ถ้าอยากสร้าง WordPress Theme เองควรเริ่มจากอะไรดี ? คำถามก็สั้นๆ รวมถึงเวลาด้วย เพราะงั้นก็ควรเริ่มที่ Starter Theme นั่นแหล่ะง่ายและเร็วสุดแล้ว แล้วจะเลือกตัวไหนดีหล่ะ เรามีตัวเลือกมาให้คร่าว ๆ ซึ่งบางธีมเองก็เวอร์ชั่นเก่าพอสมควรแต่โครงสร้างมันง่ายสำหรับการแก้ไขและทำเพิ่ม (เรียงตามตัวอักษรหล่ะกัน)

ป.ล. บล๊อกสั้น ๆ นะ พยายามบังคับตัวเองให้บล๊อกอยู่

  1. Aamla
  2. Air-light
  3. Beans
  4. Bootstrap-four
  5. FoundationPress
  6. Generic
  7. html5blank
  8. JointsWP
  9. Nebula
  10. Oceanwp
  11. _s
  12. Quark
  13. Silencio
  14. _tk
  15. WP-bootstrap-starter
  16. WP-Rootstrap
  17. Understrap
  18. twentynineteen
    twentyseventeen
    twentysixteen

 

Unfri(end)

เก็บรักที่เพิ่งพ้นไป
เก็บใจที่เพิ่งพ้นมา
บอกลาแล้ววันเก่า
เหลือแค่เพียงน้ำตา
กับแผลที่เธอให้มา

— ช่วงที่เศร้าที่สุดในเพลงกลับเป็น 24 วินาทีแรก

Create multiple directories at once mkdir

สั้น !!
ความน่ารักและมีประโยชน์ของ Bash ก็คือบางอย่างเราก็สามารถลดเวลาได้ด้วยการทำ Script สั้น ๆ เช่นกรณีนี้ ต้องการสร้าง Directory ตามโครงสร้างนี้

├── config
│   └── webui
├── data
│   ├── director
│   ├── storage
│   └── webui
├── mysql
│   ├── data
│   └── log

เราสามารถใช้คำสั่งสั้น ๆ ข้างล่าง เพื่อสร้างโฟลเดอร์

mkdir -p {data/{director,storage,webui},config/webui,mysql/{data,log}}

และกรณีอื่น ๆ อีก ไปลองกันได้ว่าผลลัพธ์จะได้ออกมายังไง

mkdir test{1..50}
mkdir -p test{1..50}/sub{1..50}
mkdir {a-z}12345 
mkdir {1,2,3}
mkdir test{01..10}
mkdir -p `date '+%y%m%d'`/{1,2,3} 
mkdir -p $USER/{1,2,3}

จบปิ๊ง !!