查找错误记录(SQL)

谢谢了，下面是postgresql 的SQL写法。

-- p1 昨天的日期
-- p2 当月统计数据范围内，上一条月累加值
-- p3 当年统计数据范围内，上一条年累加值
-- p4 当月统计数据范围内，第一条月累加值
-- p5 当月统计数据范围内，第一条日值
-- p6 当年统计数据范围内，第一条年累加值
-- p7 当年统计数据范围内，第一条日值

select * from
(

select v_d, data, month, year, 
lag(v_d, 1) over (order by v_d) p1, 
lag(month, 1) over (partition by extract(month from v_d) order by v_d) p2,
lag(year, 1) over (partition by extract(year from v_d) order by v_d) p3,
first_value(data) over(partition by extract(month from v_d) order by v_d) p4, 
first_value(month) over(partition by extract(month from v_d) order by v_d) p5,
first_value(data) over(partition by extract(year from v_d) order by v_d) p6, 
first_value(year) over(partition by extract(year from v_d) order by v_d) p7 
from tbl)

t
where v_d - p1 <>1
or
p4 <> p5
or
p6 <> p7
or
p2+data <> month
or
p3+data <> year;

不知道这种写法对不对。在这个简单例子里可以得到期望结果。

sum(data) over (partition by extract(month from v_d) order by v_d) as 计算月累,
如果记录里年不都是一年，结果就不对。
希望大拿帮助一下，解释partition by extract(month from v_d)，谢谢了。
select * from
(

select v_d, data, 
month,
sum(data) over (partition by extract(month from v_d) order by v_d) as 计算月累,
year,
sum(data) over (partition by extract(year from v_d) order by v_d) as 计算年累,
lag(v_d, 1) over (order by v_d) p1
from tbl

) t
where v_d - p1 <>1
or
month <> 计算月累
or
year <> 计算年累

--我的理解昨天应该是指上一个日期，不一定连续
--以下是PostgreSQL数据库的实现（仅供参考，没做实际数据测试）
SELECT *
FROM (SELECT *,

           lead(月累, 1, 0) over w AS last_month,
           lead(年累, 1, 0) over w AS last_year
      FROM t1 window w AS(PARTITION BY to_char(日期,'yyyy') ORDER BY 日期 DESC)) t

WHERE t.last_month + 数据 != t.月累

     OR t.last_year + 数据 != t.年累;

假设你用的是PostgreSQL数据库。
问题可能出在两处
1. 缺失日期
2. 数据错误
月起始值错误
年起始值错误
月累加值错误
年累加值错误

pipeline=# create table tbl(v_d date, data numeric, month numeric, year numeric);
CREATE TABLE
pipeline=# insert into tbl values ('2015-01-01', 100, 100, 100);
INSERT 0 1
pipeline=# insert into tbl values ('2015-01-02', 200, 300, 300);
INSERT 0 1
pipeline=# insert into tbl values ('2015-01-04', 200, 500, 500);
INSERT 0 1

pipeline=# select v_d, data, month, year, 
lag(v_d, 1) over (order by v_d) p1, 
lag(month, 1) over (partition by month(v_d) order by v_d) p2,
lag(year, 1) over (partition by year(v_d) order by v_d) p3,
first_value(data) over(partition by month(v_d) order by v_d) p4, 
first_value(month) over(partition by month(v_d) order by v_d) p5,
first_value(data) over(partition by year(v_d) order by v_d) p6, 
first_value(year) over(partition by year(v_d) order by v_d) p7 
from tbl;
    v_d     | data | month | year |     p1     | p2  | p3  | p4  | p5  | p6  | p7  
------------+------+-------+------+------------+-----+-----+-----+-----+-----+-----
 2015-01-01 |  100 |   100 |  100 |            |     |     | 100 | 100 | 100 | 100
 2015-01-02 |  200 |   300 |  300 | 2015-01-01 | 100 | 100 | 100 | 100 | 100 | 100
 2015-01-04 |  200 |   500 |  500 | 2015-01-02 | 300 | 300 | 100 | 100 | 100 | 100
(3 rows)
解释
p1  昨天的日期
p2  当月统计数据范围内，上一条月累加值
p3  当年统计数据范围内，上一条年累加值
p4  当月统计数据范围内，第一条月累加值
p5  当月统计数据范围内，第一条日值
p6  当年统计数据范围内，第一条年累加值
p7  当年统计数据范围内，第一条日值

错误判断标准
1. 缺失日期
v_d - p1 <>1
2. 数据错误
月起始值错误
p4 <> p5
年起始值错误
p6 <> p7
月累加值错误
p2+data <> month
年累加值错误
p3+data <> year

最终SQL
pipeline=# select * from 
(
select v_d, data, month, year, 
lag(v_d, 1) over (order by v_d) p1, 
lag(month, 1) over (partition by month(v_d) order by v_d) p2,
lag(year, 1) over (partition by year(v_d) order by v_d) p3,
first_value(data) over(partition by month(v_d) order by v_d) p4, 
first_value(month) over(partition by month(v_d) order by v_d) p5,
first_value(data) over(partition by year(v_d) order by v_d) p6, 
first_value(year) over(partition by year(v_d) order by v_d) p7 
from tbl)
t
where v_d - p1 <>1
or
p4 <> p5
or
p6 <> p7
or
p2+data <> month
or
p3+data <> year;
    v_d     | data | month | year |     p1     | p2  | p3  | p4  | p5  | p6  | p7  
------------+------+-------+------+------------+-----+-----+-----+-----+-----+-----
 2015-01-04 |  200 |   500 |  500 | 2015-01-02 | 300 | 300 | 100 | 100 | 100 | 100
(1 row)