1

I have a table which has Rows like these:

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

For one CASE_ID every combination of CASE_OWNER with or without WIFE and/or CHILD (1 or more) is possible.

For each CASE_ID I want to set the column TYPE, based on the information I will find in CAT and BIRTH:

a) If there is only a CASE_OWNER in a CASE_ID, TYPE should be

  • a1) SINGLE_PERSON in Column TYPE which matches CASE_ID, IF CASE_OWNER is older than 21
  • a2) UNKNOWN in Column TYPE which matches CASE_ID, IF CASE_OWNER is younger than 21

b) If a CASE_ID has CAT CASE_OWNER AND WIFE (WITHOUT CHILD) for a CASE_ID, TYPE should be PAIR_NO_CHILD in every row that matches the specific CASE_ID.

c) If a CASE_ID has CAT CASE_OWNER AND WIFE AND 1 or more CHILD(ren) for a CASE_ID, TYPE should be

  • c1) PAIR_WITH_CHILD if one or more CHILD(ren) are below 21 in every row that matches the specific CASE_ID.
  • c2) OTHER if all CHILD(ren) are are 21 in every row that matches the specific CASE_ID.

d) If a CASE_ID has CAT CASE_OWNER AND 1 or more CHILD(ren) for a CASE_ID, TYPE should be

  • d1) SINGLE_WITH_CHILD in case one or more (not all) CHILD(ren) is/are below 21 in every row that matches the specific CASE_ID.
  • d2) MULTIPLE in case all CHILD(ren) are above 21.

e) All other combinations will be of TYPE == UNKNOWN.

My questions are:

  • Is this doable using SQL?
  • Should this be solved using SQL or using a programming language?
  • If this is doable in SQL - how should it be done?

Thanks a lot for your feedback Steffi

3
  • Hi, and welcome to dba.se! Are there any other possibilities than CASE_OWNER, WIFE and CHILD? Also, am I correct in thinking that the case_owner of case_id 20033831 has 4 wives, one of whom is < 9 years old? Jan 6 at 16:44
  • 1
    You are right, these are all children. I edited my question and corrected it. And yes, the only valid possibilites are CASE_OWNER, WIFE and CHILD.
    – Stefanie
    Jan 6 at 17:59
  • Can I assume that a CASE_OWNER can have at most 1 WIFE? Jan 7 at 12:02
2

Introduction:

You have two possibilities to do what you want - one works for versions of MySQL from 5.5 (uses aggregates) and upwards and the other works for MySQL 8 and up (uses window functions).

All of the code below is available on the fiddle here. NOTE: The fiddle is for MySQL version 8. If you wish to run versions 5.5 (or 5.6 or 5.7), please change the server in the dropdown at the top of the fiddle. I did this because EXPLAIN ANALYZE can only be used with MySQL > 8.0.18 - previous versions are unusable!

First create your table (you should provide this yourself in the form of a fiddle for this type of question). I made a few changes to your schema:

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

and then populate it. I added a few records for testing purposes:

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

VERY IMPORTANT

An understanding of the CASE statement is critical for following the rest of this answer. The query will progress down through the CASE and when it comes across the first matching condition, it will perform the assignment and then drop out of the CASE and start again at the next record - this is a bit like the C (and other) programming language CONTINUE statement, used to break out of loops and start again with the next iteration.

This is why

  • a DEFAULT is important to track that your assignments are proceding correctly and that you haven't missed anything

  • you have to have a clear path for your conditions. When there are CASE statements within others, it can become very easy to confuse oneself!

1st form of the query (using window functions - only available in 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;

I'll go through it section by section as there are a couple of tricky bits!

The 1st section:

  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

This covers

  • a) If there is only a CASE_OWNER in a CASE_ID, TYPE should be

    • a1) SINGLE_PERSON in Column TYPE which matches CASE_ID, IF CASE_OWNER is older than 21
    • a2) UNKNOWN in Column TYPE which matches CASE_ID, IF CASE_OWNER is younger than 21

