PIVOT ใน BigQuery

การแปลงแถวเป็นคอลัมน์

บางครั้งคุณอาจต้องการจัดรูปแบบผลลัพธ์ตารางใหม่เพื่อให้คุณมีคอลัมน์แยกกันสำหรับแต่ละค่าที่ไม่ซ้ำกัน สิ่งนี้เรียกว่าตาราง Pivot โดยปกติจะเป็นเพียงฟังก์ชันการแสดงผลที่เครื่องมือ BI รองรับเท่านั้น อย่างไรก็ตามในบางครั้งอาจเป็นประโยชน์ในการสร้างตาราง Pivot ใน SQL วิธีใช้ตัวดำเนินการ PIVOT ใน Google BigQuery เพื่อสร้างตาราง Pivot

ภาพโดย Gerd Altmann จาก Pixabay

Pivot ทำอะไร?

Pivot เปลี่ยนแถวเป็นคอลัมน์ ตัวอย่างเช่นสมมติว่าเรามีตารางเที่ยวบินและเที่ยวบินล่าช้าดังนี้:

และเราต้องการผลลัพธ์ที่เราได้รับความล่าช้าในการออกเดินทางโดยเฉลี่ยสำหรับคู่สนามบิน - สายการบินแต่ละคู่โดยที่รหัสสายการบินคือคอลัมน์:

เราจะทำ PIVOT โปรดทราบว่าหากเราต้องการเพียงแค่ความล่าช้าในการออกเดินทางโดยเฉลี่ยและตกลงกับค่าเฉลี่ยที่อยู่ในแถวต่างๆเราสามารถทำได้:

SELECT 
  airline,
  departure_airport,
  AVG(departure_delay)
FROM `bigquery-samples.airline_ontime_data.flights`
GROUP BY 1, 2

เป็นความจริงที่ว่าเราต้องการให้มันวางเป็นตาราง 2 มิติที่ต้องใช้ PIVOT

ไวยากรณ์ของ PIVOT

ตัวดำเนินการ Pivotใน BigQuery ต้องการให้คุณระบุสามสิ่ง:

  • from_itemที่ทำหน้าที่เป็นอินพุต คอลัมน์สามคอลัมน์ (สายการบิน, เที่ยวบินขาออก _ แอร์พอร์ต, เที่ยวบินขาออก) จากตารางเที่ยวบินคือ from_item ของเรา
  • รวมเนื่องจากแต่ละเซลล์ของตารางผลลัพธ์ประกอบด้วยหลายค่า นี่คือค่าเฉลี่ยของการเดินทางออกจากปลายทาง
  • pivot_columnคอลัมน์ที่มีค่าเป็นคอลัมน์ในตารางผลลัพธ์ น่าเสียดายที่สิ่งนี้ต้องเป็นค่าคงที่คุณไม่สามารถใส่ข้อความค้นหาลงไปได้

ตารางเที่ยวบิน PIVOT

เราต้องการความล่าช้าในการออกเดินทางโดยสนามบินและสายการบิน แบบสอบถาม Pivot คือ:

SELECT * FROM
(
  -- #1 from_item
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  -- #2 aggregate
  AVG(departure_delay)
  -- #3 pivot_column
  FOR airline in ('AA', 'KH', 'DL', 'AS', 'UA')
)

ผลลัพธ์:

กองซ้อน PIVOT

มาสร้างเดือยของจำนวนคำถามด้วยแท็กที่กำหนดตามปี

SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year,
    COUNT(*) AS n
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
  GROUP BY 1, 2

      

