时间戳

首页 > 解决方案 > 对不同的重复行求和

sum - 对不同的重复行求和

问题描述

tl/dr总结：3个有层次关系的表，中间有一个数字字段，需要那个数字的总和，因为下层不重复，在DB2中寻找使用OLAP函数的替代方案。

这在某种程度上重新审视了这两个主题（基于其他列的 SUM(DISTINCT)和基于 Distinct Guids 的 Sum Values）——但我将其作为一个单独的主题来讨论，因为我想知道是否有办法使用 OLAP 函数来实现这一点。

我在 DB2 中工作。场景（不是实际的表，由于客户机密性）是：

   Table: NEIGHBORHOOD, field NEIGHBORHOOD_NAME
   Table: HOUSEHOLD, fields NEIGHBORHOOD_NAME, HOUSEHOLD_NAME, and HOUSEHOLD_INCOME
   Table: HOUSEHOLD_MEMBER, fields HOUSEHOLD_NAME, PERSON_NAME

现在，我们已经通过一个单一的 flatten-it-all 视图提取了数据。所以我们会得到类似的东西

 Shady Acres, 123 Shady Lane, 25000, Jane
 Shady Acres, 123 Shady Lane, 25000, Mary
 Shady Acres, 123 Shady Lane, 25000, Robert
 Shady Acres, 126 Shady Lane, 15000, George
 Shady Acres, 126 Shady Lane, 15000, Tom
 Shady Acres, 126 Shady Lane, 15000, Betsy
 Shady Acres, 126 Shady Lane, 15000, Timmy

如果我想要

    Shady Acres, 123 Shady Lane, 25000, 3  (household income, count of members)
    Shady Acres, 125 Shady Lane, 15000, 4

没问题：

SELECT N.NEIGHBORHOOD_NAME, H.HOUSEHOLD_NAME, H.HOUSEHOLD_INCOME, count(1)
from NEIGHBORHOOD N join HOUSEHOLD H on N.HOUSEHOLD_NAME = H.HOUSEHOLD_NAME
join HOUSEHOLD_MEMBER M on H.HOUSEHOLD_NAME = M.HOUSEHOLD_NAME
group by N.NEIGHBORHOOD_NAME, H.HOUSEHOLD_NAME, H.HOUSEHOLD_INCOME

但是，如果我想要

   Shady Acres, 2, 40000, 7 (i.e. neighborhood, number of households, sum of income, count of members)

如相关链接所示，如果没有子查询，我将无法完成它。

到目前为止我得到的最好的是

select NEIGHBORHOOD.NEIGHBORHOOD_NAME,
count(distinct HOUSEHOLD.HOUSEHOLD_NAME) household_Count,
sum(distinct HOUSEHOLD.HOUSEHOLD_INCOME) total_income,
count(1) household_members group by N.NEIGHBORHOOD_NAME

当然，如果您有两个收入相同的家庭，这将行不通。坦率地说，我很惊讶“总和（不同）”甚至起作用，因为它对我来说没有意义。

我试过了

sum(household_income) over (partition by household.household_name)

它抛出了一个错误：

一个以“HOUSEHOLD_INCOME”开始的表达式‎ 在‎ a‎ SELECT‎ 子句‎、‎ HAVING在 GROUP BY‎ 子句‎ 或‎ it‎ 在‎ a‎ SELECT‎ 子句‎、‎ HAVING‎ 子句‎、 ‎ or‎ ORDER‎ BY‎ 子句‎ with‎ a‎ column‎ function‎ 和‎ no‎ GROUP‎ BY‎子句‎是‎指定‎。‎.‎ SQLCODE‎=‎-119‎,‎ SQLSTATE‎=42803‎,‎驱动器‎=4‎.19‎.56

尝试将 HOUSEHOLD_INCOME 或 HOUSEHOLD_NAME 添加到分组中会导致错误的结果，因为我们不想通过这些字段将其分开。

除了使用子查询之外，完全有可能没有解决方案，但是我们必须对底层视图进行一些重大的重新设计（包括添加额外的视图），所以我认为问这个问题不会有什么坏处。

标签： sumduplicatesdb2olappartition

解决方案

所以这将是一个非常hacky的解决方案，只要你在非规范化/重复数据的键上没有哈希冲突并且你没有比我推出哈希值的小数位数更多的重复项，它就会起作用SUM() 的方式

WITH NEIGHBORHOOD(NEIGHBORHOOD_NAME) AS (VALUES ('Shady Acres'))
,   HOUSEHOLD (NEIGHBORHOOD_NAME, HOUSEHOLD_NAME, HOUSEHOLD_INCOME)
AS (VALUES 
   ('Shady Acres', '123 Shady Lane', 25000)
  ,('Shady Acres', '126 Shady Lane', 25000) 
  )
, HOUSEHOLD_MEMBER ( HOUSEHOLD_NAME, PERSON_NAME )
AS(VALUES
      ('123 Shady Lane', 'Jane'  )
     ,('123 Shady Lane', 'Mary'  )
     ,('123 Shady Lane', 'Robert')
     ,('126 Shady Lane', 'George')
     ,('126 Shady Lane', 'Tom'   )
     ,('126 Shady Lane', 'Betsy' )
     ,('126 Shady Lane', 'Timmy' )    
)
SELECT
    NEIGHBORHOOD_NAME
,   COUNT(DISTINCT HOUSEHOLD_NAME  )  AS HOUSEHOLD_COUNT
,   BIGINT(SUM(DISTINCT DECFLOAT(HOUSEHOLD_INCOME || '.000000' || ABS(HASH4(HOUSEHOLD_NAME))))) AS TOTAL_INCOME
,   COUNT(1)                          AS HOUSEHOLD_MEMBERS
FROM NEIGHBORHOOD N
JOIN HOUSEHOLD    H     USING ( NEIGHBORHOOD_NAME )
JOIN HOUSEHOLD_MEMBER M USING ( HOUSEHOLD_NAME )
GROUP BY N.NEIGHBORHOOD_NAME

返回

 NEIGHBORHOOD_NAME  HOUSEHOLD_COUNT     TOTAL_INCOME    HOUSEHOLD_MEMBERS
 -----------------  ---------------     ------------    -----------------
 Shady Acres                      2        50000                    7

注意我让两个家庭的收入相同，以证明这个解决方案在那种情况下有效

我猜你可能会争辩说 SQL 缺少一些语法特性，其中使用 DISTINCT 关键字的 OLAP 函数应该能够定义与聚合的内容分开的 DISTINCT 内容。

推荐阅读