Ranking Functions: ROW_NUMBER

Четыре функции ранжирования: ROW_NUMBER, RANK, DENSE_RANK и NTILE появились в SQL Server 2005 и отличаются от обычных скалярных функций тем, что результат, который они выдают для строки, зависит от других строк выборки. От агрегатных функций они отличаются тем, что возвращают только одну строку для каждой строки на входе, т. е. они не объединяют набор строк в одну. В этой статье мы рассмотрим ROW_NUMBER — самую простую из всех функций ранжирования.

Для примеров будем использовать следующий набор данных:

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
INSERT T VALUES (0, 1, 😍
INSERT T VALUES (0, 3, 6)
INSERT T VALUES (0, 5, 4)
INSERT T VALUES (0, 7, 2)
INSERT T VALUES (0, 9, 0)
INSERT T VALUES (1, 0, 9)
INSERT T VALUES (1, 2, 7)
INSERT T VALUES (1, 4, 5)
INSERT T VALUES (1, 6, 3)
INSERT T VALUES (1, 8, 1)

https://habr.com/ru/articles/803211/

#db

👉 @database_info

Статический анализ структуры базы данных (часть 1)

Статический анализ структуры базы данных — это процесс выявления ошибок, нерекомендуемых практик и потенциальных проблем в базе данных только на основе структуры, типов данных, свойств объектов. Статический анализ структуры не задействует ни пользовательские данные, ни статистику по таким данным.

https://habr.com/ru/articles/800121/

#db

👉 @database_info

Статический анализ структуры базы данных (часть 2)

Продолжим разбор проверок структуры базы данных, на примере PostgeSQL. Данная статья будет посвящена проверкам связанным с ограничением FOREIGN KEY (FK). Часть проверок целесообразно выполнять на регулярной основе, а некоторые позволяют лучше понять структуру проекта при первом знакомстве и применяются только один раз.

https://habr.com/ru/articles/803841/

#db

👉 @database_info

Главное — порядок в данных, а остальное СУБД.

Приглашаем на вебинар «PostgreSQL, MongoDB, Redis в облаке: с чего начать». Валерий Ишутин, руководитель направления развития платформенных сервисов T1 Cloud, расскажет:

🔵 Как развернуть базу данных нажатием одной кнопки
🔵 Что включает в себя услуга Managed Services
🔵 Что делать, если Junior-разработчик удалил часть базы данных
🔵 О распределении зон ответственности T1 Cloud и клиента

💬 В конце вебинара можно будет задать вопросы спикеру.

Дата: 16 апреля 2024 (вторник)
Время: 11:00 – 12:00 МСК

➡️ Зарегистрироваться и добавить вебинар в календарь можно прямо сейчас.

Реклама. Информация о рекламодателе

PostgreSQL. Устройство карты свободного пространства

В этой статье мы разберемся, как в СУБД PostgreSQL реализован поиск страниц с необходимым свободным пространством для быстрой вставки записей в таблицу.

Для понимания статьи необходимо:
- Понимание основных механизмов работы PostgreSQL
- Базовое понимание языков Си и SQL
- Базовые навыки работы с терминалом на ОС Linux

https://habr.com/ru/articles/806415/

#db

👉 @database_info

SQL за полтора часа в одном видео! Полный базовый курс по SQL. Базы данных курс для начинающих!

00:00:00 Введение
00:02:43 Платные курсы и что изучать дальше
00:04:55 Коротко об онлайн редакторе
00:07:11 Что такое база данных
00:08:10 Что такое CRUD
00:10:05 Запрос на создание первой таблицы
00:11:57 Типы данных у атрибутов(колонок) (DATA TYPES)
00:17:26 Прописываем атрибуты(колонки) первой таблицы
00:21:07 Создаем первую таблицу (CREATE TABLE)
00:21:38 Проверка на наличие таблицы при создании (IF NOT EXISTS)
00:22:18 Запрос на удаление таблицы(DROP TABLE)
00:22:33 Проверка на наличие таблицы при удалении(IF EXISTS)
00:22:57 Запрос на добавление объекта в таблицу (INSER INTO table)
00:23:35 Запрос на чтение(получение) объектов из таблицы(SELECT * FROM table)
00:24:18 Модификаторы для атрибутов(колонок) таблицы(NOT NULL, DEFAULT, UNIQUE)
00:29:26 Изменение уже существующей таблицы(ALTER TABLE, ADD, DROP, RENAME, MODIFY COLUMN)
00:36:08 Удаление объектов из таблицы(DELETE FROM table)
00:37:06 Редактирование объекта в таблице(UPDATE table)
00:39:03 Первичный ключ(PRIMARY KEY)
00:45:37 Композиция в бд
00:50:38 "Иностранный" ключ(FOREIGN KEY)
00:57:26 Индексы в бд(INDEX)
00:59:24 Готовим данные для темы алиас, юнион и слияние таблиц
01:03:30 Слияние таблиц(INNER JOIN)
01:06:02 Слияние таблиц(LEFT JOIN)
01:06:53 Слияние таблиц(RIGHT JOIN)
01:07:26 Слияние таблиц(FULL JOIN/OUTER JOIN) и Union
01:09:04 Алиас(table AS alias)
01:12:18 Select Distinct в SQL
01:13:42 AND OR NOT в SQL
01:16:18 ORDER BY и LIMIT в SQL
01:17:35 MIN и MAX в SQL
01:18:25 COUNT SUM AVG в SQL
01:19:29 LIKE в SQL
01:21:31 IN и BETWEEN в SQL
01:22:50 GROUP BY в SQL
01:25:17 EXISTS в SQL
01:27:37 ANY и SOME в SQL
01:29:00 INSERT INTO в SQL
01:30:40 Отношения в базе данных
01:32:11 Отношения один к одному
01:33:09 Отношения один ко многим
01:34:09 Отношения многие ко многим
01:37:10 Отношения один к одному и один ко многим "через"
01:39:10 SQL инъекции

источник

#db

👉 @database_info

Как посчитать для каждой строки таблицы количество вхождений этой строки в другую таблицу?

Есть две таблицы course и history
В таблице course: id и name
В таблице history: id, course_id, status
Нужно для каждой строчки course посчитать количество строк в history

На выходе должно быть см. скрин

Ответ

SELECT name AS course_name, COUNT(history.id) AS quantity
FROM course
LEFT JOIN history ON course_id = course.id AND status = 1
GROUP BY name

#db

👉 @database_info

Хранимые процедуры в SQL

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

https://telegra.ph/Hranimye-procedury-v-SQL-04-16

#SQL

👉 @database_info

Скрипт для создания бекапов БД с помощью Percona XtraBackup

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

#!/bin/bash
# Скрипт делает резервную копию БД
# если сервер MySQL не запущен, то выходим

MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))

