57 lines
1.6 KiB
SQL
57 lines
1.6 KiB
SQL
|
|
select upper(trim(vm.client_id)) as wz
|
|
,item.key as gpu_model
|
|
,sum(item.value::int*p.value)/12. as gpu_h -- 5 minute sampling
|
|
,avg(item.value::int*p.value)*744 as gpu_ha -- 5 minute sampling
|
|
from gpu.vm
|
|
JOIN gpu.vm_power_state p on (vm.obj_uuid=p.obj_uuid)
|
|
CROSS JOIN LATERAL jsonb_each(vm.gpu) AS item(key, value)
|
|
where 1=1
|
|
AND p.ts >= '2026-07-01'
|
|
AND p.ts < '2026-07-31'
|
|
group by vm.client_id, item.key
|
|
order by vm.client_id, item.key;
|
|
|
|
--select * from gpu.vm_power_state limit 100;
|
|
--select * from gpu.vm limit 100;
|
|
|
|
select count(*), sum(value) from gpu.vm_power_state p
|
|
where 1=1
|
|
AND p.ts >= '2026-07-01'
|
|
AND p.ts < '2026-07-31'
|
|
group by obj_uuid
|
|
|
|
select item.key as gpu_model,
|
|
case
|
|
WHEN org_vdc LIKE '%-v1cl1%' THEN 'iaas.ngc.i29'
|
|
WHEN org_vdc LIKE '%-v1cl2%' THEN 'iaas.ngc.i31'
|
|
WHEN org_vdc LIKE '%-v1cl4%' THEN 'iaas.ngc.a28'
|
|
WHEN org_vdc LIKE '%-v1cl5%' THEN 'iaas.ngc.a45'
|
|
WHEN org_vdc LIKE '%-v1cl6%' THEN 'iaas.ngc.i28'
|
|
WHEN org_vdc LIKE '%-v1cl7%' THEN 'iaas.ngc.a40'
|
|
--WHEN org_vdc LIKE '%-v2cl2-1c%' THEN '1c'
|
|
else '['||org_vdc||']' end as code
|
|
from gpu.vm
|
|
CROSS JOIN LATERAL jsonb_each(vm.gpu) AS item(key, value)
|
|
group by item.key, code
|
|
ORDER BY 1
|
|
|
|
|
|
select client_id, org, obj_id, org_vdc, item.key as gpu_model, item.value as gpu_cnt
|
|
from gpu.vm
|
|
CROSS JOIN LATERAL jsonb_each(vm.gpu) AS item(key, value)
|
|
ORDER BY client_id, org, obj_id
|
|
|
|
|
|
select
|
|
count(distinct obj)
|
|
sum(p.value)/12. as vm_h -- 5 minute sampling
|
|
from gpu.vm
|
|
JOIN gpu.vm_power_state p on (vm.obj_uuid=p.obj_uuid)
|
|
where 1=1
|
|
AND p.ts >= '2026-08-01'
|
|
AND p.ts < '2026-08-31'
|
|
AND vm.client_id='WZ01516'
|
|
|
|
|