Conditional aggregate on existing query

Question

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.

Answer 1

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.