if [ $ping_alive = 0 ]; then
logger -t mysql-backup 'MYADMIN ping FAIL'
exit
fi

BACKUP="/usr/bin/innobackupex-1.5.1 --no-lock"
workdir="/usr/local/tmp/"

DofW=`date +'%u'`
full=0
incr=0
# если суббота - делаем полный бекап, иначе инкрементальный
if [ "$DofW" = "6" ];
then
full=1
logger -t mysql_backup 'make full backup'
else
incr=1
logger -t mysql_backup 'make incremental backup'
fi

if [ $full == '1' ];
then
logger -t mysql_backup 'start mysql full backup'
dump="mysql"`date +%Y%m%d`"full.tar"
ext=".gz"
# при распаковке использовать ключ -i, например tar -ixvf backup.tar
$BACKUP --user=USER --password=PASSWORD --stream=tar ./ | pigz -p 5 > ${workdir}${dump}${ext}
logger -t mysql_backup 'finish mysql full backup, start rsync'
# с помощью rsync копируем резервную копию на удаленный сервер
rsync --bwlimit=5000 ${workdir}${dump}${ext} 192.168.0.4::backup
logger -t mysql_backup 'finish rsync'
rm -f ${workdir}/mysql*
fi

if [ $incr == '1' ];
then
logger -t mysql_backup 'start mysql incremental backup'
# получаем значение LSN из последнего лога бекапа
LSN=`cat /usr/local/sbin/innobackupex.log | grep "The latest check point (for incremental):" | awk -F"'" '{print $2}' | tail -n 1`
echo $LSN > /usr/local/sbin/lsn
dump="mysql"`date +%Y%m%d`"incr"
archiv="mysql"`date +%Y%m%d`"incr.tar.gz"
ext=".tar.gz"
$BACKUP --user=USER --password=PASSWORD --incremental --incremental-lsn=$LSN --stream=xbstream ./ > ${workdir}${dump}.xbstream
logger -t mysql_backup 'finish mysql incremental backup, start tar'
cd ${workdir}
tar -czf $archiv $dump.xbstream
rm -f ${workdir}/*.xbstream
logger -t mysql_backup 'finish tar, start rsync'
# с помощью rsync копируем резервную копию на удаленный сервер
rsync --bwlimit=5000 ${workdir}${dump}${ext} 192.168.0.4::backup
logger -t mysql_backup 'finish rsync'
rm -f ${workdir}/mysql*
fi


#Percona #XtraBackup

👉 @database_info

Работа с json в PostgreSQL

JSON (JavaScript Object Notation) является одним из наиболее распространенных форматов для передачи и хранения данных. PostgreSQL поддерживает работу с JSON, что позволяет эффективно использовать его возможности для обработки и анализа данных.

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

источник

#db

👉 @database_info

Пример рекурсивного запроса для работы с иерархией в postgres

СУБД postgres предоставляет специальный синтаксис для работы с иерархиями с помощью рекурсивного запроса. Такой запрос начинается с ключевых слов with recursive.

В этом видео мы создадим простую иерархию географических объектов в виде таблицы. Затем напишем рекурсивный запрос, чтобы наглядно отобразить весь путь от одного из самых низкоуровневых элементов и до самой вершины иерархии. Таким образом, мы как бы собираем по пути "хлебные крошки" (breadcrumbs), которые могут пригодиться, если этот путь мы хотим отобразить пользователю.

источник

#db

👉 @database_info

Вы инженер связи, слаботочник или ИТ-шник, или безопасник? Обратите внимание на эту выставку, не пропускаю ее никогда. Мало вообще посещаю мероприятия не-онлайн, но тут делаю исключения.
Точка притяжения для телеком-специалистов, инженеров и сисадминов. Связь, телеком, интернет вещей, ЦОДы и оборудование.
На @sviaz_expocentr вход свободный по электронной регистрации на сайте.
Если ИТ и телекоммуникации ваша работа, увидимся на "Связи" 2024.

👉 @database_info

SQL (Основы языка, база PostgreSQL)

00:00 Введение
01:32 Реляционные базы данных
05:22 Установка PostgreSQL
06:46 Подключение к базе через Idea
10:31 CREATE DATABASE
11:44 CREATE SCHEMA
13:35 CREATE TABLE
15:10 INSERT
16:11 SELECT
16:27 Множественный INSERT
17:57 DROP TABLE
18:17 UNIQUE, NOT NULL
19:34 PRIMARY KEY
20:16 Автогенерация PRIMARY KEY
21:58 LIMIT, OFFSET
24:40 WHERE
27:50 Все группы SQL команд
30:15 UPDATE
32:00 FOREIGN KEY
37:43 Diagram. Визуализация таблиц
38:06 SELECT из нескольких таблиц
41:00 ORDER BY (сортировка)
42:00 DISTINCT
42:20 COUNT
42:32 SUM, AVG
43:16 Дополнительные возможности SELECT
45:22 DELETE (Каскадное удаление)
49:33 Полезные функции
52:18 Задание

источник

#db

👉 @database_info

Порядок выполнения SQL-запросов

#db

👉 @database_info

SQL (проектирование баз данны, база PostgreSQL)

00:00 Нормализация базы данных
05:32 OneToMany
07:10 OneToOne
07:31 ManyToMany
08:55 Практика. Готовим тестовые данные.
11:48 JOIN
14:00 INNER JOIN
18:15 CROSS JOIN
19:38 LEFT JOIN
22:07 Визуальное сравнение разных JOIN
24:13 RIGHT JOIN
24:49 FULL JOIN
25:13 GROUP BY, HAVING
27:38 ALTER
30:11 Что еще можно изучить?
33:00 Задания

источник

#db

👉 @database_info

Потенциальные проблемы с автоинкрементным ключом. MySQL <8.0, PostgreSQL

В данной публикации я поделюсь двумя основными причинами, по которым я предпочитаю избегать использования автоинкрементных полей в PostgreSQL и MySQL в будущих проектах. Вместо этого я предпочитаю использовать UUID-поля, за исключением случаев, когда есть очень веские аргументы против этого подхода.

https://habr.com/ru/articles/807751/

original https://samwho.dev/blog/practical-problems-with-auto-increment/

#db

👉 @database_info

Learning Snowflake SQL and Scripting: Generate, Retrieve, and Automate Snowflake Data
Автор: Alan Beaulieu (2023)

Чтобы помочь вам стать профессионалом в области Snowflake, в этом кратком, но исчерпывающем руководстве рассматриваются основы и лучшие практики использования языков SQL и Scripting в Snowflake. Разработчики и специалисты по работе с данными узнают, как генерировать, модифицировать и запрашивать данные в реляционной системе управления базами данных Snowflake, а также как применять аналитические функции для создания отчетов. Автор также покажет, как создавать сценарии, хранимые функции и хранимые процедуры для возврата наборов данных с помощью Snowflake Scripting. Эта книга идеально подходит для тех, кто только начинает работать с базами данных и нуждается в выполнении запросов и отчетов на базе данных Snowflake или переходит с таких баз данных, как Oracle, SQL Server или MySQL, на облачные платформы.

#db

👉 @database_info

NULL-значения в PostgreSQL: правила и исключения

Навскидку многим кажется, что они знакомы с поведением NULL-значений в PostgreSQL, однако иногда неопределённые значения преподносят сюрпризы. Мы с коллегами написали статью на основе моего доклада с PGConf.Russia 2022 — он был полностью посвящён особенностям обработки NULL-значений в Postgres.

https://habr.com/ru/companies/postgrespro/articles/697300/

#db

👉 @database_info