Все знают, как в питоне форматировать текст по шаблону:

import datetime as dt

date = dt.date(2020, 11, 20)
who = "Френк"
count = 42

tmpl = "{:%Y-%m-%d}: {} и его {:d} друга вылетели в Копенгаген"

>>> tmpl.format(date, who, count)
'2020-11-20: Френк и его 42 друга вылетели в Копенгаген'

А благодаря библиотеке parse от Ричарда Джонса, с такой же легкостью можно разбирать текст обратно по переменным:

import parse

tmpl = "{:ti}: {} и его {:d} друга вылетели в Копенгаген"
txt = "2020-11-20: Френк и его 42 друга вылетели в Копенгаген"

>>> date, who, count = parse.parse(tmpl, txt)
>>> date
datetime.datetime(2020, 11, 20, 0, 0)
>>> who
'Френк'
>>> count
42

parse по большей части поддерживает стандартный питонячий мини-язык форматирования, так что новый синтаксис учить не придется.

Внутри работает на регулярках. Ноль зависимостей, питон 2 и 3.

Мало у какого языка такая нажористая стандартная библиотека, как у питона. Но все равно для работы с HTTP люди пользуются сторонним пакетом requests.

А я вот отказался от него в пользу замечательного httpx от Тома Кристи. Синхронный и асинхронный интерфейсы, поддержка wsgi/asgi, плюс все фичи requests — и совместимость с ним!

Можно заменить requests → httpx, и все продолжит работать:

>>> import httpx
>>> r = httpx.get("http://httpbingo.org/json")

>>> r.status_code
200

>>> r.headers["content-type"]
'application/json; encoding=utf-8'

>>> r.json()["slideshow"]["title"]
'Sample Slide Show'

Питон 3.6+

Бывает, есть множество объектов с разными свойствами, и хочется идентифицировать группы похожих:

• объединить людей с одинаковым ФИО и адресами-телефонами в клиентской базе;
• сегментировать покупателей магазина по товарам, которые они приобрели;
• определить персоны в зависимости от сценариев, по которым люди взаимодействуют с сервисом.

Давайте посмотрим, как решить эту задачу на «голом» SQL.

Постановка задачи

Рассмотрим на конкретном примере. Есть таблица attributes с атрибутами пользователей:

┌─────────┬─────────┐
│ user_id │  attr   │
├─────────┼─────────┤
│ 1       │ alpha   │
│ 1       │ beta    │
│ 2       │ beta    │
│ 2       │ gamma   │
│ 3       │ delta   │
│ 3       │ epsilon │
│ 4       │ delta   │
│ 4       │ zeta    │
│ 5       │ alpha   │
│ 5       │ zeta    │
│ 6       │ iota    │
│ 7       │ iota    │
│ 7       │ kappa   │
│ 8       │ kappa   │
│ 8       │ lambda  │
└─────────┴─────────┘

Хотим объединить пользователей в группы. При этом действуют правила:

• Если пользователи A и B обладают общим свойством → они входят в одну группу.
• Если A и B обладают общим свойством P1, B и C обладают общим свойством P2 → A, B, C входят в одну группу.
• Идентификатором группы считается минимальный идентификатор из входящих в нее пользователей.

Для каждой группы хотим еще определить список атрибутов ее пользователей.

В итоге должно получиться две группы:

┌──────────┬─────────┬─────────────────────────────────────┐
│ group_id │ user_id │                attrs                │
├──────────┼─────────┼─────────────────────────────────────┤
│ 1        │ 1       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 2       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 3       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 4       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 5       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 6        │ 6       │ iota,kappa,lambda                   │
│ 6        │ 7       │ iota,kappa,lambda                   │
│ 6        │ 8       │ iota,kappa,lambda                   │
└──────────┴─────────┴─────────────────────────────────────┘

Как решить задачу на чистом SQL? Использовать процедурные расширения вроде pl/sql и pl/pgSQL — нельзя.

0. Какие бывают группы

Сначала договоримся о терминах.

Пользователи A и B напрямую похожи, если у них есть общее свойство P. В нашем примере пользователи 1 и 5 похожи напрямую:

┌─────────┬─────────┐
│ user_id │  attr   │
├─────────┼─────────┤
│ 1       │ alpha   │
│ 5       │ alpha   │
└─────────┴─────────┘

Пользователи A и C транзитивно похожи, если у них нет общих свойств, но существует пользователь B такой, что у A и B есть общее свойство P1, а у B и C — общее свойство P2. В нашем примере пользователи 3 и 5 похожи транзитивно, через пользователя 4:

┌─────────┬─────────┐
│ user_id │  attr   │
├─────────┼─────────┤
│ 3       │ delta   │
│ 4       │ delta   │
│ 4       │ zeta    │
│ 5       │ zeta    │
└─────────┴─────────┘

Вообще, когда слышишь задачу «найти группы» — первая мысль должна быть «сортировка». Например, найти группы напрямую похожих пользователей очень просто — достаточно отсортировать или сгруппировать по значению атрибута:

select * from attributes order by attr;

┌─────────┬─────────┐
│ user_id │  attr   │
├─────────┼─────────┤
│ 1       │ alpha   │
│ 5       │ alpha   │
│ 1       │ beta    │
│ 2       │ beta    │
│ 3       │ delta   │
│ 4       │ delta   │
│ 3       │ epsilon │
│ 2       │ gamma   │
│ 6       │ iota    │
│ 7       │ iota    │
│ 7       │ kappa   │
│ 8       │ kappa   │
│ 8       │ lambda  │
│ 4       │ zeta    │
│ 5       │ zeta    │
└─────────┴─────────┘

select
  attr,
  group_concat(user_id) as users
from attributes
group by attr
order by attr;

Здесь я использую функцию group_concat(), чтобы объединить значения в строку. Так она называется в MySQL и SQLite, но в других СУБД тоже есть аналогичные. Например, string_agg() в PostgreSQL и listagg() в Oracle.

