使用子查询连接配置单元中的两个表

vyswwuz2  于 2021-06-02  发布在  Hadoop
关注(0)|答案(1)|浏览(303)

我需要在某个特定的日期和时间得到一件物品的成本。我有两张table: create table sales ( product_id int, items_sold int, date_loaded date ); create table product ( product_id int, description string, item_cost double, date_loaded date ); 产品表是每个项目的历史记录。如果今天一件物品的价格是1美元,而昨天那件物品的价格是0.99美元,我会有两张记录,每天一张。当我加载我的销售数据时,我需要反映昨天的成本,而不是今天的成本。
下面是我尝试执行的查询: SELECT s.product_id, s.items_sold, p.description, s.items_sold * p.item_cost as total_cost FROM sales s, product p WHERE p.product_id = s.product_id and p.date_loaded <= ( SELECT MAX(pp.date_loaded) FROM product pp WHERE pp.product_id = s.product_id and pp.date_loaded <= s.date_loaded ) 销售表: |PRODUCT_ID |ITEMS_SOLD |DATE_LOADED | |1 |4 |2016-06-30 | |1 |5 |2016-07-01 | |1 |6 |2016-07-02 | |1 |3 |2016-07-03 | 产品表: |PRODUCT_ID |DESCRIPTION |ITEM_COST |DATE_LOADED | |1 |ITEM A |0.99 |2016-06-20 | |1 |ITEM A |1.00 |2016-07-02 | 我希望看到这样的结果: |PRODUCT_ID |ITEMS_SOLD |DESCRIPTION |ITEM_COST |TOTAL_COST | |1 |4 |ITEM A |0.99 |3.96 | |1 |5 |ITEM A |0.99 |4.95 | |1 |6 |ITEM A |1.00 |6.00 | |1 |3 |ITEM A |1.00 |3.00 | 据我所知,这种形式的子查询是不允许的。那么我怎样才能在Hive里完成这个任务呢?

pxy2qtax

pxy2qtax1#

它可以通过cte和lag-widow函数来实现

With result as(select PRODUCT_ID, DESCRIPTION, ITEM_COST , DATE_LOADED ,
     LEAD(DATE_LOADED, 1,'2999-01-01')
     OVER (ORDER BY DATE_LOADED) AS fromdate from PRODUCT ) 
     SELECT s.product_id, s.items_sold, p.description, s.items_sold * p.item_cost 
     as total_cost  FROM sales s join result p on s.product_id = p.product_id 
     where s.DATE_LOADED >= p.DATE_LOADED  and s.DATE_LOADED < p.fromdate  ;

相关问题