Как развернуть данные sql и объединить результаты в ненулевые строки по дате на идентификатор
Неплохой заголовок для поста, но, надеюсь, он заинтересует.
У меня очень сложная ситуация в T-SQL, которую я не могу решить. Я надеюсь, что кто-то с опытом знает элегантное и быстрое решение, чтобы не повлиять на мою производительность. Я имею дело с миллиардами строк.
ПРЕДИСЛОВИЕ
У меня есть таблица с названием " Клиенты" с уникальным идентификатором. Эти клиенты имеют файлы, файлы имеют свойства, и каждое имя свойства соответствует одному значению.
Таблицы:
- Клиенты
- Файлы -
- Свойство - содержит как имя, так и значение
Идентификатор клиента присутствует во всех этих таблицах, как и поля аудита, такие как UpdatedDtm и CreationDtm.
Случай использования
Мне нужно присоединить всех клиентов к своим файлам (фильтрация для нескольких), а затем привязать каждый файл к их свойствам (снова фильтруя их). Это легко, но в результате получается много строк, по одной для каждого клиента x file x property.
Я знаю, что имена свойств никогда не изменятся, и я хочу вернуть только несколько избранных, поэтому я использовал сводную таблицу и привел к хорошей таблице, но она развалилась после того, как я начал делать более сложные запросы.
ПРОБЛЕМА Во-первых, свойства имеют DateTime, когда они были изменены (updatedDtm), и мне нужно вернуть все измененное с 1 часа от даты создания (CreationDtm) в таблице файлов.
Это приводит к тому, что я урезаю свой список потенциальных свойств, но теперь у меня есть таблица с RowNumber() для идентификатора и нет хорошего способа развернуть и выбрать первый, который не равен NULL и при этом сохраняется количество столбцов для таблица определений. Это важно, потому что я использую Dynamic SQL и помещаю его в индексированную временную таблицу с составным ключом для CustomerID и FileName.
ДО PIVOT
| UpdatedDtm | CustomerID | FileName | Property | Value |
| ---------- | ---------- | ---------- | -------- | -------------- |
| 1/1/2015 | 1 | FileOne | Size | NULL |
| 1/1/2015 | 1 | FileOne | Format | JPG |
| 1/7/2015 | 1 | FileOne | Size | 88KB |
| 1/7/2015 | 1 | FileOne | Format | JPG |
| 1/7/2015 | 1 | FileOne | Comment | NULL |
| 1/11/2015 | 1 | FileOne | Comment | NULL |
| 1/1/2015 | 1 | FileTwo | Size | 91KB |
| 1/1/2015 | 1 | FileTwo | Format | PNG |
| 1/11/2015 | 1 | FileTwo | Comment | NULL |
| 1/2/2015 | 2 | FileThree | Size | 74KB |
| 1/2/2015 | 2 | FileThree | Format | XLS |
| 1/2/2015 | 2 | FileThree | State | Open |
| 1/7/2015 | 2 | FileThree | State | Closed |
| 1/10/2015 | 2 | FileThree | Comment | NULL |
| 1/1/2015 | 3 | FileFour | Size | 2KB |
| 1/2/2015 | 3 | FileFour | Size | 10KB |
| 1/3/2015 | 3 | FileFour | Size | 13KB |
| 1/4/2015 | 3 | FileFour | Size | 21KB |
| 1/5/2015 | 3 | FileFour | Size | 27KB |
| 1/6/2015 | 3 | FileFour | Size | 32KB |
| 1/7/2015 | 3 | FileFour | Size | 39KB |
| 1/8/2015 | 3 | FileFour | Size | 44KB |
| 1/1/2015 | 3 | FileFour | Format | TXT |
| 1/1/2015 | 3 | FileFour | Comment | NULL |
Пожалуйста, не спрашивайте меня, почему база данных настроена таким образом или для изменения схемы. Это находится в камне и вне моего контроля. Я должен быть в состоянии решить вариант использования, как описано.
ПОСЛЕ PIVOT (Ожидание)
| CustomerID | FileName | Size | Format | State | Comment |
| ---------- | ---------- | ---- | ------ | ------ | ------- |
| 1 | FileOne | 88KB | JPG | NULL | NULL |
| 1 | FileTwo | 91KB | PNG | NULL | NULL |
| 2 | FileThree | 74KB | XLS | Closed | NULL |
| 3 | FileFour | 44KB | TXT | NULL | NULL |
Я включил некоторые значения NULL и пропущенные значения, чтобы продемонстрировать, что мне нужно сохранять одинаковые свойства столбцов, независимо от того, имеют ли они данные, но мне также нужно сжать данные по первому ненулевому значению в моем диапазоне дат.
КОД (Моя попытка)
IF Object_id('tempdb..#FilesQuery') IS NOT NULL DROP TABLE #FilesQuery;
CREATE TABLE #FilesQuery (
SeqNum int,
CustomerID numeric(16,0),
FileName varchar(64),
PropertyName varchar(64),
PropertyValue varchar(64)
)
INSERT INTO #FilesQuery
SELECT
CASE WHEN P.[Value] IS NOT NULL
THEN ROW_NUMBER() OVER (partition by C.CustomerID order by UpdatedDtm)
ELSE 0
END as SeqNum,
C.CustomerID
,F.Name as FileName
,P.Name as PropertyName
,P.Value as PropertyValue
FROM Customers C
INNER JOIN Files F ON F.CustomerID = C.CustomerID
LEFT JOIN Properties P
ON P.CustomerID = C.CustomerID
AND P.FileID = F.FileID
WHERE F.FileName IN ('FileOne','FileTwo','FileThree','FileFour')
AND P.Name IN ('Size','Format','State','Comment')
--PIVOT
DECLARE @cols AS nvarchar(MAX)
SELECT @cols = STUFF(
(SELECT DISTINCT ',' + QUOTENAME(PropertyName)
FROM #FilesQuery fq
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
DECLARE @dynSql AS nvarchar(MAX)
SET @dynSql = '
SELECT DISTINCT *
FROM (
SELECT
fq.CustomerID,
fq.FileName,
fq.PropertyName,
fq.PropertyValue
FROM #FilesQuery fq
) SRC
PIVOT (
Max([PropertyValue])
FOR PropertyName IN (' + @cols + ')
) PVT
'
IF Object_id('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (
CustomerID varchar(16) NOT NULL,
FileName varchar(64) NOT NULL,
FileSize varchar(64) NULL,
FileFormat varchar(64) NULL,
FileState varchar(64) NULL,
FileComment varchar(64) NULL,
CONSTRAINT pk_CustDoc PRIMARY KEY (CustomerID,FileName)
)
INSERT INTO #Results EXEC @dynSql;
Извините, этот код не полный, это рабочий раздел, который у меня есть. Другие попытки привели к сбоям данных.
Я попытался использовать SeqNum и комбинацию операторов case, чтобы попытаться выбрать первое ненулевое значение для каждой строки, чтобы все данные были в одной строке, но в итоге получилось больше.
FileOne NULL NULL Open NULL
FileOne NULL JPG NULL NULL
и так далее...
Я некоторое время пытался решить этот особый случай и собираюсь отказаться, и он сделает что-то процедурное с циклом, но это убьет время и производительность моего запроса.
У кого-нибудь есть хорошее решение? Я обдумывал вещи?
2 ответа
Вы должны отфильтровать свои данные перед вами PIVOT и вы получите желаемый результат. Вот версия cte, чтобы показать вам, как получить то, что вы хотите.
;WITH cteDefineRowPrecedence AS (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY CustomerId, FileName, Property ORDER BY
CASE WHEN Value IS NOT NULL THEN 0 ELSE 1 END
,UpdatedDtm DESC) as RowNum
FROM
@Table
)
, cteDesiredRwows AS (
SELECT
CustomerId
,FileName
,Property
,Value
FROM
cteDefineRowPrecedence t
WHERE
t.RowNum = 1
AND t.Value IS NOT NULL
)
SELECT *
FROM
cteDesiredRwows t
PIVOT (
MAX(Value)
FOR Property IN (Size,[Format],[State],Comment)
) p
ORDER BY
CustomerId
,FileName
А вот версия с вложенным запросом, которая облегчит встраивание / вставку в ваш динамический sql....
SELECT *
FROM
(
SELECT CustomerId, FileName, Property, Value
FROM
(SELECT *
,ROW_NUMBER() OVER (PARTITION BY CustomerId, FileName, Property ORDER BY
CASE WHEN Value IS NOT NULL THEN 0 ELSE 1 END
,UpdatedDtm DESC) as RowNum
FROM
@Table) r
WHERE
r.RowNum = 1
AND r.Value IS NOT NULL
) t
PIVOT (
MAX(Value)
FOR Property IN (Size,[Format],[State],Comment)
) p
ORDER BY
CustomerId
,FileName
Возможно, вам понадобится добавить условие WHERE в определение CTE, чтобы ограничить диапазон дат / времени тем, что вы хотите.
WITH CTE AS (
SELECT DISTINCT
CustomerID
, FileName
, Property
, Value
FROM
<table_name>
)
SELECT *
FROM
CTE
PIVOT (MAX(value) FOR Property IN( 'Size', 'Format', 'State', 'Comment')) p