┌─────────┬───────┐
│  attr   │ users │
├─────────┼───────┤
│ alpha   │ 1,5   │
│ beta    │ 1,2   │
│ delta   │ 3,4   │
│ epsilon │ 3     │
│ gamma   │ 2     │
│ iota    │ 6,7   │
│ kappa   │ 7,8   │
│ lambda  │ 8     │
│ zeta    │ 4,5   │
└─────────┴───────┘

Проблема в том, что по условиям задачи мы должны учитывать и транзитивную похожесть. Обычной сортировкой уже не обойдешься.

1. Находим прямые пары

Если подход с сортировкой не работает, следующая мысль должна быть «пары». Если мы найдем все пары похожих пользователей — сможем построить по ним и группы.

Для начала найдем пары с прямой похожестью:

create view pairs as 
select
  min(a1.user_id, a2.user_id) as uid1,
  max(a1.user_id, a2.user_id) as uid2
from attributes as a1
  join attributes as a2
    on a1.attr = a2.attr and a1.user_id <> a2.user_id
;

┌──────┬──────┐
│ uid1 │ uid2 │
├──────┼──────┤
│ 1    │ 2    │
│ 1    │ 2    │
│ 1    │ 5    │
│ 1    │ 5    │
│ 3    │ 4    │
│ 3    │ 4    │
│ 4    │ 5    │
│ 4    │ 5    │
│ 6    │ 7    │
│ 6    │ 7    │
│ 7    │ 8    │
│ 7    │ 8    │
└──────┴──────┘

Здесь левый элемент пары — всегда меньший из двух идентификаторов, а правый — больший. Правило «левый меньший, правый больший» не обязательно, но поможет нам не плодить лишние пары на следующем шаге.

2. Находим транзитивные пары

Теперь добавим к прямым парам транзитивные. Это самый сложный шаг алгоритма: дело в том, что между транзитивно похожими пользователями X и Y может быть не одно промежуточное звено, а сколько угодно:

X → U1 → U2 → ... → Un → Y

Есть только один вид запроса, которой умеет размотать цепочку произвольной длины — WITH RECURSIVE. Подробно расскажу о нем в отдельной заметке, но общий принцип такой:

1. Однократно выполнить запрос Q0.
2. Снова и снова выполнять запрос Qi, пока он возвращает результаты.
3. Объединить результаты всех шагов и вернуть итоговый.

В нашем случае алгоритм такой:

1. Выбрать все прямые пары.
2. Для очередной пары пользователей (x, y):
   2.1. Найти пользователей (u1, ... un), напрямую похожих на y.
   2.2. Добавить к результатам пары (x, u1), (x, u2), ... (x, un).
3. Выполнить шаг 2 для следующей пары.

На SQL:

create view allpairs as
with recursive cte(uid1, uid2) as (
  -- Пары пользователей (uid1, uid2) с одинаковыми свойствами
  select uid1, uid2
  from pairs
  
  union
  
  -- Пары, в которых uid2 фигурирует слева
  -- (они транзитивно похожи на uid1)
  select cte.uid1, p.uid2
  from pairs as p
    join cte on cte.uid2 = p.uid1
  
  union
  
  -- Пары, в которых uid2 фигурирует справа
  -- (тоже транзитивно похожи на uid1)
  select cte.uid1, p.uid1
  from pairs as p
    join cte on cte.uid2 = p.uid2
)
select * from cte;

┌──────┬──────┐
│ uid1 │ uid2 │
├──────┼──────┤
│ 1    │ 1    │
│ 1    │ 2    │
│ 1    │ 3    │
│ 1    │ 4    │
│ 1    │ 5    │
│ 3    │ 1    │
│ 3    │ 2    │
│ 3    │ 3    │
│ 3    │ 4    │
│ 3    │ 5    │
│ 4    │ 1    │
│ 4    │ 2    │
│ 4    │ 3    │
│ 4    │ 4    │
│ 4    │ 5    │
│ 6    │ 6    │
│ 6    │ 7    │
│ 6    │ 8    │
│ 7    │ 6    │
│ 7    │ 7    │
│ 7    │ 8    │
└──────┴──────┘

Теперь для каждого пользователя (столбец uid1) мы знаем всех похожих на него пользователей (столбец uid2) — как напрямую, так и транзитивно. Осталось из пар сформировать группы.

3. Объединяем пары в группы

Пары есть, можно объединить их в группы. Проблема только в том, что в allpairs группы дублируются:

┌──────┬──────┐
│ uid1 │ uid2 │
├──────┼──────┤
│ 1    │ 1    │
│ 1    │ 2    │
│ 1    │ 3    │
│ 1    │ 4    │
│ 1    │ 5    │
│ 3    │ 1    │
│ 3    │ 2    │
│ 3    │ 3    │
│ 3    │ 4    │
│ 3    │ 5    │
└──────┴──────┘

Формально здесь есть группа для uid1 = 1 и группа для uid1 = 3 — но по сути это одна и та же группа. Дубли нас не интересуют, так что выберем только уникальные группы:

create view groups as
select
  min(uid1) as group_id,
  uid2 as user_id
from allpairs
group by uid2;

Группировка по uid2 гарантирует, что каждый пользователь входит только в одну группу. А выбор min(uid1) в качестве идентификатора группы — требование из постановки задачи.

┌──────────┬─────────┐
│ group_id │ user_id │
├──────────┼─────────┤
│ 1        │ 1       │
│ 1        │ 2       │
│ 1        │ 3       │
│ 1        │ 4       │
│ 1        │ 5       │
│ 6        │ 6       │
│ 6        │ 7       │
│ 6        │ 8       │
└──────────┴─────────┘

В целом, задача решена. Осталось только собрать список атрибутов для каждой группы.

4. Объединяем атрибуты групп

Тут все просто:

1. Для каждой группы выбираем ее пользователей.
2. Для каждого пользователя выбираем его атрибуты.
3. Объединяем атрибуты в одну строку, отсеивая дубли.

create view group_attributes as
select
  g.group_id,
  group_concat(distinct a.attr) as attrs
from groups as g
  join attributes as a on a.user_id = g.user_id
