impala:选择只返回表2中最大(日期)但小于表1中日期的行的数据

mwecs4sa  于 2021-06-26  发布在  Impala
关注(0)|答案(1)|浏览(494)

我有一个几乎与下面讨论的例子相同的情况。
选择日期最大值(日期)小于x的数据
我也有table
新加坡货币

cdate                          ratio                     currency                                            
-------------------------------------------------------------------
2017-06-06 00:00:00.0          1                         USD                                                 
2017-06-05 00:00:00.0          1                         USD                                                 
2017-06-04 00:00:00.0          1                         USD

s\交易

tdate                          amount                    currency                                            
-------------------------------------------------------------------
2017-06-05 00:00:00.0          100                       USD                                                 
2017-06-08 00:00:00.0          55                        USD      
2017-06-08 00:00:00.0          55                        USD      
2017-06-08 00:00:00.0          60                        USD

我想要的结果是

tdate                          amount    currency    ratio     cdate                                          
--------------------------------------------------------------------------
2017-06-05 00:00:00.0          100        USD          1      2017-06-05 00:00:00.0                                
2017-06-08 00:00:00.0          55         USD          1      2017-06-06 00:00:00.0
2017-06-08 00:00:00.0          55         USD          1      2017-06-06 00:00:00.0    
2017-06-08 00:00:00.0          60         USD          1      2017-06-06 00:00:00.0

如果cdate应以tdate为基础,则其最新货币日期等于或早于交易日期。
另一篇文章中的解决方案在select子句中使用子查询,但在impala中不起作用。我尝试使用cte和子查询连接,但没有一个返回所需的结果。下面是我构建的一些查询及其结果

SELECT tdate, amount, t1.currency, ratio, cdate FROM s_transaction t1 , s_currency t2 
        WHERE t1.currency = t2.currency AND 
        t2.cdate = (select max(cdate) from s_currency 
                     where currency = t1.currency and cdate <= t1.tdate);

但是它连接表并返回所有货币的事务,少于事务日期,所以我得到

tdate                         amount       currency     ratio        cdate
------------------------------------------------------------------------------------------
2017-06-08 00:00:00.0          60           USD          1            2017-06-05 00:00:00.0          
2017-06-08 00:00:00.0          60           USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          60           USD          1            2017-06-04 00:00:00.0          
2017-06-05 00:00:00.0          100          USD          1            2017-06-05 00:00:00.0          
2017-06-05 00:00:00.0          100          USD          1            2017-06-04 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-05 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-04 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-05 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-04 00:00:00.0

所以,我去掉了max(cdate)并使用了order by with limit 1,但是impala抛出了一个错误,即它不支持的相关子查询带有limit子句。
我试着用cte写

with lastupdate as (
select t2.currency, ratio, max(cdate) as cdate from s_currency t2 join s_transaction t1
on cdate <= tdate and t2.currency = t1.currency group by t2.currency, ratio limit 1
) select t11.*, lst.ratio, lst.cdate
from s_transaction t11 join lastupdate lst

但在这里,cte选择一个值并将其用于所有事务,因此

tdate                         amount       currency     ratio        cdate
-------------------------------------------------------------------------------------------
2017-06-05 00:00:00.0          100          USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          60           USD          1            2017-06-06 00:00:00.0          
2017-06-08 00:00:00.0          55           USD          1            2017-06-06 00:00:00.0

凡于6月5日成交的,应有一个截至6月5日的cdate。
我甚至尝试在子查询中使用row\ u number()函数,但它无法解析t1.tdate来比较日期的值。
我怎样才能达到我想要的?

zour9fqk

zour9fqk1#

我在mysql数据库中测试了下面的sql语句,它可以工作。

select  tdate,amount,b.currency,ratio,max(cdate) from s_transaction a,s_currency b where a.tdate>=b.cdate group by tdate

相关问题