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:
- Is there a way to calculate the minimum with better query than in my 3rd example
- How can I add also median calculation there from the same joined table as
avg
(without additional conditions for minimum) likeMEDIAN(t2.price_per_acre)
in first query, but obviously mysql doesn't haveMEDIAN
aggregate function.
P.S. Here is fiddle with demo data if you want work with that.
MEDIAN()
, switch to MariaDB 10.3 or later.