group by g.group_id;

┌──────────┬─────────────────────────────────────┐
│ group_id │                attrs                │
├──────────┼─────────────────────────────────────┤
│ 1        │ alpha,beta,gamma,delta,epsilon,zeta │
│ 6        │ iota,kappa,lambda                   │
└──────────┴─────────────────────────────────────┘

5. Собираем все вместе

Для каждого пользователя выбираем номер его группы и список атрибутов, которые в нее входят:

select
  g.group_id,
  g.user_id,
  ga.attrs
from groups as g
  join group_attributes as ga on ga.group_id = g.group_id
order by g.group_id, g.user_id;

┌──────────┬─────────┬─────────────────────────────────────┐
│ group_id │ user_id │                attrs                │
├──────────┼─────────┼─────────────────────────────────────┤
│ 1        │ 1       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 2       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 3       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 4       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 1        │ 5       │ alpha,beta,gamma,delta,epsilon,zeta │
│ 6        │ 6       │ iota,kappa,lambda                   │
│ 6        │ 7       │ iota,kappa,lambda                   │
│ 6        │ 8       │ iota,kappa,lambda                   │
└──────────┴─────────┴─────────────────────────────────────┘

Тадам! Задача решена.

Если угодно, можно обойтись без промежуточных вьюх.

with recursive

-- Уникальные пары пользователей с одинаковыми свойствами.
-- Если есть пара (1,2), то пары (2,1) уже не будет.
pairs as (
  select distinct
    -- Левый элемент пары - меньший из двух идентификаторов,
    min(a1.user_id, a2.user_id) as uid1,
    -- а правый - больший.
    -- Правило «левый меньший, правый больший» нужно,
    -- чтобы собрать транзитивные пары в allpairs ниже.
    max(a1.user_id, a2.user_id) as uid2
  from attributes as a1
    join attributes as a2
      on a1.attr = a2.attr and a1.user_id <> a2.user_id
),

-- Все пары похожих пользователей (в том числе транзитивно похожих).
-- Общий принцип: для каждой пары пользователей с одинаковыми свойствами
-- выбираем все пары, которые пересекаются с ними. Если выбрать все такие пары,
-- то для каждого пользователя получим полный набор пар, в которые он входит.
allpairs(uid1, uid2) as (
  -- Пары пользователей (uid1, uid2) с одинаковыми свойствами
  select uid1, uid2
  from pairs
  
  union
  
  -- Пары, в которых uid2 фигурирует слева
  -- (они транзитивно похожи на uid1)
  select ap.uid1, p.uid2
  from pairs as p
    join allpairs as ap on ap.uid2 = p.uid1
  
  union
  
  -- Пары, в которых uid2 фигурирует справа
  -- (тоже транзитивно похожи на uid1)
  select ap.uid1, p.uid1
  from pairs as p
    join allpairs as ap on ap.uid2 = p.uid2
),

-- Для каждого пользователя собираем группу похожих на него пользователей.
-- Каждый пользователь может входить только в одну группу:
-- если есть группа 1:[1,2,3], то группы 3:[1,2,3] уже не будет.
groups as (
  select
    -- id группы = минимальный user_id входящих в нее пользователей
    min(uid1) as group_id,
    -- пользователи, входящие в группу
    uid2 as user_id
  from allpairs
  -- группируем по пользователю, чтобы не было разных групп,
  -- в которые входит один и тот же пользователь
  group by uid2
),

-- Атрибуты пользователей для каждой группы (одной строкой)
group_attributes as (
  select
    g.group_id,
    group_concat(distinct a.attr) as attrs
  from groups as g
    join attributes as a on a.user_id = g.user_id
  group by g.group_id
)

-- Итог: для каждого пользователя номер его группы
-- и список атрибутов пользователей, входящих в группу
select
  g.group_id,
  g.user_id,
  ga.attrs
from groups as g
  join group_attributes as ga on ga.group_id = g.group_id
order by g.group_id, g.user_id;

Итого

Вот общий алгоритм решения задач типа «объединить элементы в группы»:

1. Попытаться сформировать группы через обычную сортировку.
2. Если не получается — идентифицировать все пары элементов.
3. Объединить пары в группы.

Далеко не всегда такие задачи стоит решать на SQL. Часто процедурный код будет понятнее и эффективнее.

Есть куча способов отправлять уведомления — от проверенного SMTP и удобного Telegram до смс и специальных приложений для мобилок вроде Pushover.

Обычно для этого используют 3rd-party библиотеку соответствующего провайдера. Но есть более удобный способ — пакет notifiers от Ора Карми. Он предоставляет простой универсальный интерфейс для отправки сообщений через любой сервис.

Например, через телеграм:

import notifiers

token = "bot_token"
chat_id = 1234
tg = notifiers.get_notifier("telegram")
tg.notify(message="Привет!", token=token, chat_id=chat_id)

Поддерживается аж 16 провайдеров, а интерфейс один — метод .notify(). И никаких дополнительных 3rd-party библиотек. Удобно!

Питон 3.6+

Хороший код — понятный и непрожорливый до ресурсов. Давайте поговорим об этом.

Время на понимание

Главный критерий хорошего кода — это время T, которое требуется не-автору, чтобы разобраться в коде. Причем разобраться не на уровне «вроде понятно», а достаточно хорошо, чтобы внести изменения и ничего не сломать.

Чем меньше T, тем лучше код.

Допустим, Нина и Витя реализовали одну и ту же фичу, а вы хотите ее доработать. Если разберетесь в коде Нины за 10 минут, а в коде Вити за 30 минут — код Нины лучше. Неважно, насколько у Вити чистая архитектура, функциональный подход, современный фреймворк и всякое такое.

T-метрика для начинающего и опытного программиста отличается. Поэтому имеет смысл ориентироваться на средний уровень коллег, которые будут работать с кодом. Если у вас в коллективе люди трудятся 10+ лет, и каждый написал по компилятору — даже очень сложный код будет иметь низкое T. Если у вас огромная текучка, а нанимают вчерашних студентов — код должен быть совершенно дубовым, чтобы T не зашкаливало.

