快速查询最新记录的方法？

Question

我有一个这样的表：

USER |  PLAN |  START_DATE  |   END_DATE
1    |  A    |  20110101    |   NULL
1    |  B    |  20100101    |   20101231
2    |  A    |  20100101    |   20100505

如果 END_DATE 为 null，则意味着该用户当前有该计划处于活动状态。

我想查询的是： (a) 他当前正在执行的计划，或 (b) 他最近执行的计划。我只需要为每个给定用户返回一行。

现在，我设法通过使用联合和子查询来做到这一点，但碰巧表很大并且这些效率不够高。 你们中有人有更快的方法来查询吗？

谢谢，

[编辑] 这里的大多数答案都会返回一个值。那是我的错。我的意思是为每个用户返回一个值，但同时返回所有用户。我已经调整了我能给出的答案（并纠正了问题），但只是为了将来的参考而清楚地说明了这一点。

Answer 1

如果没有有关数据和表格的进一步信息，这个问题有点难以回答。当您在评论中说您拥有所需的所有索引时，这些索引是什么？

另外，时间段是否相邻且不重叠？您能否获取最新 START_DATE 的时间段？

查看 END_DATE 的问题是普通的 B 树索引不会索引 NULL。因此，where end_date is nulll 形式的谓词不太可能使用索引。您可以对列使用位图索引，因为这些类型的索引会对空值进行索引，但这可能并不理想，因为位图索引还有一些其他缺点。

由于上面给出的原因，我可能会使用类似于以下查询的查询：

select user, plan, start_date, end_date
from (
  select 
    user, 
    plan, 
    start_date, 
    end_date, 
    row_number() over (partition by user order start_date desc) as row_num_1,
    row_number() over (partition by user order end_date desc nulls first) as row_num_2
  from user_table
  where user = :userid
)
where row_num_1 = 1

根据具体要求，您可以在此处使用 row_num_1 或 row_num_2 列。

或者，

select user, plan, start_date, end_date
from (
  select 
    user, 
    plan, 
    start_date, 
    end_date, 
  from user_table
  where user = :userid
  order by start_date desc
)
where rownum = 1

无论您是尝试恢复所有用户还是仅恢复一个用户，第一个查询都应该有效。第二个查询仅适用于一个用户。

如果您可以使用架构的更多详细信息（索引、开始/结束日期的含义）来补充问题，您可能会得到更好的答案。

Answer 2

CREATE TABLE XY
( USERID      INTEGER                 NOT NULL
, PLAN        VARCHAR2(8)             NOT NULL
, START_DATE  DATE                    NOT NULL
, END_DATE    DATE                    )
  TABLESPACE USERS;


INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 1, 'A', To_Date('22-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('22-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 1, 'B', To_Date('01-04-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), NULL );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 2, 'A', To_Date('03-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('04-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 2, 'B', To_Date('15-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('20-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
COMMIT WORK;

SELECT USERID, PLAN, END_DATE, START_DATE
  FROM (SELECT USERID,
               PLAN,
               END_DATE,
               START_DATE,
               ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY END_DATE DESC) SEQUEN
          FROM XY)
 WHERE SEQUEN < 2

Answer 3

这可能会有所帮助：

SELECT user,plan,end_date,start_date 
FROM ( SELECT users,plans,end_date,start_date, DENSE_RANK() OVER ( PARTITION BY user 
                                                                   ORDER BY end_date DESC) sequen 
        FROM table_name 
     ) 
WHERE sequen <= 2

Answer 4

您是否尝试过使用 rownum 来限制结果集？

select  plan
from    (
        select  plan
        from    YourTable
        where   User = 1
        order by
                case when end_date is null then '99991231' else end_date end desc
        )
where   rownum < 2

Answer 5

AFAIK 使用 CASE 和子查询会导致查询变得非常慢。所以最好小心使用它们。怎么样：

SELECT User, Plan, start_Date, MAX(End_Date) FROM Plans WHERE User NOT IN 
(SELECT User FROM Plans WHERE End_Date IS NULL)
GROUP BY Start_Date, Plan, User  
UNION  
SELECT User,Plan,Start_Date FROM Plans WHERE End_Date IS NULL

我不是 SQL 专家。将此视为一个建议。
希望这有帮助。

Answer 6

这有效吗？

SELECT U.user 
,(SELECT Plan FROM t WHERE t.user=u.user AND end_date IS NULL LIMIT 1) AS Current_Plan
,(SELECT Plan FROM t WHERE t.user=u.user AND end_date IS NOT NULL ORDER BY end_date DESC LIMIT 1) AS Last_Plan
FROM 
( SELECT DISTINCT USER FROM t ) AS U

如果速度很慢，请将查询的 EXPLAIN 输出发送给我们。

Answer 7

这个怎么样？

select PLAN
from USER_TABLE
where END_DATE is null or END_DATE = (
        select max(END_DATE)
        from USER_TABLE
        where USER = 1 and END_DATE is not null)
    and USER = 1

Answer 8

我建议如下：

with t as 
(select 1 as col_id, 1 as USER_id, 'A' as PLAN , 20110101 as START_DATE, NULL as  END_DATE from dual union all
 select 2,1,'B', 20100101,20101231 from dual union all
 select 3,2,'A', 20100102,20100505 from dual union all
 select 4,2,'C', 20100101,20100102 from dual)
--
SELECT user_id, plan
  FROM (SELECT user_id,
               plan,
               MAX(nvl(END_DATE, 99999999)) over(PARTITION BY user_id) max_date,
               nvl(END_DATE, 99999999) END_DATE
          FROM t)
 WHERE max_date = end_date