Postgresql – Updating column with multiple sub-queries

postgispostgresql-9.4spatialsubquery

I have created the following query which is used to calculate a total from the results of a spatial query:

SELECT
  SUM(med_price)
FROM (SELECT
  ST_INTERSECTION(a.geom, (SELECT
    ST_SETSRID(geom, 4326)
  FROM catalogue_table
  WHERE id_number = '2211654')
  ) AS geom,
  small_price,
  med_price,
  large_price
FROM pricing_table AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 4326), (SELECT
  ST_SETSRID(geom, 4326)
FROM catalogue_table
WHERE id_number = '2211654')
)) AS total_price

My tables look like this:

CREATE TABLE pricing_table
(
  gid serial NOT NULL,
  tile_id numeric,
  small_price numeric,
  med_price numeric,
  large_price numeric,
  geom geometry(MultiPolygon,4326)
)

And:

CREATE TABLE catalogue_table
(
  ogc_fid serial NOT NULL,
  geom geometry(Geometry,4326),
  id_number character varying,
  scale integer,
  scale_code character varying
)

I am trying to modify this query so that it calculates the total price for all rows, instead of just a single row with a specific id_number. However, whenever I try and modify my query to do this, I get:

ERROR: more than one row returned by a subquery used as an expression. SQL state: 21000

I would like to create a 'price' column on the catalogue table, and populate this with calculated prices, which, depending on the 'scale_code' from the catalogue_table (either small, medium or large) will calculate a total from the relevant column in the 'pricing_table'.

Best Answer

The trick here is to not to get overwhelmed by the fact that there is a spatial datatype involved. Then it becomes a fairly simple aggregate query over a join between the pricing table and the catalog table where the join just happens to be a spatial intersection.

SELECT c.id_number, SUM(med_price) 
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
GROUP BY c.id_number

Then to incorporate the scaling factor into the sum you can use a case statement in a subquery, similar to the following

SELECT p.id_number, SUM(price)
FROM (
SELECT c.id_number, 
    CASE WHEN scaling_code = 'small' THEN small_price
        WHEN scale_code = 'medium' THEN med_price
        ELSE large_price
    END price
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
    ) p
GROUP BY p.id_number

Related Solutions

Mysql – Optimizing multiple MySQL sub-queries and joins for multiple runs

Avoid derived tables, like ypercube mentioned.

SELECT 
    TRIM(W_OEINVCD.pn) as productId,
    W_OEINVCD.qtyord as qty,
    W_OEINVCH.invno as invoiceId,
    W_OEINVCH.invdate as invoiceDate
FROM
    W_OEINVCH
    JOIN W_OEINVCD 
        ON W_OEINVCH.invno = W_OEINVCD.invno
WHERE
    W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
    AND W_OEINVCH.custid = '13651'

SQL Server – Optimize SELECT on Subquery with COALESCE

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).

Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages:

The optimizer has less transformation options available for FULL joins than for (the more common) INNER and LEFT joins.
The Z derived table can be removed (you can do that anyway) from the view definition.
The NOT(pe.ThisThing = 1 AND se.OtherThing = 0) will be needed only on the INNER join part.
Minor improvement, the use COALESCE() will be minimal if any at all (I assumed that se.SEId and pe.PEId are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE() calls.)
More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE() is not blocking the push.)
All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.

In all, the view can be written as:

SELECT 
    se.SEId + '-' + pe.PEId AS Id,
    se.SEId, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    COALESCE(pe.EventType, se.EventType) AS EventType,
    pe.Duration,
    COALESCE(pe.Data, se.Data) AS Data,
    COALESCE(pe.Field, se.Field) AS Field,
    pe.ThisThing, se.OtherThing,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0) 

UNION ALL

SELECT 
    '0-0',
    NULL, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    pe.EventType,
    pe.Duration,
    pe.Data,
    pe.Field,
    pe.ThisThing, NULL,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
  AND se.StaffName IS NULL

UNION ALL

SELECT 
    '0-0',
    se.SEId, NULL,
    se.StaffName, 
    se.EventTime,
    se.EventType,
    se.Duration,
    se.Data,
    se.Field,
    NULL, se.OtherThing, 
    DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
  AND pe.StaffName IS NULL ;

Best Answer

Related Solutions

Mysql – Optimizing multiple MySQL sub-queries and joins for multiple runs

SQL Server – Optimize SELECT on Subquery with COALESCE

Related Question