Напрямую T не очень-то померяешь, поэтому часто отслеживают вторичные метрики, которые влияют на T:

• соответствие код-стайлу (black для питона),
• «запашки» в коде (pylint, flake8),
• цикломатическую сложность (mccabe),
• зависимости между модулями (import-linter).

Плюс код-ревью.

Количество ресурсов

Второй критерий хорошего кода — количество ресурсов R, которое он потребляет (времени, процессора, памяти, диска). Чем меньше R, тем лучше код.

Если Нина и Витя реализовали фичу с одинаковым T, но код Нины работает за O(n), а код Вити за O(n²) (при одинаковом потреблении прочих ресурсов) — код Нины лучше.

Насчет ситуации «пожертвовать понятностью ради скорости». Для каждой задачи есть порог потребления ресурсов R0, в который должно уложиться решение. Если R < R0, не надо ухудшать T ради дальнейшего сокращения R.

Если некритичный сервис обрабатывает запрос за 50мс — не надо переписывать его с питона на C, чтобы сократить время до 5мс. И так достаточно быстро.

Иногда, если ресурсы ограничены, или исходные данные большие — не получается достичь R < R0 без ухудшения T. Тогда действительно приходится жертвовать понятностью. Но:

1. Это последний вариант, когда все прочие уже испробованы.
2. Участки кода, где T↑ ради R↓, должны быть хорошо изолированы.
3. Таких участков должно быть мало.
4. Они должны быть подробно документированы.

Итого

Мнемоника хорошего кода:

T↓ R<R0

Оптимизируйте T, следите за R. Коллеги скажут вам спасибо.

В стандартной библиотеке есть класс Counter. Он отлично подходит, чтобы считать количество объектов разных типов. Но что делать, если объектов миллиарды, и счетчик просто не помещается в оперативную память?

Поможет bounter — это счетчик, который предоставляет схожий интерфейс, но внутри построен на вероятностных структурах данных. За счет этого он занимает в 30–250 раз меньше памяти, но может (слегка) привирать.

from bounter import bounter
counts = bounter(size_mb=128)
counts.update(["a", "b", "c", "a", "b"])

>>> counts.total()
5

>>> counts["a"]
2

Ноль зависимостей, питон 3.3+

Однажды мы уже смотрели, как множества помогают быстро проверить, входит ли элемент в коллекцию.

Конечно, это не единственная возможность. Множества в питоне идеально подходят, чтобы поэлементно сравнивать коллекции.

Допустим, мы ведем учет посетителей:

jan = ["Питер", "Клер", "Френк"]
feb = ["Френк", "Зоя", "Дуглас"]
mar = ["Клер", "Питер", "Зоя"]

И хотим узнать, кто приходил в январе и феврале. Нет ни малейшего желания писать вложенный цикл с перебором jan и feb. Намного приятнее (и быстрее) использовать множества.

jan = {"Питер", "Клер", "Френк"}
feb = {"Френк", "Зоя", "Дуглас"}
mar = {"Клер", "Питер", "Зоя"}

Были в январе и феврале:

>>> jan & feb
{'Френк'}

В январе или марте:

>>> jan | mar
{'Питер', 'Клер', 'Зоя', 'Френк'}

В феврале, но не в марте:

>>> feb - mar
{'Френк', 'Дуглас'}

В январе или феврале, но не в оба месяца:

>>> jan ^ feb
{'Питер', 'Клер', 'Зоя', 'Дуглас'}

Вернулись ли зимние посетители в марте?

>>> (jan & feb) <= mar
False

Все эти операции выполняются за линейное время O(n) вместо квадратичного O(n²), как было бы на списках.

Кроме обычных множеств бывают замороженные (их нельзя менять):

>>> visitors = frozenset ().union (jan, feb, mar)
>>> visitors
frozenset ({'Питер', 'Клер', 'Зоя', 'Френк', 'Дуглас'})

Множество можно слепить из любого iterable-типа. Например, из строки:

>>> frozenset ('abcde')
frozenset ({'b', 'd', 'e', 'c', 'a'})

Или даже из диапазона:

>>> set (range (1, 10))
{1, 2, 3, 4, 5, 6, 7, 8, 9}

Ключи словаря тоже работают как множество:

>>> stats = { "Френк": 99, "Клер": 50 }
>>> stats.keys() & jan
{'Клер', 'Френк'}

В общем, полезная штука.

В стандартной библотеке есть встроенный планировщик задач (а чего вообще в ней нет?). Подробно расскажу в другой раз, но в целом он, скажем так, не слишком юзер-френдли.

Поэтому Дэн Бэйдер сделал schedule — «планировщик для людей». Смотрите, какой милый:

import schedule
import time

def job():
  print("I'm working...")

schedule.every().hour.do(job)
schedule.every(5).to(10).minutes.do(job)
schedule.every().day.at("10:30").do(job)

while True:
  schedule.run_pending()
  time.sleep(1)

Ноль зависимостей, чистый и великолепно документированный код, примеры на все случаи жизни.

Допустим, у нас есть список важных гостей. Он в легком беспорядке:

data = [
  "4 - Дуглас",
  "2 - Клер",
  "11 - Зоя",
  "1 - Френк",
  "31 - Питер",
]

Отсортируем:

>>> sorted(data)
['1 - Френк', '11 - Зоя', '2 - Клер', '31 - Питер', '4 - Дуглас']

Порядка не прибавилось — sorted() не знает, что здесь нужна числовая сортировка, а не алфавитная. Поможем ему:

def _key(src):
    parts = src.partition(" - ")
    return int(parts[0])

>>> sorted(data, key=_key)
['1 - Френк', '2 - Клер', '4 - Дуглас', '11 - Зоя', '31 - Питер']

Так хорошо! А чтобы добавить семантичности и не таскать везде дополнительный параметр key, создадим собственную функцию на основе sorted():

def natsorted(iterable, reverse=False):
    return sorted(iterable, key=_key, reverse=reverse)

