CODEWITHSUNDEEP
mysqlsum
pawelglow
pawelglow

Reputation: 708

Sum until certain point from each group - mysql

I have a MySQL table with about 1000 rows, filled with different types of fruit trees and their locations on a farm. The data looks like this:

Fruit Trees Location on Farm

  7 |
  6 |    G G
  5 |    G   
Y 4 |    G   G
  3 |      A X G G
  2 |    A A A
  1 |_ _ _ _ _ _ _ 
     1 2 3 4 5 6 7
           X

Fruit Trees MySQL Table

 ID   Type    X  Y
 --   ----    -- --
 1    Apple   3  2
 2    Grape   3  4
 3    Grape   3  5
 4    Grape   3  6
 5    Apple   4  2
 6    Apple   4  3
 7    Grape   4  6
 8    Apple   5  2
 9    Grape   5  4
10    Grape   6  3
11    Grape   7  3

Now, I am looking to query this table to get the nearest "3" trees only of each type from the center X (5,3).

The query result should return something like:

 ID   Type    X  Y
 --   ----    -- --
 6    Apple   4  3
 5    Apple   4  2
 8    Apple   5  2
 9    Grape   5  4
10    Grape   6  3
11    Grape   7  3

There may be several trees with the same distance from the center but that should not matter because once the maximum number of nearest trees (3) is meet then we wouldn't include anymore trees from that type of tree. Hope this makes sense.

To get the distance from the center X to a nearby tree I use the below formula:

Distance = SQRT( POW(ABS(5 - X),2) + POW(ABS(3 - Y),2) )

Here is a reference that I have been trying to use: Sum until certain point - MySql

How would I write a MySQL query to get the results? I may have to write several queries to do this but am just unsure how to structure it all.

Thank you

Upvotes: 2

Views: 291

Answers (2)

martin clayton
martin clayton

Reputation: 78105

You can simulate row numbering with variables.

SELECT @i:=0, @type:=''
;

SELECT
      id, type, x, y
FROM (
    SELECT
          id, type, x, y
        , SQRT( POW(ABS(5 - x),2) + POW(ABS(3 - y),2) ) AS distance
        , IF( type=@type, @i:=@i+1, @i:=0             ) AS rank
        , IF( type=@type, @type,    @type:=type       ) AS new_type

    FROM
        fruit_trees
    ORDER BY
          type
        , distance
    ) x
WHERE
    rank < 3

Seems to work for me, but I expect performance will be ropey.

Upvotes: 1

gpeche
gpeche

Reputation: 22504

Maybe this?

select id, type, x, y
from fruit_trees as ft1
where not exists (
    select null
    from fruit_trees as ft2
    where 
    ft2.type = ft1.type and
    ft2.distance <= ft1.distance
    limit 3, 1
)

EDIT: Better try this:

select id, type, x, y
from fruit_trees as ft1
where id in (
    select id
    from fruit_trees as ft2
    where 
    ft2.type = ft1.type
    order by ft2.distance
    limit 3
)

I have no MySQL available at the moment so i cannot test that query.

Upvotes: 1

Related Questions