时间戳

首页 > 解决方案 > 如何从非规范化表中计算非膨胀 SUM

sql - 如何从非规范化表中计算非膨胀 SUM

问题描述

这是建立在我之前提出的问题的基础上的。假设我有一个看起来像这样的非规范化表：

    Apple_ID | Tree_ID | Orchard_ID | Tree_Height | ...other columns...
   ---------------------------------------------------------------------
           1 |       1 |          1 |          12 |  ...other values...
   ---------------------------------------------------------------------
           2 |       1 |          1 |          12 |  ...other values...
   ---------------------------------------------------------------------
           3 |       1 |          1 |          12 |  ...other values...
   ---------------------------------------------------------------------
           4 |       2 |          1 |          15 |  ...other values...
   ---------------------------------------------------------------------
           5 |       2 |          1 |          15 |  ...other values...
   ---------------------------------------------------------------------
           6 |       2 |          1 |          15 |  ...other values...
   ---------------------------------------------------------------------
           7 |       2 |          1 |          15 |  ...other values...
   ---------------------------------------------------------------------
           8 |       3 |          1 |          20 |  ...other values...
   ---------------------------------------------------------------------
           9 |       3 |          1 |          20 |  ...other values...
   ---------------------------------------------------------------------
          10 |       4 |          2 |          30 |  ...other values...
   ---------------------------------------------------------------------
          11 |       5 |          2 |          10 |  ...other values...
   ---------------------------------------------------------------------
          12 |       5 |          2 |          10 |  ...other values...
   ---------------------------------------------------------------------
          13 |       5 |          2 |          10 |  ...other values...
   ---------------------------------------------------------------------

我想计算每个果园中 Tree_Heights 的总和，所以我想得到的结果是：

Orchard_ID | sum(Tree_Height)
-------------------------------
         1 |               47
-------------------------------
         2 |               40

但是，由于非规范化，总和膨胀为：

Orchard_ID | sum(Tree_Height)
-------------------------------
         1 |              136
-------------------------------
         2 |               60

我之前提到的问题的解决方案无法在这里实现，因为我们不能通过唯一的行列 sum()。如何编写一个简单的查询来获得预期的结果？

标签： sqlpostgresqldenormalization

解决方案

最简单的编写方法是使用CTE，但如果您的系统不支持，您可以使用派生表。我们使用ROW_NUMBER()forApple_ID每棵树来给我们一个唯一的行来求和：

SELECT "Orchard_ID", SUM("Tree_Height") AS Total_Height
FROM (
  SELECT "Orchard_ID", "Tree_Height", 
         ROW_NUMBER() OVER (PARTITION BY "Orchard_ID", "Tree_ID" ORDER BY "Apple_ID") AS rn
  FROM data
) d
WHERE rn = 1
GROUP BY "Orchard_ID"

输出

Orchard_ID  total_height
1           47
2           40

如果你可以使用CTEs 这就是它的写法：

WITH CTE AS (
  SELECT "Orchard_ID", "Tree_Height", 
         ROW_NUMBER() OVER (PARTITION BY "Orchard_ID", "Tree_ID" ORDER BY "Apple_ID") AS rn
  FROM data
)
SELECT "Orchard_ID", SUM("Tree_Height") AS Total_Height
FROM CTE
WHERE rn = 1
GROUP BY "Orchard_ID"

SQLFiddle 上的演示

推荐阅读