MySQL - 基於多個條件更新列

January 9, 2022

我有一張桌子，裡面有這樣的行：
+----------+--------------+---------------------+------+
| CASE_ID  | CAT          | BIRTH               | TYPE |
+----------+--------------+---------------------+------+
| 20033738 | CASE_OWNER   | 1996-04-08 00:00:00 | NULL |
| 20033738 | WIFE         | 1995-08-22 00:00:00 | NULL |
| 20039334 | CASE_OWNER   | 1994-03-10 00:00:00 | NULL |
| 20039301 | CASE_OWNER   | 1999-07-27 00:00:00 | NULL |
| 20039301 | WIFE         | 2001-07-05 00:00:00 | NULL |
| 20039301 | CHILD        | 2018-10-22 00:00:00 | NULL |
| 20033831 | CASE_OWNER   | 1975-03-05 00:00:00 | NULL |
| 20033831 | CHILD        | 2005-03-19 00:00:00 | NULL |
| 20033831 | CHILD        | 2006-03-25 00:00:00 | NULL |
| 20033831 | CHILD        | 2010-05-20 00:00:00 | NULL |
| 20033831 | CHILD        | 2013-10-25 00:00:00 | NULL |
+----------+--------------+---------------------+------+
對於一個 CASE_ID，每個 CASE_OWNER 的組合都有或沒有 WIFE 和/或 CHILD（1 個或多個）都是可能的。
對於每個 CASE_ID，我想根據在 CAT 和 BIRTH 中找到的資訊設置列 TYPE：
a) 如果 CASE_ID 中只有 CASE_OWNER，則 TYPE 應為
a1) 匹配 CASE_ID 的列 TYPE 中的 SINGLE_PERSON，如果 CASE_OWNER 早於 21
a2) 與 CASE_ID 匹配的列 TYPE 中的 UNKNOWN，如果 CASE_OWNER 小於 21
b) 如果 CASE_ID 的 CASE_ID 具有 CAT CASE_OWNER AND WIFE (WITHOUT CHILD)，則在與特定 CASE_ID 匹配的每一行中，TYPE 都應為 PAIR_NO_CHILD。
c) 如果一個 CASE_ID 有 CAT CASE_OWNER AND WIFE AND 1 個或多個 CHILD(ren) 對應一個 CASE_ID，則 TYPE 應該是
c1) PAIR_WITH_CHILD 如果在匹配特定 CASE_ID 的每一行中有一個或多個 CHILD(ren) 低於 21。
c2) OTHER 如果所有 CHILD(ren) 在與特定 CASE_ID 匹配的每一行中都是 21。
d) 如果一個 CASE_ID 有 CAT CASE_OWNER 並且一個 CASE_ID 有 1 個或多個 CHILD(ren)，則 TYPE 應該是
d1) SINGLE_WITH_CHILD，以防匹配特定 CASE_ID 的每一行中一個或多個（不是全部）CHILD(ren) 低於 21。
d2) 如果所有 CHILD(ren) 都在 21 歲以上，則為 MULTIPLE。
e) 所有其他組合將是 TYPE == UNKNOWN。
我的問題是：
使用 SQL 是否可行？
這應該使用 SQL 還是使用程式語言來解決？
如果這在 SQL 中是可行的 - 應該怎麼做？
非常感謝您的回饋

介紹：
你有兩種可能做你想做的事——一種適用於 MySQL 5.5（使用聚合）及更高版本，另一種適用於 MySQL 8 及更高版本（使用視窗函式）。
下面的所有程式碼都可以在 fiddle here上找到。**注意：**小提琴適用於 MySQL 版本 8。如果您希望執行版本 5.5（或 5.6 或 5.7），請在小提琴頂部的下拉列表中更改伺服器。我這樣做是因為EXPLAIN ANALYZE只能與 MySQL > 8.0.18 一起使用 - 以前的版本無法使用！
首先創建您的表格（您應該以小提琴的形式自己提供此類問題的表格）。我對您的架構進行了一些更改：
CREATE TABLE cas              -- French for "case" - CASE is an SQL keyword and should not be used for table names!
(
 case_id INTEGER NOT NULL,
 cat     TEXT    NOT NULL,
 birth   DATE    NOT NULL,   -- store as a date - only requires 4 bytes
 c_type  TEXT    NULL        -- type is also a keyword, best avoided https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words
);
然後填充它。我添加了一些記錄用於測試目的：
INSERT INTO cas (case_id, cat, birth) VALUES

