我正在使用Hive数据库并尝试执行以下操作:
有一个事务表,带有date/store/product/movetype/stock(这意味着要找到某一天某个商店中某个产品的库存值,必须选择DATE,STORE,PRODUCT,SUM(stock_value)GROUP BY 1,2,3,因为一天中可能有许多产品移动,最终的库存值由它们的总和组成)。
我目前的目标是找到过去14天股票的最小值,并将其作为一个字段添加到股票表中,以查看股票价值在过去两周内是否遭受了任何痛苦的下跌。
试过这个:select min(stock_value) over (partition by store, product order by calendar_date range between 13 preceding and current row)
但是,正如我前面所说,该表包括几个不同的'产品运动类型',其中一些是负数。所以这意味着我得到的不是最小的day值,而是(看起来)这14天中每一行的最小值。寻找一种方法来总和值作为窗口函数的第一步,并找到这些多个总和的最小值作为第二步。
我知道有一些方法可以使用子查询或CTE来实现这个目标,但是我相信窗口函数会更有效,如果有可能在这种情况下使用它的话。
在此先谢谢您!
1条答案
按热度按时间tyu7yeag1#
听起来你在正确的轨道上使用窗口函数来实现你的目标。但是,由于您希望首先对每天的股票值求和,然后在14天的窗口中找到这些总和的最小值,因此您需要执行聚合,然后应用窗口函数。不幸的是,Hive的窗口函数不支持直接在窗口框架内聚合。
要获得所需的结果,可以使用公用表表达式(CTE)首先聚合每天的股票值,然后应用窗口函数查找指定天数范围内的最小值。下面是如何构造查询:
在此查询中,CTE
daily_stock_sums
计算每天、商店和产品的库存值之和。然后,在主查询中,对CTE的结果应用窗口函数,以查找前14天的最小总和,按商店和产品划分。虽然使用CTE似乎不那么直观,但这种方法应该是有效的,特别是如果您有一个大型数据集,因为聚合在CTE中完成一次,然后窗口函数对聚合的数据进行操作。
替代解决方案:
在此查询中,内部子查询执行初始聚合,以计算每天、商店和产品的库存值之和。此外,在该子查询中,使用
MIN
函数和前14天的窗口框架来查找该期间每个商店和产品组合的最小总和。然后,外部查询使用子查询的结果来应用最终的窗口函数,该函数为每个商店和产品组合获取最近14天的最小库存值总和。
让我知道如果它工作了!:)