SQL 更新資料函式庫資料一致性與交易控制

資料函式庫中資料不一致是常見問題，可能包含拼寫錯誤、格式不一致等。本文將示範如何使用 SQL 的 UPDATE 陳述式解決這些問題，例如更新州程式碼、標準化公司名稱、修正郵遞區號格式，並使用備份機制確保資料安全。此外，文章也說明如何透過跨資料表更新，以及如何刪除不需要的資料列或欄位，更進一步探討如何利用交易控制功能，在正式提交變更前預覽更新結果，確保資料修改的準確性。針對大型資料表，文章也提供效能最佳化的技巧，例如透過建立備份表並交換名稱，避免直接修改大型資料表造成的效能負擔。最後，文章以一個實際案例示範如何分析肉類別和家禽加工廠的資料，展現 SQL 在資料處理和分析方面的應用。

更新資料函式庫中的不一致資料

在處理 meat_poultry_egg_establishments 資料表時，我們發現了一些資料不一致的問題。這些問題包括某些公司的名稱拼寫不一致、郵遞區號缺少前導零等。為瞭解決這些問題，我們將使用 SQL 的 UPDATE 陳述式來更新資料。

更新州程式碼

首先，我們需要更新 st 欄位中缺失的州程式碼。我們透過建立一個備份欄位 st_copy，然後使用 UPDATE 陳述式將缺失的州程式碼更新為正確的值。

UPDATE meat_poultry_egg_establishments
SET st = 'WI'
WHERE st IS NULL;

執行上述陳述式後，我們可以使用 RETURNING 子句來檢視更新後的結果。

UPDATE meat_poultry_egg_establishments
SET st = 'WI'
WHERE st IS NULL
RETURNING establishment_number, company, city, st, zip;

內容解密：

• UPDATE meat_poultry_egg_establishments：指定要更新的資料表。
• SET st = 'WI'：將 st 欄位的值設定為 ‘WI’。
• WHERE st IS NULL：只更新 st 欄位為 NULL 的列。
• RETURNING establishment_number, company, city, st, zip：傳回更新後的列。

還原原始值

如果更新過程中出現錯誤，我們可以透過備份欄位或備份資料表來還原原始值。

UPDATE meat_poultry_egg_establishments
SET st = st_copy;

或者

UPDATE meat_poultry_egg_establishments original
SET st = backup.st
FROM meat_poultry_egg_establishments_backup backup
WHERE original.establishment_number = backup.establishment_number;

內容解密：

• UPDATE meat_poultry_egg_establishments：指定要更新的資料表。
• SET st = st_copy：將 st 欄位的值還原為備份欄位 st_copy 的值。
• 第二個陳述式透過連線備份資料表來還原 st 欄位的值。

更新公司名稱以保持一致性

我們發現某些公司的名稱拼寫不一致。為瞭解決這個問題，我們建立了一個新的欄位 company_standard，然後使用 UPDATE 陳述式來標準化公司名稱。

ALTER TABLE meat_poultry_egg_establishments ADD COLUMN company_standard text;
UPDATE meat_poultry_egg_establishments
SET company_standard = company;

接下來，我們可以使用 UPDATE 陳述式來更新 company_standard 欄位中的公司名稱。

UPDATE meat_poultry_egg_establishments
SET company_standard = 'Armour-Eckrich Meats'
WHERE company LIKE 'Armour%'
RETURNING company, company_standard;

內容解密：

• ALTER TABLE meat_poultry_egg_establishments ADD COLUMN company_standard text;：新增一個名為 company_standard 的欄位。
• UPDATE meat_poultry_egg_establishments SET company_standard = company;：將 company 欄位的值複製到 company_standard 欄位。
• WHERE company LIKE 'Armour%'：只更新公司名稱以 ‘Armour’ 開頭的列。
• RETURNING company, company_standard：傳回更新後的公司名稱。

修復郵遞區號

最後，我們需要修復郵遞區號缺少前導零的問題。我們使用字串連線運算元 (||) 來新增前導零。

