CODEWITHSUNDEEP
sqlpostgresql
bozdoz
bozdoz

Reputation: 12870

How to perform mathematical formulas on separate query sets

We have a program that gets data for GPS coordinates. We can get a dataset based on a region id in our database, which would look something like this:

gps_coords | year      | value
105        | 2010      |  5.63
102        | 1990      |  3.2
103        | 2000      |  13.23
...

Now, we want to combine that and another query set, with a sql like a.value + b.value, or (a.value+50)*b.value/100. We also filter our query by metrics (what dataset they want to query).

The problem is how to retrieve both gps_coords as one column. I figured we have to do a JOIN on the same table, but I don't know how to get both a.gps_coords and b.gps_coords in the same column.

My query (below) executes in 100 ms with zero rows. So, I'm not sure what's going wrong. Does anyone know how I can get both a's and b's gps_coords in the same column? I'm using Postgresql, but anything would help. Thanks!

Schema

data:

gps_coords
year
value
metric

regions:

gps_coords
region_id

Sample Data:

Data

|  gps_coords  |  year  |  value  |  metric  |
|  506         |  2010  |  23.23  |  5       |
|  507         |  2010  |  10.32  |  5       |
|  508         |  2010  |  28.5   |  5       |
|  509         |  2010  |  45.24  |  5       |
|  506         |  2010  |  213.53 |  4       |
|  507         |  2010  |  0      |  4       |
|  508         |  2010  |  434.4  |  4       |
|  509         |  2010  |  381.1  |  4       |

Regions

|  gps_coords  |  region_id  |
|  506         |  1          |
|  506         |  2          |
|  506         |  3          |
|  507         |  1          |
|  508         |  1          |
|  508         |  3          |
|  509         |  1          |
|  509         |  2          |

Desired Output:

Supposing I want coordinates for metric 5 in region 1, added with metric 4 in region 3 (which overlap on gps_coords 506), I want to return all gps_coords (no matter the region), and then the specified values (added where they intersect):

|  gps_coords  |  year  |  value  |
|  506         |  2010  |  233.76 |
|  507         |  2010  |  0      |
|  508         |  2010  |  434.4  |
|  509         |  2010  |  45.24  |

Sample (incorrect) SQL:

SELECT DISTINCT init.gps_coords, init.year, a.value + b.value as value

FROM data as init

INNER JOIN data as a USING (metric, value)
INNER JOIN data as b USING (metric, value)

INNER JOIN regions as r
ON (init.gps_coords = r.gps_coords)
AND r.region_id = 1

INNER JOIN regions as ra
ON (a.gps_coords = ra.gps_coords)
AND ra.region_id = 2

INNER JOIN regions as rb
ON (init.gps_coords = rb.gps_coords)
AND rb.region_id = 3

WHERE a.metric = 5
AND b.metric = 4
ORDER BY init.gps_coords

Above would be all coordinates, for every region (region 1), and then the values added where they intersect (ra.region 2 would include coords 506 and 509, and would add with rb.region 3's coords: 506 and 508, adding at coords 506). 507 doesn't appear in either either region id, so it is 0, or null, whichever.

Upvotes: 1

Views: 110

Answers (2)

bozdoz
bozdoz

Reputation: 12870

This SQL gives me exactly what I need:

SELECT a.gps_coords, 
  a.year, 
  COALESCE(AVG(b.v1), 0) + COALESCE(AVG(b.v2), 0)
FROM data a
LEFT JOIN (
  SELECT d.gps_coords, d.year, d.value as v1, NULL v2
    FROM data d JOIN regions r
      ON d.gps_coords = r.gps_coords
   WHERE d.metric = 4 AND r.region_id = 3
UNION
  SELECT d.gps_coords, d.year, NULL, d.value
    FROM data d JOIN regions r
      ON d.gps_coords = r.gps_coords
   WHERE d.metric = 5 AND r.region_id = 2
) b
ON b.gps_coords = a.gps_coords
AND a.year = b.year
GROUP BY a.gps_coords, a.year
ORDER BY a.gps_coords

OUTPUT

|  gps_coords  |  year  |  value  |
|  506         |  2010  |  236.76 |
|  507         |  2010  |  0      |
|  508         |  2010  |  434.4  |
|  509         |  2010  |  45.24  |

Upvotes: 0

peterm
peterm

Reputation: 92805

If understand correctly (in which I'm not sure) your query might look like

SELECT COALESCE(b.gps_coords, c.gps_coords) AS gps_coords,
       COALESCE(b.year, c.year) AS year,
       COALESCE(b.value, 0) + COALESCE(c.value, 0) AS value
  FROM
(
  SELECT d.gps_coords, d.year, SUM(d.value) AS value
    FROM data d JOIN regions r
      ON d.gps_coords = r.gps_coords
   WHERE d.metric = 5 AND r.region_id = 1
   GROUP BY d.gps_coords, d.year
) b FULL JOIN
(
  SELECT d.gps_coords, d.year, SUM(d.value) AS value
    FROM data d JOIN regions r
      ON d.gps_coords = r.gps_coords
   WHERE (d.metric = 4 AND r.region_id = 3)
   GROUP BY d.gps_coords, d.year
) c
    ON b.gps_coords = c.gps_coords
   AND b.year = c.year
 ORDER BY gps_coords

Sample output:

| GPS_COORDS | YEAR |  VALUE |
-------------|------|--------|
|        506 | 2010 | 236.76 |
|        507 | 2010 |  10.32 |
|        508 | 2010 |  462.9 |
|        509 | 2010 |  45.24 |

Here is SQLFiddle demo

Upvotes: 1

Related Questions