>>> natsorted(data)
['1 - Френк', '2 - Клер', '4 - Дуглас', '11 - Зоя', '31 - Питер']

Есть и более лакончиный способ сделать это — через functools.partial():

import functools
natsorted = functools.partial(sorted, key=_key)

partial() создает новую функцию на основе существующей. При этом можно «зафиксировать» один или несколько параметров (мы зафиксировали key), разрешив менять остальные (iterable и reverse в нашем случае).

Таким образом, partial() помогает создавать узкоспециализированные функции на базе более универсальных.

Это #пакетик — еженедельная рубрика о полезных и интересных пакетах на Python.

Мой сегодняшний выбор — пакет Сета Мортона natsort, который сортирует строки привычным для человека образом.

Допустим, у нас есть список важных гостей. Он в легком беспорядке:

data = [
  "4 - Дуглас",
  "2 - Клер",
  "11 - Зоя",
  "1 - Френк",
  "31 - Питер",
]

Отсортируем:

>>> sorted(data)
['1 - Френк', '11 - Зоя', '2 - Клер', '31 - Питер', '4 - Дуглас']

Порядка не прибавилось ツ А вот как будет с natsort:

>>> import natsort
>>> natsort.natsorted(data)
['1 - Френк', '2 - Клер', '4 - Дуглас', '11 - Зоя', '31 - Питер']

Другое дело!

Это пятая, заключительная статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

Скользящие агрегаты — это те же сумма и среднее. Только рассчитывают их не по всем элементам набора, а более хитрым способом.

Разберемся на примерах. Здесь возьмем другую табличку — expenses. Она показывает доходы и расходы одного из сотрудников (пусть это будет Марина) за 9 месяцев 2020 года:

┌──────┬───────┬────────┬─────────┐
│ year │ month │ income │ expense │
├──────┼───────┼────────┼─────────┤
│ 2020 │ 1     │ 94     │ 82      │
│ 2020 │ 2     │ 94     │ 75      │
│ 2020 │ 3     │ 94     │ 104     │
│ 2020 │ 4     │ 100    │ 94      │
│ 2020 │ 5     │ 100    │ 99      │
│ 2020 │ 6     │ 100    │ 105     │
│ 2020 │ 7     │ 100    │ 95      │
│ 2020 │ 8     │ 100    │ 110     │
│ 2020 │ 9     │ 104    │ 104     │
└──────┴───────┴────────┴─────────┘

• скользящее среднее,
• фрейм,
• нарастающий итог,
• функции.

Все запросы можно повторять в песочнице.

Скользящее среднее по расходам

Судя по данным, доходы у Марины растут: 94К ₽ в январе → 104К ₽ в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца. Например:

• скользящее среднее за февраль = (январь + февраль + март) / 3;
• за март = (февраль + март + апрель) / 3;
• за апрель = (март + апрель + май) / 3;
• и так далее.

Рассчитаем скользящее среднее по всем месяцам:

Было

Стало

Столбец roll_avg показывает скользящее среднее по расходам за три месяца (текущий, предыдущий и следующий). Теперь хорошо видно, что расходы стабильно растут.

Как перейти от «было» к «стало»?

Отсортируем таблицу по месяцам:

select
  year, month, expense,
  null as roll_avg
from expenses
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │          │
│ 2020 │ 2     │ 75      │          │
│ 2020 │ 3     │ 104     │          │
│ 2020 │ 4     │ 94      │          │
│ 2020 │ 5     │ 99      │          │
│ 2020 │ 6     │ 105     │          │
│ 2020 │ 7     │ 95      │          │
│ 2020 │ 8     │ 110     │          │
│ 2020 │ 9     │ 104     │          │
└──────┴───────┴─────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать среднее по предыдущему, текущему и следующему значению из столбца expense:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

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

1. Окно состоит из одной или нескольких секций (в нашем случае секция одна — все записи expenses).
2. Внутри секции записи упорядочены по конкретным столбцам (order by year, month).
3. У каждой записи свой фрейм.

Фрейм на каждом шаге охватывает три записи — текущую, предыдущую и следующую:

Вот как записать это на SQL:

window w as (
  order by year, month
  rows between 1 preceding and 1 following
)

С order by все понятно, а вторая строчка — это как раз определение фрейма: «выбрать строки от 1 предыдущей до 1 следующей». На следующем шаге разберемся с фреймами подробно, а пока закончим с нашим запросом.

Считаем среднее по расходам — это функция avg():

avg(expense) over w

Добавим округление и сведем все вместе:

select
  year, month, expense,
  round(avg(expense) over w) as roll_avg
from expenses
window w as (
  order by year, month
  rows between 1 preceding and 1 following
)
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │ 79.0     │
│ 2020 │ 2     │ 75      │ 87.0     │
│ 2020 │ 3     │ 104     │ 91.0     │
│ 2020 │ 4     │ 94      │ 99.0     │
│ 2020 │ 5     │ 99      │ 99.0     │
│ 2020 │ 6     │ 105     │ 100.0    │
│ 2020 │ 7     │ 95      │ 103.0    │
│ 2020 │ 8     │ 110     │ 103.0    │
│ 2020 │ 9     │ 104     │ 107.0    │
└──────┴───────┴─────────┴──────────┘

Скользящее среднее по расходам готово!

Фрейм

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «граница секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней — обрезается:

Вообще, у фреймов намного больше возможностей, но мы ограничимся этими. Подробности разберем на курсе.

Прибыль нарастающим итогом

Благодаря скользящему среднему, мы выяснили, что в expenses растут и доходы, и расходы. А как они соотносятся друг с другом? Хочется понять, находится ли человек «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.

Причем важно понимать не на конец года, а на каждый месяц. Потому что если по итогам года у Марины все ОК, а в июне ушла в минус — это потенциальная проблема (у компаний такую ситуацию называют «кассовым разрывом»).

Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):

• кумулятивный доход за январь = январь;
• за февраль = январь + февраль;
• за март = январь + февраль + март;
• за апрель = январь + февраль + март + апрель;
• и так далее.

