I have 2 Postgres tables with the following structure:
Table "public.tmp"
Column | Type | Collation | Nullable | Default
-------------------+-------------------------+-----------+----------+---------
MY_SL | character varying(50) | | |
Release | character varying(50) | | |
HOSTNAME | character varying(50) | | not null |
UN NO. | character varying(50) | | |
STATUS | character varying(50) | | |
S_DATE | character varying(50) | | not null |
Table "public.mo"
Column | Type | Collation | Nullable | Default
-------------------+-------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('mo_id_seq'::regclass)
HOSTNAME | character varying(50) | | not null |
UN NO. | character varying(50) | | |
STATUS | character varying(50) | | |
S_DATE | character varying(50) | | not null |
compliant_status | character varying(50) | | not null |
C_PERCENT | character varying(50) | | not null |
Lets say I have data in the tmp table as below:
Table: tmp MY_SL | Release | HOSTNAME | UN NO.| STATUS | S_DATE ------------+-------------+-----------+----------------------+------------------ 2 | 1 | RhelTest | 7:1:8 | COMPLIANT | 2020-08-26 12 | 1 | RhelTest | 7:1:9 | COMPLIANT | 2020-08-26 22 | 2 | RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 4 | 1 | RhelTest | 7:2:10 | NC | 2020-08-26 12 | 1 | RhelTest | 7:1:9 | COMPLIANT | 2020-08-26 22 | 2 | RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 12 | 1 | RhelTest | 7:1:9 | NC | 2020-08-26 22 | 2 | RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 11 | 2 | RhelTest | 7:2:11 | NC | 2020-08-26 1 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 23 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 1 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 23 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 1 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 23 | 3 | Demo1 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 333 | 3 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 333 | 3 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 333 | 3 | Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | COMPLIANT | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 432 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 111 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 333 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 321 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 564 | 3 | Demo3 | 7:2:11 | NC | 2020-08-26 958 | 3 | Demo3 | 7:2:11 | COMPLIANT | 2020-08-26
I have a script below that inserts the data from public.tmp to public.mo table based on the criteria that
- if the column STATUS has mix values (COMPLIANT and NC) for a particular HOSTNAME then compliant_status is PARTIAL and
- if all values are COMPLIANT for a particular HOSTNAME then compliant_status is COMPLIANT and
- if all values are NC for a particular HOSTNAME then compliant_status is NON_COMPLIANT
insert into mo ("HOSTNAME","UN NO.","STATUS","S_DATE", compliant_status)
select "HOSTNAME","UN NO.","STATUS","S_DATE",
case
when min_host_status <> max_host_status then 'PARTIAL'
when min_host_status = 'NC' then 'NON_COMPLIANT'
else min_host_status
end
from (
select t.*,
min("STATUS") over(partition by "HOSTNAME") min_host_status,
max("STATUS") over(partition by "HOSTNAME") max_host_status
from tmp_aix t
where "STATUS" != 'NOT_APPLICABLE'
) t;
Now I want to display compliant_status based on the following conditions:
- If the no. of COMPLIANT values in STATUS column for a particular HOSTNAME is greater than 80% then the compliant_status is COMPLIANT.
- If the no. of COMPLIANT values in STATUS column for a particular HOSTNAME is less than 80% then the compliant_status is NON_COMPLIANT.
Iam expecting the final output as below based on the above data:
HOSTNAME: RhelTest, COMPLIANT %: 88.88%, compliant_status: COMPLIANT
HOSTNAME: Demo1 , COMPLIANT %: 0%, compliant_status: NON_COMPLIANT
HOSTNAME: Demo2 , COMPLIANT %: 100%, compliant_status: COMPLIANT
HOSTNAME: Demo3 , COMPLIANT %: 16.67%, compliant_status: NON_COMPLIANT
Final expected table:
Table: public.mo HOSTNAME | UN NO. | STATUS | S_DATE | compliant_status | C_PERCENT ------------+-------------+-----------+----------------------+------------------ RhelTest | 7:1:8 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:1:9 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:2:10 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:1:9 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:1:9 | NC | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:2:1 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 RhelTest | 7:2:11 | COMPLIANT | 2020-08-26 | COMPLIANT | 88.88 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo1 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 0 Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 | COMPLIANT | 100 Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 | COMPLIANT | 100 Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 | COMPLIANT | 100 Demo2 | 7:2:11 | COMPLIANT | 2020-08-26 | COMPLIANT | 100 Demo3 | 7:2:11 | COMPLIANT | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | NC | 2020-08-26 | NON_COMPLIANT | 16.67 Demo3 | 7:2:11 | COMPLIANT | 2020-08-26 | NON_COMPLIANT | 16.67
Advertisement
Answer
If I follow you correctly, you can use window functions:
select m.*,
100 * avg((compliant_status = 'COMPLIANT')::int) over(partition by hostname) c_percent
case when avg((compliant_status = 'COMPLIANT')::int) over(partition by hostname) >= 0.8
then 'COMPLIANT'
else 'NON_COMPLIANT'
end as final_compliant_status
from mo m