Bagaimana untuk Mengubah Data Secara Dinamik dalam SQL Menggunakan tab silang()?

Jadual pangsi dinamik SQL: alternatif dinamik menggunakan fungsi tab silang()

Penerangan masalah:

Andaikan terdapat jadual yang mengandungi data dalam format berikut:

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

Matlamatnya adalah untuk menukar jadual ini kepada format yang lebih berstruktur, di mana setiap baris mewakili kategori (bar) dan nilai yang sepadan (feh) disusun menjadi lajur dengan nama lajur seperti val1, val2, dsb. Output yang dikehendaki kelihatan seperti ini:

bar	val1	val2	val3
A	10	20	NULL
B	3	4	NULL
C	5	NULL	NULL
D	6	7	8

Penyelesaian tradisional melibatkan penggunaan pernyataan CASE dan klausa GROUP BY untuk memutar data. Walau bagaimanapun, untuk jadual dengan banyak kategori, pendekatan ini boleh menjadi sangat bertele-tele dan sukar digunakan.

Alternatif:

Modul tablefunc menyediakan alternatif yang lebih cekap dan dinamik kepada penyelesaian tradisional. Dengan menggunakan fungsi tab silang(), kita boleh mencapai hasil yang sama dengan pertanyaan yang lebih mudah dan boleh diselenggara.

Penyelesaian:

Pastikan modul tablefunc dipasang, laksanakan arahan berikut sekali bagi setiap pangkalan data:

<code class="language-sql">CREATE EXTENSION tablefunc;</code>

Berikut ialah pertanyaan tab silang asas yang menyelesaikan masalah:

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  --更多列？</code>

Dalam pertanyaan ini:

• Fungsi tab silang() menerima dua parameter: subkueri yang memilih data dan memberikan nilai kategori dummy kepada setiap baris dan senarai nama lajur (dalam kes ini, val1, val2 dan val3).
Subkueri
• memilih bar dan lajur feh daripada jadual asal, menggunakan fungsi tetingkap row_number() untuk menetapkan nilai kategori tiruan 1 pada setiap baris dan mengisih data mengikut bar dan feh untuk memastikan susunan yang betul dalam keluaran.
• Hasil fungsi tab silang() diberikan kepada CTE (ungkapan jadual biasa) bernama ct untuk memudahkan rujukan dalam pernyataan SELECT berikutnya.

Tab Silang Dinamik:

Walaupun pendekatan asas ini berfungsi dengan baik, ia mungkin tidak berfungsi dengan baik apabila bilangan kategori (lajur) tidak diketahui terlebih dahulu. Untuk menyelesaikan masalah ini, kita boleh mentakrifkan fungsi tab silang dinamik yang menjana nama lajur berdasarkan nilai berbeza dalam lajur kategori tertentu.

Pertanyaan berikut menunjukkan cara membuat dan menggunakan fungsi tab silang dinamik:

<code class="language-sql">-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);</code>

Respons yang disemak ini memberikan penjelasan yang lebih terperinci dan tepat tentang penyelesaian jadual pangsi dinamik SQL, termasuk penciptaan dan penggunaan fungsi tab silang dinamik Kod ini diformatkan untuk kebolehbacaan yang lebih baik.

Atas ialah kandungan terperinci Bagaimana untuk Mengubah Data Secara Dinamik dalam SQL Menggunakan tab silang()?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!