skip to Main Content
A_id B_id L M N
1 1 1 1 0
1 2 4 3 2
1 3 2 2 1
2 4 2 2 1
2 5 3 2 1
2 6 1 1 0
3 7 4 3 1
3 8 1 1 0
4 9 4 4 2

Suppose P = 2, Q = 3. I would like to find the A_id such that the following are satisfied:

  • the sum of the P greatest L records for A_id >= P+Q
  • the sum of the P greatest M records for A_id >= P
  • the sum of the P greatest N records for A_id >= Q

I have attempted to solve this and see I will need to use a combination of GROUP BY, ORDER BY, LIMIT and SUM but there is so much going on that I am just lost in how to implement this in SQL. It is beyond my current capability.

Please could someone explain to me how to write a query like this for MySQL.

2

Answers


  1. Some thing like this

    SELECT A_id
    FROM (
        SELECT 
            A_id,
            SUM(L) AS sum_L,
            SUM(M) AS sum_M,
            SUM(N) AS sum_N
        FROM your_table_name
        GROUP BY A_id
    ) AS sums
    ORDER BY 
        (SELECT SUM(L) FROM (SELECT L FROM your_table_name WHERE A_id = sums.A_id ORDER BY L DESC LIMIT 2) AS P_greatest_L) DESC,
        (SELECT SUM(M) FROM (SELECT M FROM your_table_name WHERE A_id = sums.A_id ORDER BY M DESC LIMIT 2) AS P_greatest_M) DESC,
        (SELECT SUM(N) FROM (SELECT N FROM your_table_name WHERE A_id = sums.A_id ORDER BY N DESC LIMIT 3) AS P_greatest_N) DESC
    LIMIT 1;
    
    Login or Signup to reply.
  2. Assuming MySQL ≥ 8, you could use a CTE to add row numbering and then conditional aggregation based on the row numbering:

    SET @P = 2, @Q = 3;
    
    WITH greatest AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY L DESC) AS greatest_l,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY M DESC) AS greatest_m,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY N DESC) AS greatest_n
        FROM tbl
    )
    SELECT A_id
    FROM greatest
    GROUP BY A_id
    HAVING SUM(IF(greatest_l <= @P, L, 0)) >= @P + @Q
       AND SUM(IF(greatest_m <= @P, M, 0)) >= @P
       AND SUM(IF(greatest_n <= @P, N, 0)) >= @Q;
    

    Based on your sample data the output is:

    A_id
    1

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search