0

I have the following table structure:

CREATE TABLE `listings` (
  `id` bigint unsigned NOT NULL,
  `county` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `price_per_acre` decimal(12,2) GENERATED ALWAYS
           AS ((`price` / `area`)) STORED,
  `area` decimal(10,2) NOT NULL,
  `local_avg_price_per_acre` decimal(12,2) DEFAULT NULL,
  `local_median_price_per_acre` decimal(12,2) DEFAULT NULL,
  `local_min_price_per_acre` decimal(12,2) DEFAULT NULL,
  `price_to_local_ratio` decimal(8,4) GENERATED ALWAYS
       AS ((`price_per_acre` / `local_avg_price_per_acre`)) STORED,
  `status` smallint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `listings_status_index` (`status`),
  KEY `listings_county_state_index` (`county`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And I have built following query:

SELECT
    MIN(`t1`.`price_per_acre`) AS `min`,
    `t3`.`avg`
FROM
    `listings` AS `t1`
    JOIN `listings` AS `t2` ON (
        `t1`.`area` >= `t2`.`area` * 0.5
        AND `t1`.`area` <= `t2`.`area` * 1.5
        AND `t1`.`state` = `t2`.`state`
        AND `t1`.`county` = `t2`.`county`
    )
    JOIN (
        SELECT
            AVG(`t1`.`price_per_acre`) AS `avg`,
            STDDEV_SAMP(`t1`.`price_per_acre`) AS `stddev`
        FROM
            `listings` AS `t1`
            JOIN `listings` AS `t2` ON (
                `t1`.`area` >= `t2`.`area` * 0.5
                AND `t1`.`area` <= `t2`.`area` * 1.5
                AND `t1`.`state` = `t2`.`state`
                AND `t1`.`county` = `t2`.`county`
            )
        WHERE
            `t2`.`id` = 1731961
            AND `t1`.`status` IN (1, 2)
    ) AS `t3` ON (
        `t1`.`price_per_acre` >= `t3`.`avg` - 2 * `t3`.`stddev`
        AND `t1`.`price_per_acre` <= `t3`.`avg` + 2 * `t3`.`stddev`
    )
WHERE
    `t2`.`id` = 1731961
    AND `t1`.`status` IN (1, 2)
GROUP BY
    `t3`.`avg`;

I'm sure the query is not the most readable one, but I couldn't make it better. At least it does the job. To summarize what the query does: for specific listing (with specified id) it gets locally similar listings based on +/-50% area in the same county, state and specific statuses (the first join). Then it calculates the average price per acre for them (the same set, second join with subquery) and filters those that are +/- 2 standard deviations from average to remove outliers, and finally selects the minimum one and the average from t3. The GROUP BY clause is there only to be able to select t3.avg (I'm not sure how to do that without that GROUP BY clause).

Now I have two problems here that I don't know how to solve:

  1. I want to add median(mean) to t3 so that I have it in the result.
  2. I want to convert this whole query to UPDATE query, so that it runs the updates for each listing in my table and sets appropriate local_avg_price_per_acre, local_median_price_per_acre, local_min_price_per_acre for all listings, two of which I already have in SELECT statement, and the median is the problem 1.

You can find the fiddle with demo data here: https://www.db-fiddle.com/f/qFEKdXwpWF7GSCjpseswU1/1
Any help would be appreciated.

UPDATE
I have made the query more readable by eliminating two joins and used variables to correctly calculate minimum. I still get correct results (https://www.db-fiddle.com/f/qFEKdXwpWF7GSCjpseswU1/3 - the updated fiddle doesn't calculate minimum, but my local setup works, not sure why), but the problems still remain: how to calculate also median, and more importantly how to convert this to UPDATE statement.

SELECT
    @avg := AVG(`t2`.`price_per_acre`) AS `avg`,
    @stddev := STDDEV_SAMP(`t2`.`price_per_acre`) AS `stddev`,
    MIN(
        CASE
            WHEN `t2`.`price_per_acre` >= (@avg - 2 * @stddev)
            AND `t2`.`price_per_acre` <= (@avg + 2 * @stddev) THEN `t2`.`price_per_acre`
        END
    ) AS `min`
FROM
    `listings` AS `t1`
    JOIN `listings` AS `t2` ON `t2`.`area` >= `t1`.`area` * 0.5
    AND `t2`.`area` <= `t1`.`area` * 1.5
    AND `t1`.`county` = `t2`.`county`
    AND `t1`.`state` = `t2`.`state`
WHERE
    `t2`.`status` IN (1, 2)
    AND `t1`.`id` = 1731961;
2
  • Are the monetary values under $10M? (Just noting the max allowed by (12,2))
    – Rick James
    Commented Jun 24, 2021 at 21:42
  • @RickJames Yes, the values are under $10M.
    – Arman P.
    Commented Jun 24, 2021 at 22:14

1 Answer 1

0

Replace this

   KEY `listings_county_state_index` (`county`,`state`)

with these (I don't know which one is better):

INDEX(`county`,`state`, status, area)
INDEX(`county`,`state`, area)

A side note: The "average of averages" is not mathematically 'correct'. But it may be "good enough" for what you are doing. A "correct" calculation would be SUM(price)/SUM(acres). (However, it may not be practical to "correct" the standard deviation.)

A side note: Please move AND t2.id = 1731961 into WHERE, since it is for filtering, not for JOINing. (This won't make any difference to performance, just to clarity.)

4
  • The indices are actually for the client application use-case and not particularly for this query and as I already have them, is there a reason to add another combined index here? Isn't AVG(price_per_acre) mathematically the same as SUM(price)/SUM(acres) not taking into account decimal rounding issues which won't matter here, as I don't mind some error after the floating point? For the side note, yes, I agree, will edit the query in question though the MySQL optimizer does the same. Have you any idea how to solve the problems (calculate also median and/or convert to update query)?
    – Arman P.
    Commented Jun 24, 2021 at 22:19
  • @ArmanP. - MEDIAN() is available in MySQL 8.0 or MariaDB 10.2.
    – Rick James
    Commented Jun 25, 2021 at 6:16
  • @ArmanP. - $11 / 1 ac and $21 / 7 ac: (16/4) is not equal (11+3)/2, not even close enough to blame rounding error.
    – Rick James
    Commented Jun 25, 2021 at 6:22
  • @ArmanP. - The index suggestion was for performance (if that is an issue).
    – Rick James
    Commented Jun 25, 2021 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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