select
u.id
,u.owner
,(select count(*) from s3billing.bucket_stat b where b.timestamp_addition=t.timestamp_addition AND b.owner=u.owner) as bucket_cnt
,(select sum(b.usage_rgw_main_size_actual) from s3billing.bucket_stat b where b.timestamp_addition=t.timestamp_addition AND b.owner=u.owner) as size_byte
,(select STRING_AGG(i.name ||'['||
case
when ( p.name = 'default-placement') then 'HOT'
else p.name
end || ']', ', ' order by i.name)
from s3billing.bucket_stat b
join s3billing.bucket_info i on (b.bucket_id= i.id)
join s3billing.placement p on (i.placement_id = p.id)
where b.timestamp_addition=t.timestamp_addition AND b.owner=u.owner
) as buckets
,'WZ' || lpad(u.name, 5, '0') as wz
from s3billing.user_info u
cross join (select max(timestamp_addition) as timestamp_addition from s3billing.bucket_stat) t
WITH current_state AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY instance_uid ORDER BY version DESC) as rn
FROM instance_state
)
SELECT s.instance_data->'job'->>'contragentCode' as wz, s.instance_data->'job'->>'userId' AS user_id, e.display_name
,k.contragent_id, k.external_code, k.contragent
FROM current_state s
JOIN instance e on (s.instance_uid=e.instance_uid)
left join specification_item i on (e.specification_item_id=i.specification_item_id)
left join specification p on (i.specification_id=p.specification_id)
left join contract d on (p.contract_id=d.contract_id)
left join contragent k on (d.contragent_id=k.contragent_id)
WHERE rn = 1 AND e.service_id=12
AND NOT cast(s.instance_data->>'isDeleted' as boolean) /*не вдаваясь в другие статусы*/
select upper(trim(k.id_klienta)) as wz, k.__id::text as contragent_uid, k.__name as contragent,
c.__name as responsible, c.email as responsible_email, c.__status_status as responsible_status
from elma.companies k
LEFT OUTER JOIN elma.comp_users_ext c on (k.responsible=c.__id)
where k."__deletedAt" IS NULL
select
s.id
s.owner
s.bucket_cnt
s.size_byte
s.buckets
c.wz
c.contragent
c.contragent_uid
c.responsible
c.responsible_email
u.user_id
u.wz as wzs
u.external_code
u.contragent as cmdb_contragent
u.display_name
from qS3 s
left outer join qContragent c on (s.wz=c.wz)
full outer join qS3User u on (s.owner=u.user_id)
where 1=1
order by
select count(*) as cnt
from qS3 s
left outer join qContragent c on (s.wz=c.wz)
full outer join qS3User u on (s.owner=u.user_id)
Учетные записи S3 - дефектовка
#gridFooter#