пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅ. пїЅпїЅпїЅпїЅ
По вашему запросу ничего не найдено :(
Убедитесь, что запрос написан правильно, или посмотрите другие
наши статьи:
В этой статье мы рассмотрим, как составить выражение GROUP BY, как будет выглядеть ваш запрос и как с его помощью выполнять группировку.
Настройка базы данных
Прежде чем писать запросы, нам нужно настроить базу данных.
В этих примерах мы будем использовать PostgreSQL, но приведенные здесь запросы и концепции легко применимы к любой другой современной системе баз данных (например, MySQL, SQL Server и т. д.).
Для работы с базой данных PostgreSQL мы можем использовать psql - интерактивную программу командной строки PostgreSQL. Если у вас есть другой клиент базы данных, с которым вам нравится работать, это тоже хорошо.
Для начала давайте создадим нашу базу данных. Если PostgreSQL уже установлен, мы можем выполнить команду createdb в терминале, чтобы создать новую базу данных. Я назвал свою базу данных fcc:
$ createdb fcc
Далее запустим интерактивную консоль с помощью команды psql и подключимся к базе данных, которую мы только что создали, используя \c :
$ psql
psql (11.5)
Type "help" for help.
john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#
Примечание: Я очистил вывод psql в этих примерах, чтобы его было легче читать, поэтому не беспокойтесь, если вывод, показанный здесь, не совсем такой, как в вашем терминале.
Я рекомендую вам последовать за этими примерами и выполнить эти запросы самостоятельно. Вы узнаете и запомните гораздо больше, если будете работать с этими примерами, а не просто читать их.
Настройка данных (создание продаж)
В наших примерах мы будем использовать таблицу, в которой хранятся данные о продажах различных товаров в разных магазинах.
Мы назовем эту таблицу sales, и она будет представлять собой простое представление продаж в магазине: название места, название продукта, цена и время, когда он был продан.
Если бы мы создавали эту таблицу в реальном приложении, мы бы установили внешние ключи к другим таблицам (например, местоположениям или продуктам). Но для иллюстрации концепции GROUP BY мы будем использовать простые столбцы TEXT.
Давайте создадим таблицу и вставим в нее данные о продажах:
CREATE TABLE sales(
location TEXT,
product TEXT,
price DECIMAL,
sold_at TIMESTAMP
);
INSERT INTO sales(location, product, price, sold_at) VALUES
('HQ', 'Coffee', 2, NOW()),
('HQ', 'Coffee', 2, NOW() - INTERVAL '1 hour'),
('Downtown', 'Bagel', 3, NOW() - INTERVAL '2 hour'),
('Downtown', 'Coffee', 2, NOW() - INTERVAL '1 day'),
('HQ', 'Bagel', 2, NOW() - INTERVAL '2 day'),
('1st Street', 'Bagel', 3, NOW() - INTERVAL '2 day' - INTERVAL '1 hour'),
('1st Street', 'Coffee', 2, NOW() - INTERVAL '3 day'),
('HQ', 'Bagel', 3, NOW() - INTERVAL '3 day' - INTERVAL '1 hour');
У нас есть три места расположения: HQ, Downtown и 1st Street.
У нас есть два продукта, Coffee и Bagel, и мы вставляем эти продажи с разными значениями sold_at, чтобы представить товары, проданные в разные дни и время.
Есть продажи сегодня, есть вчера, а есть позавчера.
Как работает GROUP BY?
Чтобы проиллюстрировать, как работает предложение GROUP BY, давайте сначала рассмотрим пример.
Представьте, что у нас есть комната, полная людей, родившихся в разных странах.
Если бы мы хотели найти средний рост людей в комнате по странам, мы бы сначала попросили этих людей разделиться на группы по странам рождения.
После того как они будут разделены на группы, мы сможем вычислить средний рост в этой группе.
Вот как работает предложение GROUP BY. Сначала мы определяем, как мы хотим сгруппировать строки, а затем мы можем выполнить вычисления или агрегирование для групп.
Множественные группы
Мы можем разделить данные на столько групп или подгрупп, сколько захотим.
Например, попросив людей разделиться на группы по странам рождения, мы можем попросить каждую из этих групп разделиться на группы по цвету глаз.
Таким образом, мы получим группы людей, основанные на сочетании их страны рождения и цвета глаз.
Теперь мы можем найти средний рост в каждой из этих небольших групп, и получим более конкретный результат: средний рост по стране и цвету глаз.
Предложения GROUP BY часто используются в ситуациях, когда можно использовать фразу per something или for each something:
Средний рост по стране рождения
Общее количество людей для каждой комбинации цвета глаз и волос
Общее количество продаж по каждому продукту
Написание предложений GROUP BY
Предложение GROUP BY очень просто написать - мы просто используем ключевые слова GROUP BY, а затем указываем поле (поля), по которому хотим сгруппировать данные:
SELECT ...
FROM sales
GROUP BY location;
Этот простой запрос группирует наши данные о продажах по столбцу местоположения.
Мы выполнили группировку, но что мы должны вставить в SELECT?
Очевидно, что нужно выбрать местоположение - мы группируем по нему, поэтому хотим видеть хотя бы название групп, которые мы создали:
SELECT location
FROM sales
GROUP BY location;
В результате у нас появилось три места:
location
------------
1st Street
HQ
Downtown
(3 rows)
Если мы посмотрим на данные нашей исходной таблицы (SELECT * FROM sales;), то увидим, что у нас есть четыре строки с местоположением HQ, две строки с местоположением Downtown и две строки с местоположением 1st Street:
product | location | price | sold_at
---------+------------+-------+----------------------------
Coffee | HQ | 2 | 2020-09-01 09:42:33.085995
Coffee | HQ | 2 | 2020-09-01 08:42:33.085995
Bagel | Downtown | 3 | 2020-09-01 07:42:33.085995
Coffee | Downtown | 2 | 2020-08-31 09:42:33.085995
Bagel | HQ | 2 | 2020-08-30 09:42:33.085995
Bagel | 1st Street | 3 | 2020-08-30 08:42:33.085995
Coffee | 1st Street | 2 | 2020-08-29 09:42:33.085995
Bagel | HQ | 3 | 2020-08-29 08:42:33.085995
(8 rows)
Группируя по столбцу «местоположение», наша база данных берет эти строки и определяет среди них уникальные местоположения - эти уникальные местоположения и служат нашими «группами».
Но как быть с другими столбцами в нашей таблице?
Если мы попытаемся выбрать такой столбец, как товар, который мы не группировали по...
SELECT
location,
product
FROM sales
GROUP BY location;
...мы столкнулись с этой ошибкой:
ERROR: column "sales.product" must appear in the GROUP BY clause or be used in an aggregate function
Проблема в том, что мы взяли восемь строк и сжали или сократили их до трех.
Мы не можем просто вернуть остальные столбцы, как обычно - у нас было восемь строк, а теперь осталось три.
Что делать с оставшимися пятью строками данных? Какие данные из восьми строк должны отображаться в этих трех разных строках расположения?
Здесь нет четкого и однозначного ответа.
Чтобы использовать оставшиеся данные таблицы, нам также нужно свести данные из этих оставшихся столбцов к трем группам местоположений.
Это означает, что нам нужно произвести агрегирование или вычисления, чтобы получить какую-то сводную информацию об оставшихся данных.
Агрегаторы (COUNT, SUM, AVG)
После того как мы решили, как сгруппировать данные, мы можем выполнить агрегацию оставшихся столбцов.
Это такие действия, как подсчет количества строк в группе, суммирование определенного значения по группе или усреднение информации в группе.
Для начала давайте найдем количество продаж на одно место.
Поскольку каждая запись в нашей таблице продаж - это одна продажа, количество продаж в каждой локации будет равно количеству строк в каждой группе локаций.
Для этого мы воспользуемся агрегатной функцией COUNT(), чтобы подсчитать количество строк в каждой группе:
SELECT
location,
COUNT(*) AS number_of_sales
FROM sales
GROUP BY location;
Мы используем COUNT(*), который подсчитывает все входные строки для группы.
(COUNT() также работает с выражениями, но у него немного другое поведение).
Вот как база данных выполняет этот запрос:
FROM sales — сначала извлекаем все записи из таблицы sales.
GROUP BY location — далее определяем уникальные группы местоположений
SELECT ... — наконец, выберите название местоположения и подсчет количества строк в этой группе.
Мы также дадим этому количеству строк псевдоним AS number_of_sales, чтобы сделать вывод более читабельным. Это выглядит следующим образом:
location | number_of_sales
------------+-----------------
1st Street | 2
HQ | 4
Downtown | 2
(3 rows)
Здесь мы видим, как мы взяли оставшиеся данные столбцов из наших восьми независимых строк и преобразовали их в полезную итоговую информацию для каждого местоположения: количество продаж.
SUM
Аналогичным образом, вместо подсчета количества строк в группе, мы можем суммировать информацию внутри группы - например, общее количество денег, заработанных в этих местах.
Для этого мы воспользуемся функцией SUM():
SELECT
location,
SUM(price) AS total_revenue
FROM sales
GROUP BY location;
Вместо подсчета количества строк в каждой группе мы суммируем долларовую сумму каждой продажи, и это показывает нам общий доход для каждого местоположения:
location | total_revenue
------------+---------------
1st Street | 5
HQ | 9
Downtown | 5
(3 rows)
Среднее значение (AVG)
Найти среднюю цену продажи для каждого места можно, просто заменив функцию SUM() на функцию AVG():
SELECT
location,
AVG(price) AS average_revenue_per_sale
FROM sales
GROUP BY location;
Работа с несколькими группами
До сих пор мы работали только с одной группой: местоположение.
А что, если мы захотим разделить эту группу еще больше?
По аналогии со сценарием «страны рождения и цвет глаз», с которого мы начали, что если мы захотим найти количество продаж одного товара в зависимости от местоположения?
Для этого нам нужно всего лишь добавить второе условие группировки в оператор GROUP BY:
SELECT ...
FROM sales
GROUP BY location, product;
Добавив второй столбец в нашу GROUP BY, мы еще больше разделим наши группы местоположений на группы местоположений по каждому продукту.
Поскольку теперь мы группируем и по столбцу продукта, мы можем вернуть его в наш SELECT!
(Я собираюсь добавить несколько предложений ORDER BY к этим запросам, чтобы сделать вывод более удобным для чтения).
SELECT
location,
product
FROM sales
GROUP BY location, product
ORDER BY location, product;
Глядя на результат нашей новой группировки, мы видим уникальные сочетания местоположения и продукта:
location | product
------------+---------
1st Street | Bagel
1st Street | Coffee
Downtown | Bagel
Downtown | Coffee
HQ | Bagel
HQ | Coffee
(6 rows)
Теперь, когда у нас есть группы, что мы хотим сделать с остальными данными столбцов?
Ну, мы можем найти количество продаж одного продукта в каждом месте, используя те же агрегатные функции, что и раньше:
SELECT
location,
product,
COUNT(*) AS number_of_sales
FROM sales
GROUP BY location, product
ORDER BY location, product;
location | product | number_of_sales
------------+---------+-----------------
1st Street | Bagel | 1
1st Street | Coffee | 1
Downtown | Bagel | 1
Downtown | Coffee | 1
HQ | Bagel | 2
HQ | Coffee | 2
(6 rows)
Использование функций в GROUP BY
Далее попробуем найти общее количество продаж за день.
Если мы будем действовать по той же схеме, что и в случае с местоположениями, и сгруппируемся по столбцу sold_at.
SELECT
sold_at,
COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at
ORDER BY sold_at;
Мы могли бы ожидать, что каждая группа будет представлять собой каждый уникальный день, но вместо этого мы видим следующее:
sold_at | sales_per_day
----------------------------+---------------
2020-08-29 08:42:33.085995 | 1
2020-08-29 09:42:33.085995 | 1
2020-08-30 08:42:33.085995 | 1
2020-08-30 09:42:33.085995 | 1
2020-08-31 09:42:33.085995 | 1
2020-09-01 07:42:33.085995 | 1
2020-09-01 08:42:33.085995 | 1
2020-09-01 09:42:33.085995 | 1
(8 rows)
Кажется, что наши данные вообще не сгруппированы - мы получаем каждую строку по отдельности.
Но на самом деле наши данные сгруппированы! Проблема в том, что sold_at каждого ряда - это уникальное значение, поэтому каждый ряд получает свою собственную группу!
GROUP BY работает правильно, но это не тот результат, который мы хотим получить.
Виной тому уникальная информация о часах/минутах/секундах в метке времени.
Каждая из этих временных меток отличается на часы, минуты или секунды, поэтому они помещаются в свою группу.
Нам нужно преобразовать каждое из этих значений даты и времени в просто дату:
2020-09-01 08:42:33.085995 => 2020-09-01
2020-09-01 09:42:33.085995 => 2020-09-01
Преобразованные в дату, все временные метки одного и того же дня вернут одно и то же значение даты и, следовательно, будут помещены в одну группу.
Для этого мы приведем значение временной метки sold_at к дате:
SELECT
sold_at::DATE AS date,
COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at::DATE
ORDER BY sold_at::DATE;
В предложении GROUP BY мы используем ::DATE для усечения временной метки до «дня». Это эффективно отсекает часы/минуты/секунды временной метки и возвращает только день.
В нашем SELECT мы также возвращаем это же выражение и даем ему псевдоним, чтобы украсить вывод.
По той же причине, по которой мы не можем вернуть product без группировки по нему или выполнения какого-либо агрегата, база данных не позволит нам вернуть просто sold_at - все в SELECT должно быть либо в GROUP BY, либо в каком-то агрегате для результирующих групп.
В результате мы получаем данные о продажах за день, которые мы изначально хотели увидеть:
date | sales_per_day
------------+---------------
2020-08-29 | 2
2020-08-30 | 2
2020-08-31 | 1
2020-09-01 | 3
(4 rows)
Фильтрация групп с помощью HAVING
Далее рассмотрим, как отфильтровать сгруппированные строки.
Для этого попробуем найти дни, когда у нас было более одной продажи.
Без группировки мы обычно фильтруем наши строки с помощью предложения WHERE. Например:
SELECT *
FROM sales
WHERE product = 'Coffee';
В наших группах мы можем захотеть сделать что-то вроде этого, чтобы фильтровать наши группы на основе количества строк
SELECT
sold_at::DATE AS date,
COUNT(*) AS sales_per_day
FROM sales
WHERE COUNT(*) > 1 -- filter the groups?
GROUP BY sold_at::DATE;
К сожалению, это не работает, и мы получаем эту ошибку:
ERROR: aggregate functions are not allowed in WHERE
Агрегатные функции не допускаются в предложении WHERE, потому что предложение WHERE оценивается перед предложением GROUP BY - в нем еще нет групп для выполнения вычислений.
Но есть тип предложения, который позволяет нам фильтровать, выполнять агрегацию, и он оценивается после предложения GROUP BY: предложение HAVING.
Предложение HAVING - это как предложение WHERE для ваших групп.
Чтобы найти дни, когда у нас было больше одной продажи, мы можем добавить предложение HAVING, которое проверяет количество строк в группе:
SELECT
sold_at::DATE AS date,
COUNT(*) AS sales_per_day
FROM sales
GROUP BY sold_at::DATE
HAVING COUNT(*) > 1;
Это предложение HAVING отфильтровывает все строки, в которых количество строк в этой группе не больше единицы, и мы видим это в нашем наборе результатов:
date | sales_per_day
------------+---------------
2020-09-01 | 3
2020-08-29 | 2
2020-08-30 | 2
(3 rows)
Для полноты картины приведем порядок выполнения всех частей SQL-оператора:
FROM — Извлечь все строки из таблицы FROM
JOIN — Выполнить любые соединения
WHERE — Отфильтровать строки
GROUP BY — Формировать группы
HAVING — Фильтровать группы
SELECT — Выбрать возвращаемые данные
ORDER BY — Упорядочить выводимые строки
LIMIT — Возврат определенного количества строк
Агрегаты с неявной группировкой
Последняя тема, которую мы рассмотрим, — это агрегаты, которые можно выполнять без GROUP BY - или, лучше сказать, они имеют неявную группировку.
Такие агрегации полезны в сценариях, когда вы хотите найти один конкретный агрегат из таблицы - например, общую сумму доходов или наибольшее или наименьшее значение столбца.
Например, мы можем найти общую выручку по всем отделениям, просто выбрав сумму по всей таблице:
SELECT SUM(price)
FROM sales;
sum
-----
19
(1 row)
На данный момент мы сделали 19 продаж во всех местах (ура!).
Еще одна полезная вещь, которую мы можем запросить, - это дата первой или последней продажи.
Например, какова дата нашей первой продажи?
Чтобы узнать это, мы просто используем функцию MIN():
SELECT MIN(sold_at)::DATE AS first_sale
FROM sales;
first_sale
------------
2020-08-29
(1 row)
(Чтобы найти дату последней продажи, просто замените MAX()на MIN()).
Использование MIN / MAX
Хотя эти простые запросы могут быть полезны как самостоятельные, они часто являются частью фильтров для более крупных запросов.
Например, давайте попробуем найти общее количество продаж за последний день, когда у нас были продажи.
Этот запрос можно написать следующим образом:
SELECT
SUM(price)
FROM sales
WHERE sold_at::DATE = '2020-09-01';
Этот запрос работает, но мы, очевидно, жестко задали дату 2020-09-01.
09/01/2020 может быть последней датой, когда у нас была распродажа, но это не всегда будет именно эта дата. Нам нужно динамическое решение.
Этого можно добиться, объединив данный запрос с функцией MAX() в подзапросе:
SELECT
SUM(price)
FROM sales
WHERE sold_at::DATE = (
SELECT MAX(sold_at::DATE)
FROM sales
);
В нашем предложении WHERE мы находим самую большую дату в нашей таблице с помощью подзапроса:
SELECT MAX(sold_at::DATE) FROM sales.
Затем мы используем эту максимальную дату в качестве значения, по которому мы фильтруем таблицу, и суммируем цену каждой продажи.
Неявная группировка
Это неявная группировка, потому что если мы попытаемся выбрать агрегированное значение с неагрегированным столбцом, как здесь.
SELECT
SUM(price),
location
FROM sales;
мы получаем знакомую ошибку:
ERROR: column "sales.location" must appear in the GROUP BY clause or be used in an aggregate function
GROUP BY - это инструмент
Как и многие другие темы в разработке программного обеспечения, GROUP BY - это инструмент.
Существует множество способов написать и переписать эти запросы, используя комбинации GROUP BY, агрегатных функций или других инструментов, таких как DISTINCT, ORDER BY и LIMIT.
Понимание и работа с GROUP BY потребуют немного практики, но как только вы освоите их, вы обнаружите, что теперь перед вами открылась совершенно новая группа проблем!
В современном технологическом мире контейнерные технологии используются везде. Самой популярной платформой для управления контейнерами является Docker. С ее помощью использовать контейнеры, разрабатывать и запускать приложения становится проще.
К сожалению, она может занимать довольно много места на диске, и в конечном счете диск может просто переполниться.
Совсем неважно, где вы используете Docker – на своем устройстве или на сервере, это руководство научит вас анализировать дисковое пространство, которые вы используете, и очищать Docker от ненужных ресурсов.
Все, что вам понадобиться, - это запущенный демон Docker и терминал.
Как проанализировать, сколько места занимает Docker
Вы можете просмотреть, сколько места занимает Docker, запустив следующую команду:
$ docker system df
TYPE TOTAL ACTIVE SIZE RECLAIMABLE
Images 61 16 21.1GB 15.25GB (72%)
Containers 69 0 12.26MB 12.26MB (100%)
Local Volumes 3 2 539.1MB 50.04MB (9%)
Build Cache 76 0 1.242GB 1.242GB
А добавив флаг
-v
, вы можете получить более подробную информацию:
$ docker system df -v
REPOSITORY TAG IMAGE ID CREATED SIZE SHARED
teamatldocker/jira e50b8390945c 4 weeks ago 842.3MB 0B
vw ed9e125a8925 2 months ago 1.659GB 134.8MB
Containers space usage:
CONTAINER ID IMAGE COMMAND SIZE
94e03a4a17d0 teamatldocker/jira "/sbin/tini -- /usr/…" 1.4MB
Local Volumes space usage:
VOLUME NAME LINKS SIZE
play-with-jira_postgresqldata 1 84.19MB
play-with-jira_jiradata 1 404.8MB
Build cache usage: 1.242GB
CACHE ID CACHE TYPE SIZE CREATED LAST USED
oxil5sdicb91 regular 135MB 2 months ago 2 months ago
kxz13fmdbodg regular 13B 2 months ago 2 months ago
nysus21ej7pf regular 0B 2 months ago 2 months ago
Как вы можете видеть, вы получили информацию о том:
Сколько места занимают образы
Сколько места занимают контейнеры
Сколько места занимают локальные тома
Сколько места занимает кэш сборки
Как очистить Docker
Вы можете очистить Docker полностью или только какие-то определенные его ресурсы, например, образы, тома контейнеров или кэш сборки.
Для того, чтобы очистить максимально большую часть данных (кроме тех компонентов, которые сейчас используются), вам нужно выполнить следующую команду:
$ docker system prune -a
Флаг
-a
обозначает неиспользуемые или висячие контейнеры. Если вы не укажите флаг
-a
, то команда удалит только висячие образы, то есть нетегированные образы, которые никак не связаны с другими образами.
Если вы хотите удалить большую часть ресурсов Docker, но при этом оставить тегированные образы, то вам нужна вот эта команда:
$ docker system prune
Эти команды помогут вам быстро освободить место на диске. Но помимо этого вы можете удалять отдельные компоненты.
Вот несколько полезных команд:
Удаление неиспользуемых и висячих образов
$ docker image prune
Удаление только висячих образов
$ docker image prune -a
Удаление остановленных контейнеров
$ docker container prune
Удаление неиспользуемых томов
$ docker volume prune
Как управлять пространством, которое использует Docker, на постоянной основе
Какие-то команды вы можете запускать на ежедневной основе или при запуске платформы. А для того, чтобы не забывать об этом, вы можете добавить флаг
-f
к команде, которую хотите запускать автоматически.
Только помните, что, так как вы регулярно удаляете ресурсы Docker, вам придется чаще загружать образы.
Если у вас нет проблем с нехваткой дискового пространства, можете быть спокойны. Просто делайте «уборку» всякий раз, как заметите, что Docker начинает использовать слишком много памяти.
Заключение
На сегодняшний день существует огромное количество способов освобождения дискового пространства Docker с помощью docker-команд. Вы даже можете выполнять эти команды в автоматическом режиме, если хотите периодически очищать ресурсы Docker.
В сегодняшней статье мы покажем, как настроить PPTP клиент на роутерах Mikrotik. Мы будем проводить все настройки на модели RB951Ui-2HnD, но написанное в этой статье будет актуально для любой модели Mikrotik, поддерживающей PPP.
Протокол PPTP
Для начала немного теории. PPTP (Point To Point Tunnel Protocol) это протокол, который позволяет создать незашифрованный туннель через публичную сеть Интернет. Для шифрования данного туннеля вместе с PPTP обычно применяется протокол Microsoft Point-to-Point Encryption (MPPE). PPTP поддерживает различные методы аутентификации, которые включены в PPP, такие как: pap, chap, mschap2, mschap1. Для использования PPTP зарезервирован порт 1723 и протокол GRE для энкапсуляции фреймов.
Основные задачи, которые решает PPTP:
Безопасное соединение роутеров через Интернет по защищенному туннелю
Подключение удаленных пользователей для доступа к ресурсам локальной сети
Соединение распределенных локальных сетей через Интернет
Настройка PPTP клиента
Для того, чтобы настроить PPTP клиент на роутерах Mikrotik при помощи приложения WinBox, нужно открыть раздел PPP → Interface → + и выбрать PPTP Client, как показано ниже:
Откроется окно добавления нового интерфейса. На вкладке General можно оставить всё по умолчанию.
Переходим на вкладку Dial Out и выполняем следующие настройки:
Connect To - Сюда вписываем адрес или ddns сервера PPTP/ VPN (Например - OpenVPN).
User - Логин для подключения к серверу PPTP/VPN
Password - Пароль для подключения к серверу PPTP/VPN
На самом базовом уровне этих данных вполне достаточно.
Осталось только добавить несколько правил, которые разрешат использование PPTP.
Для этого открываем раздел IP> → Firewall → NAT и нажимаем +. Перед нами открывается страница добавления нового правила, заполняем её следующим образом:
Здесь Out Interface - интерфейс для PPTP клиента, который мы создавали ранее.