average of averages (postgresql)

[NogDog]

I can run this query to get a set of n average values:

select avg(r.cat_experience_pro) as avg_exp
from review as r
inner join provider_plan as pp on r.provider_plan_id = pp.provider_plan_id
inner join provider as p on pp.provider_id = p.provider_id
group by p.provider_id

However, what I need is the average of those averages. I tried this:

select avg(
  select avg(r.cat_experience_pro) as avg_exp
  from review as r
  inner join provider_plan as pp on r.provider_plan_id = pp.provider_plan_id
  inner join provider as p on pp.provider_id = p.provider_id
  group by p.provider_id
)

That, unfortunately, is not allowed, returning an error that a subquery being used as an expression returned more than one value.

The two immediate work-arounds I see are either to dump the result into a temp table against which I could then run a 2nd query to get the average of averages, or to do the "outer" average on the PHP side. Any of you SQL gurus have an alternative, or a preference for either of those work-arounds?

[NogDog]

I did the following, and it appears to work fine and reasonably quickly; but if you have a better way, feel free to share. 🙂

	/**
	 * Get the average of the average experience rating of each rated provider
	 * @return float
	 */
	public function getAvgExp()
	{
		$tmpName = uniqid('t_');
		$this->dbh->beginTransaction();
		if($this->dbh->query("CREATE TEMP TABLE $tmpName (doc_avg INT) ON COMMIT DROP") == false) {
			$error = $this->dbh->errorInfo();
			throw new FException(500, "create tmp:\n".print_r($error,1));
		}
		if($this->dbh->query("
			INSERT INTO $tmpName (doc_avg) (
				SELECT AVG(r.cat_experience_pro) AS doc_avg
				FROM {$this->dbSchema}.review AS r
				INNER JOIN {$this->dbSchema}.provider_plan AS pp ON r.provider_plan_id = pp.provider_plan_id
				INNER JOIN {$this->dbSchema}.provider AS p ON pp.provider_id = p.provider_id
				WHERE r.status_code <> 'D'
				GROUP BY p.provider_id
			)"
		) == false) {
			$error = $this->dbh->errorInfo();
			throw new FException(500, "populate tmp:\n".print_r($error,1));
		}
		$stmt = $this->dbh->query("SELECT AVG(doc_avg) FROM $tmpName");
		if($stmt == false) {
			$error = $this->dbh->errorInfo();
			throw new FException(500, "prepare failed\n".print_r($error,1));
		}
		$result = $stmt->fetchColumn();
		$this->dbh->commit();
		return $result;
	}

[WyCnet]

The following is mySQL compatible:

select avg(P.avg_exp) from  

(select avg(r.cat_experience_pro) as avg_exp
  from review as r
  inner join provider_plan as pp on r.provider_plan_id = pp.provider_plan_id
  inner join provider as p on pp.provider_id = p.provider_id
  group by p.provider_id) as P

p.s. When a subquery is named it is akin to a create temp table.

[NogDog]

Thanks, WyCnet. I'll give it a shot tomorrow and check the PostgreSQL compatibility. If it works, it certainly seems like it should be somewhat more efficient, and certainly will clean up my code. 🙂

[NogDog]

That worked just fine. My method is now much cleaner (though I needed to add a bit more to the final query):

/**
 * Get the average of the average experience rating of each rated provider
 * @return float
 */
public function getAvgExp()
{
	$sql = "
		SELECT AVG(all_avgs.prov_avg) FROM (
			SELECT AVG(r.cat_experience_pro) AS prov_avg
			FROM {$this->dbSchema}.review AS r
			INNER JOIN {$this->dbSchema}.provider_plan AS pp ON r.provider_plan_id = pp.provider_plan_id
			INNER JOIN {$this->dbSchema}.provider AS p ON pp.provider_id = p.provider_id
			WHERE
				r.status_code <> 'D'
			AND
				(SELECT mod_status FROM {$this->dbSchema}.moderation_log
				WHERE review_id = r.review_id AND mod_status <> 'L'
				ORDER BY mod_timestamp DESC LIMIT 1) IN ('A', 'I', 'P')
			GROUP BY p.provider_id
		) AS all_avgs
	";
	$stmt = $this->dbh->query($sql);
	if($stmt == false) {
		$error = $this->dbh->errorInfo();
		throw new FException(500, "query failed\n".print_r($error,1));
	}
	return $stmt->fetchColumn();
}