Трюк дня. Агрегирование записей в PostgreSQL. Решение

WITH projects (task_id, start_date, end_date)
AS (VALUES
(1, CAST('10-01-20' AS date), CAST('10-02-20' AS date)),
(2, CAST('10-02-20' AS date), CAST('10-03-20' AS date)),
(3, CAST('10-03-20' AS date), CAST('10-04-20' AS date)),
(4, CAST('10-13-20' AS date), CAST('10-14-20' AS date)),
(5, CAST('10-14-20' AS date), CAST('10-15-20' AS date)),
(6, CAST('10-28-20' AS date), CAST('10-29-20' AS date)),
(7, CAST('10-30-20' AS date), CAST('10-31-20' AS date))),

-- получим такие даты начала, которых не существует в колонке даты окончания (это 'настоящие' даты начала проекта)

t1 AS (
SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects) ),

-- получим такие даты окончания, которые не существуют в колонке дат начала (это 'настоящие' даты окончания проекта)
t2 AS (
SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects) ),

--отфильтруем допустимые пары начало-окончание (начало < окончание), затем найдем правильную дату окончания для каждой даты начала (минимальная дата окончания, поскольку нет пересекающихся проектов)

t3 AS (
SELECT
start_date,
MIN(end_date) AS end_date
FROM t1, t2
WHERE start_date < end_date
GROUP BY 1 )
SELECT
*,
end_date - start_date AS project_duration
FROM t3
ORDER BY 3, 1

#tips

Иерархические структуры данных и Doctrine


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

В первую очередь, это связано с тем, что реляционные базы не приспособлены к хранению иерархических структур (как, например, XML-файлы), структура реляционных таблиц представляет из себя простые списки. Иерархические же данные имеют связь родитель-наследники, которая не реализована в реляционной структуре.

Тем не менее, задача хранить деревья в базе данных рано или поздно возникает перед любым разработчиком.

Ниже мы подробно рассмотрим, какие существуют подходы в организации хранения деревьев в реляционных БД, а также рассмотрим инструментарий, который нам предоставляет ORM Doctrine для работы с такими структурами.

Читать дальше

@sqlhub

Колоночные базы данных
#nosql

"Колоночные базы данных позволяют эффективно делать сложные выборки на больших таблицах. Изменение структуры больших таблиц происходит мгновенно, а сжатие данных позволяет сэкономить кучу места. Однако не следует использовать колоночные базы для случаев с обычными выборками по ключу и известными структурами запросов. Для этого лучше подойдут обычные (строчные) СУБД."

Читать

@data_study

Типы баз данных

@sqlhub

MSSQL server cheat-sheet

@sqlhub

Поясните разницу между кластерными и некластерными индексами

#вопросы_с_собеседований

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

Некластерный индекс хранит данные в одном месте, а индексы — в другом. Этот индекс имеет указатели на расположение данных. Поскольку индекс не хранится там же, где и данные, для каждой таблицы может существовать много некластерных индексов.

Давайте рассмотрим основные различия между кластерными и некластерными индексами.

@sqlhub

✅ 50 популярных вопросов и ответов на собеседовании по SQL Server

Здесь мы составили список из 50 популярных вопросов для собеседований по SQL. Эти вопросы охватывают все, от основ SQL Server до некоторых его сложных концепций:

Читать

@sqlhub

⚡️ Аналитика данных с SQL- блог ведущего Дата саентиста, работющего с данными в Uber, одного из авторов🔥 Machine Learning. Материал канала поможет реально вырасти до профессионала по работе с данными и получить самую высокоплачиваю ит-профессию.

1 канал вместо тысячи учебников и курсов, подписывайтесь: 👇👇👇

@data_analysis_ml

🚀 Советы по производительности оператора SQL TOP

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

SELECT TOP 10 Name,ProductNumber,SafetyStockLevel FROM Production.Product

Читать дальше

@sqlhub

Hunner - микро фреймворк для тестирования на проникновение. Сканирует на sql, xss, ddos, bruteforce ftp, ssh и д.р уязвимости.

Установка ⚙️
$ git clone https://github.com/b3-v3r/Hunner
$ cd Hunner

Использование ▶️
$ python hunner.py

💡 GitHub

@sqlhub