黑斑羚:选择仅返回表 2 中 max(date) 但小于表 1 中日期的行的数据

Question

我的情况与以下线程中讨论的示例几乎相同。

选择日期最大(日期)小于 x 的数据

我有表格作为

s_currency

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_transaction

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) 并使用了限制 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日。

我什至尝试在子查询中使用 row_number() 函数，但它无法解析 t1.tdate 来比较日期的值。

我怎样才能实现我想要的？

Answer 1

我在mysql db中测试了以下SQL语句，它可以工作。

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

Answer 2

我遇到了类似的问题，因此将其发布在这里以供参考。

TL，DR：我们需要使用 Impala 的窗口函数来过滤子查询结果。下面是 SQL：

SELECT t.* FROM 
(
SELECT rank() OVER (ORDER BY cdate DESC) daterank, c1.cdate, t1.*
FROM s_currency c1, s_transaction t1
WHERE c1.currency = t1.currency 
AND c1.cdate <= t1.tdate
) t
WHERE t.daterank = 1;

详：

问题是 Impala 不支持与 coo 相关的子查询中的复杂操作。

来自 Impala 官方网站 ： https://impala.apache.org/docs/build/html/topics/impala_limit.html & https://impala.apache.org/docs/build/html/topics/impala_subqueries.html

• 限制

  1. EXISTS 和 IN 运算符中使用的相关子查询不能包含 LIMIT 子句
  2. 对于 EXISTS 和 NOT EXISTS 子句，将外部查询块中的值与另一个表进行比较的任何子查询都必须至少使用一个相等比较，而不是专门使用其他类型的比较，例如小于、大于、BETWEEN 或 ！=。

• 那么什么是相关子查询？

  相关子查询将外部查询块中的一个或多个值与子查询的 WHERE 子句中引用的值进行比较。外部 WHERE 子句计算的每一行都可以使用一组不同的值来计算。这些类型的子查询在它们可以在内部和外部表的列之间进行的比较类型方面受到限制

• 我发现 Impala的窗口函数在这种情况下很有用，尤其是使用限制(限制 1)或小于(限制 2)从子查询中超过最大等效项的排名

  https://impala.apache.org/docs/build/html/topics/impala_analytic_functions.html

Answer 3

您可以将 CTE 与 Lead 一起使用来创建每个货币比率有效的范围，然后在连接中不使用子查询来联接该范围。我发现这比使用排名要快得多。

WITH startend as (
SELECT
currency,
ratio,
cdate as starttime,
ISNULL( LEAD(cdate) OVER ( PARTITION BY currency ORDER BY cdate), '9999-12-31') endtime
FROM s_currency)
SELECT
tdate,
amount,
t.currency,
ratio,
c.starttime as cdate
FROM s_transaction t
INNER JOIN startend c
ON t.currency = c.currency
AND tdate >= starttime
AND tdate < endtime

不要忘记将所有连接条件添加到分区子句中。