代码之家 › 专栏 › 技术社区 › Scott

选择日期间隔,快速执行,并始终返回带有结果的最新条目

intervals aggregate optimization sql

Scott · 技术社区 · 6 年前

我有一个数据库和一个表,存储了几个账户的账户余额变化,共有三列;

float   balance, #The account balance after the change
Date    date,    #Date that balance change occurred
int     aid      #Account that the balance change occurred on

它包含一年中每天的几个条目,我想每五天检索一次余额。我还希望它在帐户之间分开(即如果两个帐户发生变化) 发生在同一天,但在不同的帐户上,返回两者)。

问题是这个 :有时会有几天(或几周)没有可用数据。发生这种情况时,我希望确保返回最新的条目之前数据集中的“洞”。这是问题的简化版本,实际数据库很大(几GB),大小是我想要返回数据子集的原因。它不能使用特定于平台的方法,因为它需要同时在oracle和mySQL上工作。

我的问题是 :有什么办法吗 ? 我可以编写一个查询来完成这项工作,但我希望有一种神奇的方法来完成它,它不需要大量的嵌套查询和聚合函数。。

3 回复 | 直到 16 年前

Tom H 16 年前

我会使用Andromar的周期表思想,但我会尝试一个稍微不同的最终查询。这假设您的账户余额表有一个关于援助和日期的主键。如果您在相同的日期和时间为同一帐户生成了两个余额,那么您将得到一些重复的行。

SELECT
     P.start_date,
     P.end_date,
     AB1.account_id,
     AB1.balance
FROM
     Periods P
LEFT OUTER JOIN Account_Balances AB1 ON
     AB1.date <= P.end_date
LEFT OUTER JOIN Account_Balances AB2 ON
     AB2.aid = AB1.aid AND
     AB2.date > AB1.date AND
     AB2.date <= P.end_date
WHERE
     AB2.aid IS NULL

如果该帐户在给定时间段之前或期间没有行,您将无法为其返回行。

digiguru 16 年前

您可以通过创建一个期间表以一种相对简单的方式来实现这一点,您可以将该表与accounts表结合起来,在每个期间为每个帐户创建一行。

create table #balance (
    id int identity,
    balance float,
    date datetime,
    aid int
)

create table #period (
    id int identity,
    startdt datetime,
    enddt datetime
)

输入一些测试数据:

insert into #yourtable (balance, date, aid) values (4,'2009-01-01',1)
insert into #yourtable (balance, date, aid) values (5,'2009-01-10',1)
insert into #yourtable (balance, date, aid) values (6,'2009-01-10',1)
insert into #yourtable (balance, date, aid) values (7,'2009-01-16',1)
insert into #yourtable (balance, date, aid) values (2,'2009-01-01',2)
insert into #yourtable (balance, date, aid) values (3,'2009-01-10',2)
insert into #yourtable (balance, date, aid) values (4,'2009-01-10',2)
insert into #yourtable (balance, date, aid) values (5,'2009-01-16',2)

insert into #period (startdt, enddt) values ('2009-01-01','2009-01-06')
insert into #period (startdt, enddt) values ('2009-01-06','2009-01-11')
insert into #period (startdt, enddt) values ('2009-01-11','2009-01-16')
insert into #period (startdt, enddt) values ('2009-01-16','2009-01-21')

现在让我们查询所有时段:

from #period p

在期末前为每个余额添加一行:

left join #balance b1 on 
    b1.date <= p.enddt

搜索从第一次加入到期末的余额之间的余额:

left join #balance b2 on 
    b2.aid = b1.aid
    and b1.id < b2.id
    and b2.date <= p.enddt

where
    b2.aid is null

select 
    b1.aid
,   p.startdt
,   b1.balance
from #period p
left join #balance b1 on 
    b1.date <= p.enddt
left join #balance b2 on 
    b2.aid = b1.aid
    and b1.id < b2.id
    and b2.date <= p.enddt
where
    b2.aid is null
order by b1.aid, p.startdt

注意:查询假定日期较晚的余额始终具有较大的id。如果您不必使用完全相同的结束日期的余额,则可以将“b1.id<b2.id”替换为“b1.date<b2.date”。

mikelikespie 16 年前

如果您等待postgresql 8.4,您可能可以使用窗口函数

http://www.postgresql.org/docs/8.4/static/tutorial-window.html

http://www.postgresql.org/docs/8.4/static/functions-window.html