sql - 双 "group by"没有加入？

Question

我有用户数据:

user  store  item  cost
1     10     100   5
1     10     101   3
1     11     102   7
2     10     101   3
2     12     103   4
2     12     104   5

我想要一张表，它会告诉我每个用户他从每家商店购买了多少以及他总共购买了多少:

user store  cost_this_store  cost_total
1    10     8                15
1    11     7                15
2    10     3                12
2    12     9                12

我可以用两个 group by 和一个 join 来做到这一点:

select s.user, s.store, s.cost_this_store, u.cost_total
from (select user, store, sum(cost) as cost_this_store
      from my_data
      group by user, store) s
join (select user, sum(cost) as cost_total
      from my_data
      group by user) u
on s.user = u.user

但是，如果我用任何其他语言编写它，我绝对不会这样做(join 显然是可以避免的，而两个 group by 则不是独立)。
是否可以避免 sql 中的 join？

附言。我需要在 hive 中工作的解决方案。

Answer 1

您可以使用 windowing function 来做到这一点... Hive 去年增加了支持:

select distinct
  user, 
  store, 
  sum(cost) over (partition by user, store) as cost_this_store,
  sum(cost) over (partition by user) as cost_total
from my_data

但是，我认为您最初的实现没有任何明显的错误。您基本上得到了两组不同的数据，您通过 JOIN 将它们合并。

重复可能看起来像是另一种语言的代码味道，但这不一定是 SQL 中的错误方法，而且通常您必须采用这样的方法，在两个中间体之间复制查询的一部分出于性能原因的结果集。

SQL Fiddle (SQL Server)