我有一个表,它每28天记录一次架构中所有表的计数。该表包含以下列:"TABLE_NAME"VARCHAR2(100字节),"RECORD_COUNT"编号,"创建"日期
我想假脱机一个日志文件,其中包含以下数据:
- 表名称
- 当前记录计数
- 28天前的记录计数
- 2和3之间的差异
谢谢你的帮助。
像这样的东西应该可以做到
SELECT table_name,
record_count,
prior_record_count,
record_count - prior_record_count diff
FROM (SELECT table_name,
record_count,
lag(record_count)
over (partition by table_name
order by created) prior_record_count,
rank()
over (partition by table_name
order by created) rnk
FROM <<name of table>>)
WHERE rnk = 1
Oracle的LAG函数应该能满足您的需要。这是一个分析函数,用于返回前一行的数据。以下内容应该有效:
select
table_name,
record_count,
prev_record_count,
record_count - nvl(previous_record_count, 0) as difference
from (
select
table_name,
record_count,
lag(record_count) over (order by created) as prev_record_count
from
log_table
);