如果在同一天可以找到多个记录,请选择一个值
有人建议我改进此子查询,以便能够根据以下计算获取单个记录,本帖子下面的子查询会带来以下结果:如果在同一天可以找到多个记录,请选择一个值
PERCENT ¦ LOG_DATE ¦ APP ¦ REQ_ID
55 ¦ 2017-02-07 15:44:22 ¦ HUO ¦ 253333
63 ¦ 2017-02-08 10:42:18 ¦ CQS ¦ 265265
75 ¦ 2017-02-08 06:55:12 ¦ CQS ¦ 265265
84 ¦ 2017-02-09 08:35:42 ¦ CQS ¦ 265265
40 ¦ 2017-02-09 09:45:14 ¦ PLK ¦ 277777
我想看到的只是结果,它具有每日期的最新日期(al.AU_TIME)内的记录。我们的目标是努力寻找具有'63'百分比值的记录。查询应该带来:
55 ¦ 2017-02-07 15:44:22 ¦ HUO ¦ 253333
63 ¦ 2017-02-08 10:42:18 ¦ CQS ¦ 265265
84 ¦ 2017-02-09 08:35:42 ¦ CQS ¦ 265265
40 ¦ 2017-02-09 09:45:14 ¦ PLK ¦ 277777
那么我应该怎么做,如果多个记录在同一天有相同的REQ_ID。
SELECT TO_NUMBER(RTRIM(ap.AP_NEW_VALUE,'%')) as PERCENT,
al.AU_TIME as LOG_DATE,
req.RQ_USER_03 as APP,
req.RQ_REQ_ID as REQ_ID
FROM AUDIT_PROPERTIES ap,
AUDIT_LOG al,
REQ
WHERE al.AU_ACTION_ID = ap.AP_ACTION_ID and
req.RQ_REQ_ID = al.AU_ENTITY_ID and
req.rq_req_date BETWEEN TO_DATE('05/02/2017','dd/mm/yyyy') AND TO_DATE('20/02/2017','dd/mm/yyyy') and
ap.AP_FIELD_NAME = 'RQ_USER_58'
在此先感谢。
这可以在单个聚合查询中完成(无子查询)。按req_id
和trunc(log_date)
分组,然后为每个组选择一条记录。 req_id
和max(log_date)
(每个组内的,即每一天内)是显而易见的。然后使用first/last
功能(keep (dense_rank...)
。你需要照顾的只是一点点,如果有可能有完全相同的日期和时间(精确到秒),对于一个给定req_id
两行。
下面是做这件事,我把你的测试和演示目的的WITH
条款提供的测试数据,但是这是查询的不一部分。
with
test_data (percent, log_date, app, req_id) as (
select 55, to_date('2017-02-07 15:44:22', 'yyyy-mm-dd hh24:mi:ss'), 'HUO', 253333
from dual union all
select 63, to_date('2017-02-08 10:42:18', 'yyyy-mm-dd hh24:mi:ss'), 'CQS', 265265
from dual union all
select 75, to_date('2017-02-08 06:55:12', 'yyyy-mm-dd hh24:mi:ss'), 'CQS', 265265
from dual union all
select 84, to_date('2017-02-09 08:35:42', 'yyyy-mm-dd hh24:mi:ss'), 'CQS', 265265
from dual union all
select 40, to_date('2017-02-09 09:45:14', 'yyyy-mm-dd hh24:mi:ss'), 'PLK', 277777
from dual
)
-- End of test data (not part of the solution). SQL query begins BELOW THIS LINE
select max(percent) keep (dense_rank last order by log_date) as percent,
max(log_date) as log_date,
max(app) keep (dense_rank last order by log_date, percent) as app,
req_id
from test_data
group by req_id, trunc(log_date)
order by log_date, req_id
;
PERCENT LOG_DATE APP REQ_ID
------- ---------- --- -------
55 2017-02-07 HUO 253333
63 2017-02-08 CQS 265265
84 2017-02-09 CQS 265265
40 2017-02-09 PLK 277777
4 rows selected.
这样做的典型方法是使用row_number()
。最好的,我可以在你的情况下猜测:
select a.*
from (select a.*,
row_number() over (partition by req_id order by log_date desc) as seqnum
from atable a
) a
where seqnum = 1;
*每日期*在叙述中,以及提供的测试数据和期望的结果,表明OP希望'trunc(log_date)'成为'partition by'和'req_id'的一部分。 – mathguy
您可以通过编写内部查询或创建一个中间表,通过只是时间组的数据做计算每个日期 内部查询会看的MAX_TIME像这样:
SELECT cast(al.AU_TIME as date) as LOG_DATE,
max(al.AU_TIME) as max_LOG_TIME
FROM AUDIT_PROPERTIES ap,
AUDIT_LOG al,
REQ
WHERE al.AU_ACTION_ID = ap.AP_ACTION_ID and
req.RQ_REQ_ID = al.AU_ENTITY_ID and
req.rq_req_date BETWEEN TO_DATE('05/02/2017','dd/mm/yyyy') AND TO_DATE('20/02/2017','dd/mm/yyyy') and
ap.AP_FIELD_NAME = 'RQ_USER_58'
GROUP BY LOG_DATE
再查询外层的需要与时间戳值这个内部查询结合,以获得只是这些记录
Select TO_NUMBER(RTRIM(ap.AP_NEW_VALUE,'%')) as PERCENT,
al.AU_TIME,
req.RQ_USER_03 as APP,
req.RQ_REQ_ID as REQ_ID
FROM AUDIT_PROPERTIES ap
inner join (
SELECT cast(al.AU_TIME as date) as LOG_DATE,
max(al.AU_TIME) as max_LOG_TIME
FROM AUDIT_PROPERTIES ap,
AUDIT_LOG al,
REQ
WHERE al.AU_ACTION_ID = ap.AP_ACTION_ID and
req.RQ_REQ_ID = al.AU_ENTITY_ID and
req.rq_req_date BETWEEN TO_DATE('05/02/2017','dd/mm/yyyy') AND TO_DATE('20/02/2017','dd/mm/yyyy') and
ap.AP_FIELD_NAME = 'RQ_USER_58'
GROUP BY LOG_DATE)
AS X
ON ap.AU_TIME= X.max_LOG_TIME
INNER JOIN AUDIT_LOG al
on al.AU_ACTION_ID = ap.AP_ACTION_ID
INNER JOIN REQ
ON req.RQ_REQ_ID = al.AU_ENTITY_ID
WHERE req.rq_req_date BETWEEN TO_DATE('05/02/2017','dd/mm/yyyy') AND TO_DATE('20/02/2017','dd/mm/yyyy') and
ap.AP_FIELD_NAME = 'RQ_USER_58'
我有“表”。您的样本代码有*三*表格。你真正的问题是什么? –
对不起我的英文不好,上面编辑。 – imi36