Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Queries with aggregates

memorize.ai (lvl 286)

(0)

45 cards

()

Section 1

Preview this deck

PostgreSQL Alias

Front

1 / 45

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (45)

Section 1

(45 cards)

PostgreSQL Alias

Front

A PostgreSQL alias assigns a table or a column a temporary name in a query. The aliases only exist during the execution of the query. SELECT column_name AS alias_name FROM table; The main purpose of the column alias is to make the output of a query more meaningful. For the other clauses evaluated before the SELECT clause such as WHERE, GROUP BY, and HAVING, you cannot reference the column alias in these clauses. ONLY for ORDER BY.

Back

Почему отработает запрос SELECT P.id, P.name, COUNT(1) ... GROUP BY P.id, почему не возникнет ситуация, что Postgres попросит добавить p.name в GROUP BY?

Front

Postgres понимает, когда группировка проиходит по ключу и выбираем атрибуты, которые от него зависят.

Back

Как использовать оконную функцию внутри GROUP BY?

Front

Нужно поместить в оконную функцию аггрегирующую функцию. RANK() OVER(ORDER BY SUM(col_name) ... GROUP BY col_name.

Back

When we use Case function and what it does?

Front

The PostgreSQL CASE expression is the same as IF/ELSE statement in other programing languages. PostgreSQL provides two forms of the CASE expression. In this general form, each condition is an expression that returns a boolean value, either true or false. If the condition evaluates to true, it returns the result which follows the condition, and all other CASE branches do not process at all. If all conditions evaluate to false, the CASE expression will return the result in the ELSE part. If you omit the ELSE clause, the CASE expression will return null. SELECT name, CASE WHEN (monthlymaintenance < 100) THEN 'expensive' ELSE 'cheap' END as cost FROM cd.facilities;

Back

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

Front

SELECT city, MAX (rating) FROM Customers GROUP BY city;

Back

Что такое CTE?

Front

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

Back

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

Front

SELECT MIN (cname) FROM Customers WHERE cname LIKE 'G%';

Back

Что сделает SELECT COUNT (DISTINCT snum)?

Front

Посчитает сколько уникальных строчек.

Back

Как можно обойтись без оконных функций?

Front

В простых случаях, можно создать CTE и с помощью INNER JOIN объединить нужные значения.

Back

Оконная функция vs Агрегатные функции

Front

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

Back

Различия между ALL и * когда они используются с COUNT?

Front

* - COUNT со звездочкой включает и NULL и дубликаты. ALL - наоборот, не может считать NULL, но считает дубликаты.

Back

Что сделает SELECT COUNT (*)

Front

Подсчёт общего числа строк в таблице. COUNT со звездочкой включает и NULL и дубликаты, по этой причине DISTINCT не может быть использован.

Back

Как подсчитать сумму всех предыдущих значений?

Front

ROWS UNBOUNDED PRECEDING Мы можем ограничивать строки в окне.

Back

CTE vs Views

Front

Views - часть схемы БД, нужны права для создания представлений.

Back

Wha's an aggregate function? List them

Front

Aggregate functions perform a calculation on a set of rows and return a single row. PostgreSQL provides all standard SQL's aggregate functions as follows: AVG() - return the average value. COUNT() - return the number of values. MAX() - return the maximum value. MIN() - return the minimum value. SUM() - return the sum of all or distinct values.

Back

Как подсчитать количество полётов для каждого капитана по отдельности?

Front

1) Подсчитать по отдельности для каждого капитана - плохой вариант, потому что мы можем не знать имя капитана. 2) С помощью GROUP BY. SELECT SUM() FROM Flight GROUP BY Flight.commander_id

Back

Например у нас есть последовательность (1, 1) (5,2) (6,3) (6,3) (6,3) С какого номера продолжится нумерация, если в последовательности чисел появится 7?

Front

Если использовать функцию RANK - 6; Если использовать функцию DENSE_RANK - 4.

Back

Что делает функция COUNT?

Front

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

Back

Почему при выборке максимального значения из столбца лучше не использовать LIMIT?

Front

Если существует несколько самых дорогих изделий (например, каждое из них стоит 19,95), запрос, использующий LIMIT, возвращает лишь одно из них! MAX - выдаст все максимальные значения.

Back

Разница между RANK и DENSE RANK

Front

Разница в том, что они по-разному смотрят на одинаковый ранг. Если использовать RANK и у нас 2 одинаковых ранга, например 1 и 1, то следующий ранк будет пропущенный и будет равен 3, а не 2. DENSE RANK не пропускает значения.

Back

OVER()

Front

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

Back

В чем разница между GROUP BY и DISTINCT?

Front

Если вы используете выражение GROUP BY без агрегатной функции, то оно будет выполнять роль ключевого слова DISTINCT. Единственное отличие между ними заключается в следующем: GROUP BY сначала сортирует данные, а затем осуществляет группировку; Ключевое слово DISTINCT не выполняет сортировки. Если вы используете ключевое слово DISTINCT вместе с выражением ORDER BY, то получите тот же результат, что и при применении GROUP BY

Back

Что быстрее работает MIN(field) vs ORDER BY field LIMIT 1?

Front