(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE'      , '1995-08-22'),

(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD'     , '2005-03-19'),
(20033831, 'CHILD'     , '2006-03-25'),
(20033831, 'CHILD'     , '2010-05-20'),
(20033831, 'CHILD'     , '2013-10-25'),

(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE'      , '2001-07-05'),
(20039301, 'CHILD'     , '2018-10-22'),


(20039334, 'CASE_OWNER', '1994-03-10'),

(30033333, 'CASE_OWNER', '1980-01-01'),  -- added a single case owner with one child!
(30033333, 'CHILD'     , '2012-09-01'),

(30044444, 'CASE_OWNER', '2015-08-10'),  -- added a case owner &lt; 21 yrs of age!

(30055555, 'CASE_OWNER', '1970-02-10'),  -- added a couple whose children are all &gt; 21!
(30055555, 'WIFE'      , '1972-07-05'),
(30055555, 'CHILD'     , '1995-11-22'),
(30055555, 'CHILD'     , '1997-05-19'),


(30066666, 'CASE_OWNER', '1970-02-10'),  -- added single case owner whose children
(30066666, 'CHILD'     , '1989-07-05'),  -- are all over 21!
(30066666, 'CHILD'     , '1992-11-22'),
(30066666, 'CHILD'     , '1994-05-19');
很重要
理解該CASE陳述對於遵循此答案的其餘部分至關重要。查詢將向下進行CASE，當遇到第一個匹配條件時，它將執行分配，然後退出CASE並從下一條記錄重新開始——這有點像C（和其他）程式語言CONTINUE語句，用於跳出循環並從下一次迭代重新開始。
這就是為什麼
aDEFAULT對於跟踪您的作業是否正確進行以及您沒有錯過任何內容很重要
你必須為你的條件有一條清晰的道路。別人裡面有CASE說法的時候，很容易迷惑自己！
第一種查詢形式（使用視窗函式 - 僅在 MySQL >= 8 中可用）。
SELECT
 case_id,
 cat,
 birth,
 c_type,
 CASE
 
 -- 1st section:
 
   WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1 
     THEN 
       CASE
         WHEN (DATEDIFF(NOW(), birth) / 365.25) &gt;= 21
           THEN 'SINGLE_PERSON'
         ELSE 'UNKNOWN'
       END        
   
 -- 2nd section:

   WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2 
   AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
     THEN 'PAIR_NO_CHILD'


 -- 3rd section:


   WHEN COUNT(case_id) OVER (PARTITION BY case_id) &gt;= 3
   AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
     THEN
       CASE
         WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) &lt; 21
           THEN 'PAIR_WITH_CHILD'
         ELSE 'OTHER'
       END 
 
 -- 4th section:

   WHEN COUNT(case_id) OVER (PARTITION BY case_id) &gt;= 2
   AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
     THEN
       CASE
         WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) &lt; 21
           THEN 'SINGLE_WITH_CHILD'
         ELSE 'MULTIPLE'
       END 

   ELSE 'No assigned type!'  -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
 END

-- 5th section:     

FROM
 cas
ORDER BY 
 case_id,
 CASE
   WHEN cat = 'CASE_OWNER' THEN 1
   WHEN cat = 'WIFE'       THEN 2
   WHEN cat = 'CHILD'      THEN 3
 END,
 birth DESC;
我將逐節介紹它，因為有幾個棘手的地方！
第 1 節：
 CASE
   WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1 
     THEN 
       CASE
         WHEN EXTRACT('YEAR' FROM AGE(birth)) &gt;= 21
           THEN 'SINGLE_PERSON'
         ELSE 'UNKNOWN'
       END
這涵蓋
a) 如果 CASE_ID 中只有 CASE_OWNER，則 TYPE 應為
a1) 匹配 CASE_ID 的列 TYPE 中的 SINGLE_PERSON，如果 CASE_OWNER 早於 21
a2) 與 CASE_ID 匹配的列 TYPE 中的 UNKNOWN，如果 CASE_OWNER 小於 21
這是 aCASE內的一個範例CASE！如果只有一個給定的記錄，case_id那麼根據定義，它必須是案例所有者！然後，我們檢查他們的生日，如果他們超過 21 歲（正常情況），則將值設置為SINGLE_PERSON，UNKNOWN否則！
這COUNT(case_id) OVER(...是一個視窗函式的例子。這些功能非常強大，非常值得深入了解（此處的簡短介紹）——它們將回報您多次學習它們所付出的任何努力！
這裡還有其他計算年齡的方法- 取決於您需要的精度。
第2節：
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2 
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
 THEN 'PAIR_NO_CHILD'
這涵蓋了這種情況：
b) 如果 CASE_ID 的 CASE_ID 具有 CAT CASE_OWNER AND WIFE (WITHOUT CHILD)，則在與特定 CASE_ID 匹配的每一行中，TYPE 都應為 PAIR_NO_CHILD。
這裡有趣的片段是SUM(CASE WHEN...允許我們區分case_id有和沒有 s 的結構WIFE。
第三節：
WHEN COUNT(case_id) OVER (PARTITION BY case_id) &gt;= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
 THEN
   CASE
     WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) &lt; 21
       THEN 'PAIR_WITH_CHILD'
     ELSE 'OTHER'
   END