t_income показывает доходы нарастающим итогом, t_expense — расходы, а t_profit — прибыль.

t_profit = t_income - t_expense

Как рассчитать кумулятивные показатели?

Отсортируем таблицу по месяцам:

select
  year, month, income, expense,
  null as t_income,
  null as t_expense,
  null as t_profit
from expenses
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │          │           │          │
│ 2020 │ 2     │ 94     │ 75      │          │           │          │
│ 2020 │ 3     │ 94     │ 104     │          │           │          │
│ 2020 │ 4     │ 100    │ 94      │          │           │          │
│ 2020 │ 5     │ 100    │ 99      │          │           │          │
│ 2020 │ 6     │ 100    │ 105     │          │           │          │
│ 2020 │ 7     │ 100    │ 95      │          │           │          │
│ 2020 │ 8     │ 100    │ 110     │          │           │          │
│ 2020 │ 9     │ 104    │ 104     │          │           │          │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать суммарные показатели от начала таблицы до текущей строки:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно на каждом шаге охватывает строки от начала таблицы до текущей записи. Мы уже значем, как сформулировать подходящий фрейм:

window w as (
  order by year, month
  rows between unbounded preceding and current row
)

Считаем сумму по доходам и расходам — это функция sum():

sum(income) over w as t_income,
sum(expense) over w as t_expense,

Прибыль считаем как разницу между доходами и расходами:

(sum(income) over w) - (sum(expense) over w) as t_profit

Все вместе:

select
  year, month, income, expense,
  sum(income) over w as t_income,
  sum(expense) over w as t_expense,
  (sum(income) over w) - (sum(expense) over w) as t_profit
from expenses
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │ 94       │ 82        │ 12       │
│ 2020 │ 2     │ 94     │ 75      │ 188      │ 157       │ 31       │
│ 2020 │ 3     │ 94     │ 104     │ 282      │ 261       │ 21       │
│ 2020 │ 4     │ 100    │ 94      │ 382      │ 355       │ 27       │
│ 2020 │ 5     │ 100    │ 99      │ 482      │ 454       │ 28       │
│ 2020 │ 6     │ 100    │ 105     │ 582      │ 559       │ 23       │
│ 2020 │ 7     │ 100    │ 95      │ 682      │ 654       │ 28       │
│ 2020 │ 8     │ 100    │ 110     │ 782      │ 764       │ 18       │
│ 2020 │ 9     │ 104    │ 104     │ 886      │ 868       │ 18       │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

По t_profit видно, что дела у Марины идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.

Функции агрегации

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

• min() и max()
• count(), avg() и sum()

Разница только в наличии фрейма у скользящих агрегатов.

⌘ ⌘ ⌘

Мы рассмотрели четыре класса задач, которые решаются с помощью оконных функций в SQL:

• Ранжирование (всевозможные рейтинги).
• Сравнение со смещением (соседние элементы и границы).
• Агрегация (количество, сумма и среднее).
• Скользящие агрегаты (сумма и среднее в динамике).

Теперь попробуйте применить «окошки» на практике!

Чтобы узнать больше об оконных функциях или потренироваться — записывайтесь на курс 🚀

Если вы давно и хорошо знаете питон, некоторые моменты в нем могут напрягать. Например, необходимость начинать каждый новый проект с создания виртуального окружения и установки пачки тулов вроде black, pylint и pytest. Или отсутствующий рефакторинг и частые ошибки из-за динамической типизации. Или убоговатая асинхронность и костыль в виде GIL.

Если у вас так — возможно, пора освоить второй язык. Логично выбрать вариант без питонячих недостатков: статически типизированный, с готовым тулингом, продуманной асинхронностью и параллелизмом.

Давайте посмотрим, кто из топа StackOverflow подходят на роль второго языка.

JavaScript. Динамический язык, который во всем хуже питона. Единственный плюс — только с ним нативно работают браузеры. Если мечтаете о фронтенде — хороший выбор, если нет — с негодованием отметаем.

Bash/Shell/PowerShell. Моя любимая тройка write-only языков! Легко написать что угодно, уже через неделю невозможно понять, что написал. Нет.

C#. Интересный вариант. Новее питона, отличная стандартная библиотека, статически типизирован, все в порядке с асинхронностью. Начинался как очень простой, но за 20 лет превратился в один из самых фичастых языков. Кросс-платформенный, несмотря на виндовые корни.

PHP. Динамический язык с тяжелым наследием, который в свежих версиях решили превратить в Java образца 2005 года. Я даже не знаю, что может быть хуже.

TypeScript. А это JavaScript, который решили превратить в современную Java. Классическая дырявая абстракция, джаваскрипт протекает из всех щелей. Статически типизирован, куча наворотов. Стандартная библиотека отсутствует. Фронтендеры на него молятся, но у питониста вряд ли вызовет что-то помимо отвращения.

C++. В рейтинге мозговыносящих языков точно занял бы первое место. Старый язык, в который запихнули все что только можно. Гарантирую, писать на C++ вам будет физически больно.

C. Самый старый, но относительно простой язык. Статические типы, очень низкоуровневый, скудная стандартная библиотека. В 1991 году был бы хорошим выбором. Зато быстрый, да.

Go. Интересный вариант. Новый, отличная стандартная библиотека, статически типизирован, классная асинхронность. Одним из основных принципов считает простоту, так что не превратился в фича-монстра, в отличие от C#. Можно делать как низкоуровневые штуки, так и бизнес-приложения.

Kotlin. Интересный вариант. Котлин — это Java, если бы ее изобрели в 2010 году. Статический, много фич. Работает поверх JVM (виртуальная машина джавы), можно использовать любые джава-либы.

И несколько нишевых языков с преданным сообществом:

Rust. Современная альтернатива C++. Отличный вариант для низкоуровневых штук, плохо подходит для остального. Много фич, тяжело освоить. Хорошая стандартная библиотека.

Clojure. Современный LISP. Язык, в котором простота возведена в абсолют — всё есть данные. Акцент на неизменяемых данных, продуманная стандартная библиотека. Работает поверх JVM.

