在where子句中使用子查询从表中选择第二高的日期

stackq 发表于 Dev

Stackq

我需要做（用伪代码）

where yyyy_mm_dd >= '2019-02-01' 
and yyyy_mm_dd <= second highest date in a table

为此，我使用了以下代码：

where
    p.yyyy_mm_dd >= "2019-02-02"
    and p.yyyy_mm_dd <= (select max(yyyy_mm_dd) from schema.table1 where yyyy_mm_dd < (select max(yyyy_mm_dd) from schema.table1 where yyyy_mm_dd is not null))

上面的代码在包装时有效，spark.sql()但是当我运行不带Spark的查询（即作为原始HQL）时，遇到了此错误：

编译语句时出错：失败：ParseException行102：25无法识别表达式规范中'select''max''（'附近的输入

我试图通过别名子查询中的所有列来解决此问题：

where
    p.yyyy_mm_dd >= "2019-02-02"
    and p.yyyy_mm_dd <= (select max(t1.yyyy_mm_dd) from schema.table1 t1 where t1.yyyy_mm_dd < (select max(t2.yyyy_mm_dd) from schema.table2 t2 where t2.yyyy_mm_dd is not null))

虽然，我仍然遇到相同的错误。

编辑以包括样本数据和查询：

表格1：

| yyyy_mm_dd | company_id | account_manager |
|------------|------------|-----------------|
| 2020-11-10 | 321        | Peter           |
| 2020-11-09 | 632        | John            |
| 2020-11-08 | 598        | Doe             |
| 2020-11-07 | 104        | Bob             |
| ...        | ...        | ...             |
| ...        | ...        | ...             |

表2：

| yyyy_mm_dd        | company_id | tier   |
|-------------------|------------|--------|
| 2020-11-10        | 321        | Bronze |
| 2020-11-09        | 632        | Silver |
| 2020-11-08        | 598        | Gold   |
| 2020-11-07        | 104        | Bob    |
| ...               | ...        | ...    |
| ...               | ...        | ...    |
| 2019_12_13_backup | 321        | Bronze |
| 2019_12_13_backup | 632        | Silver |
| ...               |            |        |

查询：

select
    p.yyyy_mm_dd,
    p.company_id,
    p.account_manager,
 t.tier
from
    table1 p
left join(
    select
        yyyy_mm_dd,
        company_id,
        max(tier) as tier
    from 
        table2
    where
        yyyy_mm_dd >= "2019-02-02"
    group by
        1,2
) t on (t.company_id = p.company_id and t.yyyy_mm_dd = p.yyyy_mm_dd)

where
    p.yyyy_mm_dd >= "2019-02-02"
    and p.yyyy_mm_dd <= (select max(yyyy_mm_dd) from table2 where yyyy_mm_dd < (select max(yyyy_mm_dd) from table2 where yyyy_mm_dd is not null))

正如列中所table2包含backup_2019_12_31的yyyy_mm_dd那样max()，对表进行操作时将返回这些行。因此，我需要获取第二高的值，这是从此处的数据集中获得的2020-11-10。company_ids每个有多个yyyy_mm_dd。

从本质上讲，我想询问table1哪里yyyy_mm_dd是与table1起点（硬编码2019-02-02），并从真正的最大日期table2

左连接

要从table3获得第二高的日期，可以使用density_rank。日期第二高的所有行将被分配rn = 2。使用LIMIT获得单行或使用max（）或同一行的不同聚合，然后将表与max_date和filter交叉连接。

with max_date as(
select yyyy_mm_dd
from
(
select yyyy_mm_dd, 
       dense_rank() over(order by yyyy_mm_dd desc) rn
 from table2
)s 
where rn=2 --second max date
limit 1    --need only one record
)

select t1.*   
   from table1 t1
        cross join max_date t2
 where t1.yyyy_mm_dd <= t2.yyyy_mm_dd

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。