這涵蓋了以下情況：
c) 如果一個 CASE_ID 有 CAT CASE_OWNER AND WIFE AND 1 個或多個 CHILD(ren) 對應一個 CASE_ID，則 TYPE 應該是
c1) PAIR_WITH_CHILD 如果在匹配特定 CASE_ID 的每一行中有一個或多個 CHILD(ren) 低於 21。
c2) OTHER 如果所有 CHILD(ren) 在與特定 CASE_ID 匹配的每一行中都是 21。
第四節：
WHEN COUNT(case_id) OVER (PARTITION BY case_id) &gt;= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
 THEN
   CASE
     WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) &lt; 21
       THEN 'SINGLE_WITH_CHILD'
     ELSE 'MULTIPLE'
   END
這涵蓋了以下情況：
d) 如果一個 CASE_ID 有 CAT CASE_OWNER 並且一個 CASE_ID 有 1 個或多個 CHILD(ren)，則 TYPE 應該是
d1) SINGLE_WITH_CHILD，以防匹配特定 CASE_ID 的每一行中一個或多個（不是全部）CHILD(ren) 低於 21。
d2) 如果所有 CHILD(ren) 都在 21 歲以上，則為 MULTIPLE。
第五節：
FROM
 cas
ORDER BY 
 case_id,
 CASE
   WHEN cat = 'CASE_OWNER' THEN 1
   WHEN cat = 'WIFE'       THEN 2
   WHEN cat = 'CHILD'      THEN 3
 END,
 birth DESC;
在這裡，我們使用ORDER BY帶有CASE“嵌入”的 an。這使我們能夠完全控制記錄的排序 - 考慮到要求，這是一種合乎邏輯的排序方法，對測試非常有幫助。
結果：
case_id            cat  birth   c_type  c_t
20033738    CASE_OWNER  1996-04-08      PAIR_NO_CHILD
20033738    WIFE        1995-08-22      PAIR_NO_CHILD
20033831    CASE_OWNER  1975-03-05      SINGLE_WITH_CHILD
20033831    CHILD       2013-10-25      SINGLE_WITH_CHILD
...
... snipped for brevity
...
查詢的第二種形式（使用聚合和子查詢） - 從至少 5.5 開始工作：
SELECT
 s.case_id,
 s.cat,
 s.birth,

--
-- 1st section: these sections correspond to the sections in query 1 above. 
--

 CASE
   WHEN s.c_cnt = 1
     THEN
       CASE
         WHEN s.a_min &gt;= 21
           THEN 'SINGLE PERSON'
         ELSE 'UNKNOWN'
       END
 
       
--
-- 2nd section:
--
       
   WHEN s.c_cnt = 2  AND s.w_cnt = 1
     THEN 'PAIR_NO_CHILD'
 
       
--  
-- 3rd section:
--
       
   WHEN s.c_cnt &gt;= 3 AND s.w_cnt = 1
     THEN
       CASE
         WHEN s.a_min &lt; 21
           THEN 'PAIR_WITH_CHILD'
         ELSE 'OTHER'
       END          
 
 