Итого, из топ-10 мне кажутся интересными C#, Go и Kotlin. Лично я выбрал Go, потому что устал от фичастых языков и наслоений абстракций. Хочу простоты.

Если вам тоже интересно попробовать — присоединяйтесь ко мне на бесплатном курсе:

Go на практике

Одиннадцать лет назад, весной 2010 года, Adobe Flash был на пике популярности. Adobe построил вокруг этого поделия аж целую платформу, с прицелом на мобильные устройства и встраиваемое ПО.

Флеш был так успешен, что Microsoft еще в 2007 году выкатила конкурента — аналогичную хреновину под названием Silverlight. Борьба намечалась нешуточная.

Apple, владея стремительно набирающей популярность iOS, не могла остаться в стороне. И анонсировала собственную альтернативу флешу — iSlate. Аналогичный шаг сделал и Google. В следующие 10 лет развернулась кровавая битва флешеподобных плагинов, а обычный веб (HTML и JS) остались совсем не у дел.

А, нет. Вычеркните последний абзац. Ничего этого не было, и вот почему.

29 апреля 2010 года Стив Джобс опубликовал открытое письмо под названием «Thoughts on Flash», в котором разгромил флеш и поддержал HTML5. В течение двух следующих лет Adobe Flash и Microsoft Silverlight были уничтожены. Технически они продолжали существовать еще довольно долго, но и разработчики, и сами производители признали, что это тупик.

HTML5, CSS и JS получили такой мощный пинок, что до сих пор не могут остановиться (вот кому мы обязаны мириадами глючных фич веба и армией js-фреймворков). При всех недостатках, веб действительно оказался лучшим выбором, потому что не принадлежал одному монополисту.

Заботился ли Джобс о процветании Apple, когда писал свое письмо? Конечно. Он честно сказал, что благодаря отмиранию флеша Apple продаст больше устройств. Но он также думал и о разработчиках, и о пользователях. Джобс понимал, что ждет нас всех, если индустрия пойдет по пути конкурирующих плагинов вместо развития общей платформы — веба.

Джобс не был ангелом, это уж точно. Но у него было видение, и эмпатия, и смелость. Не будь этого письма в 2010 году — не было бы веба, каким мы его знаем сегодня.

В 2020 году Apple тихо удалила открытое письмо Джобса с сайта компании. Конечно, зачем эти слова про открытый веб — у нынешнего Apple совсем другие приоритеты. Да и у других техногигантов тоже. Очень жаль.

Thoughts on Flash

Это четвертая статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

Агрегация — это когда мы считаем суммарные или средние показатели. Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.

Мы будем агрегировать данные по сотрудникам из таблички employees:

┌────┬──────────┬────────┬────────────┬────────┐
│ id │   name   │  city  │ department │ salary │
├────┼──────────┼────────┼────────────┼────────┤
│ 11 │ Дарья    │ Самара │ hr         │ 70     │
│ 12 │ Борис    │ Самара │ hr         │ 78     │
│ 21 │ Елена    │ Самара │ it         │ 84     │
│ 22 │ Ксения   │ Москва │ it         │ 90     │
│ 23 │ Леонид   │ Самара │ it         │ 104    │
│ 24 │ Марина   │ Москва │ it         │ 104    │
│ 25 │ Иван     │ Москва │ it         │ 120    │
│ 31 │ Вероника │ Москва │ sales      │ 96     │
│ 32 │ Григорий │ Самара │ sales      │ 96     │
│ 33 │ Анна     │ Москва │ sales      │ 100    │
└────┴──────────┴────────┴────────────┴────────┘

• сумма по секции,
• фильтрация,
• описание окна,
• функции агрегации.

Все запросы можно повторять в песочнице.

Сравнение с фондом оплаты труда

У каждого департамента есть фонд оплаты труда — денежная сумма, которая ежемесячно уходит на выплату зарплат сотрудникам. Посмотрим, какой процент от этого фонда составляет зарплата каждого сотрудника:

Было

Стало

Столбец fund показывает фонд оплаты труда отдела, а perc — долю зарплаты сотрудника от этого фонда. Видно, что в HR и продажах все более-менее ровно, а у айтишников есть заметный разброс зарплат.

Как перейти от «было» к «стало»?

Отсортируем таблицу по департаментам:

select
  name, department, salary,
  null as fund,
  null as perc
from employees
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┬──────┐
│   name   │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья    │ hr         │ 70     │      │      │
│ Борис    │ hr         │ 78     │      │      │
│ Елена    │ it         │ 84     │      │      │
│ Ксения   │ it         │ 90     │      │      │
│ Леонид   │ it         │ 104    │      │      │
│ Марина   │ it         │ 104    │      │      │
│ Иван     │ it         │ 120    │      │      │
│ Вероника │ sales      │ 96     │      │      │
│ Григорий │ sales      │ 96     │      │      │
│ Анна     │ sales      │ 100    │      │      │
└──────────┴────────────┴────────┴──────┴──────┘

Для удобства я добавил сортировку по зарплате и идентификатору. Но, как вы увидите дальше, в данном случае можно обойтись и без них.

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать:

• fund — сумму зарплат по департаменту в целом (она одинакова для всех сотрудников департамента);
• perc — долю зарплаты сотрудника от этой суммы.

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно состоит из секций по департаментам. При этом порядок записей в секции неважен: мы считаем сумму значений salary, а она не зависит от порядка.

window w as (
  partition by department
)

Для расчета fund подойдет обычная функция sum() — ее можно использовать поверх окна. А perc посчитаем как salary / fund:

