sql - 稀疏数据 : efficient storage and retrieval in an RDBMS

Question

我有一个表示跨项目修订的源文件指标值的表，如下所示:

Revision FileA FileB FileC FileD FileE ...
1           45     3    12   123   124
2           45     3    12   123   124
3           45     3    12   123   124
4           48     3    12   123   124
5           48     3    12   123   124
6           48     3    12   123   124
7           48    15    12   123   124

(以上数据的关系 View 不同。每行包含以下列:Revision、FileId、Value。计算数据的文件及其修订存储在 Subversion 存储库中，因此我们试图在关系模式中表示存储库的结构。)

在 10000 个修订中最多可以有 23750 个文件(ImageMagick 绘图程序就是这种情况)。如您所见，大多数值在连续修订之间是相同的，因此该表的有用数据非常稀少。我正在寻找一种方法来存储

• 避免复制并有效地使用空间(目前非稀疏表示需要 260 GB(数据+索引)来存储不到 10% 的数据)
• 允许我使用 SQL 查询有效地检索特定修订的值(无需显式循环修订或文件)
• 允许我有效地检索特定指标值的修订。

理想情况下，解决方案不应依赖于特定的 RDBMS并且应该与 Hibernate 兼容.如果这不可能，我可以忍受使用 Hibernate、MySQL 或 PostgreSQL 特定的功能。

Answer 1

这就是我可能建模的方式。我省略了 Revisions 表和 Files 表，因为它们应该是不言自明的。

CREATE TABLE Revision_Files
(
    start_revision_number   INT NOT NULL,
    end_revision_number     INT NOT NULL,
    file_number             INT NOT NULL,
    value                   INT NOT NULL,
    CONSTRAINT PK_Revision_Files PRIMARY KEY CLUSTERED (start_revision_number, file_number),
    CONSTRAINT CHK_Revision_Files_start_before_end CHECK (start_revision_number <= end_revision_number)
)
GO

要获取特定修订版文件的所有值，您可以使用以下查询。使用外部联接联接到文件表将使您获得那些没有为该修订定义的值。

SELECT
    REV.revision_number,
    RF.file_number,
    RF.value
FROM
    Revisions REV
INNER JOIN Revision_Files RF ON
    RF.start_revision_number <= REV.revision_number AND
    RF.end_revision_number >= REV.revision_number
GO

假设我正确理解了您在第三点中想要的内容，这将使您获得特定文件具有特定值的所有修订:

SELECT
    REV.revision_number
FROM
    Revision_Files RF
INNER JOIN Revisions REV ON
    REV.revision_number BETWEEN RF.start_revision_number AND RF.end_revision_number
WHERE
    RF.file_number = @file_number AND
    RF.value = @value
GO