Statistics
| Branch: | Tag: | Revision:

root / contrib / stats-calculator.sql @ faad3c72

History | View | Annotate | Download (3.1 kB)

1 de793207 Antony Chazapis
# Top level
2 de793207 Antony Chazapis
create temporary table tmp_stats as select 0 as "level", 0 as "node", 0 as "parent", count(serial) as "population", sum(size) as "bytes", max(mtime) as "mtime", cluster, false as "final" from versions group by cluster;
3 de793207 Antony Chazapis
4 de793207 Antony Chazapis
# Account level
5 de793207 Antony Chazapis
insert into tmp_stats select 1 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent=0 and n.node!=0 group by node, cluster;
6 de793207 Antony Chazapis
create temporary table tmp_nodes select distinct node, level from tmp_stats where level=1;
7 de793207 Antony Chazapis
8 de793207 Antony Chazapis
# Container level
9 de793207 Antony Chazapis
insert into tmp_stats select 2 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent in (select node from tmp_nodes where level=1) group by node, cluster;
10 de793207 Antony Chazapis
insert into tmp_nodes select distinct node, level from tmp_stats where level=2;
11 de793207 Antony Chazapis
12 de793207 Antony Chazapis
# Object level
13 de793207 Antony Chazapis
insert into tmp_stats select 3 as "level", n.node, n.parent, count(v.serial) as "population", sum(v.size) as "bytes", max(v.mtime) as "mtime", cluster, false as "final" from versions v, nodes n where n.node=v.node and n.parent in (select node from tmp_nodes where level=2) group by node, cluster;
14 de793207 Antony Chazapis
insert into tmp_nodes select distinct node, level from tmp_stats where level=3;
15 de793207 Antony Chazapis
16 de793207 Antony Chazapis
# Update containers
17 de793207 Antony Chazapis
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=3 group by parent, cluster;
18 de793207 Antony Chazapis
insert into tmp_stats select 2 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;
19 de793207 Antony Chazapis
drop table tmp_sums;
20 de793207 Antony Chazapis
21 de793207 Antony Chazapis
# Update accounts
22 2bdc9dc4 Antony Chazapis
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;
23 2bdc9dc4 Antony Chazapis
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;
24 2bdc9dc4 Antony Chazapis
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;
25 de793207 Antony Chazapis
drop table tmp_sums;
26 2bdc9dc4 Antony Chazapis
drop table tmp_population;
27 de793207 Antony Chazapis
28 de793207 Antony Chazapis
# Update top level
29 2bdc9dc4 Antony Chazapis
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;
30 2bdc9dc4 Antony Chazapis
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;
31 2bdc9dc4 Antony Chazapis
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;
32 de793207 Antony Chazapis
drop table tmp_sums;
33 2bdc9dc4 Antony Chazapis
drop table tmp_population;
34 de793207 Antony Chazapis
35 de793207 Antony Chazapis
# Clean up
36 de793207 Antony Chazapis
drop table tmp_nodes;
37 2bdc9dc4 Antony Chazapis
delete from tmp_stats where final=false;