SQL:将本月的记录数附加为列/字段

Question

我正在使用 HiveQL 尝试查询 Hadoop。我的问题是这个。

假设我有一个这样的查询(和结果表):

SELECT CustomerID, PurchaseID, DateOfPurchase
FROM MyTableName;

+------------+------------+----------------+
| CustomerID | PurchaseID | DateOfPurchase |
+------------+------------+----------------+
|        101 |        501 | 2014-01-01     |
|        101 |        502 | 2014-01-15     |
|        101 |        503 | 2014-01-20     |
|        101 |        504 | 2015-01-19     |
|        101 |        505 | 2015-08-25     |
|        102 |        506 | 2014-01-02     |
|        102 |        507 | 2014-01-03     |
|        103 |        508 | 2016-05-05     |
+------------+------------+----------------+

我想添加另一列，表示客户每月的订单数量，与每一行中的日期相对应。这是我对结果表的想法:

+------------+------------+----------------+--------------------+
| CustomerID | PurchaseID | DateOfPurchase | PurchasesThisMonth |
+------------+------------+----------------+--------------------+
|        101 |        501 | 2014-01-01     |                  3 |
|        101 |        502 | 2014-01-15     |                  3 |
|        101 |        503 | 2014-01-20     |                  3 |
|        101 |        504 | 2015-01-19     |                  1 |
|        101 |        505 | 2015-08-25     |                  1 |
|        102 |        506 | 2014-01-02     |                  2 |
|        102 |        507 | 2014-01-03     |                  2 |
|        103 |        508 | 2016-05-05     |                  1 |
+------------+------------+----------------+--------------------+

也就是说，对于每一行，PurchasesThisMonth 列表示该客户在该月购买了多少商品。客户 101 在 2014 年 1 月进行了 3 次购买，因此 2014 年 1 月的每一行在 PurchasesThisMonth 中都有 3。

我能够在同一张表的子查询上使用 INNER JOIN 来实现它。但是考虑到我的数据集非常大，这需要相当长的时间。但是，有更好/更快的方法吗？

这是我的暴力破解方法。

SELECT CustomerID, PurchaseID, DateOfPurchase, Sub.PurchasesThisMonth
FROM MyTableName
INNER JOIN (
    SELECT 
    CustomerID, 
    COUNT(Inner.PurchaseID) as PurchasesThisMonth
    MONTH(Inner.DateOfPurchase) as month, 
    YEAR(Inner.DateOfPurchase) as year

    FROM MyTableName Inner
    GROUP BY Inner.CustomerID, 
             MONTH(Inner.DateOfPurchase), 
             YEAR(Inner.DateOfPurchase)
) Sub
ON CustomerID=Sub.CustomerID AND
   MONTH(DateOfPurchase)=Sub.month AND
   YEAR(DateOfPurchase)=Sub.year

此查询在完全相同的表上进行整个子查询。这是必要的吗？如果不是，这里的最佳做法是什么？

谢谢!

Answer 1

我认为您可以使用窗口函数 count() over()。

SELECT CustomerID, PurchaseID, DateOfPurchase
,count(*) over(partition by
               customerid,
               MONTH(DateOfPurchase), 
               YEAR(DateOfPurchase) order by customerid)
FROM MyTableName;