2

I have following table structure (I'm using MySQL 8):

CREATE TABLE `listings` (
  `id` bigint unsigned NOT NULL,
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `types` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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,
  `zip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT 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,
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` smallint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `checked_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `listings_state_county_area_index` (`state`,`county`,`area`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The table is populated with the data except following fields local_avg_price_per_acre, local_median_price_per_acre, local_min_price_per_acre and I need to calculate them to do the update query with calculated values. By local here I mean more or less similar listings and similars for this project are those (who are in the same state and county and have +/-50% area of the listing that I'm doing calculation for) For calculating local_avg_price_per_acre I have come up with the following query:

SELECT
    `t1`.`id`,
    (SUM(`t2`.`price`) / SUM(`t2`.`area`)) AS `avg`
FROM
    `listings` AS `t1`
    JOIN (
        SELECT
            `j1`.`id`,
            `j1`.`price`,
            `j1`.`area`,
            `j1`.`price_per_acre`,
            `j1`.`county`,
            `j1`.`state`
        FROM
            `listings` AS `j1`
        WHERE
            `j1`.`status` IN (1, 2)
    ) AS `t2` ON `t2`.`area` BETWEEN `t1`.`area` * 0.5 AND `t1`.`area` * 1.5
    AND `t1`.`county` = `t2`.`county`
    AND `t1`.`state` = `t2`.`state`
WHERE
    `t1`.`status` IN (1, 2)
GROUP BY
    `t1`.`id`
HAVING
    COUNT(`t2`.`id`) > 8
ORDER BY
    `t1`.`id`
LIMIT
    100 OFFSET 0;

and the output for EXPLAIN:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL index PRIMARY,listings_state_county_area PRIMARY 8 NULL 795479 20.00 Using where
1 SIMPLE j1 NULL ref listings_state_county_area listings_state_county_area 2044 cheaplandsearch.t1.state,cheaplandsearch.t1.county 188 2.22 Using index condition; Using where

Now I need to also add MIN calculation but with added condition, that those accounting for min will be from the same set, but their price_per_acre will be in average +/- 2 * standard deviation, so I thought to do something like:

SELECT
    `t1`.`id`,
    (SUM(`t2`.`price`) / SUM(`t2`.`area`)) AS `avg`,
    STDDEV_SAMP(`t2`.`price_per_acre`) AS `stddev`
FROM
    `listings` AS `t1`
    JOIN (
        SELECT
            `j1`.`id`,
            `j1`.`price`,
            `j1`.`area`,
            `j1`.`price_per_acre`,
            `j1`.`county`,
            `j1`.`state`
        FROM
            `listings` AS `j1`
        WHERE
            `j1`.`status` IN (1, 2)
    ) AS `t2` ON `t2`.`area` BETWEEN `t1`.`area` * 0.5 AND `t1`.`area` * 1.5
    AND `t1`.`county` = `t2`.`county`
    AND `t1`.`state` = `t2`.`state`
    JOIN (
        SELECT
            `j2`.`id`,
            `j2`.`price_per_acre`
        FROM
            `listings` AS `j2`
    ) AS `t3` ON `t3`.`id` = `t2`.`id`
    AND `t3`.`price_per_acre` >= (`avg` - 2 * `stddev`)
    AND `t3`.`price_per_acre` <= (`avg` + 2 * `stddev`)
WHERE
    `t1`.`status` IN (1, 2)
GROUP BY
    `t1`.`id`
HAVING
    COUNT(`t2`.`id`) > 8
ORDER BY
    `t1`.`id`
LIMIT
    100 OFFSET 0;

But the added conditions (for price_per_acre) for second join don't work, I get error Unknown column 'avg' in 'on clause'. As I understand avg and stddev are not available by the time of join yet. The only way currently to add conditional MIN I got is (wrapped the original one in subquery, did another join with added conditions):

SELECT
    `o1`.`id`,
    `o2`.`avg`,
    `o2`.`stddev`,
    MIN(`o3`.`price_per_acre`) AS `min`
FROM
    `listings` AS `o1`
    JOIN (
        SELECT
            `t1`.`id`,
            (SUM(`t2`.`price`) / SUM(`t2`.`area`)) AS `avg`,
            STDDEV_SAMP(`t2`.`price_per_acre`) AS `stddev`
        FROM
            `listings` AS `t1` 
            JOIN (
                SELECT
                    `j1`.`id`,
                    `j1`.`price`,
                    `j1`.`area`,
                    `j1`.`price_per_acre`,
                    `j1`.`county`,
                    `j1`.`state`
                FROM
                    `listings` AS `j1`
                WHERE
                    `j1`.`status` IN (1, 2)
            ) 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
            `t1`.`status` IN (1, 2)
        GROUP BY
            `t1`.`id`
        HAVING
            COUNT(`t2`.`id`) > 8
        ORDER BY
            `t1`.`id`
        LIMIT
            100 OFFSET 0
    ) AS `o2` ON `o1`.`id` = `o2`.`id`
    JOIN (
        SELECT
            `j2`.`id`,
            `j2`.`price`,
            `j2`.`area`,
            `j2`.`price_per_acre`,
            `j2`.`county`,
            `j2`.`state`
        FROM
            `listings` AS `j2`
        WHERE
            `j2`.`status` IN (1, 2)
    ) AS `o3` ON `o3`.`area` >= `o1`.`area` * 0.5
    AND `o3`.`area` <= `o1`.`area` * 1.5
    AND `o3`.`county` = `o1`.`county`
    AND `o3`.`state` = `o1`.`state`
    AND `o3`.`price_per_acre` >= (`avg` - 2 * `stddev`)
    AND `o3`.`price_per_acre` <= (`avg` + 2 * `stddev`);

and the output for EXPLAIN:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY derived2 NULL ALL NULL NULL NULL NULL 100 100.00 Using temporary
1 PRIMARY o1 NULL eq_ref PRIMARY,listings_state_county_area PRIMARY 8 o2.id 1 100.00 NULL
1 PRIMARY j2 NULL ref listings_state_county_area listings_state_county_area 2044 cheaplandsearch.o1.state,cheaplandsearch.o1.county 188 0.25 Using index condition; Using where
2 DERIVED t1 NULL index PRIMARY,listings_state_county_area PRIMARY 8 NULL 795479 20.00 Using where
2 DERIVED j1 NULL ref listings_state_county_area listings_state_county_area 2044 cheaplandsearch.t1.state,cheaplandsearch.t1.county 188 2.22 Using index condition; Using where

As you see from EXPLAIN output it already becomes very heavy query with derived temporary table, which I'm not sure is the most optimal way, but it still works more or less ok if it is the only way.

So the questions are:

  1. Is there a way to calculate the minimum with better query than in my 3rd example
  2. How can I add also median calculation there from the same joined table as avg (without additional conditions for minimum) like MEDIAN(t2.price_per_acre) in first query, but obviously mysql doesn't have MEDIAN aggregate function.

P.S. Here is fiddle with demo data if you want work with that.

Improve this question
4
  • @HannahVernon Thanks, I've replaced images with MD tables, though they don't have horizontal scrolling (not sure if it was possible).
    – Arman P.
    Aug 20 '21 at 20:38
  • that's awesome - thanks! And they have scrollbars for me 🙂
    – Hannah Vernon ♦
    Aug 20 '21 at 20:42
  • I don't see any reference to latitude or longitude; is "local" based only on "in the same county"? (Lat/lng would add a lot of complexity.)
    – Rick James
    Sep 3 '21 at 21:30
  • If you are eager for MEDIAN(), switch to MariaDB 10.3 or later.
    – Rick James
    Sep 3 '21 at 21:30
0

Instead of computing avg in the SELECT, compute it in a derived table.

Then move the use of avg outside of a derived table.

Something like

SELECT ...
    FROM ( SELECT ... AS avg
             ... ) AS x
    JOIN ...
    WHERE ... >= (x.avg - 2 * `stddev`)

You may need another level of nested Selects, I don't know.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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