使用时间戳记SQL Redshift的最大阶段 [英] Max stage using timestamp SQL Redshift

问题描述

我想找到与每个application_id的最大exited_on相关的记录(以下示例表).

I'd like to find the record associated with the max exited_on per application_id (sample table below).

我从下面的SQL开始，但是收到一条错误消息，告诉我子查询的列太多.

I started out with the following SQL but get an error message telling me that my subquery has too many columns.

SELECT *
FROM application_stages
where application_stages.application_id = '91649746' and 
(application_stages.application_id, max(exited_on) in (select application_stages.application_id, max(exited_on) from application_stages group by application_stages.application_id))

表1

+----------------+-------+--------------------+----------------+------------------+------------------+
| requisition_id | order |     stage_name     | application_id |    entered_on    |    exited_on     |
+----------------+-------+--------------------+----------------+------------------+------------------+
| a              |     0 | Application Review |       91649746 | 6/8/2018 18:27   | 8/28/2018 22:04  |
| a              |     1 | Recruiter Screen   |       91649746 | 6/8/2018 18:27   | 6/21/2018 0:17   |
| a              |     2 | Phone Interview    |       91649746 | 6/21/2018 0:17   | 7/18/2018 12:17  |
| a              |     3 | Assessment         |       91649746 |                  |                  |
| a              |     4 | Interview          |       91649746 |                  |                  |
| a              |     5 | Interview 2        |       91649746 |                  |                  |
| a              |     6 | Interview 3        |       91649746 |                  |                  |
| a              |     7 | Offer              |       91649746 |                  |                  |
| a              |     0 | Application Review |       91991364 | 6/13/2018 14:21  | 6/19/2018 23:56  |
| a              |     1 | Recruiter Screen   |       91991364 | 6/19/2018 23:56  | 9/4/2018 14:01   |
| a              |     2 | Phone Interview    |       91991364 |                  |                  |
| a              |     3 | Assessment         |       91991364 |                  |                  |
| a              |     4 | Interview          |       91991364 |                  |                  |
| a              |     5 | Interview 2        |       91991364 |                  |                  |
| a              |     6 | Interview 3        |       91991364 |                  |                  |
| a              |     7 | Offer              |       91991364 |                  |                  |
| b              |     0 | Application Review |       96444221 | 8/8/2018 16:59   | 8/14/2018 5:42   |
| b              |     1 | Recruiter Screen   |       96444221 | 8/14/2018 5:42   | 10/16/2018 20:02 |
| b              |     2 | Phone Interview    |       96444221 |                  |                  |
| b              |     3 | Interview          |       96444221 | 10/16/2018 20:02 | 10/24/2018 4:27  |
| b              |     4 | Interview 2        |       96444221 | 10/24/2018 4:27  | 11/5/2018 22:38  |
| b              |     5 | Offer              |       96444221 |                  |                  |
+----------------+-------+--------------------+----------------+------------------+------------------+

推荐答案

如所指出的，IN仅适用于单列子查询.要完成所需的操作，可以使用联接:

As pointed out, IN works just with a single column subquery. To accomplish what you want, you can use a join:

SELECT t1.*
FROM application_stages t1
JOIN (
    select application_id, max(exited_on) as exited_on
    from application_stages 
    group by application_id
) t2 
USING (application_id,exited_on)

这篇关于使用时间戳记SQL Redshift的最大阶段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！