qSpecification (from CRM) #getTickCount() - request.startTickCount# select max(capacity_resource.timestamp) as dt_load from ngcloud_ru.capacity_resource #getTickCount() - request.startTickCount# select max(capacity_storage.timestamp) as dt_load from ngcloud_ru.capacity_storage #getTickCount() - request.startTickCount# select max(timestamp_addition) as dt_load from s3billing.bucket_stat #getTickCount() - request.startTickCount# select max(time) as dt_load from s3billing.usage_bucket_by_user #getTickCount() - request.startTickCount# select s.catalogName as svc, s.component, s.serviceCode as service_code, s.fullCode as code, s.measureShort as unit , s.priceWVat as price, (s.discount + s.serviceDiscount - s.discount*s.serviceDiscount/100) as discount ,s.serviceStart, s.serviceEnd, s.serviceHash, s.serviceCustomName, f.free_tier from qSpecification s, qFreeTier f WHERE s.fullCode=f.code AND s.serviceType = 3 UNION select s.catalogName as svc, s.component, s.serviceCode as service_code, s.fullCode as code, s.measureShort as unit , s.priceWVat as price, (s.discount + s.serviceDiscount - s.discount*s.serviceDiscount/100) as discount ,s.serviceStart, s.serviceEnd, s.serviceHash, s.serviceCustomName, 0 from qSpecification s WHERE s.fullCode NOT IN () AND s.serviceType = 3 qSpec (Left Outer Join with Free Tier) #getTickCount() - request.startTickCount# select 'WZ'||to_char(tenant.wzcode,'FM00000') as wz, vdc.name as vdc_name, case WHEN vdc.name LIKE '%-v1cl1%' THEN 'iaas.ngc.i29' WHEN vdc.name LIKE '%-v1cl2%' THEN 'iaas.ngc.i31' WHEN vdc.name LIKE '%-v1cl4%' THEN 'iaas.ngc.a28' WHEN vdc.name LIKE '%-v1cl5%' THEN 'iaas.ngc.a45' WHEN vdc.name LIKE '%-v1cl6%' THEN 'iaas.ngc.i28' WHEN vdc.name LIKE '%-v1cl7%' THEN 'iaas.ngc.a40' -- *** смапить все кластера ELSE '['||vdc.name||']' end as code, sum(cpu_used/cpu_speed)/4. as core_h, sum(mem_used)/1024/4. as gb_h, capacity_resource.timestamp::date as dt, vdc.name as dimension_1, null as dimension_2 from ngcloud_ru.capacity_resource join ngcloud_ru.vdc on capacity_resource.vdc_id=vdc.id join ngcloud_ru.tenant on tenant.id=vdc.tenant_id where tenant.wzcode = AND capacity_resource.timestamp >= AND capacity_resource.timestamp < group by tenant.wzcode, vdc.name, capacity_resource.timestamp::date order by tenant.wzcode, vdc.name, capacity_resource.timestamp::date; qComputing #getTickCount() - request.startTickCount# select 'WZ'||to_char(tenant.wzcode,'FM00000') as wz, vdc.name as vdc_name, case WHEN vdc.name LIKE '%-v1cl1%' THEN 'iaas.ngc.i29' WHEN vdc.name LIKE '%-v1cl2%' THEN 'iaas.ngc.i31' WHEN vdc.name LIKE '%-v1cl4%' THEN 'iaas.ngc.a28' WHEN vdc.name LIKE '%-v1cl5%' THEN 'iaas.ngc.a45' WHEN vdc.name LIKE '%-v1cl6%' THEN 'iaas.ngc.i28' WHEN vdc.name LIKE '%-v1cl7%' THEN 'iaas.ngc.a40' ELSE '['||vdc.name||']' END || CASE WHEN storage_profile_types.name ILIKE '%FASTSSD%' THEN '.fstssd-m' WHEN storage_profile_types.name ILIKE '%SSD%' THEN '.ssd-m' WHEN storage_profile_types.name ILIKE '%SAS%' THEN '.sas-m' WHEN storage_profile_types.name ILIKE '%SATA%' THEN '.sata-m' ELSE '.['|| storage_profile_types.name ||']' END as code, storage_profile_types.name, -- *** это не тип, а профайл sum("limit")/1024./4./720 as gb_m_limit, sum(used)/1024./4./720 as gb_m_used, capacity_storage.timestamp::date as dt, vdc.name as dimension_1, storage_profile_types.name as dimension_2 from ngcloud_ru.capacity_storage join ngcloud_ru.vdc on capacity_storage.vdc_id=vdc.id join ngcloud_ru.tenant on tenant.id=vdc.tenant_id join ngcloud_ru.storage_profile_types on capacity_storage.storage_profile_types_id=storage_profile_types.id where tenant.wzcode = --AND 1=0 AND capacity_storage.timestamp >= AND capacity_storage.timestamp < group by tenant.wzcode, vdc.name, storage_profile_types.name, capacity_storage.timestamp::date order by tenant.wzcode, vdc.name, storage_profile_types.name, capacity_storage.timestamp::date ; qStorage #getTickCount() - request.startTickCount# select /* *** ниже дублирование кода агрегации - round ceil etc, обратить внимание при правке */ /* *** переделать на avg - сначала агрегировать по бакетам*/ (sum(bucket_stat.usage_rgw_main_size_actual)/720) as vol_B ,ceil(sum(bucket_stat.usage_rgw_main_size_actual)/720/1024/1024/1024) as vol_GB ,case when bucket_stat.placement_id = 1 then 'paas.s3.ceph' when bucket_stat.placement_id = 2 then 'paas.s3.cphc' else '['|| TO_CHAR(bucket_stat.placement_id,'FM9') ||']' end as code --,bucket_stat.placement_id ,'WZ'||lpad(bucket_stat.owner, 5, '0') as wz -- *** здесь не число, а строка, поэтому другая формула обратной сборки WZ ,bucket_stat.timestamp_addition::date as dt ,bucket_info.name || ' - ' || case when bucket_stat.placement_id = 1 then 'paas.s3.ceph' when bucket_stat.placement_id = 2 then 'paas.s3.cphc' else '['|| TO_CHAR(bucket_stat.placement_id,'FM9') ||']' end as dimension_1 ,null as dimension_2 from s3billing.bucket_stat join s3billing.placement ON s3billing.placement.id=bucket_stat.placement_id join s3billing.bucket_info ON bucket_info.id=bucket_stat.bucket_id where bucket_stat.placement_id in (1,2) AND bucket_stat.owner = AND bucket_stat.timestamp_addition >= AND bucket_stat.timestamp_addition < group by bucket_stat.owner, bucket_stat.placement_id, bucket_info.name, bucket_stat.timestamp_addition::date order by bucket_stat.owner, bucket_stat.placement_id, bucket_info.name, bucket_stat.timestamp_addition::date; qS3Vol #getTickCount() - request.startTickCount# select sum( CASE WHEN (category.name LIKE 'get%' OR category.name LIKE 'head%' OR category.name LIKE 'options%' ) THEN ops ELSE 0 END ) as get ,ceil(sum( CASE WHEN (category.name LIKE 'get%' OR category.name LIKE 'head%' OR category.name LIKE 'options%' ) THEN ops ELSE 0 END )/10000) as get_10k /*,greatest(ceil(sum( CASE WHEN (category.name LIKE 'get%' OR category.name LIKE 'head%' OR category.name LIKE 'options%' ) THEN ops ELSE 0 END )/10000) - CASE WHEN bucket_info.placement_id = 1 THEN 10 ELSE 0 END, 0 ) as get_10k_free_tier_subtracted*/ ,sum( CASE WHEN (category.name LIKE 'put%' OR category.name LIKE 'post%' OR category.name LIKE 'patch%' OR category.name LIKE 'list%' ) THEN ops ELSE 0 END ) as put ,ceil(sum( CASE WHEN (category.name LIKE 'put%' OR category.name LIKE 'post%' OR category.name LIKE 'patch%' OR category.name LIKE 'list%' ) THEN ops ELSE 0 END )/10000) as put_10k /*,greatest(ceil(sum( CASE WHEN (category.name LIKE 'put%' OR category.name LIKE 'post%' OR category.name LIKE 'patch%' OR category.name LIKE 'list%' ) THEN ops ELSE 0 END )/10000) - CASE WHEN bucket_info.placement_id = 1 THEN 1 ELSE 0 END, 0 ) as put_10k_free_tier_subtracted*/ ,(sum(bytes_sent)) as bytes_sent ,ceil(sum(bytes_sent)/1024/1024/1024) as bytes_sent_GB /*,greatest(ceil(sum(bytes_sent)/1024/1024/1024) - CASE WHEN bucket_info.placement_id = 1 THEN 100 ELSE 0 END, 0 ) as bytes_sent_GB_free_tier_subtracted */ ,case when bucket_info.placement_id = 1 then 'paas.s3.ceph' when bucket_info.placement_id = 2 then 'paas.s3.cphc' else '['|| TO_CHAR(bucket_info.placement_id,'FM9') ||']' end as code --*** зачем делать там ключ bigint ,bucket_info.placement_id --category.name, bucket_info.name ,'WZ'||lpad(user_info.name, 5, '0') as wz ,usage_bucket_by_user.time::date as dt ,bucket_info.name || ' - ' || case when bucket_info.placement_id = 1 then 'paas.s3.ceph' when bucket_info.placement_id = 2 then 'paas.s3.cphc' else '['|| TO_CHAR(bucket_info.placement_id,'FM9') ||']' end as dimension_1 ,null as dimension_2 from s3billing.usage_bucket_by_user join s3billing.user_info on usage_bucket_by_user.user_id=user_info.id join s3billing.category on usage_bucket_by_user.category_id=category.id join s3billing.bucket_info on usage_bucket_by_user.bucketid=bucket_info.id join s3billing.placement on bucket_info.placement_id=placement.id where 1=1 AND user_info.name = AND usage_bucket_by_user.time >= AND usage_bucket_by_user.time < group by user_info.name, bucket_info.placement_id, bucket_info.name, usage_bucket_by_user.time::date order by user_info.name, bucket_info.placement_id, bucket_info.name, usage_bucket_by_user.time::date; qS3OpsTrf #getTickCount() - request.startTickCount# select dt, wz, code + '.vcpu-m' as code, core_h as raw_metric, core_h as metric, dimension_1, 'vdc' as dimension_1_name, dimension_2, NULL as dimension_2_name, 'ядро-ч' as unit from qComputing union all select dt, wz, code + '.ram-m' as code, gb_h as raw_metric, gb_h as metric, dimension_1, 'vdc' as dimension_1_name, dimension_2, NULL as dimension_2_name, 'ГБайт-ч' as unit from qComputing union all select dt, wz, code, gb_m_used as raw_metric, gb_m_used as metric, dimension_1, 'vdc' as dimension_1_name, dimension_2, 'storage policy' as dimension_2_name, 'ГБайт-м' as unit from qStorage union all select dt, wz, code + '.vol-m', vol_b as raw_metric, vol_gb as metric, dimension_1, 'bucket' as dimension_1_name, dimension_2, NULL as dimension_2_name, 'ГБайт-м' as unit from qS3Vol union all select dt, wz, code + '.get-m', get as raw_metric, get_10k as metric, dimension_1, 'bucket' as dimension_1_name, dimension_2, NULL as dimension_2_name, '10тыс.шт.' as unit from qS3OpsTrf union all select dt, wz, code + '.put-m', put as raw_metric, put_10k as metric, dimension_1, 'bucket' as dimension_1_name, dimension_2, NULL as dimension_2_name, '10тыс.шт.' as unit from qS3OpsTrf union all select dt, wz, code + '.trf-m', bytes_sent as raw_metric, bytes_sent_gb as metric, dimension_1, 'bucket' as dimension_1_name, dimension_2, NULL as dimension_2_name, 'ГБайт' as unit from qS3OpsTrf order by dt, code, dimension_1, dimension_2 qUnifiedMetric #getTickCount() - request.startTickCount# SELECT s.code as s_code m.code as m_code s.service_code as service_code s.svc s.component m.unit s.price*(100-s.discount)/100 as discounted_price s.price s.discount m.raw_metric m.metric s.serviceStart s.serviceEnd s.serviceHash s.serviceCustomName m.dt s.free_tier m.dimension_1 m.dimension_1_name m.dimension_2 m.dimension_2_name FROM qSpec as s, qUnifiedMetric as m WHERE s.code=m.code AND s.serviceStart <= m.dt AND (m.dt <= s.serviceEnd OR s.serviceEnd IS NULL) AND m.wz= UNION ALL SELECT NULL, m.code, NULL, NULL, NULL, NULL, 0, NULL, NULL, m.raw_metric, m.metric, NULL, NULL, NULL, NULL, m.dt, 0, m.dimension_1, m.dimension_1_name, m.dimension_2, m.dimension_2_name FROM qUnifiedMetric as m WHERE m.code NOT IN () UNION ALL SELECT s.code, NULL, s.service_code, s.svc, s.component, s.unit, s.price*(100-s.discount)/100 as discounted_price, s.price, s.discount, NULL, NULL, s.serviceStart, s.serviceEnd, s.serviceHash, s.serviceCustomName, NULL, 0, NULL, NULL, NULL, NULL FROM qSpec as s WHERE s.code NOT IN () ORDER by dt, s_code, m_code, dimension_1, dimension_2 qChargeDaily #getTickCount() - request.startTickCount# SELECT s_code m_code s_code as code service_code svc serviceCustomName component unit discounted_price sum(raw_metric) as raw_metric sum(metric) as metric serviceStart serviceEnd min(dt) as dt_billed_from max(dt) as dt_billed_to serviceHash free_tier dimension_1 dimension_1_name dimension_2 dimension_2_name year(dt) as year month(dt) as month from qChargeDaily group by s_code, m_code, svc, service_code, serviceCustomName, component, unit, discounted_price, serviceStart, ServiceEnd, serviceHash, free_tier, year(dt), month(dt), dimension_1_name, dimension_1, dimension_2_name, dimension_2 order by year(dt), month(dt), m_code, code, serviceStart, dimension_1, dimension_2 qChargeMonthly #getTickCount() - request.startTickCount# #getTickCount() - request.startTickCount#