This is an example of a CASE within a CASE! If there's only one record with a given case_id then, by definition, it must be the case owner! Then, we check their birthday and if they're over 21 (normal course of events), then set the value to SINGLE_PERSON, UNKNOWN otherwise!

The COUNT(case_id) OVER(... is an example of a window function. These are extremely powerful and well worth getting to know well (short intro here) - they will repay any effort spent learning them many times over!

There are other ways of calculating age here - depending on the precision you require.

The 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'

This covers the case:

  • b) If a CASE_ID has CAT CASE_OWNER AND WIFE (WITHOUT CHILD) for a CASE_ID, TYPE should be PAIR_NO_CHILD in every row that matches the specific CASE_ID.

The interesting snippet here is the SUM(CASE WHEN... construct which allows us to distinguish between case_ids which do and do not have a WIFE.

The 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(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
        THEN 'PAIR_WITH_CHILD'
      ELSE 'OTHER'
    END

This covers the cases:

  • c) If a CASE_ID has CAT CASE_OWNER AND WIFE AND 1 or more CHILD(ren) for a CASE_ID, TYPE should be

    • c1) PAIR_WITH_CHILD if one or more CHILD(ren) are below 21 in every row that matches the specific CASE_ID.
    • c2) OTHER if all CHILD(ren) are are 21 in every row that matches the specific CASE_ID.

The 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(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
        THEN 'SINGLE_WITH_CHILD'
      ELSE 'MULTIPLE'
    END

This covers the cases:

  • d) If a CASE_ID has CAT CASE_OWNER AND 1 or more CHILD(ren) for a CASE_ID, TYPE should be

    • d1) SINGLE_WITH_CHILD in case one or more (not all) CHILD(ren) is/are below 21 in every row that matches the specific CASE_ID.
    • d2) MULTIPLE in case all CHILD(ren) are above 21.

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

Here, we use an ORDER BY with a CASE "embedded" in it. This allows us to have total control over the ordering of our records - given the requirements, this is a logical ordering method and is extremely helpful for testing.

Result:

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

2nd form of the query (using aggregates and a subquery) - works from at least 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;

Result:

Same as for query 1.

Just a couple of points to note:

  1. As mentioned above, please always provide a fiddle with your questions when appropriate - normally if you want to display data of any sort!

  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.

9
  • Wow, Verace. Thank you so much for this awsome answer and the time it took to write it down. I will study your code carefully. Thanks!
    – Stefanie
    Jan 7 at 17:34
  • @Stefanie Oops! Big red face 😳! I used PostgreSQL in my original response - I've changed it to a MySQL one. The CTEs will only work with version 8 - and I've also included an update! My teachers in school were blue in the face telling us: always, always, read the damn question! - :-) Jan 7 at 19:09
  • Your first answer is/was a challenge for me since I noticed it is PostgreSQL and had never heard about the windows functions. So I looked at the mysql docs and saw that they are there also. Now I have to study these and you are right, they are incredible useful. It will take me some time to understand and find free time slots to study. I want to thank you for your second version a lot. This is kind of what I would have expected as an answer (using group by).
    – Stefanie
    Jan 9 at 8:45
  • @Stefanie - glad to see that I'm not the only one up early on a Sunday! :-) Don't forget that the Aggregation - (as best I can determine - I may ask a question of my own) scans the table 4 times - not too much of an issue with ~ 20 records, but if your table grows, it could be an issue. Yes, I corrected the PostgreSQL error - but producing the fiddle produced some interesting results - the plans are certainly easier to understand. Also, as a tip, if ever you're having difficulty understanding something, try it on PostgreSQL - it's considerably more standards compliant than MySQL! Best... Jan 9 at 9:11
  • could you please have a look, I wasn't fast enough to save your first answer. Could it be that the update itsself got lost or am I missing something?
    – Stefanie
    Jan 9 at 13:19
0

KISS -- Write (and run) a separate UPDATE for each case.

CASE can sometimes be used to combine multiple conditions into a single Update, but reading such makes my brain hurt.

If one Update is modifying column(s) that the following Updates will be testing, be sure to run the Updates in a suitable order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.