Persediaan Temu Duga Pengaturcaraan · Pelajaran

PIVOT Dinamik dengan Lajur Tidak Diketahui

Menjana lajur pivot apabila kategori belum diketahui terlebih dahulu.

Pelajaran 4 daripada 413 langkah

PIVOT Dinamik dengan Lajur Tidak Diketahui ialah pelajaran Persediaan Temu Duga Pengaturcaraan percuma di CoddyKit. Ini ialah pelajaran 4 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga Pengaturcaraan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Soalan Pivot yang Sukar

Setiap pivot statik, sama ada pengagregatan CASE, PIVOT SQL Server atau crosstab Postgres, mempunyai satu batasan yang sama: anda mesti menyenaraikan lajur hasil semasa menulis pertanyaan.

Tetapi bagaimana jika kategori tidak diketahui, seperti nama produk yang berubah setiap minggu atau satu lajur bagi setiap bulan yang aktif? Itulah pivot dinamik, dan ia merupakan soalan temu duga peringkat kanan kerana SQL biasa tidak boleh mengembalikan hasil yang senarai lajurnya ditentukan pada masa jalan.

Mengapa SQL Sahaja Tidak Boleh Melakukannya

SQL mempunyai penaipan statik pada peringkat set hasil: perancang mesti mengetahui lajur dan jenisnya sebelum pelaksanaan. Satu pertanyaan tidak boleh menyatakan cipta satu lajur bagi setiap nilai yang anda temui.

Jadi, teknik sejagatnya ialah menjana teks SQL dalam dua langkah: mula-mula tanyakan kategori unik, kemudian bina rentetan pertanyaan pivot daripadanya dan laksanakan rentetan tersebut.

Langkah 1: Kumpulkan Kategori

Langkah pertama ialah pertanyaan biasa yang menyenaraikan nilai unik yang akan menjadi lajur. Biasanya, susun nilai tersebut untuk mendapatkan susun atur lajur yang konsisten.

Hasil ini menjadi input kepada langkah pembinaan rentetan. Dalam sistem sebenar, anda menjalankan langkah ini, menangkap barisnya dan menyusun pertanyaan seterusnya daripadanya.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Langkah 2: Bina Senarai Lajur

Seterusnya, tukarkan nilai tersebut kepada senarai ungkapan CASE yang dipisahkan dengan koma (atau nama dalam tanda kurung bagi PIVOT). Pangkalan data menyediakan fungsi pengagregatan rentetan untuk melakukan hal ini dalam SQL itu sendiri.

Dalam Postgres, fungsinya ialah string_agg; dalam MySQL, GROUP_CONCAT; dalam SQL Server, STRING_AGG atau helah lama FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Langkah 3: Susun dan Laksanakan

Gabungkan serpihan yang dijana ke dalam rentetan pertanyaan lengkap, kemudian jalankannya dengan pelaksanaan dinamik: EXECUTE dalam PL/pgSQL, sp_executesql dalam SQL Server atau PREPARE/EXECUTE dalam MySQL.

Inilah inti pivot dinamik: SQL menulis SQL, kemudian melaksanakannya.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Contoh Lengkap PostgreSQL

Dalam Postgres, bungkus ketiga-tiga langkah dalam blok DO atau fungsi. Bina senarai lajur dengan string_agg, selitkannya ke dalam pertanyaan dan jalankannya dengan EXECUTE.

Oleh sebab lajur hasil tidak diketahui sehingga masa jalan, fungsi yang mengembalikan hasil ini sering menggunakan RETURNS SETOF record atau mengembalikan baris sebagai json, yang kemudiannya dikembangkan oleh pemanggil.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL dengan Pernyataan Disediakan

MySQL tidak mempunyai pengendali pivot, jadi pivot dinamik membina rentetan pengagregatan bersyarat dengan GROUP_CONCAT, kemudian menjalankannya melalui pernyataan yang disediakan.

GROUP_CONCAT mempunyai had panjang (group_concat_max_len) yang mungkin disebut oleh penemuduga; tingkatkan had itu jika anda mempunyai banyak kategori.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

Risiko Suntikan SQL

Oleh sebab anda menggabungkan nilai data ke dalam SQL boleh laku, pivot dinamik membawa risiko suntikan. Jika nilai kategori mengandungi tanda petikan atau teks berniat jahat, nilai itu boleh merosakkan atau mengambil alih pertanyaan yang dijana.