--
-- 4th section:
--
       
   WHEN s.c_cnt &gt;= 2 AND s.w_cnt = 0
     THEN
       CASE
         WHEN s.a_min &lt; 21 
           THEN 'SINGLE_WITH_CHILD'
         ELSE 'MULTIPLE'
       END                 
       
       
   ELSE 'No assigned type!'

 END AS c_t
 
FROM
(
 SELECT 
   case_id,
   cat, 
   birth,
   
   (SELECT COUNT(c2.case_id) 
      FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
   
   (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
      FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
   
   (SELECT FLOOR(MIN(DATEDIFF(NOW(), c4.birth) / 365.25)) 
      FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min    
      
 FROM cas c1
 ORDER BY 
   c1.case_id,
   CASE
     WHEN c1.cat = 'CASE_OWNER' THEN 1
     WHEN c1.cat = 'WIFE'       THEN 2
     WHEN c1.cat = 'CHILD'      THEN 3
   END,
 c1.birth DESC
) AS s;
結果：
Same as for query 1.
只需注意幾點：
如上所述，請始終在適當的時候提出您的問題——通常是如果您想顯示任何類型的數據！
PAIR_WITH_CHILD聽起來不協調 - a"pair"通常是指某種野生動物，或者可能是家畜或農場動物，但不是人類！但是，“孩子” and“妻子”都絕對指的是人類。你可能想把“夫婦與孩子”或類似的！
我UPDATE在小提琴的底部加入了一個。
所以，回答問題：
我的問題是：
使用 SQL 是否可行？
是的，見上文。
這應該使用 SQL 還是使用程式語言來解決？
在這種情況下沒有理由不使用 SQL。SQL 現在是圖靈完備的，但是僅僅因為你可以用給定的語言做某事，並不意味著你應該用那種語言來做。
會有一個點，您有非常複雜的要求，使用 SQL 會導致您的工作量與結果的收益遞減 - 經驗會告訴您何時使用其他工具更好！
如果這在 SQL 中是可行的 - 應該怎麼做？
往上看！此處給出了一個顯示如何使用聚合和 CTE 進行更新的小提琴。
最後，性能分析：
我查看了這些計劃（來自 MySQL >= 8）並且無法理解它們！關於性能測試的常見警告適用 - 此數據集中只有 22 條記錄。您應該使用您自己的硬體和其他系統約束在您自己的完整數據集上測試您的查詢/查詢。然而，為了記錄，在本地安裝的 MySQL (8.0.27) 實例、Windows 11、16GB RAM、8 核處理器、512GB NVMe 驅動器上，我獲得了以下結果：
MySQL：
Q2：‘Aggregates_no_ORDER_BY - 開始’；0.187165 秒
Q1：‘Window_no_ORDER_BY - 結束’；0.229719 秒
Q2：‘Aggregates_with_ORDER_BY - 結束’；0.296987 秒
Q1 ‘Window_with_ORDER_BY - 結束’; 0.344441 秒
PostgreSQL（同一台機器）- 使用 EnterpriseDB 的 14.1 二進製文件 from here。請參閱此處了解 PostgreSQL 的EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>.
Q1：Windows_order_by 1.328 毫秒
Q2：Windows-NO-order_by 1.35 ms
Q1：Aggregate_order_by 1.8 毫秒
Q2：Aggregate_no_order_by 2.7 毫秒
MySQL 的結果似乎與計劃的複雜性或表必須掃描 4 次的事實不符（或者是嗎？）。
真正令人費解的是 MySQL 比 PostgreSQL 慢 140 倍？坦率地說，我很困惑——你必須自己測試。

引用自：https://dba.stackexchange.com/questions/305706

MySQL - 基於多個條件更新列

介紹：

很重要

第一種查詢形式（使用視窗函式 - 僅在 MySQL >= 8 中可用）。

第 1 節：

第2節：

第三節：

第四節：

第五節：

結果：

查詢的第二種形式（使用聚合和子查詢） - 從至少 5.5 開始工作：

結果：

所以，回答問題：

最後，性能分析：

相關問答

將執行/累計總數與靜態目標進行比較

當另一列發生更改時更新有效列

聚合重疊的日期間隔

如果特定數據在另一個表中可用，MySQL 觸發器以防止在一個表中更新

將一個表中的日期值與另一個表中的多個值進行比較以按年齡分類

查找數據庫中任何表的最後更新/插入/刪除的實用方法？