Bagaimana untuk mengalih keluar pendua dalam Postgres SQL

---

Disiarkan silang pada blog saya
Anda boleh membacanya di sini

---

Skema kami

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

Kini kami ingin memastikan setiap pengguna tidak boleh menyukai siaran yang sama lebih daripada sekali.
Ini boleh dicegah dengan:

• menggunakan kekangan unik pada pasangan lajur post_id user_id pada jadual post_like.
• atau mengalih keluar lajur id jadual post_like dan gunakan kunci utama komposit pada post_id user_id

Tetapi, dengan mengandaikan kita berada pada titik di mana pendua sudah ada, kita perlu mengalih keluarnya.

Semak sama ada terdapat pendua

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

Output ini memberitahu kami bahawa pengguna 2 telah menyukai siaran 3 lebih daripada satu kali, khususnya 2 kali.

Alih keluar pendua

Sekarang kami tahu bahawa terdapat pendua, kami boleh mengalih keluarnya.

Kami membahagikan proses ini dalam dua langkah:

• baca pendua
• alih keluar pendua (lari kering)
• alih keluar pendua (lari sebenar)

Baca pendua

Kembali urus niaga

Untuk menguji pertanyaan kami tanpa mengalih keluar data sebenar, sehingga kami pasti pertanyaan itu betul, kami menggunakan ciri rollback transaksi.

Dengan melakukan ini pertanyaan kami tidak akan pernah dilakukan, adalah serupa dengan
konsep "dry run" yang boleh anda temui pada aplikasi lain (seperti
rsync).

CTE

Kami menggunakan CTE kerana ia memberikan DX yang baik.

Dengan CTE, kita boleh menjalankan pertanyaan, menyimpan keputusan dalam jadual sementara dan kemudian menggunakan jadual yang sama untuk pertanyaan berikutnya.

Model mental ini serupa dengan apa yang biasa kita lakukan dalam pengekodan dengan mencipta pembolehubah sementara.

Sintaks CTE ialah

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

Dengan kedua-dua transaksi dan CTE, kami boleh melakukan perkara berikut:

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

Barisan keputusan terkini, dengan indeks_kumpulan ialah 2, bermakna baris ini adalah baris kedua dalam kumpulan dengan post_id = 3 dan user_id = 2.

Apakah yang berlaku di sini dengan sintaks?

row_number() over (partition by ...) sebagai group_index ialah fungsi tetingkap yang, pertama kumpulan baris mengikut lajur dalam partition mengikut klausa, dan kemudian memberikan nombor kepada setiap baris, berdasarkan indeks baris dalam kumpulan.

partition adalah serupa dengan kumpulan oleh, kerana ia mengumpulkan baris mengikut lajur biasa, tetapi jika kumpulan mengikut mengembalikan hanya 1 baris untuk setiap kumpulan, partition marilah kami menambah lajur baharu pada jadual sumber berdasarkan kumpulan.

group_index ialah alias nama lajur, sintaks sql biasa.

Tapis hanya pendua

Sekarang mari kita simpan hanya item dengan group_index > 1, yang bermaksud bahawa baris itu bukan yang pertama dalam kumpulan, atau dengan kata lain, ia adalah pendua.

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

Kita perlu mengalih keluar baris ini sahaja, dengan id 4.

Alih keluar pendua - larian kering

Sekarang sambung semula pertanyaan akhir supaya kita membaca dari jadual post_like dan bukan lagi dari cte duplicates_info.
Kami masih menggunakan cte duplicates_info untuk mendapatkan id pendua.

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

Kami akan melihat rekod yang ingin kami alih keluar.

Selepas kami menyemak sama ada ia betul, kami menukar pilih dengan padam.

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

Pertanyaan terakhir inilah yang akhirnya kami mahu laksanakan.

Tetapi kerana kami masih mempunyai penyataan rollback, perubahan ini disimulasikan dan tidak digunakan pada pangkalan data.

Alih keluar pendua - larian sebenar

Akhirnya kami boleh mengalih keluar pendua secara nyata.
Di sini kami menggunakan commit dan bukannya rollback, supaya perubahan digunakan pada pangkalan data.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

Kod Akhir

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

Kesimpulan

Saya menulis artikel terutamanya untuk membantu masa depan diri saya atau untuk membantu pertumbuhan alatan yang saya gunakan dalam kerja saya.

Jika artikel ini membantu anda tinggalkan like.

Adakah anda mahu saya bercakap tentang topik tertentu?

Beritahu saya dalam komen!

Atas ialah kandungan terperinci Bagaimana untuk mengalih keluar pendua dalam Postgres SQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!