UPDATE meat_poultry_egg_establishments
SET zip = '00' || zip
WHERE st IN ('PR', 'VI') AND length(zip) = 3;

以及

UPDATE meat_poultry_egg_establishments
SET zip = '0' || zip
WHERE st IN ('CT', 'MA', 'ME', 'NH', 'NJ', 'RI', 'VT') AND length(zip) = 4;

內容解密：

• SET zip = '00' || zip：在郵遞區號前新增兩個零。
• WHERE st IN ('PR', 'VI') AND length(zip) = 3：只更新特定州且郵遞區號長度為 3 的列。
• 第二個陳述式用於更新其他州的郵遞區號，新增一個前導零。

透過上述步驟，我們成功地更新了資料函式庫中的不一致資料，保持了資料的一致性和完整性。

更新資料表的欄位與刪除資料

在前面的章節中，我們已經瞭解如何使用SQL來更新資料表中的資料。現在，我們將繼續探討如何根據其他資料表中的資料來更新欄位，以及如何刪除不再需要的資料。

跨資料表更新欄位

在關聯式資料函式庫中，主鍵和外部索引鍵建立了資料表之間的關聯。在某些情況下，我們需要根據一個資料表中的資料來更新另一個資料表中的欄位。

假設我們要為meat_poultry_egg_establishments資料表中的公司設定檢查截止日期，但區域劃分（如Northeast、Pacific等）並不存在於我們的資料表中。然而，這些區域劃分存在於state_regions.csv檔案中，該檔案包含匹配的州程式碼。

首先，我們需要建立一個state_regions資料表，並將state_regions.csv檔案中的資料匯入其中。程式碼如下所示：

CREATE TABLE state_regions (
    st text CONSTRAINT st_key PRIMARY KEY,
    region text NOT NULL
);

COPY state_regions
FROM 'C:\YourDirectory\state_regions.csv'
WITH (FORMAT CSV, HEADER);

內容解密：

1. CREATE TABLE state_regions：建立一個名為state_regions的資料表，包含兩個欄位：st（州程式碼）和region（區網域名稱）。
2. CONSTRAINT st_key PRIMARY KEY：將st欄位設定為主鍵，以確保每個州程式碼都是唯一的。
3. COPY state_regions FROM ...：將state_regions.csv檔案中的資料匯入state_regions資料表中。

接下來，我們需要在meat_poultry_egg_establishments資料表中新增一個檢查截止日期的欄位，並根據state_regions資料表中的資料更新該欄位。程式碼如下所示：

ALTER TABLE meat_poultry_egg_establishments
ADD COLUMN inspection_deadline timestamp with time zone;

UPDATE meat_poultry_egg_establishments establishments
SET inspection_deadline = '2022-12-01 00:00 EST'
WHERE EXISTS (SELECT state_regions.region
              FROM state_regions
              WHERE establishments.st = state_regions.st
              AND state_regions.region = 'New England');

內容解密：

1. ALTER TABLE ... ADD COLUMN inspection_deadline：在meat_poultry_egg_establishments資料表中新增一個名為inspection_deadline的欄位，資料型別為帶時區的時間戳記。
2. UPDATE ... SET inspection_deadline = '2022-12-01 00:00 EST'：將inspection_deadline欄位的值設定為'2022-12-01 00:00 EST’。
3. WHERE EXISTS ...：使用子查詢來連線meat_poultry_egg_establishments和state_regions資料表，並根據state_regions資料表中的區網域名稱過濾需要更新的列。

刪除不需要的資料

刪除資料是修改資料的一種最不可逆的方式。SQL提供了多種選項來刪除列、欄位或整個資料表。在執行這些操作時，我們需要謹慎，以避免刪除重要資料。

從資料表中刪除列

要從資料表中刪除列，可以使用DELETE FROM或TRUNCATE陳述式。兩者都是ANSI SQL標準的一部分，提供不同的選項以滿足不同的需求。

使用DELETE FROM陳述式，可以刪除資料表中的所有列，或使用WHERE子句刪除符合特定條件的列。語法如下所示：

DELETE FROM table_name;
DELETE FROM table_name WHERE expression;

