Sql Postgresql Group By String Aggregation

วิธีการเชื่อมต่อสตริงในคำถาม Group By ของ PostgreSQL

เมื่อทำงานกับฐานข้อมูล โดยเฉพาะอย่างยิ่งกับ PostgreSQL คุณอาจพบสถานการณ์ทั่วไปที่คุณต้องการเชื่อมต่อสตริงภายในกลุ่มของระเบียน สิ่งนี้มักเกิดขึ้นเมื่อคุณมีชุดข้อมูลที่รวมหลายรายการสำหรับหมวดหมู่เดียว (เช่น พนักงานในบริษัทเดียวกัน) และคุณต้องการรวบรวมรายการเหล่านั้นลงในสตริงเดียวเพื่อการนำเสนอหรือการวิเคราะห์

บล็อกโพสต์นี้จะอธิบายวิธีการทำการเชื่อมต่อสตริงด้วยคำถาม GROUP BY ใน PostgreSQL เราจะครอบคลุมทั้งวิธีแก้ไขที่ทันสมัยและวิธีการสำหรับเวอร์ชันเก่าของ PostgreSQL เพื่อความเข้าใจที่ครอบคลุม

ปัญหา

ตัวอย่างดังต่อไปนี้คือแถบพนักงาน:

ID	COMPANY_ID	EMPLOYEE
1	1	Anna
2	1	Bill
3	2	Carol
4	2	Dave

คุณต้องการจัดกลุ่มรายการเหล่านี้โดย COMPANY_ID โดยที่ผลลัพธ์แสดงรายการพนักงานที่เกี่ยวข้องกับแต่ละบริษัท ผลลัพธ์ที่ต้องการมีลักษณะดังนี้:

COMPANY_ID	EMPLOYEE
1	Anna, Bill
2	Carol, Dave

วิธีแก้ไข

PostgreSQL 9.0 หรือใหม่กว่า

หากคุณใช้ PostgreSQL เวอร์ชัน 9.0 หรือใหม่กว่า คุณสามารถใช้ฟังก์ชันในตัวที่ทรงพลัง string_agg() เพื่อเชื่อมต่อสตริงในคำสั่ง GROUP BY ของคุณได้อย่างมีประสิทธิภาพ

นี่คือวิธีการเขียนคำถาม:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

พร้อมการเรียงลำดับ

นอกจากนี้ เนื่องจาก PostgreSQL เวอร์ชัน 9.0 รองรับ ORDER BY ภายในฟังก์ชันการรวม คุณสามารถระบุลำดับของพนักงานได้:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

นี่เป็นการรับประกันว่าชื่อพนักงานของคุณจะถูกเชื่อมต่อในลำดับที่เฉพาะเจาะจง

PostgreSQL 8.4.x

หากคุณยังทำงานอยู่กับ PostgreSQL 8.4 ซึ่งเป็นเวอร์ชันที่ไม่มีการสนับสนุนแล้ว คุณสามารถใช้ฟังก์ชัน array_agg() ร่วมกับ array_to_string() เพื่อให้ได้ผลลัพธ์ที่คล้ายกัน นี่คือคำถาม SQL ที่คุณจะใช้:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x และเวอร์ชันเก่า

สำหรับผู้ใช้ PostgreSQL 8.3 และเวอร์ชันก่อนหน้านี้ ไม่มีฟังก์ชันในตัวให้ทำการเชื่อมต่อสตริงโดยตรง การดำเนินการที่กำหนดเองสามารถทำงานรอบข้อจำกัดนี้ได้:

สร้างฟังก์ชันรวมใหม่โดยใช้ฟังก์ชัน textcat:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

หากต้องการรวมตัวแบ่ง เช่น , ระหว่างสตริงที่เชื่อมต่อ ให้สร้างฟังก์ชันที่กำหนดเอง:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
BEGIN
  IF acc IS NULL OR acc = '' THEN
    RETURN instr;
  ELSE
    RETURN acc || ', ' || instr;
  END IF;
END;
$$ LANGUAGE plpgsql;

นี่จะทำให้สตริงเชื่อมต่อกันอย่างเหมาะสม

หากคุณต้องการลบลูกน้ำเพิ่มเติมสำหรับค่า NULL หรือค่าที่ว่างเปล่า นี่คือเวอร์ชันที่ปรับปรุงเมื่อ:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
BEGIN
  IF acc IS NULL OR acc = '' THEN
    RETURN instr;
  ELSIF instr IS NULL OR instr = '' THEN
    RETURN acc;
  ELSE
    RETURN acc || ', ' || instr;
  END IF;
END;
$$ LANGUAGE plpgsql;

ข้อสรุป

การเชื่อมต่อสตริงใน PostgreSQL โดยเฉพาะอย่างยิ่งระหว่างการดำเนินการ GROUP BY เป็นสิ่งสำคัญสำหรับการสร้างรายงานและชุดข้อมูลที่มีความหมาย ด้วยการแนะนำฟังก์ชันอย่าง string_agg() ในเวอร์ชันล่าสุด งานนี้จึงกลายเป็นเรื่องง่าย

หากคุณอยู่ในเวอร์ชันเก่า การใช้วิธีการรวมที่กำหนดเองที่ระบุไว้ในที่นี้สามารถให้ผลลัพธ์ที่คล้ายคลึงกัน พิจารณาอัปเกรดเวอร์ชัน PostgreSQL ของคุณเพื่อใช้ประโยชน์จากฟีเจอร์และการปรับปรุงด้านความปลอดภัยใหม่ๆเสมอ

ขอให้ query ได้ผล!