sql-server - 在 SQL 中聚合通用列

Question

我们有一个表格，用于从连接到嵌入式计算机的传感器收集计数。我有一个具有以下架构的表:

CREATE TABLE Demo (
  PKColumn BIGINT
  , FKColumn INT
  , ...
  , EventDateTime DATETIME
  , Count1 INT
  , Count2 INT
  , Count3 INT
  , Count4 INT
)

问题是，对于 FKColumn = 1，净计数为 (Count1 + Count3) - (Count2 + Count4)。但是对于 FKColumn = 2，净计数将为 (Count2 + Count4) - (Count1 + Count3)。数据从嵌入式计算机流入，根据该计算机上传感器的校准方式，我们可以对每个计数的含义获得不同的定义。

幸运的是，一旦安装了传感器，它们就永远不会改变。

不幸的是，在传感器的安装方式方面缺乏控制……这就是问题所在。

由于每个 FKColumn 值的每个列的定义可能不同，因此构建聚合函数的最高效方法是什么。我正在考虑一个映射表来定义每个 FKColumn 的每个传感器是什么，但是当我构建该解决方案时，看起来每次计算的查找成本都很高。

数据库在 Azure 中运行，因此没有 CLR 函数。

有没有其他人遇到过这种情况？你是怎么解决的？

Answer 1

像这样创建一个映射表:

CREATE TABLE Map
(
  PKColumn INT NOT NULL 
  , Count1 INT NOT NULL 
  , Count2 INT NOT NULL 
  , Count3 INT NOT NULL 
  , Count4 INT NOT NULL 
)

根据您的示例，填充:

INSERT INTO MAP (Count1, Count2, Count3, Count4)
VALUES (1, -1, 1, -1)

INSERT INTO MAP (Count1, Count2, Count3, Count4)
VALUES (-1, 1, -1, 1)

然后创建一个 View :

CREATE View DemoSum
AS
SELECT CountSum = (m.Count1*d.Count1 + m.Count2*d.Count2 + 
                   m.Count3*d.Count3 + m.Count4*d.Count4)
       , d.PKColumn
       , d.FKColumn
FROM Demo d
INNER JOIN Map m ON m.PKColumn = d.FKColumn

[您可能需要考虑 Demo 表计数列中的空值。]