如何透过sql数据，并按每个ID的日期将结果压缩到非空行

这个帖子不是一个好头衔，但希望它会引起一些注意 .

我在T-SQL中遇到了一个非常复杂的情况，我无法完成 . 我'm hoping someone with expertise knows an elegant and fast solution so that my performance is not impacted. I' m处理数十亿行 .

PREFACE

我有一个名为 Customers 的表，其中包含唯一的ID . 这些客户有 Files ，文件有属性，每个属性 Name 对应一个 Value .

Tables:

• 客户

• 档案 -

• 属性 - 包含名称和值

Customer ID 出现在所有这些表中，审计字段如 UpdatedDtm 和 CreationDtm 也是如此 .

USE CASE

我需要将所有客户加入他们的文件（过滤一些），然后将每个文件绑定到他们的属性（再次过滤这些文件） . 这很容易，但会产生很多行，每个行一个 customer x file x property .

我知道属性名称永远不会改变，我想只返回少数几个，所以我使用了一个数据透镜并产生了一个漂亮的表，但是在我开始执行更复杂的查询后它就崩溃了 .

THE PROBLEM 首先，属性具有更改它们的日期时间（ UpdatedDtm ），并且我需要在File表中返回从创建日期的1小时（ CreationDtm ）更改的所有内容 .

这导致我减少了我的潜在属性列表，但是现在我有一个每个ID为 RowNumber() 的表，并且没有好的方法来转动并选择第一个使用 Dynamic SQL 并且将它放在索引的临时表中 . Composite Key 在 CustomerID 和 FileName .

BEFORE 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           |

请不要问我为什么以这种方式设置数据库或更改架构 . 这是一成不变的，是我无法控制的 . 我需要能够解决所描述的用例 .

AFTER PIVOT (Expectation)

| 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值和缺失值来展示我需要保留相同的柱状属性而不管它们是否有数据，但我还需要按照日期范围内的第一个非null值来压缩数据 .

CODE (My attempt)

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

等等...

我一直在努力解决这个特殊情况一段时间，我即将废弃它，它做了一些程序化的循环，但这会杀死我的查询时间和性能 .

谁有一个很好的解决方案？我是否过度思考？