我有2个具有以下结构的Postgres表:
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 |
可以说我在tmp表中有如下数据:
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
我下面有一个脚本,该脚本根据以下条件将数据从public.tmp插入public.mo表:
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;
现在,我想根据以下条件显示Compliance_status:
我期望基于上述数据的最终输出如下:
主机名:RhelTest,合规%:88.88%,兼容状态:合规
主机名:Demo1,符合%:0%,兼容状态:NON_COMPLIANT
主机名:Demo2,合规%:100%,兼容状态:合规
主机名:Demo3,合规%:16.67%,兼容状态:NON_COMPLIANT
最终预期表:
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
如果我正确地跟随您,则可以使用窗口功能:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句