SELECT * FROM 
(
  -- #1 from_item
  SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
)
PIVOT
(
  -- #2 aggregate
  COUNT(*) AS n
  -- #3 pivot_column
  FOR year in (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
)

      

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

DECLARE airlines STRING;
SET airlines = (
  SELECT 
    CONCAT('("', STRING_AGG(DISTINCT airline, '", "'), '")'),
  FROM `bigquery-samples.airline_ontime_data.flights`
  WHERE airline != '9E'
);

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  AVG(departure_delay)
  FOR airline in %s
)
ORDER BY departure_airport ASC
""", airlines);

Suggested posts

ข้อมูลเบื้องต้นเกี่ยวกับ SQL: ทุกสิ่งที่คุณจำเป็นต้องรู้ในคู่มือสั้นๆ ฉบับเดียว

ข้อมูลเบื้องต้นเกี่ยวกับ SQL: ทุกสิ่งที่คุณจำเป็นต้องรู้ในคู่มือสั้นๆ ฉบับเดียว

SQL หรือ Structured Query Language เป็นเครื่องมือสำคัญสำหรับการจัดการและจัดการข้อมูลในฐานข้อมูลเชิงสัมพันธ์ ด้วยคำสั่งที่หลากหลาย SQL ช่วยให้คุณสามารถดู สร้าง และลบฐานข้อมูลและตาราง แก้ไขข้อมูล และเลือกข้อมูลโดยใช้คำสั่งต่างๆ

พื้นฐานของเทคโนโลยีขั้นสูง

พื้นฐานของเทคโนโลยีขั้นสูง

สาขาเทคโนโลยีเติบโตขึ้นอย่างก้าวกระโดดในช่วงไม่กี่ปีที่ผ่านมา และหนึ่งในการพัฒนาที่น่าตื่นเต้นที่สุดคือการเกิดขึ้นของปัญญาประดิษฐ์ (AI) และกำเนิดของปัญญาประดิษฐ์ (Generative AI) แต่อะไรกันแน่ที่รวมอยู่ในเทคโนโลยีล้ำสมัยเหล่านี้ที่ทำให้พวกเขาล้ำหน้า? วันนี้เรามาดูปัจจัยพื้นฐานทางเทคโนโลยีขั้นพื้นฐานที่สนับสนุนนวัตกรรมเหล่านี้ให้ละเอียดยิ่งขึ้น

Related posts

การค้นหาข้อมูลเกี่ยวกับชื่อโดเมน

ตัดตอนมาจาก Small, Sharp Software Tools โดย Brian P. Hogan

การค้นหาข้อมูลเกี่ยวกับชื่อโดเมน

เมื่อคุณส่งคำขอไปที่ facebook.com คอมพิวเตอร์ของคุณจะค้นหาชื่อโดเมนเพื่อแปลชื่อโดเมนนั้นเป็นที่อยู่ IP เพื่อให้คอมพิวเตอร์ของคุณสามารถเชื่อมต่อได้

บทสนทนาอันตราย

บทสนทนาอันตราย

หากเราต้องการจะเติบโตบนโลกใบนี้ต่อไป เราจะต้องสนทนาที่อันตราย คนที่ท้าทายสิ่งที่เราทำ สิ่งที่เรากำลังทำ และสิ่งที่เรากำลังจะทำต่อไป (หรือกำลังวางแผนที่จะทำ)

เครือข่ายทีวีทวิตเตอร์

เครือข่ายทีวีทวิตเตอร์

ของเก่ากลายเป็นของใหม่อีกครั้ง และของที่เคยเป็นประกายระยิบระยับกลับดูจืดชืด ฉันไม่ได้กำลังพูดถึงกางเกงยีนส์ JNCO ที่กลับมาเป็นที่นิยมในหมู่เด็กเท่ๆ ในตัวเมืองพร้อมกับแฟชั่นอื่นๆ ในช่วงปลายทศวรรษ 1990

ศิลปะเคยไม่ฝักใฝ่ฝ่ายใดหรือไม่?

ศิลปะเคยไม่ฝักใฝ่ฝ่ายใดหรือไม่?

เมื่อวานนี้ เราปิดการแข่งขัน Eurovision Song Contest ปีนี้ด้วยตอนจบที่สดใสและวุ่นวาย โดย Loreen นักร้องชาวสวีเดนคว้าถ้วยรางวัลกลับบ้าน กลายเป็นผู้หญิงคนแรกที่ชนะสองครั้งและนำมงกุฎกลับมายังสวีเดน 50 ปีหลังจาก ABBA ชนะการแข่งขัน กับ “วอเตอร์ลู” พวกเขากลายเป็นหนึ่งในการแสดงที่ยิ่งใหญ่ที่สุดในวงการเพลงป๊อป