Changeset 7157
- Timestamp:
- Apr 20, 2018, 1:05:26 PM (3 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php
r7156 r7157 398 398 } 399 399 // memory 400 $sql_2 G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem < 2048000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";400 $sql_2g = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem < 2048000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year desc,month desc,rel asc"; 401 401 $cache_key='extended_memory_2G'; 402 402 $data = $cache->get($cache_key); … … 422 422 } 423 423 } 424 $sql_4G = "select year( t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 2048000 and mem < 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";424 $sql_4G = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem > 2048000 and mem < 4096000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year desc,month desc,rel asc"; 425 425 $cache_key='extended_memory_4G'; 426 426 $data = $cache->get($cache_key); … … 446 446 } 447 447 } 448 $sql_8G = "select year( t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";448 $sql_8G = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem > 4096000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year desc,month desc,rel asc"; 449 449 $cache_key='extended_memory_8G'; 450 450 $data = $cache->get($cache_key); … … 471 471 } 472 472 // cpu 473 $sql_mono = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu = 1 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";473 $sql_mono= "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu = 1 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel"; 474 474 $cache_key='extended_memory_cpu_mono'; 475 475 $data = $cache->get($cache_key); … … 485 485 while($row=$result->fetch_array(MYSQLI_ASSOC)){ 486 486 $date=$row['year'].'_'.$row['month']; 487 $tmp[$date][$row['rel']]=intval($row[' count']);487 $tmp[$date][$row['rel']]=intval($row['total']); 488 488 } 489 489 foreach ($tmp as $date){ … … 495 495 } 496 496 } 497 //$sql_dualquad = "select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where ncpu > 1 and ncpu < 5 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;"; 498 $sql_dualquad = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 1 and ncpu < 5 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;"; 497 $sql_dualquad = "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu > 1 and ncpu < 5 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel"; 499 498 $cache_key='extended_memory_cpu_dualquad'; 500 499 $data = $cache->get($cache_key); … … 510 509 while($row=$result->fetch_array(MYSQLI_ASSOC)){ 511 510 $date=$row['year'].'_'.$row['month']; 512 $tmp[$date][$row['rel']]=intval($row[' count']);511 $tmp[$date][$row['rel']]=intval($row['total']); 513 512 } 514 513 foreach ($tmp as $date){ … … 520 519 } 521 520 } 522 $sql_more = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 4 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";521 $sql_more = "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu > 4 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel"; 523 522 $cache_key='extended_memory_cpu_other'; 524 523 $data = $cache->get($cache_key); … … 534 533 while($row=$result->fetch_array(MYSQLI_ASSOC)){ 535 534 $date=$row['year'].'_'.$row['month']; 536 $tmp[$date][$row['rel']]=intval($row[' count']);535 $tmp[$date][$row['rel']]=intval($row['total']); 537 536 } 538 537 foreach ($tmp as $date){ … … 545 544 } 546 545 // CLIENT UPDATES 547 $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month having count(Client_uid) >= 1 ) t group by year,month order by year desc,month desc";548 546 $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated,Releases_name as rel,Flavours_name as fla from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month,Releases_name,Flavours_name from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month,Releases_name,Flavours_name having count(Client_uid) >= 1 ) t group by year,month,Releases_name,Flavours_name order by year desc,month desc"; 549 547 … … 624 622 625 623 626 //$sql= "select ltsp,count(*) as total from Client_Versions group by ltsp";627 624 $sql="select compact.year as year,compact.month as month,if(compact.type='NONE',null,compact.type) as type,ifnull(results.total,0) as total from (select * from 628 625 ( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as type union all select 0 union all select 1) types) compact left join (select month(date) as month,year(date) as year,ifnull(ltsp,'NONE') as type,count(ifnull(ltsp,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),ltsp order by year(date) desc ,month(date) desc, ltsp desc ) results on compact.month = results.month and compact.year = results.year and compact.type = results.type"; 629 //$count_ltsp=array('null'=>0,'true'=>0,'false'=>0);630 626 $count_ltsp=array(); 631 627 $cache_key='extended_5'; … … 649 645 } 650 646 $cache_key='extended_6'; 651 //$sql = "select t1.mode,count(t2.uuid) as total from (select 'THIN' as mode union all select 'SEMI' UNION ALL select 'FAT') t1 left join (select uuid,mode from Client_Versions where Flavours_name = 'client') t2 on t1.mode = t2.mode group by t1.mode";652 647 $sql= "select compact.year as year,compact.month as month,if(compact.mode='NONE',null,compact.mode) as mode,ifnull(results.total,0) as total from (select * from ( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as mode union all select 'THIN' union all select 'SEMI' union all select 'FAT') types) compact left join (select month(date) as month,year(date) as year,ifnull(mode,'NONE') as mode,count(ifnull(mode,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),mode order by year(date) desc ,month(date) desc, mode desc ) results on compact.month = results.month and compact.year = results.year and compact.mode = results.mode"; 653 648 $data= $cache->get($cache_key); 654 649 $stime=microtime(true); 655 //$count_mode=array('thin'=>0,'semi'=>0,'fat'=>0);656 650 $count_mode=array(); 657 651 if ($data != false){
Note: See TracChangeset
for help on using the changeset viewer.