meanother

#!/root/env/bin/python

import subprocess
from prettytable import PrettyTable


services = [
    "wg-quick@wg0",
    "wg-bot",
    "wg-slave",

meanother

select 
	id 
	, title
	, create_dt
	, score
	, status 
	, comments
	, action
	, valid_from
	, coalesce(

meanother

create trigger banki_hist_insert_trigger after insert on home.dt_banki_responses
for each row execute procedure home.banki_hist_insert();

meanother

CREATE OR REPLACE FUNCTION home.banki_hist_insert() 
RETURNS trigger 
LANGUAGE plpgsql AS
$function$
    BEGIN
      INSERT INTO home.dth_banki_responses
        (id, link, ... action, valid_from)
      VALUES
        (NEW.id, NEW.link, ... 'I', current_timestamp);
      RETURN NEW;

meanother

create table if not exists home.dth_banki_responses (
    id int,
    link varchar(255) not null,
    ...
    action char(1) not null,
    valid_from timestamptz not null
);

meanother

select 
	round(
        avg(date_part('day', bank_answer_date-create_dt)::int), 1
        ) as "Среднее время ответа в днях"
	, round(
        median(date_part('day', bank_answer_date-create_dt)::int), 1
        ) as "Медианное время ответа в днях"
from 
	home.dt_banki_responses 
where 

meanother

select 
	substr(create_dt::varchar,1,7) as month
	, round(avg(coalesce(score, 0)), 1) as "Среднее"
	, trunc(median(coalesce(score, 0))) as "Медиана"
	, count(*) as "Общее кол-во"
	, sum(case when score = 1 then 1 else 0 end) as "Оценка 1"
	, sum(case when score = 2 then 1 else 0 end) as "Оценка 2"
	, sum(case when score = 3 then 1 else 0 end) as "Оценка 3"
	, sum(case when score = 4 then 1 else 0 end) as "Оценка 4"
	, sum(case when score = 5 then 1 else 0 end) as "Оценка 5"

meanother

select 
	status as "Статус"
	, count(*) as "Кол-во"
	, sum(case when score = 1 then 1 else 0 end) as "Оценка 1"
	, sum(case when score = 2 then 1 else 0 end) as "Оценка 2"
	, sum(case when score = 3 then 1 else 0 end) as "Оценка 3"
	, sum(case when score = 4 then 1 else 0 end) as "Оценка 4"
	, sum(case when score = 5 then 1 else 0 end) as "Оценка 5"
	, sum(case when score is null then 1 else 0 end) as "Без оценки"
from 

meanother

select 
	status as "Статус"
	, count(*) as "Кол-во"
from 
	home.dt_banki_responses
where
	date(create_dt) >= '2021-01-01'
group by status
order by 2 desc;

meanother

select 
	substr(create_dt::varchar,1,7) as month
	, round(avg(score), 1) as "Среднее"
	, trunc(median(score)) as "Медиана"
	, count(*) as "Общее кол-во"
	, sum(case when score = 1 then 1 else 0 end) as "Оценка 1"
	, sum(case when score = 2 then 1 else 0 end) as "Оценка 2"
	, sum(case when score = 3 then 1 else 0 end) as "Оценка 3"
	, sum(case when score = 4 then 1 else 0 end) as "Оценка 4"
	, sum(case when score = 5 then 1 else 0 end) as "Оценка 5"