我是HQL的新员工,我有一张桌子:
shipmentday
D >> nr对应于该商人total_listingcount的SKU总数。

有一个简单的查询,以计算加权平均每个商人与HiveQL一样,我在Excel上做的?(由于发运日期可能不同,并非每个商户都有相同的行数)
发布于 2021-05-02 13:42:14
您需要使用sum() over(order by time rows between preceding 4 and current row)的窗口函数。
SELECT
merchantName,
merchantID,
shipmentday,
listing_count,
sum(shipmentday * listing_count) over(order by period rows between preceding 4 and current row) / sum(listing_count) over(order by period rows between preceding 4 and current row) as weighed_ma
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS period
FROM your_table
);注意:如果数据中有时间列,则可以忽略子查询。我正在生成period,因为您的示例中没有时间列。
如果窗口函数抛出错误,那么我假设您的Hive版本不支持所需的windows函数。或者,您可以使用CROSS JOIN。CROSS JOIN将原始表中的所有行映射到目标表中的所有行(将其视为一个没有任何键匹配的左联接)。
WITH a AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS period
FROM your_table
)
SELECT
merchantName,
merchantID,
shipmentday,
listing_count,
sum(shipmentday_b * listing_count_b) / sum(listing_count_b) AS weighted_ma
FROM (
SELECT
a.*,
b.shipmentday AS shipmentday_b,
b.listing_count AS listing_count_b
FROM a
CROSS JOIN a AS b
WHERE b.rn BETWEEN a.rn - 4 and a.rn
) a
GROUP BY merchantName, merchantID, shipmentday,listing_count;https://stackoverflow.com/questions/67356616
复制相似问题