SQL code to compute line-neighbor probabilities for bayesian estimater

line-neighbors.sql

begin;
-- compute all probabilities for line-neighbor voltage pairs
with voltage_pair (low_v, high_v, cnt) as (
    select least(a.voltage, b.voltage), greatest(a.voltage, b.voltage), count(*)
      from topology_nodes n
      join line_structure a on a.line_id = any(n.line_id)
      join line_structure b on b.line_id = any(n.line_id)
     where a.line_id < b.line_id
       and a.voltage is not null and b.voltage is not null
     group by least(a.voltage, b.voltage), greatest(a.voltage, b.voltage)
), line_neighbor_pair (l_v, n_v, cnt) as (
    select low_v, high_v, cnt from voltage_pair
     union
    select high_v, low_v, cnt from voltage_pair
), total_pair (total_cnt) as (
    select sum(cnt) from line_neighbor_pair
), neighbor_total (n_v, neighbor_cnt) as (
    select n_v, sum(cnt)
      from line_neighbor_pair
     group by n_v
), line_total (l_v, line_cnt) as (
    select l_v, sum(cnt)
      from line_neighbor_pair
     group by l_v
), neighbor_p (n_v, p) as (
    select n_v, (neighbor_cnt*1.0)/total_cnt
      from neighbor_total, total_pair
), line_p (l_v, p) as (
    select l_v, (line_cnt*1.0)/total_cnt
      from line_total, total_pair
), neighbor_p_if_line (l_v, n_v, p) as (
    select pair.l_v, n_v, (cnt*1.0)/line_cnt
      from line_neighbor_pair pair, line_total total
     where total.l_v = pair.l_v
)
select pr.l_v, pr.n_v, l.p as "p(l)", n.p as "p(n)", nl.p as "p(n|l)"
  from line_neighbor_pair as pr
  join line_p as l on l.l_v = pr.l_v
  join neighbor_p as n on n.n_v = pr.n_v
  join neighbor_p_if_line as nl
    on nl.l_v = pr.l_v and nl.n_v = pr.n_v
 order by pr.l_v, pr.n_v;
commit;