內容解密：

1. DELETE FROM table_name;：刪除資料表中的所有列。
2. DELETE FROM table_name WHERE expression;：刪除符合特定條件的列。

在刪除列之前，請務必備份重要資料，以避免不可逆的損失。同時，也需要考慮約束條件（如外部索引鍵約束），以避免刪除操作導致約束違規。

資料函式庫資料刪除與交易控制

在資料函式倉管理中，刪除資料或結構是一項重要的操作。本章節將介紹如何使用SQL指令刪除資料表中的列、資料表本身，以及如何利用交易控制來確保資料修改的安全性。

刪除資料表中的資料列

使用DELETE FROM語法可以刪除資料表中的特定資料列。例如，若要從meat_poultry_egg_establishments表中排除美國領土的公司，可以使用如下的SQL指令：

DELETE FROM meat_poultry_egg_establishments
WHERE st IN('AS','GU','MP','PR','VI');

內容解密：

1. DELETE FROM語法用於刪除資料表中的資料列。
2. WHERE子句指定了刪除條件，本例中刪除了st欄位值為指定領土程式碼的資料列。
3. IN關鍵字允許指定多個值，本例中刪除了與美國領土相關的公司資料。

對於大型資料表，使用TRUNCATE指令可以更高效地清空資料表，因為它避免了全表掃描。語法如下：

TRUNCATE table_name;

若要重設自動增量的主鍵，可以使用RESTART IDENTITY選項：

TRUNCATE table_name RESTART IDENTITY;

內容解密：

1. TRUNCATE語法用於快速清空資料表。
2. RESTART IDENTITY選項用於重設自動增量的主鍵。

刪除資料表中的欄位

當不再需要某個欄位時，可以使用ALTER TABLE語法配合DROP COLUMN來刪除它。例如，刪除zip_copy欄位的指令如下：

ALTER TABLE meat_poultry_egg_establishments DROP COLUMN zip_copy;

內容解密：

1. ALTER TABLE語法用於修改資料表結構。
2. DROP COLUMN用於刪除指定的欄位。

刪除資料表

使用DROP TABLE語法可以刪除整個資料表。例如，刪除備份表的指令如下：

DROP TABLE meat_poultry_egg_establishments_backup;

內容解密：

1. DROP TABLE語法用於刪除整個資料表。
2. 執行此操作前應謹慎，因為它將永久刪除資料表及其所有資料。

使用交易控制儲存或還原變更

在進行資料修改時，可以使用交易控制來暫時儲存變更或還原至之前的狀態。交易控制的關鍵字包括：

• START TRANSACTION（或BEGIN）：開始一個交易區塊。
• COMMIT：提交交易，確認變更。
• ROLLBACK：回復交易，取消變更。

例如，使用交易區塊來測試更新操作的結果：

START TRANSACTION;
UPDATE meat_poultry_egg_establishments
SET company = 'AGRO Merchantss Oakland LLC'
WHERE company = 'AGRO Merchants Oakland, LLC';
SELECT company
FROM meat_poultry_egg_establishments
WHERE company LIKE 'AGRO%'
ORDER BY company;
ROLLBACK;

內容解密：

1. START TRANSACTION開始一個新的交易區塊。
2. UPDATE語法執行資料更新操作。
3. SELECT語法用於檢查更新結果。
4. ROLLBACK取消交易區塊中的所有變更。

交易控制提供了一種安全的方式來測試和驗證資料修改操作，確保在發生錯誤時能夠還原到之前的狀態。

資料清理與交易控制

在處理資料函式庫時，常常需要對資料進行修改以確保其正確性和一致性。PostgreSQL 提供了交易控制功能，讓我們能夠安全地進行資料更新。

使用交易區塊進行資料更新測試

在進行資料更新之前，使用交易區塊可以測試更新陳述式的效果。如果更新結果不如預期，可以使用 ROLLBACK 命令復原變更。

START TRANSACTION;
UPDATE meat_poultry_egg_establishments
SET company = 'AGRO Merchants Oakland, LLC'
WHERE company = 'AGRO Merchantss Oakland LLC';

