分组依据，分组依据，子查询 - 性能获取前一行值的问题

I am using MS ACCESS 2003

我正在使用MS ACCESS 2003

TABLE NAME –> tmp_cardevent

表名 - > tmp_cardevent

PERSONID   CARDEVENTDATE  CARDEVENTTIME

5008       20090805       080000
5008       20090805       140000
5008       20090809       180000
5008       20090809       220000
3405       20090805       080000
3405       20090805       180000
3405       20090809       070000
3405       20090809       230000
3010       20080806       090000
3010       20080806       230000
3010       20080810       100000
3010       20080810       160000

I want to display Today time and previous day time for the person id Previous day means not yesterday, previous cardeventtime for the particular person id.

我想显示今天人员ID的时间和前一天时间前一天意味着不是昨天,特定人员ID的前一个cardeventtime。

I make the following query for getting previous row value

我为获取前一行值进行了以下查询

select t1.Personid, 
    t1.cardeventdate, 
    t1.cardeventtime, 
    t2.Personid,  
    t2.cardeventdate,
    t2.cardeventtime 
from  tmp_cardevent  t1 inner join tmp_cardevent t2 on t1.cardno = t2.cardno 
where t2.cardeventdate =  (
    select max(cardeventdate)  
    from tmp_cardevent ds 
    where ds.cardeventdate < t1.cardeventdate
        and ds.cardno = t1.cardno 
    )

From the above query previous row is displaying perfectly

从上面的查询中,上一行显示完美

Expected Output

PERSONID   CARDEVENTDATE      LastCARDEVENTDATE  

5008       20090809           20090805       
3405       20090809           20090805       
3010       20080810           20080806

But if am using group by, order by, sub queries in the above query, it is taking to much time nothing displaying, because it's so bad in performance in huge amount of data

但是如果在上面的查询中使用group by,order by子查询,则会花费很多时间来显示,因为它在大量数据中的性能如此糟糕

So can anyone help me to get the best solution for such a problem?

那么任何人都可以帮我找到解决这个问题的最佳解决方案吗?

or any other query help?

或任何其他查询帮助?

2 个解决方案

#1

SELECT
   Seq = identity(int, 1, 1),
   CardNo,
   CardEventDate
INTO #CardSeq
FROM tmp_cardevent
ORDER BY CardNo, CardEventDate

SELECT
   t1.Personid,
   t1.cardeventdate,
   t1.cardeventtime,
   t2.Personid,
   t2.cardeventdate,
   t2.cardeventtime
from
   tmp_cardevent t1
   inner join #CardSeq S1 ON t1.CardNo = S.CardNo
   left join #CardSeq S2 ON t1.CardNo = t2.CardNo and t1.Seq - 1 = t2.Seq
   left join tmp_cardevent t2 on t1.cardno = t2.cardno

DROP TABLE #CardSeq

Putting an index on the temp table on CardNo and/or Seq should help. Creating the temp table with the indexes before filling it is probably better than adding the indexes after using SELECT INTO. Experiment with a clustered index only on each column, then clustered on one column + nonclustered on the other and vice versa to see what gives the best performance.

在CardNo和/或Seq上的临时表上放置索引应该会有所帮助。在填充之前使用索引创建临时表可能比使用SELECT INTO后添加索引更好。仅在每列上试验聚簇索引,然后在一列上聚集+在另一列上聚簇,反之亦然,以查看哪些列表提供了最佳性能。

If you have conditions on the query to limit the card numbers, do this on the insert to the temp table so it only works on what's necessary.

如果您在查询上有条件限制卡号,请在插入临时表中执行此操作,以便它仅适用于必要的操作。

The left joins are needed otherwise the first event will never show up.

需要左连接,否则第一个事件将永远不会出现。

#2

You need to have indexes on your grouped and ordered columns.

您需要在分组和有序列上具有索引。

e.g. of creating index on column Last_Name in the Customer table. CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)

例如在Customer表中的Last_Name列上创建索引。 CUSTOMER上的CREATE INDEX IDX_CUSTOMER_LAST_NAME(Last_Name)

Similarly you can create indices on the columns used in your group by and order clauses.

同样,您可以在group by和order子句中使用的列上创建索引。

#1

SELECT
   Seq = identity(int, 1, 1),
   CardNo,
   CardEventDate
INTO #CardSeq
FROM tmp_cardevent
ORDER BY CardNo, CardEventDate

SELECT
   t1.Personid,
   t1.cardeventdate,
   t1.cardeventtime,
   t2.Personid,
   t2.cardeventdate,
   t2.cardeventtime
from
   tmp_cardevent t1
   inner join #CardSeq S1 ON t1.CardNo = S.CardNo
   left join #CardSeq S2 ON t1.CardNo = t2.CardNo and t1.Seq - 1 = t2.Seq
   left join tmp_cardevent t2 on t1.cardno = t2.cardno

DROP TABLE #CardSeq