Функция MIN/MAX () обеспечит наилучшую производительность. MIN/MAX предпочтительнее. В худшем случае, когда вы смотрите на неиндексированное поле, использование MIN () требует одного полного прохода таблицы. Использование SORT и LIMIT требует файловой сортировки. При запуске с большой таблицей, вероятно, будет существенная разница в воспринимаемой производительности. В качестве бессмысленной точки данных MIN () занял 0,66 с, а SORT и LIMIT - 0,84 против таблицы строк в 106 000 на моем сервере dev. Однако, если вы смотрите на индексированный столбец, разницу заметить труднее (бессмысленная точка данных равна 0,00 с в обоих случаях). Однако, смотря на вывод команды объяснения, похоже, что MIN () может просто извлечь наименьшее значение из индекса (строки «Выбранные таблицы оптимизированы» и «NULL»), тогда как для SORT и LIMIT по-прежнему необходимо выполнить упорядоченный обход индекса (106 000 строк).

Back

Есть 1 табличка, в которой 3 столбца: имя, фамилия и сумма оплаты. Напишите запрос, чтобы найти сумму для каждого человека.

Front

SELECT first_name, last_name, SUM(amount) FROM test GROUP BY first_name;

Back

Как подсчитать сумму всех последующих значений?

Front

ROWS UNBOUNDED FOLLOWING

Back

WHERE vs HAVING

Front

WHERE используется для фильтрации того, какие данные поступают в агрегатную функцию, а HAVING используется для фильтрации данных после их вывода из функции.

Back

Напишите запрос, который посчитает строки с не NULL значениями

Front

SELECT COUNT ( ALL rating ) FROM Customers; SELECT COUNT (DISTINCT city) FROM Customers;

Back

Что такое окно?

Front

Окно описывает набор строк, над которыми работает оконная функция. Оконная функция возвращает значения из строк в окне.

Back

PostgreSQL GROUP BY

Front

The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., SUM to calculate the sum of items or COUNT to get the number of items in the groups. SELECT column_1, aggregate_function(column_2) FROM tbl_name GROUP BY column_1; The GROUP BY clause must appear right after the FROM or WHERE clause. Followed by the GROUP BY clause is one column or a list of comma-separated columns. You can also put an expression in the GROUP BY clause. SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id ORDER BY SUM (amount) DESC; You can use the GROUP BY clause without applying an aggregate function. The following query gets data from the payment table and groups the result by customer id. In this case, the GROUP BY acts like the DISTINCT clause that removes the duplicate rows from the result set.

Back

PostgreSQL HAVING

Front

We often use the HAVING clause in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition. SELECT column_1, aggregate_function (column_2) FROM tbl_name GROUP BY column_1 HAVING condition; The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies. This is the main difference between the HAVING and WHERE clauses. In PostgreSQL, you can use the HAVING clause without the GROUP BY clause. In this case, the HAVING clause will turn the query into a single group. In addition, the SELECT list and HAVING clause can only refer to columns from within aggregate functions. This kind of query returns a single row if the condition in the HAVING clause is true or zero row if it is false. SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id HAVING SUM (amount) > 200;

Back

Как работает GROUP BY?

Front

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT.

Back

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

Front

SELECT cnum, MIN (amt) FROM Orders GROUP BY cnum;

Back

Как посчитать все суммы приобретений на 3 Октября?

Front

SELECT COUNT(*) FROM Orders WHERE odate = 10/03/1990;

Back

OVER (PARTITION BY )

Front

Предложение PARTITION BY, дополняющее OVER, указывает, что строки нужно разделить по группам или разделам, объединяя одинаковые значения выражений PARTITION BY.

Back

Какое неудобство GROUP BY?

Front

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

Back

LAG & LEAD

Front

LAG - returns value from the previous row LEAD - return value from the next row

Back

Какие атрибуты использовать в GROUP BY?

Front

Нужно группировать по атрибутам, идентифицирующим строчки, которые должны попасть в одну группу. (например, если будет сортировка по имени, а имена одинаковые, то СУБД посчитает строчки одинаковыми, хотя на самом деле они разные)

Back

Напишите запрос, который найдет кол-во полетов сделанных капитаном зеленым.

Front

SELECT COUNT(1) FROM Flight F JOIN Commander C ON F.commander_id = C.id WHERE c.name = 'Зеленый'

Back

Есть 1 табличка, в которой 2 столбца: имя и сумма оплаты. Напишите запрос, чтобы выбрать уникальные имена и максимальную цифру.

Front

SELECT username, MAX(user_number) FROM exercise GROUP BY username; SELECT * FROM exercise a WHERE a.user_number = (SELECT MAX(b.user_number) FROM exercise b WHERE b.username=a.username)

Back

Правила работы с HAVING

Front

- HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. - HAVING может использовать только аргументы которые имеют одно значение на группу вывода.

Back

Как найти строку, содержащую максимальное значение некоторого столбца?

Front

1) SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); 2) SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1

Back

Вы хотели бы получить имя и фамилию последнего участника (ов), которые зарегистрировались, а не только дату. Как ты можешь это сделать?

Front

select firstname, surname, joindate from cd.members where joindate in (select max(joindate) from cd.members);

Back

Order of execution of a Query

Front

1. FROM and JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT

Back

Как найти самый большой элемент из группы элементов в выборке?

Front

1. С помощью CTE. Группируем элементы в CTE, а потом с помощью запроса из CTE находим MAX значение. 2. С помощью оконной функции RANK. Внутри подзапроса используем RANK, а во внешнем запросе сортируем значения, где RANK = 1.

Back

Можно ли использовать аггрегатную функцию в WHERE?

Front

Вы не сможете использовать агрегатную функцию в предложении WHERE (если вы не используете подзапрос).

Back