Сложные SQL запросы: решения для поиска дубликатов

Быстрый ответ

Для повышения эффективности SQL вы можете использовать CTE для модульной структуры, оконные функции для аналитики и JOIN для объединения таблиц. Вот краткий пример скрипта для расчета накопительного итога:

SELECT
  OrderID,
  SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

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

Копилка скриптов для решения SQL-задач

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

Избавление от дубликатов данных

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

WITH RankedDuplicates AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY DuplicateField ORDER BY CreationDate) AS rn
  FROM TableName
)
DELETE FROM RankedDuplicates
WHERE rn > 1;

Подробнее об этом расскажет наш спикер на видео

Управление иерархическими запросами

Рекурсивные CTE особенно удобны для работы с иерархическими структурами данных, как показывает следующий пример:

WITH RecursiveCTE (ParentID, ChildID, Level) AS (
  SELECT ParentID, ChildID, 0 AS Level
  FROM hierarchyTable
  WHERE ParentID IS NULL
  UNION ALL
  SELECT h.ParentID, h.ChildID, Level + 1
  FROM hierarchyTable h
  INNER JOIN RecursiveCTE r ON h.ParentID = r.ChildID
)
SELECT * FROM RecursiveCTE;

Увеличение производительности

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

SELECT 
  OBJECT_NAME(i.OBJECT_ID) AS TableName, 
  i.name AS IndexName, 
  user_updates AS Updates, 
  user_seeks + user_scans + user_lookups AS Reads
FROM 
  sys.dm_db_index_usage_stats ius
JOIN 
  sys.indexes i ON ius.OBJECT_ID = i.OBJECT_ID 
  AND ius.index_id = i.index_id
WHERE 
  OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
ORDER BY 
  TableName, IndexName;

Разложение сложных задач на части

Декомпозиция сложных задач на элементарные уровни облегчает поддержку и отладку SQL-скриптов.

Модульный подход

Создавайте и используйте временные результаты в запросах, применяя модульное строение SQL-скриптов с использованием CTE.

Гибкость использования динамического SQL

Динамический SQL дает возможность формировать гибкие запросы:

DECLARE @TableName NVARCHAR(128) = N'Employees';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName);

EXEC sp_executesql @SQL;

Используя sp_executesql, можно исполнять динамический SQL безопасно, избегая SQL-инъекций при помощи параметризации.

Возите с собой SQL-фрагменты

Сохраняйте часто используемые блоки кода в форме шаблонов в SQL Server Management Studio (SSMS) или Azure Data Studio для ускорения работы.

Визуализация

Воспринимайте ваш набор SQL-скриптов как инструментарий, всегда готовый к использованию:

**Набор инструментов для SQL-скриптов 🧰**

| Инструмент                | Применение               |
| ------------------------- | ----------------------- |
| Рекурсивные CTE 🔄        | Обход иерархий      |
| Оконные функции 👀        | Агрегация данных   |
| Запросы с транспонированием 🔀 | Транспонирование строк и столбцов |
| Динамический SQL 💡        | Адаптирование запросов в реальном времени |

Каждый инструмент используется для решения определенной проблемы в SQL.

Продвинутое написание SQL-скриптов

Продвинутые техники написания SQL-скриптов помогают максимизировать производительность и гибкость ваших запросов.

Трансформации и обратные трансформации

Используйте PIVOT и UNPIVOT для изменения представления данных между строками и столбцами.

SELECT *
FROM 
  (SELECT ProductID, Month, SalesAmount FROM Sales) AS SourceTable
PIVOT
  (SUM(SalesAmount) FOR Month IN ([Jan], [Feb], [Mar], ..., [Dec])) AS PivotTable;

Управление временными данными

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

CREATE TABLE Employees
(
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  Position NVARCHAR(100),
  Department NVARCHAR(100),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

За пределами стандартных JOINов

Изучите возможности Cross Apply и Outer Apply для создания коррелирующих подзапросов.

-- Получаем последний заказ каждого клиента
SELECT c.CustomerName, o.OrderID, o.OrderDate, o.TotalAmount
FROM Customers c
CROSS APPLY (
  SELECT TOP 1 OrderID, OrderDate, TotalAmount
  FROM Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) o;

Полезные материалы

1. Оконные функции SQL | Продвинутый SQL – Mode — этот учебник позволит глубоко погрузиться в оконные функции для анализа данных в SQL.
2. Скрипт для создания динамических запросов PIVOT в SQL Server — посмотрите на техники создания динамических запросов с использованием PIVOT.
3. Руководство по архитектуре и проектированию индексов в SQL Server и Azure SQL — обзор наиболее передовых рекомендаций и методик по проектированию архитектуры индексации в SQL Server и Azure SQL.