More SQL for calculating statistics.

Refs #1733

diff --git a/pithos/tools/stats-calculator.sql b/pithos/tools/stats-calculator.sql
index 72fbed2..1c715aa 100644 (file)
--- a/pithos/tools/stats-calculator.sql
+++ b/pithos/tools/stats-calculator.sql
@@ -19,17 +19,19 @@ insert into tmp_stats select 2 as "level", n.node, n.parent, t.population, t.byt
 drop table tmp_sums;
 
 # Update accounts
-create table tmp_sums as select parent as "node", sum(population) as "population", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=2 group by parent, cluster;
-insert into tmp_stats select 1 as "level", n.node, n.parent, t.population, t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t, nodes n where n.node=t.node;
+create table tmp_sums as select parent as "node", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=2 group by parent, cluster;
+create table tmp_population as select parent as "node", sum(population) as "population", cluster from tmp_stats where level=2 and final=false group by parent, cluster;
+insert into tmp_stats select 1 as "level", t.node, 0 as "parent", IFNULL(p.population, 0) as "population", t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t left join tmp_population p on p.node=t.node and p.cluster=t.cluster;
 drop table tmp_sums;
-
-# TODO: Fix population...
+drop table tmp_population;
 
 # Update top level
-create table tmp_sums as select parent as "node", sum(population) as "population", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=1 group by parent, cluster;
-insert into tmp_stats select 0 as "level", n.node, n.parent, t.population, t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t, nodes n where n.node=t.node;
+create table tmp_sums as select parent as "node", sum(bytes) as "bytes", max(mtime) as "mtime", cluster from tmp_stats where level=1 group by parent, cluster;
+create table tmp_population as select parent as "node", sum(population) as "population", cluster from tmp_stats where level=1 and final=false group by parent, cluster;
+insert into tmp_stats select 0 as "level", t.node, 0 as "parent", IFNULL(p.population, 0) as "population", t.bytes, t.mtime, t.cluster, true as "final" from tmp_sums t left join tmp_population p on p.node=t.node and p.cluster=t.cluster;
 drop table tmp_sums;
+drop table tmp_population;
 
 # Clean up
 drop table tmp_nodes;
-delete from tmp_stats where final=false;
\ No newline at end of file
+delete from tmp_stats where final=false;