我有一个几乎与下面讨论的例子相同的情况。
选择日期最大值(日期)小于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来比较日期的值。
我怎样才能达到我想要的?
1条答案
按热度按时间zour9fqk1#
我在mysql数据库中测试了下面的sql语句,它可以工作。