SELECT company
FROM meat_poultry_egg_establishments
WHERE company LIKE 'AGRO%';

ROLLBACK;

內容解密：

1. START TRANSACTION; 開始一個新的交易區塊。
2. UPDATE 陳述式用於更新 company 欄位中的錯誤拼寫。
3. SELECT 陳述式用於檢視更新後的結果。
4. ROLLBACK; 命令復原交易區塊中的所有變更。

大型表格更新的最佳化

在 PostgreSQL 中，直接對大型表格進行欄位新增和資料填充可能會導致表格大小迅速膨脹。因為每次更新都會建立一個新的資料列版本，而舊的資料列版本不會被立即刪除。

CREATE TABLE meat_poultry_egg_establishments_backup AS
SELECT *,
       '2023-02-14 00:00 EST'::timestamp with time zone AS reviewed_date
FROM meat_poultry_egg_establishments;

內容解密：

1. CREATE TABLE 陳述式建立一個新的備份表格 meat_poultry_egg_establishments_backup。
2. SELECT * 選擇原始表格中的所有欄位。
3. 新增一個名為 reviewed_date 的欄位，並賦予一個時間戳記值。
4. 使用 ::timestamp with time zone 將字串轉換為帶時區的時間戳記型別。

表格名稱交換

為了避免直接更新大型表格導致的效能問題，我們可以先建立一個包含新欄位的備份表格，然後交換兩個表格的名稱。

ALTER TABLE meat_poultry_egg_establishments RENAME TO meat_poultry_egg_establishments_temp;
ALTER TABLE meat_poultry_egg_establishments_backup RENAME TO meat_poultry_egg_establishments;
ALTER TABLE meat_poultry_egg_establishments_temp RENAME TO meat_poultry_egg_establishments_backup;

內容解密：

1. 第一個 ALTER TABLE 陳述式將原始表格重新命名為臨時名稱。
2. 第二個 ALTER TABLE 陳述式將備份表格重新命名為原始表格名稱。
3. 第三個 ALTER TABLE 陳述式將臨時表格重新命名為備份表格名稱。

練習題：分析肉類別和家禽加工廠資料

請根據 meat_poultry_egg_establishments 表格中的 activities 欄位，計算有多少家工廠進行肉類別加工和家禽加工。

步驟一：新增欄位

首先，新增兩個布林型別的欄位 meat_processing 和 poultry_processing。

ALTER TABLE meat_poultry_egg_establishments
ADD COLUMN meat_processing boolean,
ADD COLUMN poultry_processing boolean;

內容解密：

1. ALTER TABLE 陳述式用於修改現有表格結構。
2. 新增兩個布林型別的欄位，用於標記工廠是否進行肉類別或家禽加工。

步驟二：更新欄位

使用 UPDATE 陳述式根據 activities 欄位的內容更新新新增的欄位。

UPDATE meat_poultry_egg_establishments
SET meat_processing = TRUE
WHERE activities LIKE '%Meat Processing%';

UPDATE meat_poultry_egg_establishments
SET poultry_processing = TRUE
WHERE activities LIKE '%Poultry Processing%';

內容解密：

1. 第一個 UPDATE 陳述式將 meat_processing 設為 TRUE，如果 activities 欄位包含 “Meat Processing”。
2. 第二個 UPDATE 陳述式將 poultry_processing 設為 TRUE，如果 activities 欄位包含 “Poultry Processing”。

步驟三：統計分析

最後，使用更新後的欄位進行統計分析。

SELECT 
    COUNT(CASE WHEN meat_processing THEN 1 END) AS meat_processing_count,
    COUNT(CASE WHEN poultry_processing THEN 1 END) AS poultry_processing_count,
    COUNT(CASE WHEN meat_processing AND poultry_processing THEN 1 END) AS both_processing_count
FROM meat_poultry_egg_establishments;

內容解密：

1. 使用 COUNT 和 CASE WHEN 組合來統計進行肉類別加工、家禽加工和兩者兼有的工廠數量。