create table street_freqs as select coalesce(street_name,'') AS street_name, count(*) AS cnt from addresses group by coalesce(street_name,'');
create index on street_freqs (street_name);

create table city_freqs as select coalesce(city_name,'') AS city_name, count(*) as cnt from addresses group by coalesce(city_name,'');
create index on city_freqs (city_name);

analyze street_freqs;
analyze city_freqs;

with total as (
    select count(*) as cnt from addresses
)
select
  city_name,
  street_name,
  cnt,
  city_cnt,
  street_cnt,
  freq,
  base_freq,
  abs(freq - base_freq) / base_freq as error,
  greatest(freq/base_freq, base_freq/freq) AS mre
from (
  select
    a.city_name,
    a.street_name,
    a.cnt AS cnt,
    o.cnt AS city_cnt,
    u.cnt AS street_cnt,
    (1.0 * a.cnt) / (select cnt from total) AS freq, -- actual frequency
    (u.cnt * 1.0) / (select cnt from total) * (o.cnt * 1.0) / (select cnt from total) AS base_freq -- base frequency
  from (
      select
        coalesce(city_name,'') as city_name, 
        coalesce(street_name,'') as street_name,
        count(*) as cnt
      from addresses group by 1, 2 order by 3 desc
  ) a left join street_freqs u on (a.street_name = u.street_name)
      left join city_freqs o on (a.city_name = o.city_name)
) foo order by cnt desc;
