主页 / dba / 问题 / 305706
Accepted
Stefanie
Asked: 2022-01-07 04:09:45 +0800 CST

MySQL - 基于多个条件更新列

  • 772

我有一张桌子，里面有这样的行：

+----------+--------------+---------------------+------+
| 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 update

2 个回答

  1. Best Answer

    介绍：

    你有两种可能做你想做的事——一种适用于 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 < 21 yrs of age!

(30055555, 'CASE_OWNER', '1970-02-10'),  -- added a couple whose children are all > 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) >= 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) >= 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) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END 
  
  -- 4th 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) = 0
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 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)) >= 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) >= 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) < 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) >= 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) < 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 >= 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 >= 3 AND s.w_cnt = 1
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END          
  
  
--
-- 4th section:
--
        
    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 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.

    只需注意几点：

    1. 如上所述，请始终在适当的时候提出您的问题——通常是如果您想显示任何类型的数据！

    2. PAIR_WITH_CHILD sounds incongruous - a "pair" normally refers to wildlife of some sort, or possibly domestic or farm animals, but not humans! However,both '"child"and"wife"` definitely refer to human beings. You might want to put "Couple with children" or similar!

    3. I've included an UPDATE at the bottom of the fiddle.

    So, to answer the questions:

    My questions are:

    • Is this doable using SQL?

    Yes, see above.

    • Should this be solved using SQL or using a programming language?

    There's no reason not to use SQL in this case. SQL is now Turing complete, however just because you can to do something in a given language, doesn't mean that you should do it in that language.

    There will come a point where you have very complex requirements where using SQL will lead to diminishing returns in terms of your effort vs. outcome - experience will tell you when it's better to use another tool!

    • If this is doable in SQL - how should it be done?

    See above! A fiddle showing how to update using aggregates and a CTE is given here.

    Finally, a Performance analysis:

    I looked at the plans (from MySQL >= 8) and can't make much sense of them! The usual caveats about performance testing apply - there are only 22 records in this dataset. You should test your query/queries on your own full dataset with your own hardware and other system constraints. Just for the record however, on a locally installed instance of MySQL (8.0.27), Windows 11, 16GB of RAM, 8-core processor, 512GB NVMe drive I obtained these results:

    MySQL:

    Q2: 'Aggregates_no_ORDER_BY - beginning'; 0.187165 s

    Q1: 'Window_no_ORDER_BY - end'; 0.229719 s

    Q2: 'Aggregates_with_ORDER_BY - end'; 0.296987 s

    Q1 'Window_with_ORDER_BY - end'; 0.344441 s

    PostgreSQL (same machine) - using EnterpriseDB's 14.1 binary from here. See here for a PostgreSQL fiddle with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>.

    Q1: Windows_order_by 1.328 ms

    Q2: Windows-NO-order_by 1.35 ms

    Q1: Aggregate_order_by 1.8 ms

    Q2: Aggregate_no_order_by 2.7 ms

    The results for MySQL don't appear to align with the complexity of the plans or the fact that the table has to be scanned 4 times (or does it?).

    What is really puzzling is that MySQL is 140 times slower than PostgreSQL? Frankly, I'm baffled - you'll have to test for yourself.

    • 2

  2. UPDATEKISS——为每个案例编写（并运行）单独的。

    CASE有时可以用来将多个条件组合成一个更新，但读到这样会让我的大脑受伤。

    如果一个更新正在修改后续更新将测试的列，请确保以合适的顺序运行更新。

    • 0

相关问题