SQL

Язык SQL — прекрасная вещь, пожалуй, одна из лучших, что мы имеем в айти. Научиться ему значит повысить свою квалификацию на голову. Бекендеров я бы вообще оценивал по одному критерию — насколько хорошо они работают с базой.

SQL выручал меня не только по работе. У нас в ЖК стоят шлагбаумы, и однажды я запросил логи, чтобы посмотреть, кто и когда и открывает. Мне скинули несколько страшных CSV. Разбирать их Экселе было адом, и я загнал в Постгрес и построил отчеты по всем мыслимым критериям.

Импорт:

COPY logs(time, account, type, ident, status, barrier)
FROM '/Users/ivan/work/barriers/65_Ш6.csv'
DELIMITER ';' CSV HEADER;

Статусы:

select distinct status from logs

| status
|---------------------------------------
| Добавлен
| Отказ (пульт)
| Отказ (нет в базе или ошибка кнопка)
| Открыто (карта в базе)
| Допуск (пульт)
...

Типы доступа:

select distinct type from logs

| type
|--------------
| Событие
| Карта, метка
| Звонок
| Удаление
...

Общее число открываний в разрезе помещений:

select account, count(ident) as count from logs
where account <> 'Нет в базе'
and time > '2022-05-01 00:00:00'
and status in ('Допуск (телефон)', 'Допуск (пульт)')
and type in ('PP', 'Звонок')
group by account
order by count desc
limit 50

Данные по конкретной квартире по дням:

select date_trunc('day', time) as day, count(ident) from logs
where account = 'ООО "Рога и копыта"'
and time > '2022-05-01 00:00:00'
and status in ('Допуск (телефон)', 'Допуск (пульт)')
and type in ('PP', 'Звонок')
group by day
order by day desc
limit 50

Число телефонов и пультов на помещение:

select account, count(distinct ident) as amount from logs
where account <> 'Нет в базе'
and time > '2022-05-01 00:00:00'
and status in ('Допуск (телефон)', 'Допуск (пульт)')
group by account
order by amount desc
limit 100

Среднее число открываний в день:

select account, count(ident) / count(distinct date_trunc('day', time)) as per_day
from logs
where account <> 'Нет в базе'
and time > '2022-05-01 00:00:00'
and status in ('Допуск (телефон)', 'Допуск (пульт)')
and type in ('PP', 'Звонок')
group by account
order by per_day desc
limit 100

И еще штук десять подобных запросов. В итоге нашли несколько аномалий, с нарушителями был разговор.

Из недавнего: понадобилось считать рабочие часы по неделям. Что в Экселе, что Гугле это оказался адский ад, потому что тип time не переполняется. То есть если сложить 7 + 7 + 7 + 7 + 7 часов (пять рабочих дней), получим 11 часов, потому что 24 уйдут в перекрут. Просидел час, думая, как это решить, и так и не решил.

Но выручил Постгрес! Его тип time тоже не поддерживает переполнение, но есть тип interval, который прекрасно складывается сам с собой. Поэтому создаем таблицу с двумя колонками: дата и интервал:

create table work (date date, hours interval);

Импорт:

\copy work from /Users/ivan/Downloads/foobar.csv
  with (format csv);

Удаляем пустые интервалы:

delete from work where hours is null;

Всего часов:

select sum(hours) from work;

Отчет по неделям:

select date_trunc('week', date) as week, sum(hours)
from work
group by week
order by week;

И все это — не выходя из консоли psql. Представьте, какой гемор был бы построить это в Экселе, Гугле или даже на языке программирования вроде Питона. Там с одним только парсингом дат и времени наешься, а тут все из коробки.

Можно сбросить все отчеты в CSV одной командой, чтобы пересохранить в Excel и отправить кому-то по работе.

Словом, одно из немногих утешений сегодня — это SQL/Postgres. Надежный как скала, полезный, простой и востребованный.