Sentiasa lindungi pengecam dan nilai literal dengan pembantu selamat enjin: format('%I', ...) dan %L dalam Postgres, serta QUOTENAME dalam SQL Server. Jangan sekali-kali menampal nilai mentah terus ke dalam rentetan.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Mengembalikan Lajur yang Tidak Diketahui

Satu lagi bahagian yang sukar: pemanggil tidak dapat mengetahui bentuk hasil terlebih dahulu. Strategi biasa yang diterima oleh penemuduga:

Kembalikan baris sebagai JSON dan biarkan lapisan aplikasi mengembangkan kunci.
Biarkan prosedur memaparkan atau membina pertanyaan, kemudian jalankannya sebagai langkah kedua.
Lakukan pivot terakhir dalam kod aplikasi (pandas, alat BI) setelah kategori diketahui.

Tiada cara yang kemas untuk mengembalikan lajur arbitrari daripada satu panggilan statik.

Contoh Berpandu: Pivot Mengikut Produk

Andaikan produk datang dan pergi, dan laporan memerlukan satu lajur hasil bagi setiap produk yang kini terdapat dalam sales. Anda tidak boleh menetapkan senarai itu secara terus, jadi anda menjana senarai tersebut. Postgres menjadikannya mudah dibaca: bina serpihan CASE dengan string_agg dan petikan selamat, selitkannya ke dalam pertanyaan, kemudian EXECUTE.

Terangkan langkahnya kepada penemuduga: kenal pasti produk, formatkan setiap produk menjadi lajur dalam petikan, susun dan jalankan. Corak yang sama terpakai dalam mana-mana enjin; hanya pembantunya yang berubah.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Bila Perlu Mengelakkan Pivot Dinamik

Calon yang baik tahu bila tidak melakukannya dalam SQL. SQL dinamik lebih sukar dibaca, diuji, dilindungi dan dicache. Selalunya jawapan yang lebih baik ialah:

Kembalikan bentuk panjang daripada SQL dan lakukan pivot dalam aplikasi atau lapisan pelaporan.
Jika set kategori kecil dan jarang berubah, gunakan pivot statik dan kemas kininya sekali-sekala.

Simpan pivot dinamik untuk set kategori yang benar-benar terbuka dan sentiasa berubah.

Semakan Pantas

Uji sebab teras pivot dinamik diperlukan.

Jawab dalam pelajaran interaktif

Ringkasan

Pivot dinamik mengendalikan set lajur yang tidak diketahui:

Pivot statik gagal kerana lajur hasil mesti ditetapkan sebelum pelaksanaan.
Coraknya: tanyakan kategori unik, bina rentetan SQL pivot dan laksanakannya secara dinamik.
Gunakan string_agg/GROUP_CONCAT/STRING_AGG untuk membina senarai lajur.
Lindungi nilai (%I/%L, QUOTENAME) untuk mengelakkan suntikan SQL.
Selalunya lebih kemas untuk mengembalikan bentuk panjang dan melakukan pivot pada lapisan aplikasi.

Percuma untuk bermula

Pelajari Persediaan Temu Duga Pengaturcaraan dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 90
Pelajaran: 360

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “PIVOT Dinamik dengan Lajur Tidak Diketahui” percuma?

Ya — teks penuh “PIVOT Dinamik dengan Lajur Tidak Diketahui” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga Pengaturcaraan, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “PIVOT Dinamik dengan Lajur Tidak Diketahui”?

Menjana lajur pivot apabila kategori belum diketahui terlebih dahulu. Anda berlatih Persediaan Temu Duga Pengaturcaraan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga Pengaturcaraan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga Pengaturcaraan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 4 daripada 4.

Berapa lamakah pelajaran “PIVOT Dinamik dengan Lajur Tidak Diketahui” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga Pengaturcaraan ini?

Ya. Setiap pelajaran Persediaan Temu Duga Pengaturcaraan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Memusing Data dengan Pengagregatan Bersyarat
Sintaks PIVOT Vendor dan Jadual Silang
Menukar Lajur Menjadi Baris
PIVOT Dinamik dengan Lajur Tidak Diketahui

← Kembali ke Persediaan Temu Duga Pengaturcaraan