select
  name, department, salary,
  sum(salary) over w as fund,
  round(salary * 100.0 / sum(salary) over w) as perc
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┬──────┐
│   name   │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья    │ hr         │ 70     │ 148  │ 47.0 │
│ Борис    │ hr         │ 78     │ 148  │ 53.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Елена    │ it         │ 84     │ 502  │ 17.0 │
│ Ксения   │ it         │ 90     │ 502  │ 18.0 │
│ Леонид   │ it         │ 104    │ 502  │ 21.0 │
│ Марина   │ it         │ 104    │ 502  │ 21.0 │
│ Иван     │ it         │ 120    │ 502  │ 24.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Вероника │ sales      │ 96     │ 292  │ 33.0 │
│ Григорий │ sales      │ 96     │ 292  │ 33.0 │
│ Анна     │ sales      │ 100    │ 292  │ 34.0 │
└──────────┴────────────┴────────┴──────┴──────┘

Функция sum() работает без неожиданностей — считает сумму значений по всей секции, которой принадлежит текущая строка.

Фильтрация и порядок выполнения

Вернемся к запросу, который считал фонд оплаты труда по департаменту:

select
  name, department, salary,
  sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┐
│   name   │ department │ salary │ fund │
├──────────┼────────────┼────────┼──────┤
│ Дарья    │ hr         │ 70     │ 148  │
│ Борис    │ hr         │ 78     │ 148  │
│ Елена    │ it         │ 84     │ 502  │
│ Ксения   │ it         │ 90     │ 502  │
│ Леонид   │ it         │ 104    │ 502  │
│ Марина   │ it         │ 104    │ 502  │
│ Иван     │ it         │ 120    │ 502  │
│ Вероника │ sales      │ 96     │ 292  │
│ Григорий │ sales      │ 96     │ 292  │
│ Анна     │ sales      │ 100    │ 292  │
└──────────┴────────────┴────────┴──────┘

Допустим, мы хотим оставить в отчете только самарских сотрудников. Добавим фильтр:

select
  name, salary,
  sum(salary) over w as fund
from employees
where city = 'Самара'
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 188  │
│ Леонид   │ 104    │ 188  │
│ Григорий │ 96     │ 96   │
└──────────┴────────┴──────┘

Фильтр сработал. Вот только значения fund отличаются от ожидаемых:

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 502  │
│ Леонид   │ 104    │ 502  │
│ Григорий │ 96     │ 292  │
└──────────┴────────┴──────┘

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 188  │
│ Леонид   │ 104    │ 188  │
│ Григорий │ 96     │ 96   │
└──────────┴────────┴──────┘

Все дело в порядке выполнения операций. Вот в какой последовательности действует движок, когда выполняет запрос:

1. Взять нужные таблицы (from) и соединить их при необходимости (join).
2. Отфильтровать строки (where).
3. Сгруппировать строки (group by).
4. Отфильтровать результат группировки (having).
5. Взять конкретные столбцы из результата (select).
6. Рассчитать значения оконных функций (function() over window).
7. Отсортировать то, что получилось (order by).

Таким образом, окна отрабатывают предпоследним шагом, уже после фильтрации и группировки результатов. Поэтому в нашем запросе fund отражает не сумму всех зарплат по департаменту, а сумму только по самарским сотрудникам.

Решение — использовать подзапрос с окном и фильтровать его в основном запросе:

with emp as (
  select
    name, city, salary,
    sum(salary) over w as fund
  from employees
  window w as (partition by department)
  order by department, salary, id
)
select name, salary, fund
from emp
where city = 'Самара';

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 502  │
│ Леонид   │ 104    │ 502  │
│ Григорий │ 96     │ 292  │
└──────────┴────────┴──────┘

Описание окна

До сих пор мы описывали окно в блоке window и ссылались на него в выражении over:

select
  name, department, salary,
  count(*) over w as emp_count,
  sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬───────────┬──────┐
│   name   │ department │ salary │ emp_count │ fund │
├──────────┼────────────┼────────┼───────────┼──────┤
│ Дарья    │ hr         │ 70     │ 2         │ 148  │
│ Борис    │ hr         │ 78     │ 2         │ 148  │
│ Елена    │ it         │ 84     │ 5         │ 502  │
│ Ксения   │ it         │ 90     │ 5         │ 502  │
│ Леонид   │ it         │ 104    │ 5         │ 502  │
│ Марина   │ it         │ 104    │ 5         │ 502  │
│ Иван     │ it         │ 120    │ 5         │ 502  │
│ Вероника │ sales      │ 96     │ 3         │ 292  │
│ Григорий │ sales      │ 96     │ 3         │ 292  │
│ Анна     │ sales      │ 100    │ 3         │ 292  │
└──────────┴────────────┴────────┴───────────┴──────┘

Это не единственный способ. SQL разрешает вообще не использовать window и описывать окно прямо внутри over:

select
  name, department, salary,
  count(*) over (partition by department) as emp_count,
  sum(salary) over (partition by department) as fund
from employees
order by department, salary, id;

Мне больше нравится вариант с window — его легче читать, и можно явно переиспользовать окно. Но в документации часто встречается определение окна внутри over, поэтому не удивляйтесь, когда увидите его.

Кстати, определение окна может быть пустым:

select
  name, department, salary,
  count(*) over () as emp_count,
  sum(salary) over () as fund
from employees
order by department, salary, id;

Такое окно включает все строки, так что emp_count покажет общее количество сотрудников, а fund — общий фонд оплаты труда по всем записям employees.

Функции агрегации

⌘ ⌘ ⌘

Мы разобрались, как считать фиксированные агрегаты в окнах. В следующей части займемся скользящими агрегатами!

Чтобы закрепить знания на практике — записывайтесь на курс 🚀

Я вечно забываю синтаксис линуксовых утилит вроде grep, sed и find. Есть команда man, которая показывает документацию по утилите, но для меня она совершенно бесполезна — никогда не получается найти то, что нужно (возможно, это только я такой тупой).

Поэтому был бесконечно счастлив, когда на днях нашел приятную альтернативу от Игоря Чубина — «шпаргалки» с конкретными примерами. Чтобы их включить, достаточно добавить такую функцию в .bashrc:

function cht() { curl "cht.sh/$1"; }

И вызвать шпаргалку по конкретной команде:

$ cht grep
$ cht sed
$ cht tr

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

Подписывайтесь на @nalgeon в твитере, чтобы не пропустить новые заметки 🚀