Início / dba / Perguntas / 305706
Accepted
Stefanie
Asked: 2022-01-07 04:09:45 +0800 CST

MySQL - coluna de atualização com base em várias condições

  • 772

Eu tenho uma tabela que tem linhas como estas:

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

Para um CASE_ID, cada combinação de CASE_OWNER com ou sem WIFE e/ou CHILD (1 ou mais) é possível.

Para cada CASE_ID quero definir a coluna TYPE, com base nas informações que encontrarei em CAT e BIRTH:

a) Se houver apenas um CASE_OWNER em um CASE_ID, TYPE deve ser

  • a1) SINGLE_PERSON na coluna TYPE que corresponde a CASE_ID, SE CASE_OWNER tiver mais de 21 anos
  • a2) UNKNOWN na coluna TYPE que corresponde a CASE_ID, SE CASE_OWNER for menor que 21

b) Se um CASE_ID tiver CAT CASE_OWNER AND WIFE (WITHOUT CHILD) para um CASE_ID, TYPE deverá ser PAIR_NO_CHILD em cada linha que corresponda ao CASE_ID específico.

c) Se um CASE_ID tiver CAT CASE_OWNER AND WIFE AND 1 ou mais CHILD(s) para um CASE_ID, TYPE deve ser

  • c1) PAIR_WITH_CHILD se um ou mais CHILD(s) estiverem abaixo de 21 em cada linha que corresponda ao CASE_ID específico.
  • c2) OTHER se todos os CHILD(s) forem 21 em cada linha que corresponda ao CASE_ID específico.

d) Se um CASE_ID tiver CAT CASE_OWNER AND 1 ou mais CHILD(s) para um CASE_ID, TYPE deve ser

  • d1) SINGLE_WITH_CHILD no caso de um ou mais (não todos) CHILD(s) serem abaixo de 21 em cada linha que corresponda ao CASE_ID específico.
  • d2) MÚLTIPLO caso todas as CRIANÇAS sejam acima de 21 anos.

e) Todas as outras combinações serão do TIPO == DESCONHECIDO.

Minhas perguntas são:

  • Isso é possível usando SQL?
  • Isso deve ser resolvido usando SQL ou usando uma linguagem de programação?
  • Se isso for possível no SQL - como deve ser feito?

Muito obrigado pelo seu feedback Steffi

mysql update

