--
-- G-alaxy Queries
--
-- by Alan G. Labouseur
--    alan@labosueur.com
-- 

-- Create View to display the in-degree of all nodes in graph 0.
create view Graph0indegrees
  as
select v.vid,
       v.name, 
       count(e.toVertex) as "In_degree"
from Graphs g, 
     Vertexes v, 
     Edges  e
where g.gid = 0
  and v.gid = g.gid
  and e.gid = g.gid 
  and e.toVertex = v.vid     -- We need this to ensure we have a valid node. Right? (Maybe?)
group by v.vid, v.name
order by v.vid;
-- Test it.
select * 
from Graph0indegrees
;

-- Create View to display the out-degree of all nodes in graph 0.
create view Graph0outdegrees
  as
select v.vid,
       v.name, 
       count(e.fromVertex) as "Out_degree"
from Graphs g, 
     Vertexes  v, 
     Edges  e
where g.gid = 0
  and v.gid = g.gid
  and e.gid = g.gid 
  and e.fromVertex = v.vid   -- We need this to ensure we have a valid node. Right? (Maybe?)
group by v.vid, v.name
order by v.vid;
-- Test it.
select * 
from Graph0outdegrees
;

-- Create a view to display the vertex degree of all out nodes in graph 0. 
-- Vertex degree = in-degree + out-degree. (See http://reference.wolfram.com/mathematica/ref/VertexDegree.html)
create view Graph0vertexdegrees
 as
select gin.vid, 
       gin.name,
       ("In_degree" + "Out_degree") as "Vertex_degree"
from Graph0indegrees  gin, 
     Graph0outdegrees gout
where gin.vid = gout.vid
order by vid;
-- Test it.
select *
from Graph0vertexdegrees
;

-- Compute the distribution of vertex degrees.
select "Vertex_degree", 
       count("Vertex_degree")
from Graph0vertexdegrees
group by "Vertex_degree"
order by "Vertex_degree"
;

-- Count the edges in graph 0
select count(*)
from Edges
where gid = 0
;

-- Sum the vertex degrees. 
-- Check that this sum is twice the number of edges. (See http://reference.wolfram.com/mathematica/ref/VertexDegree.html)
select sum("Vertex_degree")
from Graph0vertexdegrees
;






-- Create a view to compute and display the local clustering coefficients for graph 0.
create or replace view Graph0clusteringcoefficients
 as 
select np.vid, 
       np.neighbors, 
       coalesce(na.actual, 0) as "actual_connections",
       np.possible as "possible_connections", 
       ( cast(coalesce(na.actual, 0) as real) / cast(np.possible as real) ) as "clustering coefficient"
from 
    -- Possible neighbor connections
    (select Vertexes.vid, "neighbors", ( ("neighbors" * ("neighbors" - 1) / 2) ) as "possible"
     from Vertexes , 
       (select v.vid, count(*) as "neighbors"
        from Vertexes v, 
             Edges e
        where v.gid = 0
          and e.gid = 0
          and e.fromVertex = v.vid
        group by v.vid
       ) as nptemp
     where Vertexes.vid = nptemp.vid 
       and "neighbors" > 1      -- Clustering coefficient is not defined for nodes with fewer than one neighbor.
    ) as np
left outer join
    -- Actual neighbor connections
    (select v.vid, 
            count(*) as "actual"
     from Vertexes v,
          Edges e1, 
          Edges e2,
          Edges e3
     where v.gid = 0
       and e1.gid = 0
       and e1.fromVertex = v.vid
       and e2.fromVertex = e1.toVertex
       and e3.fromVertex = e2.toVertex
       and e3.toVertex = v.vid
     group by v.vid
    ) as na
on np.vid = na.vid
order by np.vid
;
-- Test it.
select *
from Graph0clusteringcoefficients;

-- Compute the distribution of clustering coefficients.
select "clustering coefficient", 
       count("clustering coefficient")
from Graph0clusteringcoefficients
group by "clustering coefficient"
order by "clustering coefficient"
;