我有一个表,结构如下。
create table t1 (
日期 date,
数据 double precision,
月累 double precision,
年累 double precision
)
表中的月累,和年累,是通过当日数据加昨日月累,年累所得。
当表中数据积累到一定量时,需要找出错误的,月累和年累。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)
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。