2 respostas

  1. Best Answer

    Introdução:

    Você tem duas possibilidades para fazer o que quiser - uma funciona para versões do MySQL a partir de 5.5 (usa agregados) e superior e a outra funciona para MySQL 8 e superior (usa funções de janela).

    Todo o código abaixo está disponível no violino aqui . NOTA: O violino é para o MySQL versão 8. Se você deseja executar as versões 5.5 (ou 5.6 ou 5.7), altere o servidor no menu suspenso na parte superior do violino. Eu fiz isso porque EXPLAIN ANALYZEsó pode ser usado com MySQL > 8.0.18 - as versões anteriores são inutilizáveis!

    Primeiro crie sua tabela (você mesmo deve fornecer isso na forma de um violino para esse tipo de pergunta). Fiz algumas alterações no seu esquema:

    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
);

    e, em seguida, preenchê-lo. Adicionei alguns registros para fins de teste:

    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');

    MUITO IMPORTANTE

    Uma compreensão da CASEdeclaração é fundamental para seguir o restante desta resposta. A consulta irá progredir através do CASEe quando se deparar com a primeira condição de correspondência, ele irá executar a atribuição e, em seguida, sair do CASEregistro e começar novamente no próximo registro - isso é um pouco como a instrução Cda linguagem de programação (e outras) , CONTINUEusado para sair de loops e começar de novo com a próxima iteração.

    Isso é por que

    • a DEFAULTé importante acompanhar se suas tarefas estão sendo executadas corretamente e se você não perdeu nada

    • você tem que ter um caminho claro para suas condições. Quando há CASEdeclarações dentro dos outros, pode se tornar muito fácil confundir-se!

    1ª forma da consulta (usando funções de janela - disponível apenas no 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;

    Vou passar por isso seção por seção, pois há algumas partes complicadas!

    A 1ª seção:

      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

    Isso cobre

    • a) Se houver apenas um CASE_OWNER em um CASE_ID, TYPE deve ser

      • a1) SINGLE_PERSON na coluna TYPE que corresponde a CASE_ID, SE CASE_OWNER tiver mais de 21 anos
      • a2) UNKNOWN na coluna TYPE que corresponde a CASE_ID, SE CASE_OWNER for menor que 21

    Este é um exemplo de um CASEdentro de um CASE! Se houver apenas um registro com um dado case_id, por definição, deve ser o proprietário do caso! Em seguida, verificamos a data de nascimento e, se tiverem mais de 21 anos (curso normal dos eventos), configuramos o valor para SINGLE_PERSON, UNKNOWNcaso contrário!

    O COUNT(case_id) OVER(...é um exemplo de uma função de janela . Estes são extremamente poderosos e vale a pena conhecê-los bem (pequena introdução aqui ) - eles vão compensar qualquer esforço gasto em aprendê-los muitas vezes!

    Existem outras maneiras de calcular a idade aqui - dependendo da precisão que você precisa.

    A 2ª seção:

    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'

    Isso abrange o caso:

    • b) Se um CASE_ID tiver CAT CASE_OWNER AND WIFE (WITHOUT CHILD) para um CASE_ID, TYPE deverá ser PAIR_NO_CHILD em cada linha que corresponda ao CASE_ID específico.

    O trecho interessante aqui é a SUM(CASE WHEN...construção que nos permite distinguir entre case_ids que possuem e não possuem um WIFE.

    A 3ª seção:

    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

    Isso abrange os casos:

    • c) Se um CASE_ID tiver CAT CASE_OWNER AND WIFE AND 1 ou mais CHILD(s) para um CASE_ID, TYPE deve ser

      • c1) PAIR_WITH_CHILD se um ou mais CHILD(s) estiverem abaixo de 21 em cada linha que corresponda ao CASE_ID específico.
      • c2) OTHER se todos os CHILD(s) forem 21 em cada linha que corresponda ao CASE_ID específico.

    A 4ª parte:

    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

    Isso abrange os casos:

    • d) Se um CASE_ID tiver CAT CASE_OWNER AND 1 ou mais CHILD(s) para um CASE_ID, TYPE deve ser

      • d1) SINGLE_WITH_CHILD no caso de um ou mais (não todos) CHILD(s) serem abaixo de 21 em cada linha que corresponda ao CASE_ID específico.
      • d2) MÚLTIPLO caso todas as CRIANÇAS sejam acima de 21 anos.

    A 5ª parte:

    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;

    Aqui, usamos um ORDER BYcom um CASE"incorporado" nele. Isso nos permite ter controle total sobre a ordenação de nossos registros - dados os requisitos, este é um método de ordenação lógico e é extremamente útil para testes.

    Resultado:

    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
...

    2ª forma da consulta (usando agregados e uma subconsulta) - funciona a partir de pelo menos 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;

    Resultado:

    Same as for query 1.

    Apenas alguns pontos a serem observados:

    1. Como mencionado acima, sempre forneça um violino com suas perguntas quando apropriado - normalmente se você quiser exibir dados de qualquer tipo!

    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. KISS -- Escreva (e execute) um separado UPDATEpara cada caso.

    CASEàs vezes pode ser usado para combinar várias condições em uma única atualização, mas ler isso faz meu cérebro doer.

    Se uma atualização estiver modificando a(s) coluna(s) que as atualizações a seguir testarão, certifique-se de executar as atualizações em uma ordem adequada.

    • 0

relate perguntas